DataOps - Liquibase to Manage Changes in Snowflake
What is this about?
Well, as the title implies, I will show how to set up Liquibase to manage changes in Snowflake as part of a DataOps practice. Why? Because I am going deep-dive into DataOps around Snowflake and I’ve been looking for tooling to implement this philosophy on data management. The very first thing I want to do is to enable database change management.
In case you are new to the term, database change management is change management applied to a database model: version control, change approvals and automated testing and deployment. This is very common nowadays for software deployments with CI/CD and even with infrastructure within the IaC paradigm, but it is not common to find it applied to databases.
After much research, I’ve determined that Liquibase is the right tool for me. It is open source, with all the pros and cons, but it also has an option for a PRO paid version. It is constantly evolving and already supports a wide variety of database systems from well-known brands like Oracle and Teradata to the newest Casandra and Snowflake.
This is just a proof of concept, so I’ll keep it as simple as possible just to demonstrate how the whole setup works once it’s in place.
Let’s do it
I will be following the instructions found in this Liquibase tutorial. I found it effective as a guide but not enough for me to get a working solution, so I mixed in the instructions found in the Liquibase PostgreSQL tutorial, which recommended that I use Maven for my setup. <rant>Well, the truth is that after several hours trying, I gave up on the basic tutorial, as I was unable to figure it out. The documentation is very poor and, as I see it, it relies a lot on Java project setup know-how context, which I don’t have. I must say, though, that I didn’t reach out to Liquibase themselves or asked in community forums, but I’d expect an integration to be easier to setup.</rant>
While Liquibase is based on XML files to define the changes to be applied on the database, there is also the option of using a formatted SQL changelog file. Being a DBA, I obviously chose the plain SQL option since it’s easier for me to implement, but the XML changelog file is surely a better option for a production environment.
Initial setup
The very first thing I did was install Maven and Liquibase. I followed the instructions provided here. Don’t forget to always check for the newest version.
Maven
Maven installation is quite straightforward:
Maven installation commands
# Download the Maven binaries
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ curl https://ftp.cixug.es/apache/maven/maven-3/3.8.1/binaries/apache-maven-3.8.1-bin.tar.gz -O apache-maven-3.8.1-bin.tar.gz
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 9313k 100 9313k 0 0 9322k 0 --:--:-- --:--:-- --:--:-- 9322k
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ curl https://downloads.apache.org/maven/maven-3/3.8.1/binaries/apache-maven-3.8.1-bin.tar.gz.sha512 -O apache-maven-3.8.1-bin.tar.gz.sha512
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 128 100 128 0 0 335 0 --:--:-- --:--:-- --:--:-- 335
# Compare the SHA512 hash of the downloaded file with the hash provided in the repository to ensure that the file has not been tampered with.
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ cat apache-maven-3.8.1-bin.tar.gz.sha512
0ec48eb515d93f8515d4abe465570dfded6fa13a3ceb9aab8031428442d9912ec20f066b2afbf56964ffe1ceb56f80321b50db73cf77a0e2445ad0211fb8e38d
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ sha512sum apache-maven-3.8.1-bin.tar.gz
0ec48eb515d93f8515d4abe465570dfded6fa13a3ceb9aab8031428442d9912ec20f066b2afbf56964ffe1ceb56f80321b50db73cf77a0e2445ad0211fb8e38d apache-maven-3.8.1-bin.tar.gz
# Un-tar the file into a previously created directory. It is not necessary for it to be under /opt.
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ sudo tar xzf apache-maven-3.8.1-bin.tar.gz --directory /opt/maven
# After adding maven to path in bash_profile PATH=$PATH:/home/jose/.local/bin:/opt/liquibase:/opt/maven/apache-maven-3.8.1/bin
# check that it is working
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ mvn -v
Apache Maven 3.8.1 (05c21c65bdfed0f71a2f2ada8b84da59348c4c5d)
Maven home: /opt/maven/apache-maven-3.8.1
Java version: 11.0.11, vendor: Ubuntu, runtime: /usr/lib/jvm/java-11-openjdk-amd64
Default locale: en_US, platform encoding: UTF-8
OS name: "linux", version: "5.8.0-59-generic", arch: "amd64", family: "unix"
Liquibase
Given that I am running this POC in my Ubuntu Linux sandbox, the only option for installing Liquibase I have is to simply download the CLI from the downloads page and, optionally, opt in for a temporary PRO license. Once the file was downloaded, I simply un-tarred it onto /opt/liquibase and added the directory to the PATH environment variable. Once this was done, I downloaded the de Snowflake JDBC driver and the “Liquibase to Snowflake extension” and added the jar files to the Liquibase lib directory, like so:
Liquibase installation commands
# Un-tar Liquibase to /opt/liquibase<br>jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ sudo tar xzf /Pythian/Pythian-internal/liquibase/liquibase-4.4.0.tar.gz --directory /opt/liquibase
<br>#Download Snowflake JDBC driver. A newer version may be available so always look for the latest one.
jose@flask:/Pythian/Pythian-internal/liquibase$ curl https://repo1.maven.org/maven2/net/snowflake/snowflake-jdbc/3.9.2/snowflake-jdbc-3.9.2.jar --output snowflake-jdbc-3.9.2.jar
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 29.2M 100 29.2M 0 0 14.1M 0 0:00:02 0:00:02 --:--:-- 14.1
jose@flask:/Pythian/Pythian-internal/liquibase$ curl https://repo1.maven.org/maven2/net/snowflake/snowflake-jdbc/3.9.2/snowflake-jdbc-3.9.2.jar.md5 --output snowflake-jdbc-3.9.2.jar.md5
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 32 100 32 0 0 102 0 --:--:-- --:--:-- --:--:-- 102
# Verify MD5 hash matches
jose@flask:/Pythian/Pythian-internal/liquibase$ cat snowflake-jdbc-3.9.2.jar.md5
efbdf01dfbd177538a6f6bb57c5f8942
jose@flask:/Pythian/Pythian-internal/liquibase$ md5sum snowflake-jdbc-3.9.2.jar
efbdf01dfbd177538a6f6bb57c5f8942 snowflake-jdbc-3.9.2.jar
#Download the lastest Liquibase Snowflake extension
jose@flask:/Pythian/Pythian-internal/liquibase$ curl https://github.com/liquibase/liquibase-snowflake/releases/download/liquibase-snowflake-4.4.0/liquibase-snowflake-4.4.0.jar --output liquibase-snowflake-4.4.0.jar
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 637 100 637 0 0 1572 0 --:--:-- --:--:-- --:--:-- 1568
# Copy the files to the lib directory for Liquibase
cp *.jar /opt/liquibase/lib
# Change the PATH environment variable
export PATH=$PATH:/opt/liquibase
# Finally check the installation
jose@flask:/Pythian/Pythian-internal/liquibase$ liquibase -v
####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 13:22:44 (version 4.4.0 #11 built at 2021-06-09 16:36+0000)
Running Java under /usr/lib/jvm/java-11-openjdk-amd64 (Version 11.0.11)
Liquibase Version: 4.4.0
Liquibase Community 4.4.0 by Datical
Snowflake
For this POC, I created a brand new database and a Liquibase-specific user in a Snowflake trial account. If you don’t have one, go to https://signup.snowflake.com/ and get yours. For the purpose of this POC, it doesn’t really matter which cloud provider you choose, the region or the Snowflake edition.
Snowflake code to create necessary objects
-- Create database and schema
create schema pro_lb.lb_schema;
-- Create Liquibase role, user and grant permissions. Set a proper password, please
create role lb_role;
create user lb_user identified by '<password>';
alter user lb_user set default_role=lb_role;
grant role lb_role to user lb_user;
grant ownership on database pro_lb to role lb_role;
grant usage on warehouse compute_wh to role lb_role;
-- Setting default warehouse and namespace for the LB user because the extension does not seem to obey the parameters in the JDBC connection URL
alter user lb_user set default_warehouse=compute_wh;
alter user lb_user set DEFAULT_NAMESPACE=pro_lb.lb_schema;
Please note the three highlighted lines at the end of the block code. During my initial testing, I found that the Liquibase extension was not obeying the warehouse and schema parameters in the URL connection, so I opted for this approach to work around this issue.
Configuration
Once we have all the software in place, it is time to prepare the configuration files.
This is now a Maven project, so the very first thing to have is a pom.xml file. This is mine:
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.my-group.app</groupId>
<artifactId>LiquiSnowFlake-app</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
<pluginManagement>
<plugins>
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>4.4.0</version>
<configuration>
<propertyFile>/Pythian/Pythian-internal/liquibase/Snowflake/liquibase.properties</propertyFile>
<promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
</configuration>
<dependencies>
<dependency>
<groupId>net.snowflake</groupId>
<artifactId>snowflake-jdbc</artifactId>
<version>3.13.5</version>
</dependency>
<dependency>
<groupId>org.liquibase.ext</groupId>
<artifactId>liquibase-snowflake</artifactId>
<version>4.4.0</version>
</dependency>
</dependencies>
</plugin>
</pluginManagement>
</build>
Next, it’s time to define the Liquibase properties file. I kept mine simple, with no reference database or Liquibase hub enabled. It looked like this after redaction:
changeLogFile=changelog.SF.sql
driver= net.snowflake.client.jdbc.SnowflakeDriver
classpath=/opt/liquibase/lib/*
url= jdbc:snowflake://xxxxxx.eu-west-1.snowflakecomputing.com/?db=PRO_LB&schema=LB_SCHEMA
username: lb_user
password: *****************
logLevel: INFO
logFile: liquibase.log
liquibase.hub.mode=off
liquibase.changelogSchemaName = LB_SCHEMA
Note the schema used is LB_SCHEMA. In my testing, I was unable to have Liquibase working on the default PUBLIC schema so I created an ad-hoc schema for my testing. Using a specific schema for our data in Snowflake, or any other database, is a good practice so I won’t complain much about this one.
Here I hit another problem, for which I opened an issue in the Liquibase Snowflake integration repo. The moral of my story is if you are using a template configuration file for a Liquibase integration, always use the one included in the Liquibase version you downloaded. It turned out that I was using an old version of the template because I downloaded it from the link provided in the tutorial. You can imagine I was not happy when I discovered what happened.
Changelog file
In the Liquibase properties file, there is the changeLogFile parameter pointing to a SQL file. This is a formatted file that accepts plain SQL to be executed on our database.
I started with a very simple one:
--liquibase formatted sql
--changeset jrodriguez:1
CREATE TABLE "DEPARTMENT" ("ID" INTEGER, "NAME" STRING, "ACTIVE" BOOLEAN);
Mind you, Liquibase tracks changes in this file and keeps a history of the file checksum, so changing this file in any other way than adding change sets to it will end up with Liquibase throwing up errors.
Of course, this file must be under a change control system like Git specially because we are here to implement DataOps and tracking changes is fundamental to it.
First run
At this point we are ready to launch Liquibase using Maven. Just for this run, I used the ‘-e’ parameter for Maven to provide a more verbose output:
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ mvn -e liquibase:update
[INFO] Error stacktraces are turned on.
[INFO] Scanning for projects...
[INFO]
[INFO] ----------------< com.my-group.app:LiquiSnowFlake-app >-----------------
[INFO] Building LiquiSnowFlake-app 1.0-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] --- liquibase-maven-plugin:4.4.0:update (default-cli) @ LiquiSnowFlake-app ---
[INFO] ------------------------------------------------------------------------
[INFO] there are no resolved artifacts for the Maven project.
[INFO] there are no resolved artifacts for the Maven project.
[project, pluginDescriptor]
[INFO] Parsing Liquibase Properties File
[INFO] File: /Pythian/Pythian-internal/liquibase/Snowflake/liquibase.properties
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/test-classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] 'logLevel' in properties file is not being used by this task.
[INFO] 'classpath' in properties file is not being used by this task.
[INFO] 'liquibase.changelogSchemaName' in properties file is not being used by this task.
[INFO] 'logFile' in properties file is not being used by this task.
[INFO] 'liquibase.hub.mode' in properties file is not being used by this task.
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO]
[INFO] Liquibase Community 4.4.0 by Datical
[INFO] ####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 13:34:23 (version 4.4.0 #11 built at 2021-06-09 16:36+0000)
[INFO] Set default schema name to LB_SCHEMA
[INFO] Parsing Liquibase Properties File /Pythian/Pythian-internal/liquibase/Snowflake/liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:snowflake://XXXXXX.eu-west-1.snowflakecomputing.com/?db=PRO_LB&schema=LB_SCHEMA
[INFO] Successfully acquired change log lock
[INFO] Creating database history table with name: DATABASECHANGELOG
[INFO] Reading from DATABASECHANGELOG
[INFO] Successfully released change log lock
[INFO] Successfully acquired change log lock
Skipping auto-registration
[WARNING] Skipping auto-registration
[INFO] Custom SQL executed
[INFO] ChangeSet changelog.SF.sql::1::jrodriguez ran successfully in 599ms
[INFO] Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 15.639 s
[INFO] Finished at: 2021-07-09T13:34:37+02:00
[INFO] ------------------------------------------------------------------------
NOTE: This run I am showing is not the first Maven run I executed on this host. The very first time Maven runs, it downloads a whole lot of packages and the output is quite long and noisy. Don’t be scared; it is just normal and expected.
As you can see in the output, Liquibase in this first execution is proceeding through not only the change log file but also a table called DATABASECHANGELOG. This is a table Liquibase uses to track changes and executions.
It doesn’t show it in the output, but it also creates another table called DATABASECHANGELOGLOCK. This is a simple table used by Liquibase to, you guessed it, lock executions to prevent concurrent changes to the same database.
Let’s take a look from Snowflake itself:
lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>show tables;
+-------------------------------+-----------------------+---------------+-------------+-------+---------+------------+------+-------+---------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------+
| created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time | automatic_clustering | change_tracking | search_optimization | search_optimization_progress | search_optimization_bytes | is_external |
|-------------------------------+-----------------------+---------------+-------------+-------+---------+------------+------+-------+---------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------|
| 2021-07-09 04:34:27.420 -0700 | DATABASECHANGELOG | PRO_LB | LB_SCHEMA | TABLE | | | 1 | 4096 | LB_ROLE | 1 | OFF | OFF | OFF | NULL | NULL | N |
| 2021-07-09 04:34:24.127 -0700 | DATABASECHANGELOGLOCK | PRO_LB | LB_SCHEMA | TABLE | | | 1 | 1536 | LB_ROLE | 1 | OFF | OFF | OFF | NULL | NULL | N |
| 2021-07-09 04:34:31.530 -0700 | DEPARTMENT | PRO_LB | LB_SCHEMA | TABLE | | | 0 | 0 | LB_ROLE | 1 | OFF | OFF | OFF | NULL | NULL | N |
+-------------------------------+-----------------------+---------------+-------------+-------+---------+------------+------+-------+---------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------+
3 Row(s) produced. Time Elapsed: 0.736s
lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>select * from DATABASECHANGELOG;
+----+-------------+------------------+-------------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
|----+-------------+------------------+-------------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------|
| 1 | jrodriguez | changelog.SF.sql | 2021-07-09 04:34:32.189 | 1 | EXECUTED | 8:8fa51fbf01f6548ede5db1ba45b164bf | sql | | NULL | 4.4.0 | NULL | NULL | 5830471343 |
+----+-------------+------------------+-------------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
1 Row(s) produced. Time Elapsed: 0.272s
lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>select * from DATABASECHANGELOGLOCK;
+----+--------+-------------+----------+
| ID | LOCKED | LOCKGRANTED | LOCKEDBY |
|----+--------+-------------+----------|
| 1 | False | NULL | NULL |
+----+--------+-------------+----------+
1 Row(s) produced. Time Elapsed: 0.195s
lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>select * from DEPARTMENT;
+----+------+--------+
| ID | NAME | ACTIVE |
|----+------+--------|
+----+------+--------+
0 Row(s) produced. Time Elapsed: 0.124s
lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>insert into DEPARTMENT values (1,'Name1',True);
+-------------------------+
| number of rows inserted |
|-------------------------|
| 1 |
+-------------------------+
1 Row(s) produced. Time Elapsed: 0.727s
lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>commit;
+----------------------------------+
| status |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.111s
In the output above, we can see that three tables now exist in the database.
The DATABASECHANGELOG table has a single row and the DATABASECHANGELOGLOCK table has a single row showing that no locks are in place.
Finally, we can see that the DEPARTMENT table is created empty, so I added a single row to demonstrate that the data is safe in subsequent Liquibase executions.
Is my data safe?
You may be wondering why we even have to ask this question. This is my DBA mentality kicking in here. Having done some work with Python and SQLAlchemy, I’ve seen way too many cases where sample code simply destroys the entire database and recreates it from scratch. This approach may be valid for a POC or starting from scratch every time when it makes sense, but it doesn’t here.
So now I’ll add a change to the DEPARTMENT table in my changelog file:
--liquibase formatted sql
--changeset jrodriguez:1
CREATE TABLE "DEPARTMENT" ("ID" INTEGER, "NAME" STRING, "ACTIVE" BOOLEAN);
--changeset jrodriguez:2
ALTER TABLE "DEPARTMENT" ADD (TS TIMESTAMP);
At this point, I am not sure what is going to happen. Fortunately Liquibase provides a sort of dry run option: updateSQL. Let’s see what happens in our database.
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ mvn liquibase:updateSQL
[INFO] Scanning for projects...
[INFO]
[INFO] ----------------< com.my-group.app:LiquiSnowFlake-app >-----------------
[INFO] Building LiquiSnowFlake-app 1.0-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] --- liquibase-maven-plugin:4.4.0:updateSQL (default-cli) @ LiquiSnowFlake-app ---
[INFO] ------------------------------------------------------------------------
[INFO] there are no resolved artifacts for the Maven project.
[INFO] there are no resolved artifacts for the Maven project.
[project, pluginDescriptor]
[INFO] Parsing Liquibase Properties File
[INFO] File: /Pythian/Pythian-internal/liquibase/Snowflake/liquibase.properties
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/test-classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] 'logLevel' in properties file is not being used by this task.
[INFO] 'classpath' in properties file is not being used by this task.
[INFO] 'liquibase.changelogSchemaName' in properties file is not being used by this task.
[INFO] 'logFile' in properties file is not being used by this task.
[INFO] 'liquibase.hub.mode' in properties file is not being used by this task.
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO]
[INFO] Liquibase Community 4.4.0 by Datical
[INFO] ####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 13:57:18 (version 4.4.0 #11 built at 2021-06-09 16:36+0000)
[INFO] Set default schema name to LB_SCHEMA
[INFO] Char encoding not set! The created file will be system dependent!
[INFO] Output SQL Migration File: /Pythian/Pythian-internal/liquibase/Snowflake/target/liquibase/migrate.sql
[INFO] Parsing Liquibase Properties File /Pythian/Pythian-internal/liquibase/Snowflake/liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:snowflake://XXXXXX.eu-west-1.snowflakecomputing.com/?db=PRO_LB&schema=LB_SCHEMA
[INFO] Successfully acquired change log lock
[INFO] Reading from DATABASECHANGELOG
[INFO] Custom SQL executed
[INFO] ChangeSet changelog.SF.sql::2::jrodriguez ran successfully in 276ms
[INFO] Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 9.954 s
[INFO] Finished at: 2021-07-09T13:57:26+02:00
[INFO] ------------------------------------------------------------------------
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ cat /Pythian/Pythian-internal/liquibase/Snowflake/target/liquibase/migrate.sql
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: changelog.SF.sql
-- Ran at: 7/9/21, 1:57 PM
-- Against: lb_user@jdbc:snowflake://XXXXXX.eu-west-1.snowflakecomputing.com:443/
-- Liquibase version: 4.4.0
-- *********************************************************************
-- Lock Database
UPDATE DATABASECHANGELOGLOCK SET LOCKED = TRUE, LOCKEDBY = '192.168.56.102 (192.168.56.102)', LOCKGRANTED = '2021-07-09 13:57:22.225' WHERE ID = 1 AND LOCKED = FALSE;
-- Changeset changelog.SF.sql::2::jrodriguez
ALTER TABLE "DEPARTMENT" ADD (TS TIMESTAMP);
INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('2', 'jrodriguez', 'changelog.SF.sql', current_timestamp::timestamp_ntz, 2, '8:d21f005da4a750b1ae1e3f4a6046d441', 'sql', '', 'EXECUTED', NULL, NULL, '4.4.0', '5831845142');
-- Release Database Lock
UPDATE DATABASECHANGELOGLOCK SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
Nice! The SQL shown in the output implies that a lock is acquired, then our table gets it’s new column, a new entry is added into the DATABASECHANGELOG table and the lock is released. Let’s do it:
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ mvn liquibase:update
[INFO] Scanning for projects...
[INFO]
[INFO] ----------------< com.my-group.app:LiquiSnowFlake-app >-----------------
[INFO] Building LiquiSnowFlake-app 1.0-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] --- liquibase-maven-plugin:4.4.0:update (default-cli) @ LiquiSnowFlake-app ---
[INFO] ------------------------------------------------------------------------
[INFO] there are no resolved artifacts for the Maven project.
[INFO] there are no resolved artifacts for the Maven project.
[project, pluginDescriptor]
[INFO] Parsing Liquibase Properties File
[INFO] File: /Pythian/Pythian-internal/liquibase/Snowflake/liquibase.properties
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/test-classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] 'logLevel' in properties file is not being used by this task.
[INFO] 'classpath' in properties file is not being used by this task.
[INFO] 'liquibase.changelogSchemaName' in properties file is not being used by this task.
[INFO] 'logFile' in properties file is not being used by this task.
[INFO] 'liquibase.hub.mode' in properties file is not being used by this task.
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO]
[INFO] Liquibase Community 4.4.0 by Datical
[INFO] ####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 13:58:11 (version 4.4.0 #11 built at 2021-06-09 16:36+0000)
[INFO] Set default schema name to LB_SCHEMA
[INFO] Parsing Liquibase Properties File /Pythian/Pythian-internal/liquibase/Snowflake/liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:snowflake://XXXXXX.eu-west-1.snowflakecomputing.com/?db=PRO_LB&schema=LB_SCHEMA
[INFO] Successfully acquired change log lock
[INFO] Reading from DATABASECHANGELOG
[INFO] Successfully released change log lock
[INFO] Successfully acquired change log lock
Skipping auto-registration
[WARNING] Skipping auto-registration
[INFO] Custom SQL executed
[INFO] ChangeSet changelog.SF.sql::2::jrodriguez ran successfully in 480ms
[INFO] Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 25.529 s
[INFO] Finished at: 2021-07-09T13:58:35+02:00
[INFO] ------------------------------------------------------------------------
lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>desc department;
+--------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|--------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| ID | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| NAME | VARCHAR(16777216) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| ACTIVE | BOOLEAN | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| TS | TIMESTAMP_NTZ(9) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
+--------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>SELECT * FROM DATABASECHANGELOG;
+----+-------------+------------------+-------------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
|----+-------------+------------------+-------------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------|
| 1 | jrodriguez | changelog.SF.sql | 2021-07-09 04:34:32.189 | 1 | EXECUTED | 8:8fa51fbf01f6548ede5db1ba45b164bf | sql | | NULL | 4.4.0 | NULL | NULL | 5830471343 |
| 2 | jrodriguez | changelog.SF.sql | 2021-07-09 04:58:33.185 | 2 | EXECUTED | 8:d21f005da4a750b1ae1e3f4a6046d441 | sql | | NULL | 4.4.0 | NULL | NULL | 5831908914 |
+----+-------------+------------------+-------------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
2 Row(s) produced. Time Elapsed: 1.033s<br><br>lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>select * from DEPARTMENT;<br>+----+-------+--------+------+<br>| ID | NAME | ACTIVE | TS |<br>|----+-------+--------+------|<br>| 1 | Name1 | True | NULL |<br>+----+-------+--------+------+<br>1 Row(s) produced. Time Elapsed: 0.200s
Well done, Liquibase! Now we have our DEPARTMENT table with its new column and the data is still there.
Rollback
This POC wouldn’t be complete without testing rollback capabilities. Liquibase does not support an automatic rollback for formatted SQL change sets, so we have to provide the commands ourselves. In this case, I added a couple changesets to the changelog file—note the DROP TABLE statement at the end and the rollback tags for the new changes:
--liquibase formatted sql
--changeset jrodriguez:1
CREATE TABLE "DEPARTMENT" ("ID" INTEGER, "NAME" STRING, "ACTIVE" BOOLEAN);
--changeset jrodriguez:2
ALTER TABLE "DEPARTMENT" ADD (TS TIMESTAMP);
--changeset jrodriguez:3
CREATE TABLE "EMPLOYEE" ("ID" INTEGER, "NAME" STRING, "ACTIVE" BOOLEAN, "HIRE_DATE" TIMESTAMP DEFAULT CURRENT_TIMESTAMP());
-- rollback DROP TABLE "EMPLOYEE";
--changeset changeowner:4
DROP TABLE "DEPARTMENT";
-- rollback UNDROP TABLE "DEPARTMENT";
And the Liquibase output:
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ mvn liquibase:update
[INFO] Scanning for projects...
[INFO]
[INFO] ----------------< com.my-group.app:LiquiSnowFlake-app >-----------------
[INFO] Building LiquiSnowFlake-app 1.0-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] --- liquibase-maven-plugin:4.4.0:update (default-cli) @ LiquiSnowFlake-app ---
[INFO] ------------------------------------------------------------------------
[INFO] there are no resolved artifacts for the Maven project.
[INFO] there are no resolved artifacts for the Maven project.
[project, pluginDescriptor]
[INFO] Parsing Liquibase Properties File
[INFO] File: /Pythian/Pythian-internal/liquibase/Snowflake/liquibase.properties
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/test-classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] 'logLevel' in properties file is not being used by this task.
[INFO] 'classpath' in properties file is not being used by this task.
[INFO] 'liquibase.changelogSchemaName' in properties file is not being used by this task.
[INFO] 'logFile' in properties file is not being used by this task.
[INFO] 'liquibase.hub.mode' in properties file is not being used by this task.
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO]
[INFO] Liquibase Community 4.4.0 by Datical
[INFO] ####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 14:21:04 (version 4.4.0 #11 built at 2021-06-09 16:36+0000)
[INFO] Set default schema name to LB_SCHEMA
[INFO] Parsing Liquibase Properties File /Pythian/Pythian-internal/liquibase/Snowflake/liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:snowflake://XXXXXX.eu-west-1.snowflakecomputing.com/?db=PRO_LB&schema=LB_SCHEMA
[INFO] Successfully acquired change log lock
[INFO] Reading from DATABASECHANGELOG
[INFO] Successfully released change log lock
[INFO] Successfully acquired change log lock
Skipping auto-registration
[WARNING] Skipping auto-registration
[INFO] Custom SQL executed
[INFO] ChangeSet changelog.SF.sql::3::jrodriguez ran successfully in 491ms
[INFO] Custom SQL executed
[INFO] ChangeSet changelog.SF.sql::4::changeowner ran successfully in 396ms
[INFO] Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 15.974 s
[INFO] Finished at: 2021-07-09T14:21:18+02:00
[INFO] ------------------------------------------------------------------------
lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>show tables;
+-------------------------------+-----------------------+---------------+-------------+-------+---------+------------+------+-------+---------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------+
| created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time | automatic_clustering | change_tracking | search_optimization | search_optimization_progress | search_optimization_bytes | is_external |
|-------------------------------+-----------------------+---------------+-------------+-------+---------+------------+------+-------+---------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------|
| 2021-07-09 04:34:27.420 -0700 | DATABASECHANGELOG | PRO_LB | LB_SCHEMA | TABLE | | | 4 | 4608 | LB_ROLE | 1 | OFF | OFF | OFF | NULL | NULL | N |
| 2021-07-09 04:34:24.127 -0700 | DATABASECHANGELOGLOCK | PRO_LB | LB_SCHEMA | TABLE | | | 1 | 1536 | LB_ROLE | 1 | OFF | OFF | OFF | NULL | NULL | N |
| 2021-07-09 05:21:14.427 -0700 | EMPLOYEE | PRO_LB | LB_SCHEMA | TABLE | | | 0 | 0 | LB_ROLE | 1 | OFF | OFF | OFF | NULL | NULL | N |
+-------------------------------+-----------------------+---------------+-------------+-------+---------+------------+------+-------+---------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------+
3 Row(s) produced. Time Elapsed: 0.732s
lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>desc employee;
+-----------+-------------------+--------+-------+---------------------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|-----------+-------------------+--------+-------+---------------------+-------------+------------+-------+------------+---------+-------------|
| ID | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| NAME | VARCHAR(16777216) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| ACTIVE | BOOLEAN | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| HIRE_DATE | TIMESTAMP_NTZ(9) | COLUMN | Y | CURRENT_TIMESTAMP() | N | N | NULL | NULL | NULL | NULL |
+-----------+-------------------+--------+-------+---------------------+-------------+------------+-------+------------+---------+-------------+
4 Row(s) produced. Time Elapsed: 0.426s
Oops! I didn’t want to drop that table, please help!
No problem; let’s rollback. There are three options to rollback a changeset: to tag, to date or by count. Any of these options will rollback all the changesets found in the changelog file. You will have to use the pro version of Liquibase to have the fine grained option of rolling back a given changeset.
So I’ll opt for the count rollback using the command line parameter liquibase.rollbackCount.
jose@flask:/Pythian/Pythian-internal/liquibase/Snowflake$ mvn liquibase:rollback -Dliquibase.rollbackCount=1
[INFO] Scanning for projects...
[INFO]
[INFO] ----------------< com.my-group.app:LiquiSnowFlake-app >-----------------
[INFO] Building LiquiSnowFlake-app 1.0-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] --- liquibase-maven-plugin:4.4.0:rollback (default-cli) @ LiquiSnowFlake-app ---
[INFO] ------------------------------------------------------------------------
[INFO] there are no resolved artifacts for the Maven project.
[INFO] there are no resolved artifacts for the Maven project.
[project, pluginDescriptor]
[INFO] Parsing Liquibase Properties File
[INFO] File: /Pythian/Pythian-internal/liquibase/Snowflake/liquibase.properties
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/test-classes does not exist
[INFO] Configured classpath location file:/Pythian/Pythian-internal/liquibase/Snowflake/target/classes does not exist
[INFO] 'logLevel' in properties file is not being used by this task.
[INFO] 'classpath' in properties file is not being used by this task.
[INFO] 'liquibase.changelogSchemaName' in properties file is not being used by this task.
[INFO] 'logFile' in properties file is not being used by this task.
[INFO] 'liquibase.hub.mode' in properties file is not being used by this task.
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO]
[INFO] Liquibase Community 4.4.0 by Datical
[INFO] ####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 18:18:01 (version 4.4.0 #11 built at 2021-06-09 16:36+0000)
[INFO] Set default schema name to LB_SCHEMA
[INFO] Parsing Liquibase Properties File /Pythian/Pythian-internal/liquibase/Snowflake/liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:snowflake://xxxxxx.eu-west-1.snowflakecomputing.com/?db=PRO_LB&schema=LB_SCHEMA
[INFO] Successfully acquired change log lock
[INFO] Reading from DATABASECHANGELOG
[INFO] Successfully released change log lock
[INFO] Successfully acquired change log lock
Skipping auto-registration
[WARNING] Skipping auto-registration
Rolling Back Changeset:changelog.SF.sql::4::changeowner
[INFO] Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 15.561 s
[INFO] Finished at: 2021-07-09T14:24:35+02:00
[INFO] ------------------------------------------------------------------------
And checking the database, we can see the DEPARTMENT table is back:
lb_user#COMPUTE_WH@PRO_LB.LB_SCHEMA>show tables;
+-------------------------------+-----------------------+---------------+-------------+-------+---------+------------+------+-------+---------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------+
| created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time | automatic_clustering | change_tracking | search_optimization | search_optimization_progress | search_optimization_bytes | is_external |
|-------------------------------+-----------------------+---------------+-------------+-------+---------+------------+------+-------+---------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------|
| 2021-07-09 04:34:27.420 -0700 | DATABASECHANGELOG | PRO_LB | LB_SCHEMA | TABLE | | | 4 | 4608 | LB_ROLE | 1 | OFF | OFF | OFF | NULL | NULL | N |
| 2021-07-09 04:34:24.127 -0700 | DATABASECHANGELOGLOCK | PRO_LB | LB_SCHEMA | TABLE | | | 1 | 1536 | LB_ROLE | 1 | OFF | OFF | OFF | NULL | NULL | N |
| 2021-07-09 04:34:31.530 -0700 | DEPARTMENT | PRO_LB | LB_SCHEMA | TABLE | | | 0 | 0 | LB_ROLE | 1 | OFF | OFF | OFF | NULL | NULL | N |
| 2021-07-09 05:21:14.427 -0700 | EMPLOYEE | PRO_LB | LB_SCHEMA | TABLE | | | 0 | 0 | LB_ROLE | 1 | OFF | OFF | OFF | NULL | NULL | N |
+-------------------------------+-----------------------+---------------+-------------+-------+---------+------------+------+-------+---------+----------------+----------------------+-----------------+---------------------+------------------------------+---------------------------+-------------+
Final thoughts
As you have seen, Liquibase integrates quite well with Snowflake and, more importantly, it allows us to maintain our database schema à la DataOps way, meaning that these changes can be integrated in the company’s CI/CD pipelines and will be fully under version control.
Now, this is only a POC and a lot of stuff has been left out—like generating the changesets out of a reference database or starting work on an existing database with the auto generate changelog feature—but it’s a starting point.
Also simply deploying Liquibase onto our architecture does not enable DataOps. Even “simply” in this statement is a big over-simplification of the process, but I strongly believe that these kinds of tools are a good foundation to start moving toward the future of loving your data automatically.
