LIQUIBASE: CREATE EXTERNAL TABLE cannot run inside a multiple commands statement;

124 views Asked by At

I have a sql file that has multiple CREATE EXTERNAL SCHEMA and CREATE EXTERNAL TABLE statements in it, that I'm trying to execute through Liquibase. This is running against a Redshift database. I'm getting the following error:

CREATE EXTERNAL TABLE cannot run inside a multiple commands statement;

I'm not sure if this is in reference to the fact that there are multiple statements in the file or if it's a delimiter issue. The statements in the file are ended by a semicolon.

I'm using a CustomSqlChange so that I can read in the file and replace some placeholders that are in it before it gets run.

Below is my generateStatements method. I am returning a RawCompoundStatement because I figured that was the correct kind of statement to use.

See below:

    @Override
    public SqlStatement[] generateStatements(Database database) throws CustomChangeException {
        String sql = null;

        try (BufferedReader reader = new BufferedReader(new InputStreamReader(new BufferedInputStream(resourceAccessor.get(getFile()).openInputStream())))) {
            StringBuilder stringBuilder = new StringBuilder();
            for (String chunk = reader.readLine(); chunk != null; chunk = reader.readLine()) {
                stringBuilder.append(chunk).append("\n");
            }
            sql = stringBuilder.toString();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        sql = sql.replaceAll(PARQUET_IAM_ROLE_PLACEHOLDER, iamRole).replaceAll(PARQUET_BUCKET_NAME_PLACEHOLDER, bucketName);

        return new SqlStatement[] { new RawCompoundStatement(sql, ";") };
    }

Here's an example of one my changeSets:

    <changeSet id="MPBI-407.PARQUET.02" author="vicrod" contextFilter="QC2 and NAMS" runInTransaction="false">
        <customChange class="com.xxxx.rms.db.ParquetMigrationCustomSqlChange">
            <param name="file" value="migrations/redshift/parquet/aces/aces_create_table_scripts.sql"/>
            <param name="iamRole" value="${parquet.iam.role}"/>
            <param name="bucketName" value="${parquet.bucket.name}"/>
        </customChange>
    </changeSet>

Any insight would be much appreciated. Thanks!

UPDATE: I tried a different way and it worked! See below:

        sql = sql.replaceAll(PARQUET_IAM_ROLE_PLACEHOLDER, iamRole).replaceAll(PARQUET_BUCKET_NAME_PLACEHOLDER, bucketName);

        SQLFileChange sqlFileChange = new SQLFileChange();
        sqlFileChange.setSql(sql);
        sqlFileChange.setSplitStatements(Boolean.TRUE);
        return sqlFileChange.generateStatements(database);

Using the SQLFileChange object, I could set splitStatements to true and then call generateStatements and it returns an array of statements having been split based on the default delimiter, which is a colon (';').

0

There are 0 answers