0

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!

Pat
  • 11
  • 3

1 Answers1

0

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.

Elliott Brossard
  • 32,095
  • 2
  • 67
  • 99
  • Thanks for the quick answer. Yes, you're right, I can save the query itself. If I execute it, however, I don't get any results and get the error message mentioned above instead. So I understand correctly that you can only display much smaller results with StandardSQL than with LegacySQL. Apart from the possibility with "AllowLargeResults"? – Pat Jun 11 '19 at 12:36
  • Yes, you need to save results to a table if they are relatively large. The UI will still show the results of the query. – Elliott Brossard Jun 11 '19 at 13:20