Bigquery add columns to table schema

Question

I am trying to add new column to BigQuery existing table. I have tried bq command tool and API approach. I get following error when making call to Tables.update().

I have tried with providing full schema with additional field and that also gives me same error as shown below.

With API I get following Error:

{     "schema": {         "fields": [{             "name": "added_column",             "type": "integer",             "mode": "nullable"         }]     } }    {     "error": {         "errors": [{             "domain": "global",             "reason": "invalid",             "message": "Provided Schema does not match Table [blah]"         }],         "code": 400,         "message": "Provided Schema does not match Table [blah]"     } } 

With BQ tool I get following error:

./bq update -t blah added_column:integer 

BigQuery error in update operation: Provided Schema does not match Table [blah]

Answer 1

Try this:

bq --format=prettyjson show yourdataset.yourtable > table.json 

Edit table.json and remove everything except the inside of "fields" (e.g. keep the [ { "name": "x" ... }, ... ]). Then add your new field to the schema.

Or pipe through jq

bq --format=prettyjson show yourdataset.yourtable | jq .schema.fields > table.json 

Then run:

bq update yourdataset.yourtable table.json 

You can add --apilog=apilog.txt to the beginning of the command line which will show exactly what is sent / returned from the bigquery server.

Answer 2

In my case I was trying to add a REQUIRED field to a template table, and was running into this error. Changing the field to NULLABLE , let me update the table.

Also more recent version on updates for anybody stumbling from Google.

#To create table bq mk --schema domain:string,pageType:string,source:string -t Project:Dataset.table #Or using schema file bq mk --schema SchemaFile.json -t Project:Dataset.table   #SchemaFile.json format [{                                                                                                                                                                                                                                                   "mode": "REQUIRED",   "name": "utcTime",   "type": "TIMESTAMP" },     {   "mode": "REQUIRED",   "name": "domain",   "type": "STRING" },   {   "mode": "NULLABLE",   "name": "testBucket",   "type": "STRING" },   {   "mode": "REQUIRED",   "name": "isMobile",   "type": "BOOLEAN"                                                                                                                                                                                                                        }, {   "mode": "REQUIRED",   "name": "Category",   "type": "RECORD",   "fields": [     {       "mode": "NULLABLE",       "name": "Type",       "type": "STRING"      },      {        "mode": "REQUIRED",        "name": "Published",        "type": "BOOLEAN"      }     ] }]  # TO update bq update --schema UpdatedSchema.json -t Project:Dataset.table # Updated Schema contains old and any newly added columns  

Some docs for template tables