I'm trying to use google bigquery to select data from tables with a date wildcard. I'd like to be able to use the TABLE_DATE_RANGE function, but I need to query over a large date range (> 1 year). Right now my query works for a year's worth of data, but anything over I get:
Error: TABLE_DATE_RANGE error: too many days
#Fails
SELECT
system_id, sample_date, e_pv_array
FROM
(TABLE_DATE_RANGE(workspace.intervaldata,
TIMESTAMP('2009-03-01'),
TIMESTAMP('2010-03-04')))
WHERE
system_id = 20006
and e_pv_array is not null;
#Works
SELECT
system_id, sample_date, e_pv_array
FROM
(TABLE_DATE_RANGE(workspace.intervaldata,
TIMESTAMP('2009-03-01'),
TIMESTAMP('2010-03-03')))
WHERE
system_id = 20006
and e_pv_array is not null;
Is this just a limitation of bigquery? Or is there a way to use table wildcards with date ranges greater than 1 year?
Notifications Stay organized with collections Save this page to your Developer Profile to get notifications on important updates. Save and categorize content based on your preferences. Wildcard tables enable you to query multiple tables using concise SQL statements.
Union in BigQuery syntaxSELECT column name, column name... FROM table1 UNION (Distinct / All) SELECT column name, column name….. FROM table2; SELECT column name, column name...
BigQuery, like other Google Cloud resources, is organized hierarchically where the Organization node is the root node, the Projects are the children of the Organization, and Datasets are descendants of Projects.
I've experienced problems with TABLE_DATE_RANGE() as well. Here's a possible workaround, assuming your tables are named workspace.intervaldata20090303, etc.:
SELECT
system_id, sample_date, e_pv_array
FROM
TABLE_QUERY(workspace, "integer(regexp_extract(table_id, r'intervaldata([0-9]+)')) BETWEEN 20090301 and 20100304")
WHERE
system_id = 20006
and e_pv_array is not null;
I have a similar scenario (date partitioned tables) and am able to run queries over 2 years' worth of data using TABLE_QUERY() without errors.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With