CODEWITHSUNDEEP
google-bigquery
binnisb
binnisb

Reputation: 175

Wildcards on subqueries

I am trying to test some logic, using dummy table and data in BigQuery. I have been successful doing this using 

WITH dummy AS (SELECT 1 AS a)
SELECT a FROM dummy

And by using more complex WITH statement, I can try out some different logic on this dummy table.

Now I am hitting a wall, since I have some data in different partitions that I want to test. Using Standard SQL I can do something like:

SELECT a
FROM
    `dummy_*`
WHERE
    _TABLE_SUFFIX BETWEEN '20161001' AND '20161003'

However I would really like to be able to do the same for the subqueries in the WITH clause:

WITH 
    dummy_20161001 AS (SELECT 1 AS a UNION ALL SELECT 4 AS a),
    dummy_20161002 AS (SELECT 7 AS a UNION ALL SELECT 10 AS a)
SELECT a FROM dummy_*
WHERE
    _TABLE_SUFFIX BETWEEN '20161001' AND '20161003'

Is there something I can replace the _TABLE_SUFFIX with to do this, or is there any other way to create this dummy representation of table partitions?

Upvotes: 1

Views: 223

Answers (2)

Pavan Edara
Pavan Edara

Reputation: 2315

The _TABLE_SUFFIX and (if you are using partitioned tables -- the _PARTITIONTIME) pseudo column work only on BigQuery managed tables.

Would it be possible for you to write your sample data into multiple tables or a single partitioned table (using SELECT query with destination_table)? After that you should be able to use the pseudo columns.

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Looks to me that below can satisfy your testing needs. At least this is an option :o) 

WITH 
  dummy_20161001 AS (SELECT 1 AS a UNION ALL SELECT 2 AS a),
  dummy_20161002 AS (SELECT 3 AS a UNION ALL SELECT 4 AS a),
  dummy_20161003 AS (SELECT 5 AS a UNION ALL SELECT 6 AS a),
  dummy_20161004 AS (SELECT 7 AS a UNION ALL SELECT 8 AS a),
  `dummy_*` as (
    SELECT *, '20161001' as _TABLE_SUFFIX FROM dummy_20161001 UNION ALL
    SELECT *, '20161002' as _TABLE_SUFFIX FROM dummy_20161002 UNION ALL
    SELECT *, '20161003' as _TABLE_SUFFIX FROM dummy_20161003 UNION ALL
    SELECT *, '20161004' as _TABLE_SUFFIX FROM dummy_20161004
  )
SELECT a 
FROM `dummy_*`
WHERE
    _TABLE_SUFFIX BETWEEN '20161001' AND '20161003'

Upvotes: 1

Related Questions