I want to add a new column to existing tables (update the existing table's schema) in BigQuery using BigQuery Python API.
However my code seems not working.
Here's my code:
flow = flow_from_clientsecrets('secret_key_path', scope='my_scope')
storage = Storage('CREDENTIAL_PATH')
credentials = storage.get()
if credentials is None or credentials.invalid:
credentials = tools.run_flow(flow, storage, tools.argparser.parse_args([]))
http = httplib2.Http()
http = credentials.authorize(http)
bigquery_service = build('bigquery', 'v2', http=http)
tbObject = bigquery_service.tables()
query_body = {'schema': {'name':'new_column_name', 'type':'STRING'}}
tbObject.update(projectId='projectId', datasetId='datasetId', tableId='tableId', body=query_body).execute()
it returns Provided schema doesn't match existing table's schema error.
Can anyone give me a working Python example?
Many thanks!
解决方案
summary of my comments (as i've got some minutes now for this):
whole schema (along with new field) needs to be supplied to api
new field will be added with null for existing rows. no way to set
value
you can have some logic in queries that you will be running against
this table to compensate this. or you can have separate table with
just this new field and some key that you will be joining your
existing table with new table to get this field