user3365840 user3365840 - 1 month ago 9
SQL Question

Table Functions and FLATTEN in BigQuery

Is it possible to use

FLATTEN
and
TABLE_QUERY
function together?

It looks like the
TABLE_QUERY
returns only the table name and
FLATTEN
requires the dataset as well.

FROM Clause:

FROM FLATTEN(TABLE_QUERY(nbr_pcrf, 'table_id CONTAINS "dump_"'), quotas) d


Error:


Query Failed
Error: Table name cannot be resolved: dataset name is missing.
Job ID: nbr-data-storage:job_44jU_diWnh4tk27UxDxFP-I5Rbg

Answer

This is actually a little bit misleading: what is happening is that FLATTEN() with anything that isn't just a table name needs an extra set of parentheses to distinguish the field you're flattening by from a table. In other words, if you do

SELECT ... FROM FLATTEN(TABLE_QUERY(...), foo)

the foo field gets interpreted as a unioned table name (as in SELECT * from bar,foo).

The workaround for this issue is simple: Add another set of parentheses. That is:

SELECT ... FROM 
FLATTEN((TABLE_QUERY(nbr_pcrf, 'table_id CONTAINS "dump_"')), quotas)
Comments