Translation table_date_range to table_suffix does not work because response is too large

Question

I have the following query in BigQuery Legacy SQL. This query runs without problems so far.

#legacySQL
SELECT
  Var1 AS Var1,
  Var2 AS Var2,
  Var3 AS Var3,
  Var4 AS Var4,

FROM
TABLE_DATE_RANGE([xxx.yyy_],   DATE_ADD(CURRENT_TIMESTAMP(), -33, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))

After we want to move all our queries step by step to StandardSQL, the new query has been written in StandardSQL as follows.

#standardSQL
SELECT
  Var1 AS Var1,
  Var2 AS Var2,
  Var3 AS Var3,
  Var4 AS Var4,

FROM
`xxx.yyy_20*`

WHERE

parse_date('%y%m%d', _table_suffix) between DATE_ADD(CURRENT_DATE(), INTERVAL -33 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)

However, this query does not run anymore, whereas the variant with LegacySQL works fine. I get the following error message: Error: Response too large to return. Consider setting allowLargeResults to true in your job configuration.

Is the variant with _table_suffix so much less performant? Or is there an alternative? I use the Table_Date_Range to query about 400 tables.

I would like to save the results as a view and not have to write them to a table via "AllowLargeResults".

Thanks a lot!

Elliott Brossard Elliott Brossard · Accepted Answer · 2019-06-06T14:31:33

You shouldn't have a problem creating a view. When you query the view, though, you will either need to select specific dates or else write the results to a table.

Translation table_date_range to table_suffix does not work because response is too large

1 Answers