Jordan Savant # Software Engineer

Google BigQuery

My experience has been interesting and mostly frustrating, though those frustrations are not worth exempting its usefulness. I mostly am frustrated over the inconsistent ways of identifying data and the regular assortment of 22 browser tabs needed to cross reference documentation for basic things.

Extending a Nested RECORD Type

Record types, also called "structs" allow you to specify sub types for tables. For the life of me I cannot get the auto-detect to create new record subfields based on inserted data so I had to resort to manually extending the record.

This is not achievable with the GUI Console so we have to turn to the BQ CLI. I used the built in CLI within the browser which gives the user a basic linux shell and filesystem to work with.

Step 1: Write the existing Table Schema to a file

$ bq show --schema --format=prettyjson ID_OF_APP:DATASET.TABLE > schema.json

Step 2: Modify the schema json, by adding the field to the RECORD type:

$ vim schema.json
    "fields": [
        "name": "existingField",
        "type": "NUMERIC"
        "name": "newFieldName",
        "type": "NUMERIC"
    "name": "nameOfRecordColumn",
    "type": "RECORD"

Step 3: Reload into Table

bq update ID_OF_APP:DATASET.TABLE schema.json