Skip to main content
Announcements
Marching toward a simplified navigation! READ ON

Qlik Google BigQuery Connector: How to setup load from Large Datasets

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
RenuLalwani
Employee
Employee

Qlik Google BigQuery Connector: How to setup load from Large Datasets

Last Update:

Jun 2, 2022 4:01:15 AM

Updated By:

RenuLalwani

Created date:

May 31, 2022 10:02:53 AM

Google BigQuery limits cached results to approximately 10 GB compressed. Queries that return larger results overtake this limit and frequently result in the following error: 

Response too large to return. Consider setting allowLargeResults to true in your job configuration

 

Environment:

  • Qlik Google BigQuery Connector (Qlik Cloud)
  • Qlik Google BigQuery Connector (Qlik Sense Enterprise on Windows August 2021 and later)

 

Resolution

 

To mitigate the "Response too large" error, you will have to set the queries to run with the Destination dataset specified 

  • In Standard SQL, specify a destination table for the query results.
  • In Legacy SQL, specify a destination table and set the allowLargeResults option.

Step by Step guide:

  1. Create a destination dataset under your GBQ project


    image2022-5-30_15-45-24.png

  2. Make sure to set "Table expiration settings" for this dataset, as the temporary tables created by the driver will inherit the settings from the parent dataset.

    image2022-5-30_15-46-57.png

  3. Configure Qlik Google big query connector with the below settings
     
    1. For Standard SQL, just provide the dataset name as shown below

      image2022-5-30_16-4-23.png
    2. For Legacy SQL,  Select Legacy SQL from language Dilect, then enable Allow Large Result Sets checkbox and provide the dataset name as shown below

      image2022-5-30_16-8-41.png

 

Performance considerations: 

As rightly quoted here "Be aware that writing very large result sets to destination tables impacts query performance (I/O)", enabling large datasets settings will impact query performance and might slow down reloads. 

So, to improve performance while working with destination tables strategy, its advisable to enable high throughput API 

image2022-5-30_16-54-15.png

Performance can be further be increased by using the alternate processing method for optimization reload by enabling Bulk Reader checkbox  (as shown in the screenshot above)

 

Related Content:

 

 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Tags (1)
Labels (1)
Version history
Last update:
‎2022-06-02 04:01 AM
Updated by: