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!