Snowflake

Snowflake

  • Verified Versions: 3.50, 8.3
  • Maintainer: Redgate

Supported Versions and Support Levels

Driver

Item Details
URL format jdbc:snowflake://account.snowflakecomputing.com/?db=database&warehouse=warehouse&role=role
(optionally &schema=schema to specify current schema)
Ships with Flyway Command-line Yes
Maven Central coordinates net.snowflake:snowflake-jdbc
Supported versions 3.6.23 and later
Default Java class net.snowflake.client.jdbc.SnowflakeDriver

Java Usage

Snowflake support is a separate dependency for Flyway and will need to be added to your Java project to access these features. Snowflake is found within the flyway-database-snowflake plugin module.

Maven

Redgate

<dependency>
    <groupId>com.redgate.flyway</groupId>
    <artifactId>flyway-database-snowflake</artifactId>
</dependency>

Open Source

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-snowflake</artifactId>
</dependency>

Gradle

Redgate

dependencies {
    implementation "com.redgate.flyway:flyway-database-snowflake"
}

Open Source

dependencies {
    implementation "org.flywaydb:flyway-database-snowflake"
}

SQL Script Syntax

Compatibility

  • DDL exported by the Snowflake web GUI can be used unchanged in a Flyway migration
  • Any SQL script executed by Flyway, can be executed by the Snowflake web GUI (after the placeholders have been replaced)
  • The Snowflake driver requires Java 8+. There is no support from Snowflake for Java 7 users.

Example

/* Single line comment */
CREATE TABLE test_data (
  value VARCHAR(25) NOT NULL PRIMARY KEY
);

/*
Multi-line
comment
*/

-- Sql-style comment

-- Placeholder
INSERT INTO ${tableName} (name) VALUES ('Mr. T');

Key-based Authentication

Snowflake JDBC supports key-based authentication. To use this, you will need to:

  • ensure you are using at least v3.11 of the Snowflake JDBC driver (Flyway currently ships with this version)
  • generate a public/private key pair
  • assign the public key to the relevant Snowflake user account using ALTER USER - for complete instructions on these steps, refer to Snowflake's documentation

Finally, amend your JDBC connection string with the extra parameters to enable key-based auth and to refer to the location of the private key: authenticator=snowflake_jwt&private_key_file=<absolute-location-of-pem-file>.

Limitations

  • Parallel migrations as described here are unavailable in Snowflake. You can track the status of this feature in our GitHub issues here.
  • Users using Java 16 or above, which includes the JRE shipped within Java Command Line, will need to add the following JVM argument to JAVA_ARGS --add-opens java.base/java.lang=ALL-UNNAMED. This can be done via the command line or environment variables This is due to a change in the Java 16 runtime which causes an error within the Snowflake JDBC driver.

Alternatively, you can instruct the JDBC driver to do this instead of altering the environment by postpending your JDBC connection string with &JDBC_QUERY_RESULT_FORMAT=JSON


Didn't find what you were looking for?