Bigquery add columns to table schema

Example using the BigQuery Node JS API:

const fieldDefinition = {
    name: 'nestedColumn',
    type: 'RECORD',
    mode: 'REPEATED',
    fields: [
        {name: 'id', type: 'INTEGER', mode: 'NULLABLE'},
        {name: 'amount', type: 'INTEGER', mode: 'NULLABLE'},
    ],
}; 

const table = bigQuery.dataset('dataset1').table('source_table_name');
const metaDataResult = await table.getMetadata();
const metaData = metaDataResult[0];

const fields = metaData.schema.fields;
fields.push(fieldDefinition);

await table.setMetadata({schema: {fields}});

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.


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

Tags:

Google Bigquery

Recent Posts