Json format not supported when using load_table_from_dataframe (Bigquery)

Question

I'm trying to load a Pandas dataframe into Bigquery which also includes a Json column. Based on the exception it appears that the load_table_from_dataframe method does not currently support the native Json type in BigQuery.

Even explicitelly setting the schema in the JobConfig or disabling automatic schema detection doesn't seem to work either.

Would you recommend any alternative approach or library to tackle this problem?

Bigquery target table:

create table `project_id`.sandbox.tmp (id int, json_data json);

Snippet:

import pandas as pd
from google.cloud import bigquery

# Create a Pandas dataframe with JSON-type columns
df = pd.DataFrame({
        'id': [1, 2, 3],
        'json_data': [{"key": "value1"}, {"key": "value2"}, {"key": "value3"}]
    })

client = bigquery.Client()
table_ref = "project_id.sandbox.tmp"
job_config = bigquery.LoadJobConfig()
job_config.autodetect = False # it does not make any difference
# job_config.autodetect = True


job = client.load_table_from_dataframe(df, table_ref, job_config=job_config)
job.result()

Exception:

/...-service/venv/lib/python3.11/site-packages/google/cloud/bigquery/_pandas_helpers.py:267: UserWarning: Unable to determine type for field 'json_data'.
  warnings.warn("Unable to determine type for field '{}'.".format(bq_field.name))
Traceback (most recent call last):
  File "....service/other/sample_load_from_dataframe.py", line 21, in <module>
    job.result()
  File "/....-service/venv/lib/python3.11/site-packages/google/cloud/bigquery/job/base.py", line 922, in result
    return super(_AsyncJob, self).result(timeout=timeout, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/....-service/venv/lib/python3.11/site-packages/google/api_core/future/polling.py", line 261, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 Unsupported field type: JSON

Edit:

• python: 3.11
• google-cloud-bigquery: 3.11.4

Answer 1

The issue is that the default source_format being used is parquet which doesn't support JSON columns.

Here's one way to workaround this:

job_config.source_format = 'CSV'

Also the JSON data in the dataframe needs to be escaped.

Before:

df = pd.DataFrame({
        'id': [1, 2, 3],
        'json_data': [{"key": "value1"}, {"key": "value2"}, {"key": "value3"}]
    })

After: (escaping the values)

df = pd.DataFrame({
        'id': [1, 2, 3],
        'json_data': [
            '{"key": "value1"}', 
            '{"key": "value2"}', 
            '{"key": "value3"}'
            ]
    })

So the entire code should be as follows:

import pandas as pd
from google.cloud import bigquery

# Create a Pandas dataframe with JSON-type columns
df = pd.DataFrame({
        'id': [1, 2, 3],
        'json_data': [
            '{"key": "value1"}', 
            '{"key": "value2"}', 
            '{"key": "value3"}'
            ]
    })

client = bigquery.Client()
table_ref = "project_id.sandbox.tmp"
job_config = bigquery.LoadJobConfig()
job_config.autodetect = False
job_config.source_format = 'CSV'

job = client.load_table_from_dataframe(df, table_ref, job_config=job_config)
job.result()