0

I am hitting the error

"Resources exceeded during query execution: Table metadata used for the query is too large"

when executing this query:

SELECT
  EXACT_COUNT_DISTINCT(a.id)
FROM (
  SELECT
    id
  FROM (TABLE_DATE_RANGE([*****],
        TIMESTAMP('2019-4-1'),
        TIMESTAMP('2019-4-22')))
  GROUP BY
    id) AS a
JOIN (
  SELECT
    id
  FROM (TABLE_DATE_RANGE([*****],
        TIMESTAMP('2017-1-1'),
        TIMESTAMP('2018-12-31')))
  GROUP BY
    id) AS b
ON
  a.id = b.id

If I change TIMESTAMP('2017-1-1'),TIMESTAMP('2018-12-31') to TIMESTAMP('2018-1-1'),TIMESTAMP('2018-12-31') , it will work. Anything i can do to fix it without change the TIMESTAMP ?

Asad Ali Choudhry
  • 4,985
  • 4
  • 31
  • 36
Evan.Chen
  • 69
  • 1
  • 6

1 Answers1

1

First, why are you using legacy SQL and not standard SQL?

Second, you are aggregating each of the subqueries by id, so there are no duplicates. You don't need to count distinct ids in the outer query COUNT(*) does the same thing. This might fix the problem.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Why legacy SQL can't work, but standard SQL can? Different limits standard? – Evan.Chen Jun 13 '19 at 11:22
  • @Evan.Chen . . . Legacy SQL is just what its name implies -- an old version of SQL. Google has gone through the effort of creating a much more standard-compatible interface with new functionality provided in that interface. You should use it. – Gordon Linoff Jun 13 '19 at 12:17