Snowflake support

[mbreault]

Snowflake is a hosted data warehousing solution.

Home: https://www.snowflake.net/
Driver: https://repo1.maven.org/maven2/net/snowflake/snowflake-jdbc/

Currently get: ERROR: Unsupported Database: Snowflake 2.5

[alexlevene]

Added support for Snowflake to a fork of v4.2.0 and submitted a pull request #1776. We've put it through its paces in development, but it would be great to get some more feedback. Please review.

[bobtiernay-okta]

Any update on this? Would be great to have Snowflake support.

[rjungwirth]

I took over ownership of pr #1776 when alex left the company. We've been using this feature branch ever since and it's working flawlessly. Please re-consider it for inclusion.

If I were to port this code to the V6 branch would it have a better chance?

[juliahayward]

We're going to talk to Snowflake about the commercial details of a testing account for us. I'll let you know the outcome of that; if good, then a port to v6 would be very welcome.

[blian7432]

Hi @juliahayward Do you have an ETA of Snowflake support?

[juliahayward]

Not yet, we're still setting up a meeting with them.

[juliahayward]

We're now working on Snowflake for 6.1. I have a question for people who are using it; in a new database connection without a schema specified in the URL, SELECT CURRENT_SCHEMA() returns null; what would you all expect Flyway's behavior to be:

• you would specify a current schema in the connection URL
• you would expect Flyway to default to PUBLIC until specified otherwise by USE SCHEMA xxx
• you would fully qualify all object names in migration scripts?

[rjungwirth]

The second option, always assume public unless specified on the URL or by a use schema command in a migration script. From: Julia Hayward <notifications@github.com> Reply-To: flyway/flyway <reply@reply.github.com> Date: Thursday, November 7, 2019 at 8:03 AM To: flyway/flyway <flyway@noreply.github.com> Cc: Russell Jungwirth <jungwirth@healthgrades.com>, Comment <comment@noreply.github.com> Subject: Re: [flyway/flyway] Snowflake support (#1735) We're now working on Snowflake for 6.1. I have a question for people who are using it; in a new database connection without a schema specified in the URL, SELECT CURRENT_SCHEMA() returns null; what would you all expect Flyway's behavior to be: * you would specify a current schema in the connection URL * you would expect Flyway to default to PUBLIC until specified otherwise by USE SCHEMA xxx * you would fully qualify all object names in migration scripts? — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#1735?email_source=notifications&email_token=AHVIEDVYCD5UVRCUYSVMT43QSQN2RA5CNFSM4DV3A5CKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEDMP7XY#issuecomment-551092191>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AHVIEDWF4CXWAHCCTW3DT2LQSQN2RANCNFSM4DV3A5CA>.

[bobtiernay-okta]

This is what we've done to get this to work:

/**
 * Snowflake implementation of a Flyway Connection.
 */
@SuppressWarnings("rawtypes")
@SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")
public class SnowflakeConnection extends Connection<SnowflakeDatabase> {

    private static final Log LOG = LogFactory.getLog(SnowflakeConnection.class);

    private final String originalRole;

    public SnowflakeConnection(Configuration configuration, SnowflakeDatabase database, java.sql.Connection connection, boolean originalAutoCommit) {
        super(configuration, database, connection, originalAutoCommit);
        LOG.debug("Creating new SnowflakeConnection");

        try {
            this.originalRole = jdbcTemplate.queryForString("SELECT CURRENT_ROLE()");
        } catch (SQLException e) {
            throw new FlywaySqlException("Unable to determine current role", e);
        }
    }

    //
    // NOTE: the following methods are overridden to provide an implementation
    //

    @Override
    protected String getCurrentSchemaNameOrSearchPath() throws SQLException {
        String result = jdbcTemplate.queryForString("SELECT CURRENT_SCHEMA()");
        LOG.debug("Current Snowflake schema is " + (result != null ? result : "none"));
        return requireNonNullElse(result, "PUBLIC");
    }

    @Override
    public Schema getSchema(String name) {
        return new SnowflakeSchema(jdbcTemplate, database, name);
    }

    //
    // NOTE: the following methods are overridden to change the implementation
    //

    @Override
    protected void doRestoreOriginalState() throws SQLException {
        // Reset the role to its original value in case a migration or callback changed it
        jdbcTemplate.execute("USE ROLE " + originalRole);
    }

    @Override
    protected void doChangeCurrentSchemaOrSearchPathTo(String schemaNameOrSearchPath) throws SQLException {
        LOG.debug("Switching to Snowflake schema " + schemaNameOrSearchPath);
        jdbcTemplate.execute("USE SCHEMA " + database.quote(schemaNameOrSearchPath));
    }

}

and

/**
 * Snowflake implementation of Flyway Schema.
 */
@SuppressWarnings("rawtypes")
@SuppressFBWarnings({"SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE", "SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING"})
public class SnowflakeSchema extends Schema<SnowflakeDatabase> {

    private static final Log LOG = LogFactory.getLog(SnowflakeSchema.class);

    private final String catalogName;

    public SnowflakeSchema(JdbcTemplate jdbcTemplate, SnowflakeDatabase database, String name) {
        super(jdbcTemplate, database, name);
        LOG.debug("Creating new SnowflakeSchema");
        try {
            catalogName = jdbcTemplate.queryForString("SELECT CURRENT_DATABASE()");
            LOG.debug("Current Snowflake database is " + catalogName);
        } catch (SQLException e) {
            LOG.error("Unable to get current Snowflake database");
            throw new FlywaySqlException("Unable to get current database", e);
        }
    }

    //
    // NOTE: the following are overridden to implement
    //

    @Override
    protected boolean doExists() throws SQLException {
        List<Map<String, String>> objects = getObjects(SCHEMAS, name, "name");
        return !objects.isEmpty();
    }

    @Override
    protected boolean doEmpty() throws SQLException {
        if (doExists()) {
            List<Map<String, String>> objects = getObjects(OBJECTS, "%", "name");
            return objects.isEmpty();
        } else {
            return true;
        }
    }

    @Override
    protected void doCreate() throws SQLException {
        jdbcTemplate.execute("CREATE SCHEMA " + database.quote(catalogName, name));
    }

    @Override
    protected void doDrop() throws SQLException {
        jdbcTemplate.execute("DROP SCHEMA " + database.quote(catalogName, name) + " CASCADE");
    }

    @Override
    protected void doClean() throws SQLException {
        for (String statement : generateDropStatements(VIEWS)) {
            jdbcTemplate.execute(statement);
        }
        for (Table table : allTables()) {
            table.drop();
        }
        for (String statement : generateDropStatements(STAGES)) {
            jdbcTemplate.execute(statement);
        }
        for (String statement : generateDropStatements(FILE_FORMATS)) {
            jdbcTemplate.execute(statement);
        }
        for (String statement : generateDropStatements(SEQUENCES)) {
            jdbcTemplate.execute(statement);
        }
        for (String statement : generateDropStatements(PIPES)) {
            jdbcTemplate.execute(statement);
        }
        for (String statement : generateDropFunctionStatements()) {
            jdbcTemplate.execute(statement);
        }
    }

    @Override
    protected Table[] doAllTables() throws SQLException {
        List<Map<String, String>> objects = getObjects(TABLES, "%", "name");
        List<Table> tables = new ArrayList<>(objects.size());
        for (Map<String, String> object : objects) {
            String tableName = object.get("name");
            tables.add(getTable(tableName));
        }
        return tables.toArray(new Table[0]);
    }

    @Override
    public Table getTable(String tableName) {
        return new SnowflakeTable(jdbcTemplate, database, this, tableName);
    }

    //
    // NOTE: the following overrides are to change the implementation
    //

    //
    // Helper functions...
    //

    /* package */ List<Map<String, String>> getObjects(SnowflakeObjectType type, String filter, String... columns) throws SQLException {
        String inClause;
        if (type == SCHEMAS) {
            inClause = " IN DATABASE " + database.quote(catalogName);
        } else {
            inClause = " IN SCHEMA " + database.quote(catalogName, name);
        }

        String sql = "SHOW " + type.getShowType() + " LIKE '" + filter + "'" + inClause;
        LOG.debug("Executing [" + sql + "]");
        RowMapper<Map<String, String>> mapper = rs -> {
            Map<String, String> result = new HashMap<String, String>();
            for (String column : columns) {
                result.put(column, rs.getString(column));
            }
            return result;
        };
        return jdbcTemplate.query(sql, mapper);
    }

    private List<String> generateDropStatements(SnowflakeObjectType type) throws SQLException {
        List<Map<String, String>> objects = getObjects(type, "%", "name");
        List<String> result = new ArrayList<String>();
        for (Map<String, String> object : objects) {
            String value = object.get("name");
            result.add("DROP " + type.getCreateDropType() + " " + database.quote(name, value));
        }
        return result;
    }

    private List<String> generateDropFunctionStatements() throws SQLException {
        List<Map<String, String>> objects = getObjects(FUNCTIONS, "%", "arguments");
        List<String> result = new ArrayList<String>();
        for (Map<String, String> object : objects) {
            String value = object.get("arguments");
            // remove the RETURN clause from the fuction signature
            value = value.replaceAll("\\sRETURN\\s.*", "");
            result.add("DROP " + FUNCTIONS.getCreateDropType() + " " + database.quote(name, value));
        }
        return result;
    }

}

Also note the need to consider CURRENT_ROLE

[rjungwirth]

Yeah, that’s understandable. SF doesn’t have a global default for user role. Most logins are associated with a default role but it is not required. Default warehouse is somewhere in between. have a DEMO_WH warehouse provisioned by default. Most migrations will not require a warehouse as DML commands don’t need a warehouse. Any migrations that have non-dml statements like inserts, deletes, selects etc. will require a warehouse to execute. From: Bob Tiernay <notifications@github.com> Reply-To: flyway/flyway <reply@reply.github.com> Date: Thursday, November 7, 2019 at 8:13 AM To: flyway/flyway <flyway@noreply.github.com> Cc: Russell Jungwirth <jungwirth@healthgrades.com>, Comment <comment@noreply.github.com> Subject: Re: [flyway/flyway] Snowflake support (#1735) This is what we've done to get this to work: /** * Snowflake implementation of a Flyway Connection. */ @SuppressWarnings("rawtypes") @SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING") public class SnowflakeConnection extends Connection<SnowflakeDatabase> { private static final Log LOG = LogFactory.getLog(SnowflakeConnection.class); private final String originalRole; public SnowflakeConnection(Configuration configuration, SnowflakeDatabase database, java.sql.Connection connection, boolean originalAutoCommit) { super(configuration, database, connection, originalAutoCommit); LOG.debug("Creating new SnowflakeConnection"); try { this.originalRole = jdbcTemplate.queryForString("SELECT CURRENT_ROLE()"); } catch (SQLException e) { throw new FlywaySqlException("Unable to determine current role", e); } } // // NOTE: the following methods are overridden to provide an implementation // @override protected String getCurrentSchemaNameOrSearchPath() throws SQLException { String result = jdbcTemplate.queryForString("SELECT CURRENT_SCHEMA()"); LOG.debug("Current Snowflake schema is " + (result != null ? result : "none")); return requireNonNullElse(result, "PUBLIC"); } @override public Schema getSchema(String name) { return new SnowflakeSchema(jdbcTemplate, database, name); } // // NOTE: the following methods are overridden to change the implementation // @override protected void doRestoreOriginalState() throws SQLException { // Reset the role to its original value in case a migration or callback changed it jdbcTemplate.execute("USE ROLE " + originalRole); } @override protected void doChangeCurrentSchemaOrSearchPathTo(String schemaNameOrSearchPath) throws SQLException { LOG.debug("Switching to Snowflake schema " + schemaNameOrSearchPath); jdbcTemplate.execute("USE SCHEMA " + database.quote(schemaNameOrSearchPath)); } } Also note the need to consider CURRENT_ROLE — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#1735?email_source=notifications&email_token=AHVIEDWJBC6YED6QDYD4DHDQSQO7VA5CNFSM4DV3A5CKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEDMQ6AQ#issuecomment-551096066>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AHVIEDT6X5N66SVQ67HD5STQSQO7VANCNFSM4DV3A5CA>.

[blian7432]

Agree with option 2

[juliahayward]

So with CURRENT_ROLE(), is that not a local property of the connection? I'm just working through some test cases and it appears to me that a migration which calls USE ROLE Foo doesn't affect anything outside its own session.

[rjungwirth]

That is true. Roles are assigned at the session level. I haven’t confirmed myself, but it may be safe to assume that a user will always be assigned a role. From: Julia Hayward <notifications@github.com> Reply-To: flyway/flyway <reply@reply.github.com> Date: Friday, November 8, 2019 at 5:53 AM To: flyway/flyway <flyway@noreply.github.com> Cc: Russell Jungwirth <jungwirth@healthgrades.com>, Comment <comment@noreply.github.com> Subject: Re: [flyway/flyway] Snowflake support (#1735) So with CURRENT_ROLE(), is that not a local property of the connection? I'm just working through some test cases and it appears to me that a migration which calls USE ROLE Foo doesn't affect anything outside its own session. — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#1735?email_source=notifications&email_token=AHVIEDQSCZCXCCCDGT5GVIDQSVHJDA5CNFSM4DV3A5CKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEDQ2EJA#issuecomment-551658020>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AHVIEDW3A3OJ7XAHACI7XQDQSVHJDANCNFSM4DV3A5CA>.

[juliahayward]

Closing this issue as we're releasing imminently. Please start new issues for any problems that arise!