Using Liquibase with BigQuery
Google BigQuery is a fully managed analytics data warehouse. For more information, see BigQuery Documentation.
Supported versions
- 2.13.6+
Prerequisites
- Introduction to Liquibase – Dive into Liquibase concepts.
- Install Liquibase – Download Liquibase on your machine.
- 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
To use Liquibase and BigQuery, you need several JAR files:
- All the JAR files in the BigQuery JDBC ZIP file (under "Current JDBC driver")
- The Liquibase extension for Google BigQuery (liquibase-bigquery-{version}.jar)
Place your JAR
file(s) in the liquibase/lib
directory.
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.20.0</version>
</dependency>
Test your connection
- 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: -
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 JDBC format: - BigQuery: Authenticating with a service account key file
- Google: Using OAuth 2.0 for Server to Server Applications
bq show bigquery-public-data:samples.shakespeare
url: jdbc:bigquery://https://googleapis.com/bigquery/v2:443/<dbname>;ProjectId=<STR>;OAuthType=<INT>;
Specify the name of your database in place of dbname
. Specify the ID of your BigQuery project as the
value of ProjectId
. Specify your BigQuery authentication method as the value of
OAuthType
. Click on the following tabs to see example JDBC URLs for each authentication type:
OAuthType=0
(Google Services)
Requires the options OAuthServiceAcctEmail
and OAuthPvtKeyPath
in your url
property. For example:
jdbc:bigquery://https://googleapis.com/bigquery/v2:443/myDatabase;
ProjectId=myProject;
OAuthType=0;
OAuthServiceAcctEmail=lbtest@bq123.iam.gserviceaccount.com;
OAuthPvtKeyPath=C:\path\serviceKey.p12;
For more information, see:
OAuthType=1
(Google User Account)
Requires your user account credentials. For example:
jdbc:bigquery://https://googleapis.com/bigquery/v2:443/myDatabase;
ProjectId=myProject;
OAuthType=1;
For more information, see Google: Authenticate installed apps with user accounts.
OAuthType=2
(Google Authorization Server Access Token)
Requires the options OAuthAccessToken
, OAuthRefreshToken
,
OAuthClientId
, and OAuthClientSecret
in your url
property. For
example:
jdbc:bigquery://https://googleapis.com/bigquery/v2:443/myDatabase;
ProjectId=myProject;
OAuthType=2;
OAuthAccessToken=a25c7cfd36214f94a79d;
OAuthRefreshToken=2kl0Qvuw9qt4abia54qga5t97;
OAuthClientId=22n6627g243322f7;
OAuthClientSecret=cDE+F2g3Hcjk4K5lazM;
For more information, see:
OAuthType=3
(Application Default Credentials)
For example:
jdbc:bigquery://https://googleapis.com/bigquery/v2:443/myDatabase;
ProjectId=myProject;
OAuthType=3;
For more information, see Google: Authenticating as a service account.
Tip: To
apply a Liquibase Pro key to your project, add the
following property to the Liquibase properties file:
licenseKey: <paste code here>
- Create a text file called changelog
(
.xml
,.sql
,.json
, or.yaml
) in your project directory and add a changeset. - Navigate to your project folder in the CLI and run the Liquibase status command to see whether the connection is successful:
- Inspect the SQL with the update-sql command. Then make changes to your database with the update command.
- From a database UI tool, ensure that your database contains the
test_table
you added along with the DATABASECHANGELOG table and DATABASECHANGELOGLOCK table.
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.
<?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="Liquibase">
<createTable tableName="test_table">
<column name="test_id" type="int">
<constraints primaryKey="true"/>
</column>
<column name="test_column" type="varchar"/>
</createTable>
</changeSet>
</databaseChangeLog>
SQL example
-- liquibase formatted sql
-- changeset liquibase:1
CREATE TABLE test_table (test_id INT, test_column VARCHAR(255), PRIMARY KEY (test_id))
Tip: Formatted
SQL changelogs generated from Liquibase versions before 4.2 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: Liquibase
changes:
- createTable:
tableName: test_table
columns:
- column:
name: test_column
type: INT
constraints:
primaryKey: true
nullable: false
JSON example
{
"databaseChangeLog": [
{
"changeSet": {
"id": "1",
"author": "Liquibase",
"changes": [
{
"createTable": {
"tableName": "test_table",
"columns": [
{
"column": {
"name": "test_column",
"type": "INT",
"constraints": {
"primaryKey": true,
"nullable": false
}
}
}
]
}
}
]
}
}
]
}
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_jdbc_url>
Liquibase command 'status' was executed successfully.
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.
Now you're ready to start making deployments with Liquibase!
Related links
Created: April 26, 2023