CODEWITHSUNDEEP
pythongoogle-bigquerytuples
Praxiteles
Praxiteles

Reputation: 6010

How do you programmatically create a Google BigQuery schema to create a table?

We are trying to create a table in BigQuery. We have code working from a Google example that creates the schema - but the code specifying the schema is in a tuple.

The code looks like this: 

table.schema = (
    bigquery.SchemaField('Name', 'STRING'),
    bigquery.SchemaField('Age', 'INTEGER'),
    bigquery.SchemaField('Weight', 'FLOAT'),
)

The problem we face is that we have the header fields we need in a list (i.e. [“Name”, “Age”, “Weight”]) - but as we understand it - tuples are immutable.

As a result, we are unable to do code like this because it throws an immutable object error:

 for field in field_list:
   table.schema.append(field, ‘STRING’)

How do we create the table schema from a list of headers when the tuple structure is immutable? How do we create all the fields in the object?

Upvotes: 1

Views: 2661

Answers (2)

Praxiteles
Praxiteles

Reputation: 6010

The new Python BigQuery API changed from a tuple to a list which means you can now programmatically do what you is asked with code like this: 

from google.cloud import bigquery
from google.cloud.bigquery.client import Client
service_account_file_path = "/auth/key.json" # your service account auth file file
client = bigquery.Client.from_service_account_json(service_account_file_path)

schema = []
for field_name in field_name_list:
    schema.append(bigquery.SchemaField(field_name, "STRING", mode="REQUIRED"))

table_id = "your_project_id.your_dataset_name.your_table_name"
table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)

Upvotes: 2

saifuddin778
saifuddin778

Reputation: 7277

I think you just need this:

from google.cloud import bigquery
headers = [
   ('Name', 'STRING'), 
   ('Age', 'INTEGER'), 
   ('Weight', 'STRING'),
   ('any_thing_else', 'STRING'), ...
]
schema = map(lambda n: bigquery.SchemaField(*n), headers)
table.schema = tuple(schema)

Upvotes: 0

Related Questions