How to Add a Column in BigQuery

BigQuery does not support ALTER TABLE (along with other common DDL statements).

In other words, you can't add a column via a SQL command in BigQuery. Fortunately there are three alternative methods.

Option 1. Adding a Column in the BigQuery Web UI

In the BigQuery Web UI:

  • select the table you wish to alter,
  • click Edit Schema, alt text
  • click the + Add Field button, alt text
  • enter the desired name, type, and mode (e.g. nullable, required, etc),
  • and click Save.

Option 2. Adding a Column in the BigQuery Command Line tool

In the command line, enter:

bq update project_id:dataset.table schema

schema refers to the path to the JSON schema file on your local machine. The JSON schema file should look like:

[
  {
    "mode": "REQUIRED",
    "name": "column1",
    "type": "STRING"
  },
  {
    "mode": "REQUIRED",
    "name": "column2",
    "type": "FLOAT"
  },
  {
    "mode": "REPEATED",
    "name": "column3",
    "type": "STRING"
  }
]

Here's an example command with the path specified:

bq update myproject:mydataset.mytable /tmp/myschema.json

Option 3. Adding a Column by calling the tables.patch API method

If you use the tables.patch API method, you have to supply the full schema (aka new and existing columns) via the API call.

Say we want to add a birthday column to our users table. We already have columns for:

  • first_name, a nullable string
  • last_name, a nullable string
  • has_tried_popsql, a nullable boolean
  • number_of_friends, a required integer 😉

We supply the mode, name, and type for all existing columns, plus our new birthday column. It should look like this:

{
  "schema": {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "first_name",
        "type": "STRING"
      },
      {
        "mode": "NULLABLE",
        "name": "last_name",
        "type": "STRING"
      },
      {
        "mode": "NULLABLE",
        "name": "has_tried_popsql",
        "type": "BOOL"
      },
      {
        "mode": "REQUIRED",
        "name": "number_of_friends",
        "type": "INT64"
      },
      {
        "mode": "NULLABLE",
        "name": "birthday",
        "type": "DATE"
      }
    ]
  }
}

After you submit via the API patch call, the new birthday column will be added 🎂🥳.

Note: You can also add a column in BigQuery using the tables.update command. However tables.patch is almost always preferred since tables.patch only updates the added/modified fields. The tables.update method replaces the entire table resource.

BigQuery documentation lists additional methods for adding a column (with Go, Python, etc).

Previous

Next

Ready for a modern SQL editor?