Specify column mapping when loading JSON into Google BigQuery

Question

Let's say we have the following JSON object:

{"keyA": "1", "keyB": 2}

We want to load this into a BigQuery table with two columns; col_a and col_b.

If the JSON keys and the column names matched, we could just use, for instance, load_table_from_json (in the Python client)

But is there any way to specify that values from JSON key keyA should be put in col_a and keyB should be put in col_b?

Preferably, I'd like to be able to do this in Python, but this is really a question about the BigQuery API in general.

Pentium10 · Accepted Answer · 2019-09-27 14:34:06Z

1

The API doesn't support column naming.

You can use autodetect

job_config.autodetect = True
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON

Or you could load as a whole JSON, then use the JSON_EXTRACT methods to create individual columns from it

create table `newtable` as
select json_extract(meta,'$.keyA') as keyA ...

answered Sep 27, 2019 at 14:34

Pentium10

209k124 gold badges435 silver badges513 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1