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