In every analytics system design, no matter how much you plan what events you gather and what properties to store for each event, there are bound to be omissions. Or sometimes code improvements present themselves that allow for better semantic normalization of the same raw data. Google BigQuery does not allow us to change field names or types and rightly so; in an analytics backend data is assumed to be immutable. We can however append new fields or make some required fields optional. I’m including some code that goes hand in hand with the infer schema snippet I posted a while back. Note that I’ve removed error checks and retry logic for brevity. Unfortunatley Google APIs 500 fairly often so I strongly recommend you code defensively.
def update_schema(self, event_name, fields):
"""
:param event_name: the name of the event
:param fields: a list of new events in the regular schema format, use `infer_schema`
"""
table_name = event_name.lower().replace(' ', '_') + '_' + now().strftime('%Y%m%d')
# get the current schema
api_call = self.service.tables().get(projectId=settings.GOOGLE_BIG_QUERY_PROJECT_ID,
datasetId=settings.GOOGLE_BIG_QUERY_DATASET_ID,
tableId=table_name
)
schema = api_call.execute()['schema']['fields']
schema.extend(fields)
# patch the table
api_call = self.service.tables().patch(projectId=settings.GOOGLE_BIG_QUERY_PROJECT_ID,
datasetId=settings.GOOGLE_BIG_QUERY_DATASET_ID,
tableId=table_name,
body={'tableReference': {'projectId': settings.GOOGLE_BIG_QUERY_PROJECT_ID,
'datasetId': settings.GOOGLE_BIG_QUERY_DATASET_ID,
tableId': table_name},
'schema': {'fields': schema},
})
api_call.execute()
Also note that I shard our tables based on date, the code above will only update the currently active table for the event.