CODEWITHSUNDEEP
google-bigquery
N.F.
N.F.

Reputation: 4184

Google BigQuery: TABLE_QUERY AND TABLE_DATE_RANGE

I have Big Query tables like below, and like to issue a query to the tables marked <=.

prefix_AAAAAAA_20170320
prefix_AAAAAAA_20170321
prefix_AAAAAAA_20170322 <=
prefix_AAAAAAA_20170323 <=
prefix_AAAAAAA_20170324 <=
prefix_AAAAAAA_20170325
prefix_BBBBBBB_20170320
prefix_BBBBBBB_20170321
prefix_BBBBBBB_20170322 <=
prefix_BBBBBBB_20170323 <=
prefix_BBBBBBB_20170324 <=
prefix_BBBBBBB_20170325
prefix_CCCCCCC_20170320
prefix_CCCCCCC_20170321
prefix_CCCCCCC_20170322
prefix_CCCCCCC_20170323
prefix_CCCCCCC_20170324
prefix_CCCCCCC_20170325

I made a query as this

SELECT * FROM 
  (TABLE_QUERY(mydataset, 
    'table_id CONTAINS "prefix" AND 
    (table_id CONTAINS "AAAAAA" OR table_id CONTAINS "BBBBBB")' )
   AND
   TABLE_DATE_RANGE(mydataset.prefix, TIMESTAMP('2017-03-22'), TIMESTAMP('2017-03-24')))

I got this error.

Error: Encountered " "AND" "AND "" at line 5, column 4. Was expecting: ")" ...

Does anybody has ideas?

Upvotes: 1

Views: 690

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

You cannot mix TABLE_QUERY and TABLE_DATE_RANGE for exactly same FROM!

Try something like below 

#legacySQL
SELECT * 
FROM (TABLE_QUERY(mydataset, 'REGEXP_MATCH(table_id, "prefix_[AB]{7}_2017032[234]")'))

Consider Migrating to BigQuery Standard SQL

In this case you can Query Multiple Tables Using a Wildcard Table

See How to Migrate from TABLE_QUERY() to _TABLE_SUFFIX

I think, in this case your query can look like 

#standardSQL
SELECT * 
FROM `mydataset.prefix_*`
WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, '[AB]{7}_2017032[234]')

I can not migrate to Standard SQL because ...
If I would like to search for example between 2017-03-29 and 2017-04-02, do you have any smart SQL

Try below version 

#legacySQL
SELECT * 
FROM (TABLE_QUERY(mydataset, 
    'REGEXP_MATCH(table_id, r"prefix_[AB]{7}_(\d){8}") AND 
    RIGHT(table_id, 8) BETWEEN "20170329" AND "20170402"'))

Of course yo can adjust above to use whatever exactly logic yo need to apply!

Upvotes: 1

Related Questions