How to insert values into nested table in python?

Question

I have created a nested table in bigQuery and I would like to insert values into this table.

I know normally we can perform as below:

INSERT INTO `tablename`(webformID,visitID,visitorID,loginID,mycat,country,webformData) VALUES ('1',2,'3','4','5','6',[STRUCT('key2','b'),('k3','c'),('k4','d')])

where webform data is a nested column.

However, in python, how can we do this?

I can create a list as follows: ["STRUCT('key2','b')",('k3','c')] but the 0th index is a problem while trying to insert.

Please advice Thank you

Hi @SriHari, Can you provide the exact error message you are getting and share the code you are using — Tamir Klein
– Tamir Klein, Commented Mar 21, 2019 at 15:00

hkanjih · Accepted Answer · 2019-03-21 18:20:31Z

3

You can insert data in the same order than the table was created.

Follow an example:

from google.cloud import bigquery

client = bigquery.Client.from_service_account_json(JSON_FILE_NAME)
dataset_id = 'test'  # replace with your dataset ID
table_id = 'tablek1'  # replace with your table ID

schema = [
        bigquery.table.SchemaField('column1', 'STRING', mode='REQUIRED'),
        bigquery.table.SchemaField('parent', 'RECORD', mode='REQUIRED', fields = [
            bigquery.table.SchemaField('children1', 'STRING', mode='NULLABLE'),
            bigquery.table.SchemaField('children2', 'INTEGER', mode='NULLABLE')])
    ]

dataset_ref = bigquery.Dataset(client.dataset(dataset_id))
table_ref = dataset_ref.table(table_id)
table = bigquery.Table(table_ref, schema=schema)
table = client.create_table(table)  # API request

# IF YOU NEED GET THE TABLE
# table_ref = client.dataset(dataset_id).table(table_id) 
# table = client.get_table(table_ref)  # API request

rows_to_insert = [
    (
        "test 1", dict(children1 = 'test', children2 = 29 ),     
    )
]

errors = client.insert_rows(table, rows_to_insert)  # API request
print(errors)

Let me know if it helps!

answered Mar 21, 2019 at 18:20

hkanjih

1,2891 gold badge11 silver badges31 bronze badges

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

1 Comment

python_interest Over a year ago

Hi @hkanjih thank you for your response.... I have just figured out the solution. There is no need to specify STRUCT in SQL command. We can provide list of tuples so that it can be passed to the query directly which can help in insertion.

Collectives™ on Stack Overflow

How to insert values into nested table in python?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related