BigQuery: KeyError inserting data with missing value into table with nullable field

[javierdvalle]

I recently updated the google-cloud-bigquery python package and I am getting a KeyError when trying to insert a row with a missing value into a table with a nullable field.
In previous version (I tested it on version 1.5.0) the missing field was automatically filled with a null value.

Environment details

OS: Ubuntu 18.04
Python version:

$ python --version
Python 3.6.7

Package version:

$ pip show google-cloud-bigquery
Name: google-cloud-bigquery
Version: 1.9.0

Note: It works on version 1.5.0

Steps to reproduce

1. Create table with nullable field
2. Insert row with missing value (provide row as dictionary, with a missing key:value)

Code example

from google.cloud import bigquery

client = bigquery.Client(project='my_project')

dataset_ref = client.dataset('my_dataset')
schema = [
    bigquery.SchemaField("user_id", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("name", "STRING", mode="NULLABLE"), # NULLABLE
]
table_ref = dataset_ref.table("my_table")
table = bigquery.Table(table_ref, schema=schema)
table = client.create_table(table)

rows_to_insert = [
    {'user_id': 1111, 'name': 'peter'},  # OK
    {'user_id': 2222, 'name': 'anna'}  # OK
]

errors = client.insert_rows(table, rows_to_insert)
assert errors == []

rows_to_insert = [
    {'user_id': 3333, 'name': 'john'},
    {'user_id': 4444} # FAILS
]

errors = client.insert_rows(table, rows_to_insert) # FAILS HERE
assert errors == []

Stack trace

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-22-a6f1bda68730> in <module>
     26 ]
     27 
---> 28 errors = client.insert_rows(table, rows_to_insert) # FAILS HERE
     29 assert errors == []

~/.local/lib/python3.6/site-packages/google/cloud/bigquery/client.py in insert_rows(self, table, rows, selected_fields, **kwargs)
   1495             raise TypeError("table should be Table or TableReference")
   1496 
-> 1497         json_rows = [_record_field_to_json(schema, row) for row in rows]
   1498 
   1499         return self.insert_rows_json(table, json_rows, **kwargs)

~/.local/lib/python3.6/site-packages/google/cloud/bigquery/client.py in <listcomp>(.0)
   1495             raise TypeError("table should be Table or TableReference")
   1496 
-> 1497         json_rows = [_record_field_to_json(schema, row) for row in rows]
   1498 
   1499         return self.insert_rows_json(table, json_rows, **kwargs)

~/.local/lib/python3.6/site-packages/google/cloud/bigquery/_helpers.py in _record_field_to_json(fields, row_value)
    399     for subindex, subfield in enumerate(fields):
    400         subname = subfield.name
--> 401         subvalue = row_value[subname] if isdict else row_value[subindex]
    402         record[subname] = _field_to_json(subfield, subvalue)
    403     return record

KeyError: 'name'

[tseaver]

/cc @adamf

This change was introduced in e633ffd, which fixed parsing nested schemas.

I think the change is as simple as using row_value.get(subname) instead of row_value[subname] in line 401. Of course, we should add a test which exercises the missing value logic.

Hey Guys, for information:

I'm having the same problem but passing a list with dicts inside to be inserted in BigQuery Table. In my old Cloud Functions it's working fine yet but I needed to change other Cloud Function and start a lot of errors because is a pipeline of ETL from Cloud Storage to BigQuery. Locally worked fine until I had updated today google-cloud-bigquery to last version.

• Follow my version:

$ python3.7 -m pip show google-cloud-bigquery
Name: google-cloud-bigquery
Version: 1.9.0

• Error ocurred locally and on Cloud Functions today:

errorsInsertRows = gcpBQAccess.insert_rows(eventsTableBQ, eventsList)
  File "~.local/lib/python3.7/site-packages/google/cloud/bigquery/client.py", line 1497, in insert_rows
    json_rows = [_record_field_to_json(schema, row) for row in rows]
  File "~.local/lib/python3.7/site-packages/google/cloud/bigquery/client.py", line 1497, in <listcomp>
    json_rows = [_record_field_to_json(schema, row) for row in rows]
  File "~.local/lib/python3.7/site-packages/google/cloud/bigquery/_helpers.py", line 401, in _record_field_to_json
    subvalue = row_value[subname] if isdict else row_value[subindex]
KeyError: 'smb_status_code'

The "smb_status_code" is a Key:Value like: {"smb_status_code": None} inside the list "eventsList".

Thank You!

Hey Guys, for information:

I'm having the same problem but passing a list with dicts inside to be inserted in BigQuery Table. In my old Cloud Functions it's working fine yet but I needed to change other Cloud Function and start a lot of errors because is a pipeline of ETL from Cloud Storage to BigQuery. Locally worked fine until I had updated today google-cloud-bigquery to last version.

• Follow my version:

$ python3.7 -m pip show google-cloud-bigquery
Name: google-cloud-bigquery
Version: 1.9.0

• Error ocurred locally and on Cloud Functions today:

errorsInsertRows = gcpBQAccess.insert_rows(eventsTableBQ, eventsList)
  File "~.local/lib/python3.7/site-packages/google/cloud/bigquery/client.py", line 1497, in insert_rows
    json_rows = [_record_field_to_json(schema, row) for row in rows]
  File "~.local/lib/python3.7/site-packages/google/cloud/bigquery/client.py", line 1497, in <listcomp>
    json_rows = [_record_field_to_json(schema, row) for row in rows]
  File "~.local/lib/python3.7/site-packages/google/cloud/bigquery/_helpers.py", line 401, in _record_field_to_json
    subvalue = row_value[subname] if isdict else row_value[subindex]
KeyError: 'smb_status_code'

The "smb_status_code" is a Key:Value like: {"smb_status_code": None} inside the list "eventsList".

Thank You!

Important Additional Information:

The files we are processing doesn't hit the smb_status_code, so when was working, BigQuery automatically placed "null" in this column and the other columns. We have 72 columns in the table and one row doesn't have data for all columns when we are inserting trough this way:

errorsInsertRows = gcpBQAccess.insert_rows(eventsTableBQ, eventsList)

Best Regards,

Hey Guys, for information:
I'm having the same problem but passing a list with dicts inside to be inserted in BigQuery Table. In my old Cloud Functions it's working fine yet but I needed to change other Cloud Function and start a lot of errors because is a pipeline of ETL from Cloud Storage to BigQuery. Locally worked fine until I had updated today google-cloud-bigquery to last version.

• Follow my version:

$ python3.7 -m pip show google-cloud-bigquery
Name: google-cloud-bigquery
Version: 1.9.0

• Error ocurred locally and on Cloud Functions today:

errorsInsertRows = gcpBQAccess.insert_rows(eventsTableBQ, eventsList)
  File "~.local/lib/python3.7/site-packages/google/cloud/bigquery/client.py", line 1497, in insert_rows
    json_rows = [_record_field_to_json(schema, row) for row in rows]
  File "~.local/lib/python3.7/site-packages/google/cloud/bigquery/client.py", line 1497, in <listcomp>
    json_rows = [_record_field_to_json(schema, row) for row in rows]
  File "~.local/lib/python3.7/site-packages/google/cloud/bigquery/_helpers.py", line 401, in _record_field_to_json
    subvalue = row_value[subname] if isdict else row_value[subindex]
KeyError: 'smb_status_code'

The "smb_status_code" is a Key:Value like: {"smb_status_code": None} inside the list "eventsList".
Thank You!

Important Additional Information:

The files we are processing doesn't hit the smb_status_code, so when was working, BigQuery automatically placed "null" in this column and the other columns. We have 72 columns in the table and one row doesn't have data for all columns when we are inserting trough this way:

errorsInsertRows = gcpBQAccess.insert_rows(eventsTableBQ, eventsList)

Best Regards,

Hi Guys,

We found another way to solve our problem, we changed from insert_rows to insert_rows_json.

We tested locally and on Cloud Functions reprocessing 44k of files from Google Storage to BigQuery totalizing 700 MB without KeyErrors.

We need to change because we insert rows with some values with "null".

What worried us about it was since January 2 of this year insert_rows worked fine for us with the old version of google-cloud-bigquery.

Now we will change other Cloud Functions to correct this call.

We found that solution here: google.cloud.bigquery.client.Client.insert_rows_json

Thanks!

[meirelesmj]

Hi @Kal1s

You just changed the function from insert_rows to `insert_rows_json' ?

Locally I am having no errors but with google cloud still have assertation errors.

You are passing a list of json tuples or a list of dicts ?

If you could share the code working it would be great.

Thanks
Manuel

Hi @Kal1s

You just changed the function from insert_rows to `insert_rows_json' ?

Locally I am having no errors but with google cloud still have assertation errors.

You are passing a list of json tuples or a list of dicts ?

If you could share the code working it would be great.

Thanks
Manuel

Hi @meirelesmj

Our list has a lot of dicts inside and no one has all columns to be inserted in BigQuery, so BigQuery takes care of that column that doesn't have value putting "null" when we execute the insert_rows_json.

Before I updated the Python 3.7 modules worked fine with insert_rows locally but not in Cloud Functions, so I decided to update my modules locally so stoped worked with same error on Cloud Functions. Looking around I founded it google.cloud.bigquery.client.Client.insert_rows_json, so I changed my code like I will explain next.

Because Cloud Functions use Python 3.7 I'm using Python 3.7 locally too, so first I updated my Python 3.7 modules. I have installed locally the module google-cloud-bigquery, so in that update, this module will be updated too if is outdated.

Update Python 3.7 modules:

python3.7 -m pip install -U $(python3.7 -m pip freeze | cut -d '=' -f 1)

After finished the updated, I changed my code.

Update Code, from:

errorsInsertRows = gcpBQAccess.insert_rows(eventsTableBQ, eventsList)

Update Code, to:

errorsInsertRows = gcpBQAccess.insert_rows_json(eventsTableBQ, eventsList)

Now our batch and streaming are working normally.

I hope I've helped you...

Best Regards,

[meirelesmj]

Thanks @Kal1s I am using 2.7 and its working ok with insert_rows_json but not in cloud functions.

Will change to python 3.7 locally to see if I have any errors like in Cloud Function.

In terms of code change on cloud functions was just that right ? no other imports needed right ?

Thanks for this great fix

Thanks @Kal1s I am using 2.7 and its working ok with insert_rows_json but not in cloud functions.

Will change to python 3.7 locally to see if I have any errors like in Cloud Function.

In terms of code change on cloud functions was just that right ? no other imports needed right ?

Thanks for this great fix

Hi @meirelesmj

On your, Cloud Functions the requirements.txt need to have at least google-cloud-bigquery more other modules you are using in your function and change your code to insert_rows_json if you need to insert rows that are missing values on few columns where BigQuery will put "null" for you in your table.

I recommend to you to use Python 3.7 locally too because are a lot of differences between Python 2 and 3, like coding and performance, and because Cloud Functions in this moment have support only to Python version 3.7 to execute our functions.

Best Regards,