Using Liquibase with Google BigQuery Pro
The Liquibase BigQuery Pro 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.
Verified database versions
-
2.13.6+
Prerequisites
- Introduction to Liquibase: Dive into Liquibase concepts.
- Install Liquibase: Download Liquibase on your machine.
- Ensure Java is installed: Liquibase requires Java to run. If you used the Liquibase Installer, Java is included automatically. Otherwise, you must install Java manually.
- Get Started with Liquibase: Learn how to use Liquibase with an example database.
- Design Your Liquibase Project: Create a new Liquibase project folder and organize your changelogs.
- How to Apply Your Liquibase Pro License Key: If you use Liquibase Pro, activate your license.
Install drivers
CLI users
liquibase/extensions
directory, so you don't need to install it yourself.
Maven users
To use Liquibase with Maven, 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:
- Maven: google-cloud-bigquery (latest version)
- Google: JDBC driver (all of the JAR files in the BigQuery JDBC ZIP file, under "Current JDBC driver")
- (Deprecated) Liquibase: OSS extension JAR (
liquibase-bigquery-{version}.jar
in the"Assets" section)
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
- Ensure your BigQuery database is configured. See BigQuery Quickstarts for more information.
-
Ensure your Liquibase Pro license key is specified. For example, in a
liquibase.properties
file (defaults file):licenseKey: zQl8kNZjZgSp9LvqWQFAtGwiHrpg97UyAfQrNSiJQBCDH8FQPdDzANUpIe4Bj3CZA2IXgDBaoYZFvMw0E/s4JcECB3/A6jO+...
- 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: - 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
, or3
) as the value ofOAuthType
. This specifies how the driver obtains or provides the credentials for OAuth 2.0 authentication. - Log into Google Cloud Console (console.cloud.google.com) with administrator ID.
- 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.
- 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 theOAuthServiceAcctEmail
. - To get the Private Service Keys for the Service Principal, select Service Accounts in the left navigation menu.
- 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.
- Select the KEYS tab at the top of the panel. Any existing keys will be displayed at the bottom of the panel.
- Create a new key by clicking ADD KEY and then Create new key.
- 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.
- Enter the Private key password and note the value for use later.
- Save the Private key and refer to it by its local path in the
OAuthPvtKeyPath
property of the JDBC URL.
url: jdbc:bigquery://<Host>:<Port>;ProjectId=<Project>;DefaultDataset=<DatasetId>;OAuthType=<AuthValue>;<Property1>=<Value1>;<Property2>=<Value2>;...
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:
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
-
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. - Navigate to your project folder in the CLI and run the Liquibase
status
command to see whether the connection is successful: - Inspect the deployment SQL with the
update-sql
command: - Then execute these changes to your database with the
update
command: - From a database UI tool, ensure that your database contains the
test_table
object you added along with the DATABASECHANGELOG table and DATABASECHANGELOGLOCK table.
--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>
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.
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.
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.
Now you're ready to start making deployments with Liquibase!