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