CODEWITHSUNDEEP
schemaliquibaseuppercasesnowflake-cloud-data-platform
hello_ish
hello_ish

Reputation: 3

Liquibase unable to execute schema (Snowflake) with mixed case eg. (This_Schema)

I have tried using liquibase tool for our snowflake db. It is all working with where SCHEMA name is in all CAPITAL(UPPERCASE). But liquibase is not picking up any of my schema's with mixed case, eg (This_Schema).

I have tried putting this but didn't help.

<defaultSchemaName>This_Schema</defaultSchemaName>

POM.XML configuration example:

          <driver>net.snowflake.client.jdbc.SnowflakeDriver</driver>
          <url>jdbc:snowflake://${env.SNOWFLAKE_ACCOUNT}.eu-central-1.snowflakecomputing.com/?db=${env.SNOWFLAKE_DB}&amp;schema=${env.SNOWFLAKE_SCHEMA}&amp;warehouse=${env.SNOWFLAKE_WH}&amp;role=${env.SNOWFLAKE_ROLE}</url>
          <username>${env.SNOWFLAKE_USERNAME}</username>
          <password>${env.SNOWFLAKE_PASSWORD}</password>

Error setting up or running Liquibase: liquibase.exception.DatabaseException: SQL compilation error: [ERROR] Schema 'LIQUIBASE_DB.THIS_SCHEMA' does not exist. [Failed SQL: CREATE TABLE THIS_SCHEMA.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP_NTZ, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]

NOTE: "This_Schema" is the name of my schema as it is showing here, but upon executing liquibase update this automatically changes to UPPERCASE value as in error above.

Upvotes: 0

Views: 1329

Answers (1)

Eric Mamet
Eric Mamet

Reputation: 3671

Found this comment in the README file from the liquibase snowflake extension.

The Snowflake JDBC drivers implementation of DatabaseMetadata.getTables() hard codes quotes around the catalog, schema and table names, resulting in queries of the form:

show tables like 'DATABASECHANGELOG' in schema "sample_db"."sample_schema"

This results in the DATABASECHANGELOG table not being found, even after it has been created. Since Snowflake stores catalog and schema names in upper case, the getJdbcCatalogName returns an upper case value.

Could this explain your problems?...

Upvotes: 1

Related Questions