如何将python字典数据插入到google cloud bigquery

Question

我正在尝试插入一个python字典数据到bigbury。

下面是我使用的数据

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

我还在

下面定义了bigquery表模式

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'}]
},
]
}

似乎不能插入数据到bigquery表中，有人对此有什么想法吗?

Answer 1

我已经在我的机器上测试过了，效果不错。请尝试下面的脚本。

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))