I am looking for a way how the schema is set with a json file in Python on Big Query. The following document says I can set it with Schema field one by one, but I want to find out more efficient way. https://cloud.google.com/bigquery/docs/schemas
Autodetect would be skeptical to make it in this case. I will appreciate it if you helped me.
You can create a JSON file with columns/data types and use the below code to build BigQuery Schema.
JSON File (schema.json):
[
{
"name": "emp_id",
"type": "INTEGER"
},
{
"name": "emp_name",
"type": "STRING"
}
]
Python Code:
import json
from google.cloud import bigquery
bigquerySchema = []
with open('schema.json') as f:
bigqueryColumns = json.load(f)
for col in bigqueryColumns:
bigquerySchema.append(bigquery.SchemaField(col['name'], col['type']))
print(bigquerySchema)
schema.json file has nested fields?Soumendra Mishra is already helpful, but here is a bit more general version that can optionally accept addition fields such as mode or description:
JSON File (schema.json):
[
{
"name": "emp_id",
"type": "INTEGER",
"mode": "REQUIRED"
},
{
"name": "emp_name",
"type": "STRING",
"description": "Description of this field"
}
]
Python Code:
import json
from google.cloud import bigquery
table_schema = []
# open JSON file read only
with open('schema.json', 'r') as f:
table_schema = json.load(f)
for entry in table_schema:
# rename key; bigquery.SchemaField expects `field` to be called `field_type`
entry["field_type"] = entry.pop("type")
# ** effectively provides data as argument:value pairs (e.g. name="emp_id")
table_schema.append(bigquery.SchemaField(**entry))
