Error: Response too large to return. Google Big Query Job is failing

[rohitk1609]

Hi Techies,

I am trying to extract data by using Google Big Query connector but  select statement is failing for one particular instance.

Connector details:

https://help.qlik.com/en-US/connectors/Subsystems/ODBC_connector_help/Content/Connectors_ODBC/Google...

This is the connection string:

CUSTOM CONNECT TO "provider=QvOdbcConnectorPackage.exe;driver=gbq;ClientId=Ol1e;ClientSecret=EVa;RefreshToken=1//05X3B28uKk0vjg;OAuthMechanism=1;Catalog=traf-qk-;Min_TLS=1.2;UseProxy=false;SQLDialect=1;EnableSQLPrepareMetadataWithLegacySQL=false;UseLegacySQLMetadata=0;AllowLargeResultSets=true;AllowLargeResults=1;LargeResultDataSetId=_odbc_temp_tables;LargeResultsTempTableExpirationTime=3600000;RowsFetchedPerBlock=16384;DefaultStringColumnLength=65535;UseSystemTrustStore=1;QueryTimeout=30;"

It is returning the error mentioned in below snapshot:

I have enabled AllowLargeResults  but still query is failing.

I have raised this issue to Google support team and here is what they said:

I reviewed the BigQuery logs for the failed jobs and allowLargeResults is not shown. Another alternative to verify if it was included is the command 'bq show', in that case the parameter "allowLargeResults": true should be listed; however, I understand that it is not listed even with 'bq show'. This makes me suspect that the tool/application is not including it.

After checking the connection string, I noticed the option "LargeResultsTempTableExpirationTime" and it makes me think that temporary tables are being used since there is not a destination table specified. According to BigQuery documentation, it is needed to indicate a destination table to enable allowLargeResults, see other limitations in this link [1].

I investigated a bit more about the tool Qlik on my best effort since it is not in the scope of Google Cloud Platform support, and I found this guide [2]. In page 5, I can see the Destination table options and 'Allow Large Results' is disabled by default. The Step 9 says "You must specify a destination table if you intend select Allow Large Results". Therefore, it seems that the connection string needs to include a destination table.

I couldn't find the option to set destination table path from temp to permanent in connector. Can you please help me to fix this issue.

Thanks in advance

Rohit

[DF_DB]

Did you ever get this figured out?  I'm having the same issue.