CODEWITHSUNDEEP
google-cloud-sqlgoogle-bigquery
Haipeng Su
Haipeng Su

Reputation: 2541

Standard SQL not raising exceptions for non-existing tables when using table wildcard function

When querying tables with date partition suffix, we have the wildcard function for both Legacy SQL and Standard SQL. However, I found that they are not working exactly in the same way, and Standard SQL will not raise the exception even though the table for a particular date doesn't exist.

For examples, I have a set of tables called myTable_YYYYMMDD, but missing table 'myTable_20170601', then,

In Legacy SQL, 

 Select *
 From table_date_range([project_id:datasetName.myTable_],
 timestamp('20170601'), timestamp('20170601'))

It will give me [ Errors: query: FROM clause with table wildcards matches no table (error code: invalidQuery) ]

In Standard Query,

Select *
From `project_id.datasetName.myTable_*`
where _table_suffix between '20170601' and '20170601'

And this will give me an empty result instead of an exception even though the table is not available.

In this case, If I am running an automated job to query again the tables and empty table is also one of the expected results, then I wouldn't know the source table doesn't exist, which affect the real result.

Is that possible to have the exception raised in Standard SQL too?

Thank you in advance.

Add a feature request on BigQuery Issue Tracker

https://issuetracker.google.com/issues/62264827

Upvotes: 1

Views: 607

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

One option is that if you know a particular table should exist, you can query just that table or take a union with it (if you are querying multiple dates). For example:

SELECT *
FROM `project_id.datasetName.myTable_20170601`;

Or, for multiple dates:

SELECT *
FROM `project_id.datasetName.myTable_20170601` UNION ALL
(SELECT *
 FROM `project_id.datasetName.myTable_*`
 WHERE _TABLE_SUFFIX BETWEEN '20170516' AND '201706531');

In either case, the query will fail if the table for June 1 does not exist. Alternatively, you can trigger a failure if the number of _TABLE_SUFFIX is zero through division:

WITH T AS (
  SELECT *, _TABLE_SUFFIX AS suffix
  FROM `project_id.datasetName.myTable_*`
  WHERE _TABLE_SUFFIX BETWEEN '20170601' AND '20170601'
)
SELECT
  * EXCEPT(suffix),
  1 / (SELECT COUNT(DISTINCT suffix) FROM T)
FROM T;

Edit: If anyone else would like to follow Haipeng's feature request, you can find it in the issue tracker.

Upvotes: 3

Related Questions