Using Liquibase with Google BigQuery Open Source

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

Read more about Database DevOps with Liquibase and BigQuery.

Verified database versions

  • 2.13.6+

Warning: Liquibase versions 4.24.0, 4.25.0, and 4.25.1 transformed table names in BigQuery 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. Get Started with Liquibase – Learn how to use Liquibase with an example database.
  4. Design Your Liquibase Project – Create a new Liquibase project folder and organize your changelogs

Install drivers

CLI users

The latest version of Liquibase has a pre-installed driver for this database in the liquibase/extensions directory, so you don't need to install it yourself.

Maven users

To use Liquibase with Maven, you must instead include the driver JAR(s) as a dependency in your pom.xml file. Using this information, Maven automatically downloads the driver JAR from Maven Central when you build your project. 

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

Make sure that the Liquibase plugin and the database extension have the same version in your POM.

If needed, you can download the JAR files manually:

Tip: The community-supported BigQuery extension is deprecated and is not compatible with built-in BigQuery functionality in Liquibase 4.29.0+. If you have JARs from the old extension installed, you must remove them from your liquibase/lib directory.

Configure connection

  1. Ensure your BigQuery database is configured. See BigQuery Quickstarts for more information.
  2. Specify the database URL in the liquibase.properties file (defaults file), along with other properties you want to set a default value for. Liquibase does not parse the URL. You can either specify the full database connection string or specify the URL using your database's standard connection format:
    3. url: jdbc:bigquery://<Host>:<Port>;ProjectId=<Project>;DefaultDataset=<DatasetId>;OAuthType=<AuthValue>;<Property1>=<Value1>;<Property2>=<Value2>;...
    • Specify the ID of your BigQuery project (string) as the value of ProjectId. This is the project that the driver queries by default, and also the project that is billed for queries that are run using the Data Source Name. For more information, see BigQuery: Projects.
    • Specify the ID of your BigQuery dataset (string) as the value of DefaultDataset. This is the dataset that the driver queries by default. Liquibase requires this parameter to know where to create necessaryTracking Tables. Specifying a default dataset enables you to use unqualified table names in SQL statements. The driver treats unqualified tables as part of the default dataset. Additionally, it treats the default dataset as part of the project that is being billed. For more information, see BigQuery: Introduction to datasets.
    • Specify your BigQuery authentication method (integer 0, 1, 2, or 3) as the value of OAuthType. This specifies how the driver obtains or provides the credentials for OAuth 2.0 authentication.

    Note: For detailed information on JDBC connections for BigQuery, including OAuthType information, see 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 (service principal identifier) and OAuthPvtKeyPath (private key) in your Liquibase url property. For example:

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

    To obtain values for OAuthServiceAcctEmail and OAuthPvtKeyPath, follow these steps:

    1. Log into Google Cloud Console (console.cloud.google.com) with administrator ID.
    2. Select the Navigation Menu in the upper left and navigate to IAM & Admin. All Principals will be shown for every role will be shown by default.
    3. Apply a filter of "BigQuery" to display only the service principals related to BigQuery. Note the value of the Principal column of the ID that will be used by Liquibase, for example: lbtest@bq123.iam.gserviceaccount.com. Use this service account email address as the OAuthServiceAcctEmail.
    4. To get the Private Service Keys for the Service Principal, select Service Accounts in the left navigation menu.
    5. From the resulting list of Service accounts, select the same service account email address chosen in the previous step. Service account details will be shown for the selected service account.
    6. Select the KEYS tab at the top of the panel. Any existing keys will be displayed at the bottom of the panel.
    7. Create a new key by clicking ADD KEY and then Create new key.
    8. Select P12 as the Key type and select CREATE. The key will be automatically downloaded in the browser's background while a prompt appears requesting a Private key password.
    9. Enter the Private key password and note the value for use later.
    10. Save the Private key and refer to it by its local path in the OAuthPvtKeyPath property of the JDBC URL.

    Google User Account authentication. Requires your user account credentials. For example:

    url: 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:

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

    Application Default Credentials. For example:

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

Test connection

  1. Create a text file called changelog (.xml, .sql, .yaml, or .json) 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.

    2. --liquibase formatted sql

--changeset your.name:1
CREATE TABLE test_table (test_id INT NOT NULL, test_column INT, PRIMARY KEY (test_id))

    Tip: Formatted SQL changelogs generated from Liquibase versions before 4.2.0 might cause issues because of the lack of space after a double dash ( -- ). To fix this, add a space after the double dash. For example: -- liquibase formatted sql instead of --liquibase formatted sql and -- changeset myname:create-table instead of --changeset myname:create-table.

    databaseChangeLog:
   - changeSet:
       id: 1
       author: your.name
       changes:
       - createTable:
           tableName: test_table
           columns:
           - column:
               name: test_id
               type: INT
               constraints:
                   primaryKey:  true
                   nullable:  false
           - column:
               name: test_column
               type: INT
    {
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "1",
        "author": "your.name",
        "changes": [
          {
            "createTable": {
              "tableName": "test_table",
              "columns": [
                {
                  "column": {
                    "name": "test_id",
                    "type": "INT",
                    "constraints": {
                      "primaryKey": true,
                      "nullable": false
                    }
                  }
                },
                {
                  "column": {
                    "name": "test_column",
                    "type": "INT"
                  }
                }
              ]
            }
          }
        ]
      }
    }
  ]
}
    <?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="your.name">
        <createTable tableName="test_table">
            <column name="test_id" type="int">
                <constraints primaryKey="true" nullable="false" />
            </column>
            <column name="test_column" type="int"/>
        </createTable>
    </changeSet>

</databaseChangeLog>
  2. Navigate to your project folder in the CLI and run the Liquibase status command to see whether the connection is successful:
    3. 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:

    4 changesets have not been applied to <your_connection_url>
Liquibase command 'status' was executed successfully.
  3. Inspect the deployment SQL with the update-sql command:
    4. liquibase update-sql --changelog-file=<changelog.xml>

    If the SQL that Liquibase generates isn't what you expect, you should review your changelog file and make any necessary adjustments.

  4. Then execute these changes to your database with the update command:
    5. 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.
  5. From a database UI tool, ensure that your database contains the test_table object 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.

Related links