Problem Note 68634: Trying to query a Google BigQuery view that is based on a partitioned table with WHERE clause being required might generate an error
 |  |  |  |  |
Trying to query a Google BigQuery view might generate an error when the following is true:
- The view is based on a BigQuery table.
- The table is partitioned based on column(s) in the table.
- The view is defined without a WHERE clause for the partitioning variable(s).
- The table is defined with a partitioning WHERE clause being required.
- The query includes the required partitioning WHERE clause.
The error might look similar to the following:
[DIAG] SQLState:42000 NativeError:-1 "Error: googleapi: Error 400: Cannot query over table 'project.dataset.table' without a filter over column(s) 'variable' that can be used for partition elimination, invalidQuery"
The issue occurs whether or not the query includes a WHERE clause for the partitioning variable. In order to gather the metadata about the view, a query is submitted to the database that does not include the WHERE clause.
Without the WHERE clause, the error is expected.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to Google BigQuery | Linux for x64 | 9.4 | 9.4 TS1M6 | ||
| SAS System | SAS/ACCESS Interface to Google BigQuery (on SAS Viya) | Linux for x64 | V.03.04 | Stable 2023.04 | Viya platform | |
A fix for this issue for SAS/ACCESS Interface to Google BigQuery 9.43 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/M7Z.html#68634A fix for this issue for SAS/ACCESS Interface to Google BigQuery 9.42 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/K2L.html#68634| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2024-08-08 09:50:11 |
| Date Created: | 2021-11-23 09:04:26 |