Solved: The query is too large. The maximum standard SQL q... - Page 2

vigneswar17 · 01-16-2024 09:04 PM

Hi Folks,

I have hit a problem and would like to know if you have any suggestions to get around it. Primarily if I can fix it without trying alternate methods of code changes like insertAll or a csv file that is uploaded into GCS or directly into table.

I’m hitting an error on INSERT sql statement from Java application doing a call to Bigquery.query(sqlStatement)

where the sqlStatement has something like :

INSERT INTO project_id.dataset_id.ts_image_t (SMRY_KEY,field1…) VALUES (….),(….),(….)

My Insert statement does have multiple rows of insert values.

The length of the above string is 1023722 characters.

The error returned by BigQuery is:

GoogleJsonResponseException: 400 Bad Request

POST https://www.googleapis.com/bigquery/v2/projects/project_id/queries

{

"code": 400,

"errors": [

{

"domain": "global",

"message": "The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters.",

"reason": "invalid"

}

],

"message": "The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters.",

"status": "INVALID_ARGUMENT"

}

BigQueryException: The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters.

com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc:114

com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc:728

com.google.cloud.bigquery.BigQueryImpl$35:1349

com.google.cloud.bigquery.BigQueryImpl$35:1346

Can anyone guide on how to implement parameterized query in my insert statement to fix this error? Or any other alternate approach to fix this error are much appreciated.

Thanks,

Vigneswar Jeyaraj

ms4446

You're encountering an error in BigQuery because your SQL INSERT query string exceeds the 1MB (1,024 KB) size limit for standard SQL queries in BigQuery. To address this, consider the following approaches:

Batch Inserts:
- Instead of using a single large INSERT statement, break down your data insertion into multiple smaller INSERT statements. Each of these should have a query length under 1MB. Execute these smaller inserts sequentially. This method involves creating separate SQL statements for each batch of data.
Load Data from File (Recommended):
- For large datasets, it's more efficient to load data from a file. First, export your data to a CSV or JSON (newline-delimited) file and upload it to Google Cloud Storage (GCS).
- Use the bq load command-line tool or the BigQuery API to load the data from the file into your BigQuery table. This method bypasses the SQL query size limit and is generally more efficient for large data volumes.
Data Streaming (For Large/Real-time Data):
- If you're dealing with very large or real-time data streams, consider using the BigQuery streaming API.
- Utilize the tabledata.insertAll method to stream data directly into your BigQuery table. This is ideal for real-time updates or handling data that doesn't easily fit in a single query or file.

Important Considerations:

Query Size Limits: Keep in mind the query size limits in BigQuery. Direct insert queries have a smaller limit compared to load jobs.
Efficiency: Loading data from a file in GCS is often the most efficient method, especially for large datasets.
Real-time Data: For real-time data insertion, streaming is the preferred approach, though it may incur higher costs compared to batch loading from a file.

While parameterized queries are useful for SQL injection prevention and data type management, they don't reduce the size of the query itself. For your case, breaking down the insert operation into smaller batches, loading data from a file, or using data streaming are more effective solutions to stay within BigQuery's query size limits.

View solution in original post