Google Big Query Error: CSV table encountered too many errors, giving up. Row: 1 errors: 1

Question

I am trying to run a query on a 12 GB csv file loaded in Google big query, I cant run any query on the dataset. I am not sure if the dataset is loaded correctly. It shows as a table in the pane, but it is not present in the job history. Can anyone help on the same.

The dataset was loaded from a google storage bucket which has around 1.2MM records and 3728 variables

Job ID: p-g-us-adv-x-dat-aia-proto-1:bquijob_b951879_1540d02c1a4

Answer 1

Check your schema - it's possible that you forgot to include the schema for one of the columns - that's what happened to me!

Answer 2

job.errors contains detailed errors for the job.

This doesn't appear to be documented anywhere, but you can see it in the source code: https://googlecloudplatform.github.io/google-cloud-python/0.20.0/_modules/google/cloud/bigquery/job.html and ctrl+f for _AsyncJob.

So your wait_for_job code could look like this:

def wait_for_job(job):
    while True:
        job.reload()
        if job.state == 'DONE':
            if job.error_result:
                raise RuntimeError(job.errors)
            return
        time.sleep(1)

Answer 3

To get more info on the errors try this from the CLI:

>bq show -j <jobid>

It prints the status and/or detailed error information.

To list all the jobids: bq ls -j

Answer 4

I had the same issue following the instructions in the GCP docs.

It failed on the second bq load, but not the first.

I found that repeating the job in the BigQuery web interface selecting the ignore unknown values option.

I have not spotted any errors with the data yet, but just getting started looking at it.

Answer 5

Another trick: If you use csv files with a header line and want to load with a defined schema, you need to add option --skip_leading_rows=1 to submit command(example: bq load --skip_leading_rows=1 --source_format=CSV ...).

Without this option, Bigquery will parse your first row(header line) as an data row, may lead to TYPE MISMATCH ERROR (your defined schema of a column is FLOAT, but its column name is STRING, and bq load command parses your column name as a FLOAT value).

Answer 6

I was also getting the same error with no clue of the actual problem.

<https://www.googleapis.com/bigquery/v2/projects/****/jobs/job_******?location=******> <{
reason: invalid message: Error while reading data, error message: JSON table encountered too many errors, giving up. Rows: 115;
errors: 1. Please look into the errors[] collection for more details.  }

Tried bq --format=prettyjson show -j => This also didn't gave any more clue.

I was trying to transfer data from a Database to Big Query using SAP BODS as the ETL Tool. To find the root cause I had to modify the ETL to transfer column by column i.e. I first transferred one column, then added 2nd, and so on. The transfer was successful for initial string columns. But when a FLOAT column came, the transfer gave the same error.

When checking the data I found values as .0345 in the decimal column in database. For values less than 1, 0 was removed before decimal which was causing error during transfer to Big Query.

To rectify, I had to apply to_decimal conversion of BODS.

to_decimal(column_name, '.', ',', 4) 

"," is the thousand separator

"." is the decimal separator

4 specifies the nos allowed after decimal

Note: : I was also transferring records to Google Cloud Storage simultaneously and it was getting successful before conversion also. Also, when I manually used Cloud Storage file to populate the same BigQuery table, it was also working fine.

Answer 7

So it looks like you're querying against a CSV file that hasn't been loaded into BigQuery, it is just being pointed to by a federated table that lives in Google Cloud Storage.

It looks like there were errors in the underlying CSV file:

Too many value in row starting at position:11398444388 in file:gs://syntheticpopulation-storage/Alldatamerged_Allgrps.csv
Too many value in row starting at position:9252859186 in file:gs://syntheticpopulation-storage/Alldatamerged_Allgrps.csv
...

Please let me know if this is enough to diagnose the issue. I believe you can see those messages as warnings on the query job if you look at the query history.

I've filed three bugs internally:

1. Poor grammar in the error message.
2. Error messages stemming from problems in federated tables are not diagnosable because they don't tell you what table has the problem.
3. Error messages from problems in federated tables aren't actionable in the UI, since the information about what went wrong is in the warning stream, which is not displayed.

Answer 8

Seems to be a known bug @google. The already made the fix, but did not push it in production. https://code.google.com/p/google-bigquery/issues/detail?id=621