CODEWITHSUNDEEP
google-bigquery
this_dkrest
this_dkrest

Reputation: 11

Wildcard with WHERE _TABLE_SUFFIX doesn't filter out tables

I have two tables with similar names and different schemas, say

project.dataset.orders_XXXXX_YYYYMMDD

and

project.dataset.orders_static_XXXXX_YYYYMMDD

WHERE XXXXX is a mix of numbers and letters, YYYYMMDD are dates. Wildcard tables can be used only for tables with similar schemas, so 

SELECT * FROM
`project.dataset.orders_*`
WHERE NOT _TABLE_SUFFIX LIKE 'static.*'

WHERE clause won't limit the tables scanned. How can I deal with filtering out static tables with wildcard?

UPD: as kindly suggested in the answers, I'm refining the problem description

the schema of the wildcard table is retrieved at query parsing...Filtering out the static tables is done at query execution.

The problem occurs at the query parsing while parsing different schemas with the following error: 

Field name field0 does not exist in STRUCT<record1 STRUCT<field1 STRING, field2 STRING, field3 STRING, ...>, record2 STRUCT<field4 FLOAT64, field5 STRING, field6 STRING>, field7 STRING, ...>

The problem can be resolved with REGEXP_MATCH in legacy SQL. It's only strange not being able to solve it in standard SQL.

Upvotes: 0

Views: 1054

Answers (2)

rsantiago
rsantiago

Reputation: 2099

As the _TABLE_SUFFIX is a string containing the value that matched the prefix, another option would be:

SELECT * 
FROM `project.dataset.orders*`
WHERE NOT _TABLE_SUFFIX LIKE '_static%'

Here more examples to filter using _TABLE_SUFFIX

UPDATE:

In LegacySQL the function REGEXP_MATCH can be used to retrieve the tables with a regular expression. I have tested and it seems that can work for your use case since the schemas were not evaluated in my testings:

SELECT * FROM TABLE_QUERY([project:dataset],'REGEXP_MATCH(table_id, r"orders_\d{5}_*")')

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

How can I deal with filtering out static tables with wildcard?

SELECT * FROM
`project.dataset.orders_*`
WHERE NOT _TABLE_SUFFIX LIKE 'static%'

Upvotes: 0

Related Questions