issue with using liquibase and snowflake db

0
votes

I'm kicking the tires on Snowflake DB and wanted to see how it works with Liquibase. I'm running into an issue when creating the databasechangelog table as Snowflake has a timestamp field but Liquibase is trying to issue SQL with data type of datetime.

I followed the idea on http://www.liquibase.org/databases.html and just created the databasechangelog table outside of liquibase deployment.

CREATE TABLE bruces.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED timestamp NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255))

And then I started the liquibase deployment via maven.

WARNING 1/24/17 5:03 PM: liquibase: Unknown database: Snowflake
[INFO] Executing on Database: jdbc:snowflake://*****.snowflakecomputing.com/?db=BRUCE_DB&warehouse=BRUCE_WH
INFO 1/24/17 5:03 PM: liquibase: Successfully acquired change log lock
INFO 1/24/17 5:03 PM: liquibase: Creating database history table with name: bruces.DATABASECHANGELOG
INFO 1/24/17 5:03 PM: liquibase: Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 15.432 s
[INFO] Finished at: 2017-01-24T17:03:56-06:00
[INFO] Final Memory: 16M/305M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.4.0:update (default) on project snowflake.snowflake_app: Error setting up or running Liquibase: SQL compilation error:
[ERROR] Unsupported data type 'TOK_DATETIME'. [Failed SQL: CREATE TABLE bruces.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255))]
[ERROR] -> [Help 1]

It would appear that liquibase can't find the databasechangelog table so it tries to create it and fails.

3
liquibasesnowflake-cloud-data-platform
I used the History mechanism of Snowflake to see the queries that have been executed and see that it executes "show tables like 'DATABASECHANGELOG' in account", which returns 1 record. But it goes ahead and tries to create the table anyway. - bruce szalwinski
I ended up writing a Liquibase extension for Snowflake. Available at github.com/CDKGlobal/liquibase-snowflake. - bruce szalwinski

3 Answers

1
votes

Not knowing anything at all about SnowflakeDB, I would suggest that the best approach is to write a new database implementation for SnowflakeDB. SQL dialects vary quite a bit, and if you are having issues early, you are likely just going to run into more issues as you move along.

1
votes

The problem is that today Snowflake does not support the DATETIME data type. It does support DATE and TIMESTAMP, which are standard SQL.

There's an ongoing effort to add it to Snowflake, will ask the team working on it to add updates here.

1
votes

I see that's added. Snowflake however, converts the data type to TIMESTAMP_NTZ while creating the attribute. Try using the Snowflake extension and creating the table using the XML and provide either TIMESTAMP_NTZ or TIMESTAMP_NTZ(9) or DATETIME. All seems to be the same in Snowflake -

enter image description here