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:
ERROR: During read: googleapi: Error 403: Response too large to return. Consider setting allowLargeResults to true in your job configuration. For more details, see https://cloud.google.com/bigquery/troubleshooting-errors retrieving query results, dump stack and retry 1 time>
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
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 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
Type: | Problem Note |
Priority: | high |
Date Modified: | 2021-06-30 11:50:53 |
Date Created: | 2021-03-21 22:40:03 |