Bigquery CSV load fails with Python API but successful using GUI

Question

I am using Python to create a CSV file and load it into the BigQuery. However, it fails to load with the following error:

Error while reading data, error message: CSV table references column position 4, but line starting at position:4137 contains only 2 columns.

Configuration I'm using is as follows:

job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.CSV
job_config.ignore_unknown_values = False
job_config.autodetect = False
job_config.field_delimiter = '|'
job_config.write_disposition = bigquery.WriteDisposition.WRITE_TRUNCATE
job_config.max_bad_records = 0
job_config.skip_leading_rows = 0

Sample record looks like as:

2c|Blackhawk gone wild|That's Just About Right|Good Times & Great Country;2019-01-16 14:22:07

CSV has total 114 records. When I've set job_config.allow_quoted_newlines = True. It loads only 60 lines or number of lines less than 114.

Creating CSV file as follows:

f.write((str(callsign).split('_')[0]).lower().encode('utf-8') + '|' + artist.encode('utf-8') + '|' + song_title.encode('utf-8') + '|' + show_title.encode('utf-8') + '|' + str(time_bq).encode('utf-8') + '\n')

def bq_load():
credential_path = "Key.json"
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = credential_path

bigquery_client = bigquery.Client()
dataset_ref = bigquery_client.dataset('POC')
table_ref = dataset_ref.table('data_poc')
job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.CSV
job_config.ignore_unknown_values = False
job_config.autodetect = False
job_config.field_delimiter = '|'
job_config.allow_quoted_newlines = True
job_config.allow_jagged_rows = True
job_config.write_disposition = bigquery.WriteDisposition.WRITE_TRUNCATE
job_config.max_bad_records = 0
job_config.skip_leading_rows = 0

with open('current_song.csv', 'rb') as source_file:
    job = bigquery_client.load_table_from_file(
        source_file,
        table_ref,
        location='US',  # Must match the destination dataset location.
        job_config=job_config)  # API request

try:
    print job.job_id
    job.result()  # Waits for table load to complete.
    return 'Success'
except Exception as ex:
    logger.error(
        "The following exception occurred in the table load of job - {} {} ".format(ex, format(job.job_id)))
    return 'Failure'

Need to load the full contents of CSV into bigquery. Any help in identifying the reason behind this error would be really helpful

Usually, those problems are data related (This is an option That's Just About Righ or this Good Times & Great . Can you set your max error to be greater than 0 and see the impact for example: max_bad_records = 10 — Tamir Klein
Found the issue. I was not closing the file context properly due to which bigquery load is loading the incomplete data. Thanks for all the help! — Sains
Great happy issue was solved. Will be useful to add a comment with how you close the context for other readers — Tamir Klein
Closed the file context with the close() method on file object. For Example: f.close() — Sains

John Hanley John Hanley · Accepted Answer · 2019-01-16T04:17:27

You are using different delimiters in your input between your BigQuery and Python examples.

Note: Your sample is using pipe delimiters and has semicolons in the fields.

2c|Blackhawk gone wild|That's Just About Right|Good Times & Great Country;2019-01-16 14:22:07

Your Python code is using semicolon delimiters:

job_config.field_delimiter = ';'

Your Bigquery is using pipe delimiters:

job_config.field_delimiter = '|'

Based upon your BigQuery example, change your Python code to match:

job_config.field_delimiter = '|'

Note: I do not know why you included your first line of code:

f.write((str(callsign).split('_')[0]).lower().encode('utf-8') + ';' + artist.encode('utf-8') + ';' + song_title.encode('utf-8') + ';' + show_title.encode('utf-8') + ';' + str(time_bq).encode('utf-8') + '\n')

Bigquery CSV load fails with Python API but successful using GUI

1 Answers