Problem Note 67638: Querying a Google BigQuery database might result in the message "Error: During read: googleapi: Error 403: Response too large to return error..."
 |  |  |  |  |
Google BigQuery has a 10 GB compressed size limit for a query response. The limit is not a specific in terms of the number of rows or columns but rather the overall size of the results. If the limit is reached, the following error is returned:
To work around this limit, BigQuery suggests writing the large query results to a destination table.
Click the Hot Fix tab in this note to access the hot fix for this issue for SAS® 9.4M6 (TS1M6) and SAS® 9.4M7 (TS1M7).
Click the Hot Fix tab in this note for a link to instructions about accessing and applying the software update for SAS® Viya® 3.5.
With this hot fix, SAS has added options to add support for large result sets. The following table shows options that you can use on the LIBNAME statement:
| LIBNAME Option Name | Alias | Values | Default Value | Description |
|---|---|---|---|---|
| ALLOW_LARGE_RESULTS | ALR LARGE_REULTS |
ALWAYS MUST OFF |
OFF |
Option to determine whether to allow large results. If set to ALWAYS, then all query results are written to a stored table, including query results below the limit. If set to MUST, then only queries that fail attempt to write the query results to a stored table. if set to OFF, then it functions with the current behavior and returns an error. |
| LARGE_RESULTS_DATASET | LRD | <A BigQuery Dataset ID> | _sasbq_temp_tables | This option specifies the BigQuery Dataset ID that the result set tables are written to. If not specified, then the default is used. If the dataset or the default does not exist, it is created. You must have Write permission to specify this option. |
| LARGE_RESULTS_EXPIRATION_TIME | LRET | <Time value in milliseconds> | 86,400,000 | This option specified the amount of time until the temporary table is deleted. If not specified, then the default (24 hours) is used. Acceptable values are from 3,600,000 to 9,223,372,036,854,775,807. The value 1 is also acceptable, which is a special case where the table never expires. |
This table shows options that you can use in your CASLIB statement:
| DATA Connector/ CASLIB Option Name | Alias | Values | Default Value | Description |
|---|---|---|---|---|
| ALLOWLARGERESULTS |
ALR LARGERESULTS |
ALWAYS MUST OFF |
OFF |
Option to determine whether to allow large results. If set to ALWAYS, then all query results are written to a stored table, including query results below the limit. If set to MUST, then only queries that fail attempt to write the query results to a stored table. If set to OFF, then it functions with the current behavior and returns an error. |
| LARGERESULTSDATASET | LRD | <A BigQuery Dataset ID> | _sasbq_temp_tables | This option specifies the BigQuery Dataset ID that the result set tables are written to. If not specified, then the default is used. If the dataset or the default does not exist, it is created. You must have Write permissions to specify this option. |
| LARGERESULTSEXPIRATIONTIME | LRET | <Time value in milliseconds> | 86,400,000 | This option specifies the amount of time until the temporary table is deleted. If not specified, then the default (24 hours) will be used. Acceptable values are from 3,600,000 to 9,223,372,036,854,775,807. The value 1 is also acceptable, which is a special case where the table never expires. |
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 | 9.4 TS1M6 | 9.4 TS1M8 |
| SAS System | SAS/ACCESS Interface to Google BigQuery (on SAS Viya) | Linux for x64 | V.03.04 | 2020.1.4 | Viya | Viya |
| SAS System | SAS Data Connector to Google BigQuery | Linux for x64 | V.03.04 | 2020.1.4 | Viya | Viya |