CODEWITHSUNDEEP
python-3.xgoogle-cloud-platformgoogle-bigquery
CYC
CYC

Reputation: 325

How to insert a python dictionary data to google cloud bigquery

I am trying to insert a python dictionary data to bigbuery.

The below is the data I use

data = {
    'columnID':'123156',
    'deviceID':'156',
    'describle':{
        'name':'car',
        'freq':'10',
        'period':'3',
    }
}

I also define the bigquery table schema below

table_schema = {
    'fields':[
        {'name':'columnID', 'type':'STRING', 'mode':'REQUIRED'},
        {'name':'deviceID', 'type':'STRING', 'mode':'REQUIRED'},
        {'name':'describle', 'type':'RECORD', 'mode':'NULLABLE', 'fields':[
            {'name':'name', 'type':'STRING', 'mode':'NULLABLE'},
            {'name':'freq', 'type':'STRING', 'mode':'NULLABLE'},
            {'name':'period', 'type':'STRING', 'mode':'NULLABLE'}]
        },
    ]
}

It seems can not insert the data into bigquery table, anyone has an idea about this?

Upvotes: 1

Views: 7853

Answers (1)

Ryan Yuan
Ryan Yuan

Reputation: 2556

I've tested in my machine and it worked. Try the following script please.

from google.cloud import bigquery

PROJECT_ID = "your-project"
DATASET_ID = "your_dataset"
TABLE_ID = "your_table"

client = bigquery.Client()

# 1) create table
schema = [
    bigquery.SchemaField("columnID", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("deviceID", "INTEGER", mode="REQUIRED"),
    bigquery.SchemaField(
        "describle",
        "RECORD",
        mode="NULLABLE",
        fields=[
            bigquery.SchemaField("name", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("freq", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("period", "STRING", mode="NULLABLE"),
        ],
    ),
]

table = bigquery.Table(f"{PROJECT_ID}.{DATASET_ID}.{TABLE_ID}", schema=schema)
table = client.create_table(table)
print(
    "Created table {}.{}.{}".format(
        table.project, table.dataset_id, table.table_id
    )
)

# 2) insert data
rows_to_insert = [
    {
        "columnID": "123156",
        "deviceID": "156",
        "describle": {
            "name": "car",
            "freq": "10",
            "period": "3",
        },
    }
]

errors = client.insert_rows_json(
    f"{PROJECT_ID}.{DATASET_ID}.{TABLE_ID}", rows_to_insert
)
    
if errors == []:
    print("New rows have been added.")
else:
    print("Encountered errors while inserting rows: {}".format(errors))

Upvotes: 9

Related Questions