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 (';').