Skip to content
Star Us On GitHub

Using Liquibase with BigQuery

The Liquibase BigQuery extension enables efficient version control and database change management for BigQuery schema and application data. This extension gives Google BigQuery users a smooth, streamlined approach to database change management and deployment, fitting effortlessly into Agile development and CI/CD automation practices.

Google BigQuery is a fully managed analytics data warehouse. For more information, see BigQuery Documentation.

Read more about Database DevOps with Liquibase and BigQuery.

Supported database versions

  • 2.13.6+

Warning

Liquibase versions 4.24.0, 4.25.0, and 4.25.1 transformed table names to lowercase which caused Liquibase to not be able to read its own DATABASECHANGELOG table so it would create a new one and redeploy all previously deployed changesets.

This issue is fixed in Liquibase 4.26.0 and later releases.

Prerequisites

  1. Introduction to Liquibase – Dive into Liquibase concepts.
  2. Install Liquibase – Download Liquibase on your machine.
  3. How to Apply Your Liquibase Pro License Key – If you use Liquibase Pro, activate your license.

Install drivers

To use Liquibase and BigQuery, you need several JAR files.

All users

  1. Download the required JAR files

  2. Copy your JAR files into your Liquibase installation

    Place your JAR file(s) in the liquibase/lib directory.

  3. (optional) Enable Liquibase Pro capabilities

    To apply a Liquibase Pro key to your project, add the following property to the Liquibase properties file:

    liquibase.licenseKey: <paste key here>

Maven users (additional step)

If you use Maven, you must include the driver JAR as a dependency in your pom.xml file.

<dependency>
  <groupId>com.google.cloud</groupId>
  <artifactId>google-cloud-bigquery</artifactId>
  <version>2.24.4</version>
</dependency>
<dependency>
  <groupId>org.liquibase.ext</groupId>
  <artifactId>liquibase-bigquery</artifactId>
  <version>4.29.1</version>
</dependency>

Database connection

Configure connection

  1. Ensure your BigQuery database is configured. See BigQuery Quickstarts for more information. For example, you can run a query of a sample table in BigQuery using the bq command-line tool. 

    bq show bigquery-public-data:samples.shakespeare

  2. Specify the JDBC URL in the liquibase.properties file (defaults file), along with other Liquibase property default values. Liquibase does not parse the JDBC URL. You can either specify the full database connection string or specify the URL using your database's standard JDBC format.

    url: jdbc:bigquery://<Host>:<Port>;ProjectId=<Project>;OAuthType=<AuthValue>;<Property1>=<Value1>;<Property2>=<Value2>;...
    • Specify the ID of your BigQuery project as the value of ProjectId.
    • Specify your BigQuery authentication method as the value of OAuthType.

    Note

    Detailed information on JDBC Connections for Big Query can be found here: Simba Google BigQuery JDBC Connector Install and Configuration Guide_1.5.0.1001.pdf

    Click on the following tabs to see example JDBC URLs for each authentication type.

    Google Services

    Requires the options OAuthServiceAcctEmail and OAuthPvtKeyPath in your Liquibase url property.

    For example: 

    jdbc:bigquery://https://googleapis.com/bigquery/v2:443;
ProjectId=myProject;
OAuthType=0;
OAuthServiceAcctEmail=lbtest@bq123.iam.gserviceaccount.com;
OAuthPvtKeyPath=C:\path\serviceKey.p12;

    Google User Account authentication

    Requires your user account credentials.

    For example: 

    jdbc:bigquery://https://googleapis.com/bigquery/v2:443;
ProjectId=myProject;
OAuthType=1;

    Google Authorization Server Access Token

    Requires the options OAuthAccessToken, OAuthRefreshToken, OAuthClientId, and OAuthClientSecret in your Liquibase url property.

    For example: 

    jdbc:bigquery://https://googleapis.com/bigquery/v2:443;
ProjectId=myProject;
OAuthType=2;
OAuthAccessToken=a25c7cfd36214f94a79d;
OAuthRefreshToken=2kl0Qvuw9qt4abia54qga5t97;
OAuthClientId=22n6627g243322f7;
OAuthClientSecret=cDE+F2g3Hcjk4K5lazM;

    Application Default Credentials

    For example: 

    jdbc:bigquery://https://googleapis.com/bigquery/v2:443;
ProjectId=myProject;
OAuthType=3;

    Workload or Workforce identity federation

    For example: 

    jdbc:bigquery://https://googleapis.com/bigquery/v2:443;
ProjectId=myProject;
OAuthType=4;
OAuthPvtKeyPath=/__w/liquibase-repo/gha-creds-80d532a88cc2f6d6.json;

Test connection

  1. Create a text file called changelog (.xml, .sql, .json, or .yaml) in your project directory and add a changeset.

    If you already created a changelog using the init project command, you can use that instead of creating a new file. When adding onto an existing changelog, be sure to only add the changeset and to not duplicate the changelog header.

    -- liquibase formatted sql

-- changeset my_name:1
CREATE TABLE test_table 
(
  test_id INT, 
  test_column INT, 
  PRIMARY KEY (test_id)
)

    <?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
  xmlns:pro="http://www.liquibase.org/xml/ns/pro"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
    http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

  <changeSet id="1" author="my_name">
    <createTable tableName="test_table">
      <column name="test_id" type="int">
        <constraints primaryKey="true"/>
      </column>
      <column name="test_column" type="INT"/>
    </createTable>
  </changeSet>

</databaseChangeLog>

    databaseChangeLog:
  - changeSet:
    id: 1
    author: my_name
    changes:
    - createTable:
      tableName: test_table
      columns:
      - column:
        name: test_column
          type: INT
          constraints:
            primaryKey:  true
            nullable:  false

    {
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "1",
        "author": "my_name",
        "changes": [
          {
            "createTable": {
              "tableName": "test_table",
              "columns": [
                {
                  "column": {
                    "name": "test_column",
                    "type": "INT",
                    "constraints": {
                      "primaryKey": true,
                      "nullable": false
                    }
                  }
                }
              ]
            }
          }
        ]
      }
    }
  ]
}

  2. Navigate to your project folder in the CLI and run the Liquibase status command to see whether the connection is successful:

    liquibase status --username=test --password=test --changelog-file=<changelog.xml>

    Note

    You can specify arguments in the CLI or keep them in the Liquibase properties file.

    If your connection is successful, you'll see a message like this:

    1 changeset has not been applied to <your_jdbc_url>
Liquibase command 'status' was executed successfully.

  3. Inspect the SQL with the update-sql command. Then make changes to your database with the update command.

    liquibase update-sql --changelog-file=<changelog.xml>
liquibase update --changelog-file=<changelog.xml>

    If your update is successful, Liquibase runs each changeset and displays a summary message ending with:

    Liquibase: Update has been successful.
Liquibase command 'update' was executed successfully.

  4. From a database UI tool, ensure that your database contains the test_table you added along with the DATABASECHANGELOG table and DATABASECHANGELOGLOCK table.

Now you're ready to start making deployments with Liquibase!

Troubleshooting

In version 4.27.0.1 and earlier of the Liquibase BigQuery extension, Liquibase automatically makes a JDBC connection to the US region if you don't specify another location in the JDBC URL. This behavior may be unexpected.

For example, if you have a multi-region dataset in the EU and a primary replica in the US, you may expect Liquibase to use EU as your region. However, if you don't specify that in the Liquibase url parameter, you may receive this error message:

The dataset replica of the cross region dataset '<dataset_id>' in region 'US' is read-only because it's not the primary replica.

To specify your intended region, add ;Location=<value> to your URL:

url: jdbc:bigquery://...;OAuthType=0;Timeout=10000;Location=<value>

In version 4.27.0.2 and later of the Liquibase BigQuery extension, Liquibase does not use the US region as a default. When you have multiple datasets in different regions, the BigQuery JDBC driver automatically chooses the correct region based on your datasets. Liquibase uses the region the driver auto-routes to. Optionally, you can still manually specify the region in the Liquibase url parameter.