Usage Note 66712: A generated list of BigQuery tables might be empty when the BigQuery dataset (schema) contains an underscore in its name
 |  |  |  |  |
Trying to generate a list of BigQuery tables that exist in a BigQuery dataset (schema) might result in an empty list even when tables exist in the BigQuery dataset. This behavior is due to a mid-August 2020 change made by Google. This issue occurs when you use one of the following methods (not exclusive):
- expand a previously defined libref
- run PROC DATASETS
- run PROC CONTENTS using _all_
SAS uses INFORMATION_SCHEMA queries to generate the list of tables when the BigQuery dataset name specified via the SCHEMA= option contains a search pattern (% or _). The INFORMATION_SCHEMA views are now being stored in the region where the dataset is stored and not in a "unified" location as previously. When a region is not specified, the unqualified INFORMATION_SCHEMA queries default to returning metadata for the US location. If the BigQuery dataset is not located in the US region, the query returns no table names, which results in the empty list.
To work around this Google change, add the LOCATION= option to your LIBNAME statement and set it to the region where the BigQuery dataset resides. There currently is no workaround for SAS® Viya® because the LOCATION= option is not supported.
Running queries against a table that exists in the dataset is not affected and should work as it did previously.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Data Connector to Google BigQuery | Linux for x64 | V.03.05 | 2020.1.1 | Viya | Viya |
| SAS System | SAS/ACCESS Interface to Google BigQuery | Linux for x64 | 9.41 | 9.4 TS1M6 | ||
| SAS System | SAS/ACCESS Interface to Google BigQuery (on SAS Viya) | Linux for x64 | V.03.05 | 2020.1.1 | Viya | Viya |