Troubleshooting BigQuery Load Errors using the error stream

Problem 🔍

While loading data into Bigquery you might have stumbled on one of the below error messages

• CSV table encountered too many errors, giving up
• have double quote “” in your CSV file
• Could not parse ‘Field1’ as double for field Field1
• Data between close double quote (“) and field separator.
• not UTF-8 encoded
• schema not matched

Lets see how we can troubleshoot the error

Solution 💡

All operations in Bigquery triggers a JOB internally. Copy the JOBID of the failed job and you can check the error stream by running the following command in the terminal:

bq --format=prettyjson show -j <JobID>

It returns a JSON with more details. ex:

"message": "Error while reading data, error message: 
Could not parse '76.66666666666667' as int for field Score_Percentage 
(position 46) starting at location 817164"

Now we know exactly that there is a numeric value in the csv file that we are trying to load to an integer field. Remove the bad data from the csv file and retry the load command. It should fix the error

Ryan-Dallas

posted on 06 Feb 19