CODEWITHSUNDEEP
liquibasesnowflake-cloud-data-platform
bruce szalwinski
bruce szalwinski

Reputation: 742

issue with using liquibase and snowflake db

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.

Upvotes: 1

Views: 1256

Answers (3)

Shaounak Nasikkar
Shaounak Nasikkar

Reputation: 314

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

Upvotes: 1

Marcin Zukowski
Marcin Zukowski

Reputation: 4729

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.

Upvotes: 1

SteveDonie
SteveDonie

Reputation: 9016

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.

Upvotes: 1

Related Questions