Is it possible to add a new field to an existing field of RECORD type in bigquery from UI?

Question

Is it possible to add a new field to an existing field of RECORD type in bigquery? So for example if my current schema is :

{u'fields': [{u'mode': u'NULLABLE', u'name': u'test1', u'type': u'STRING'},
             {u'fields': [{u'mode': u'NULLABLE',
                           u'name': u'field1',
                           u'type': u'STRING'}],
              u'mode': u'NULLABLE',
              u'name': u'recordtest',
              u'type': u'RECORD'}]}

Can I change it to add field "field2" to recordtest? So the new schema will look like:

{u'fields': [{u'mode': u'NULLABLE', u'name': u'test1', u'type': u'STRING'},
             {u'fields': [{u'mode': u'NULLABLE',
                           u'name': u'field1',
                           u'type': u'STRING'},
                          {u'mode': u'NULLABLE',
                           u'name': u'field2',
                           u'type': u'STRING'}],
              u'mode': u'NULLABLE',
              u'name': u'recordtest',
              u'type': u'RECORD'}]}

I couldn't find a way to do this from the UI. But I was able to do this using the API.

Answer 1

This can be done totally from within UI using respective API Explorers

First, you need to get schema of your table. You can do this using Tables.get API Explorer. Make sure you select/check schema in fields box (better to leave the rest unchecked). Click Authorize and Execute. When done - Copy response text - it will look somehow like below

{
 "schema": {
  "fields": [
   {
    "name": "id",
    "type": "STRING",
    "mode": "NULLABLE"
   },

... 

   {
    "name": "comment",
    "type": "STRING",
    "mode": "NULLABLE"
   }
  ]
 }
}

Then, use Tables.patch API Explorer
Add needed fields(s) into previously retrieved schema and paste it as is into Patch Body box (chose freeform editor in the right-top corner of this box and just replace whatever inside with your schema's text). Again click Authorize and Execute

You done now.
Note that no matter where you put your new field within record - it will be added to the end of this record fields

Btw, alternatively, you can use standalone Explorers
Services > BigQuery API v2

The ones that we used here are bigquery.jobs.get and bigquery.tables.patch

Answer 2

You can also use the bigquery CLI tool which comes as part of the gcloud command line tool. I found this route easier because there is less information to add to the request. You don't need an access token or API key since your session is already established. You can find everything you need to know here but the basics of it is:

bq show \ --schema \ --format=prettyjson \ YOUR_PROJECT_ID:YOUR_DATASET.YOUR_TABLE_NAME > schema_file.json Then modify the schema file to add the new nested field.

Then run: bq update YOUR_PROJECT_ID:YOUR_DATASET.YOUR_TABLE_NAME schema_file.json