CODEWITHSUNDEEP
google-bigquery
Pooja Aggarwal
Pooja Aggarwal

Reputation: 13

How to query on multiple date using TABLE_QUERY() in Big Query

I want to query on these tables using table_query(). How can I query on all the tables with date range from 2017-03-22 to 2017-03-24.
Format of my BQ tables is (auctions_other_YYYYMMDDHHM)
BQ tables are listed below:

1.auctions_other_20170322104
2.auctions_other_20170322105
3.auctions_other_20170323206
4.auctions_other_20170323207
5.auctions_other_20170324108
6.auctions_other_20170324209

I am using query:

SELECT  * FROM
TABLE_QUERY(chocolate_raw, "table_id contains 'auctions_others_' and
table_id contains '20170322104' and  table_id contains '20170324209'
")
WHERE
  id='f3667fd6-8329-4124-a4a2-737e637a5e7d'

Getting Error:

FROM clause with table wildcards matches no table

Upvotes: 1

Views: 2129

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

You should definitelly consider using BigQuery Standard SQL as Graham suggesting

If for some reason you still are bound to BigQuery Legacy SQL - try below 

#legacySQL
SELECT * 
FROM TABLE_QUERY(chocolate_raw, 
  "table_id CONTAINS 'auctions_others_' 
  AND SUBSTR(table_id, 1 + LENGTH('auctions_others_'), 8) 
  BETWEEN '20170322' AND '20170324'")
WHERE id='f3667fd6-8329-4124-a4a2-737e637a5e7d'

Similar version in BigQuery Standard SQL will looks like below 

#standardSQL
SELECT * 
FROM `chocolate_raw.auctions_others_*`
WHERE SUBSTR(_TABLE_SUFFIX, 1, 8) BETWEEN '20170322' AND '20170324'    
AND id='f3667fd6-8329-4124-a4a2-737e637a5e7d'

Upvotes: 1

Graham Polley
Graham Polley

Reputation: 14791

First of all use standard SQL (it's easier/neater). Because you have the hour and minute suffixed, it makes it a little trickier, but something like this should work:

#standardSQL
SELECT
  *
FROM
  `<dataset>.auctions_other_*`
WHERE
  _TABLE_SUFFIX LIKE '20170322%'
  OR _TABLE_SUFFIX LIKE '20170323%'
  OR _TABLE_SUFFIX LIKE '20170324%'

Upvotes: 2

Related Questions