BigQuery python insert with Record (\w client.insert_rows)

Question

I am currently trying to insert a row into a BQ dataset.

I want to use the Client.insert_row() function for this documentation

I got everything up and running, for a simple schema without any field of type 'Record'. However, when I add a field of type 'Record' to my Schema, I dont know how to define this in the insert_row() function

My Schema: Schema in BQ

My Code:

client = bigquery.Client(
    credentials=credentials,
    project=credentials.project_id,
)

dataset_ref = client.dataset('channel_data')

table_ref = dataset_ref.table('test')
table = client.get_table(table_ref)  # API call

rows_to_insert = [{"test1":"a","test2":"b","test3":"c","record":{"1":"d","2":"e"}},]

errors = client.insert_rows_json(table, rows_to_insert)  # API request
assert errors == []

I tried many different versions for the row _to_insert:

rows_to_insert = [{"test1":"a","test2":"b","test3":"c","record":["d","e"]},]
rows_to_insert = [{"test1":"a","test2":"b","test3":"c","record.1":"d","record.2":"e"}},]

none of them seem to work and I can't find any information online how to do this. Has anyone ever done this before?

It is important that it is inserted as a JSON, since sometimes some of the values are missing. I know you can just put everything into a list and transfer it like this

rows_to_insert = [("a", "b", "c", ["d", "e"])]

but that is not an option for me!

Yannic Jänike · Accepted Answer · 2020-03-24 10:56:43Z

5

The right way to insert them is as follows:

rows_to_insert = [{"test1":"a","test2":"b","test3":"c","record":{"r1":"d","r2":"e"}}]

the record field has itself a dictionary as an entry with the specific fields as key/values!

answered Mar 24, 2020 at 10:56

Yannic Jänike

1351 gold badge2 silver badges9 bronze badges

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

1 Comment

Onkar Over a year ago

One observation while using insert_rows_json API. I use this API to insert json rows like this:

result = bq_client.insert_rows_json(table=bq_table, json_rows=json_rows, skip_invalid_rows=False, ignore_unknown_values=False)     print(result)

. If there is mismatch between json rows and the bq table schema then, I don't get all the errors back in the same result list. I was expecting list of all errors for json insert. If I run the same statement multiple times, I get different schema error in each API call. any solution for this?

Collectives™ on Stack Overflow

BigQuery python insert with Record (\w client.insert_rows)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related