CODEWITHSUNDEEP
springspring-bootspring-data-jpaflywayredgate
Suresh Vasant Sargar
Suresh Vasant Sargar

Reputation: 31

Flyway clean command failing on snowflake with ASSOCIATE_SEMANTIC_CATEGORY_TAGS' does not exist or not authorized

We are getting error during flyway clean command on snowflake instance with error ASSOCIATE_SEMANTIC_CATEGORY_TAGS' does not exist or not authorized.

However this is snowflake provided stored procedure which we didn't own this,and it's not a part of any schema.

Below is the StackTrace

024-02-27T14:12:10.5828869Z Message : SQL compilation error: 2024-02-27T14:12:10.5829911Z Procedure '..ASSOCIATE_SEMANTIC_CATEGORY_TAGS' does not exist or not authorized. 2024-02-27T14:12:10.5830338Z 2024-02-27T14:12:10.5831459Z at org.flywaydb.core.internal.database.base.Schema.clean(Schema.java:123) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5832649Z at org.flywaydb.core.internal.command.DbClean.lambda$doCleanSchema$3(DbClean.java:216) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5833975Z at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:55) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5835289Z at org.flywaydb.core.internal.command.DbClean.doCleanSchema(DbClean.java:215) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5836784Z at org.flywaydb.core.internal.command.DbClean.cleanSchema(DbClean.java:208) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5837880Z at org.flywaydb.core.internal.command.DbClean.cleanSchemas(DbClean.java:196) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5839210Z at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:112) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5840542Z at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:90) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5841707Z at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:76) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5842839Z at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:67) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5844539Z at org.flywaydb.core.Flyway.doClean(Flyway.java:367) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5845578Z at org.flywaydb.core.Flyway.access$400(Flyway.java:56) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5846573Z at org.flywaydb.core.Flyway$3.execute(Flyway.java:214) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5847475Z at org.flywaydb.core.Flyway$3.execute(Flyway.java:211) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5848448Z at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:207) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5849503Z at org.flywaydb.core.Flyway.clean(Flyway.java:211) ~[flyway-core-8.5.0.jar!/:?] 2024-02-27T14:12:10.5850377Z at com.varian.acdp.flyway.executor.AcdpFlywayExecutor.clean(AcdpFlywayExecutor.java:75) ~[classes!/:2.0] 2024-02-27T14:12:10.5851227Z at com.varian.acdp.flyway.FlywayUtil.runFlyWay(FlywayUtil.java:30) ~[classes!/:2.0] 2024-02-27T14:12:10.5852058Z at com.varian.acdp.flyway.SnowflakeFlywayApp.run(SnowflakeFlywayApp.java:80) ~[classes!/:2.0] 2024-02-27T14:12:10.5853431Z at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:769) ~[spring-boot-3.0.6.jar!/:3.0.6] 2024-02-27T14:12:10.5854151Z ... 13 more 2024-02-27T14:12:10.5855061Z Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:

Upvotes: 0

Views: 40

Answers (1)

ozkanpakdil
ozkanpakdil

Reputation: 4602

This looks like a snowflake issue

SQL compilation error: Integration '<Storage_Integration>' does not exist or not authorized.

Solution

  1. Configure access without using Storage Integration

The suggested solution or workaround is instead of using a Storage Integration to configure access, configure a security policy for the S3 bucket and access credentials for a specific IAM user to access the external stage in a secure manner.

CREATE OR REPLACE STAGE s3_stage
  URL='s3://mybucket/load/files/'
  CREDENTIALS=(AWS_KEY_ID='1a2b3c' AWS_SECRET_KEY='4x5y6z')
  ENCRYPTION=(TYPE='AWS_SSE_KMS' KMS_KEY_ID = 'aws/key');

CREATE OR REPLACE VIEW v1 (
    first_name, 
    last_name, 
    email, 
    gender, 
    zip_code
) AS (
SELECT
    $1,
    $2,
    $3,
    $4,
    $5
FROM @s3_stage/testfile.csv);

//Extract the semantic and privacy categories for the columns in the view 'v1' using function:

SELECT EXTRACT_SEMANTIC_CATEGORIES('v1', 10);

//Apply the results of the EXTRACT_SEMANTIC_CATEGORIES function as tags on the columns in the view 'v1' using Stored Procedure:

CALL ASSOCIATE_SEMANTIC_CATEGORY_TAGS('v1',EXTRACT_SEMANTIC_CATEGORIES('v1',10));

Using the EXTRACT_SEMANTIC_CATEGORIES function or calling Stored Procedure ASSOCIATE_SEMANTIC_CATEGORY_TAGS on the view 'v1' now works as expected.

  1. Use External Table / View created on External Table

Another workaround is to use an External Table to use the data from the external storage location and using the function and stored procedure on both the external table and a view created on this external table works as expected:

CREATE OR REPLACE EXTERNAL TABLE et1
 WITH LOCATION = @s3_stage/
 REFRESH_ON_CREATE =  TRUE
 FILE_FORMAT = (TYPE = CSV);

CREATE OR REPLACE VIEW ext_v1
 AS
SELECT * FROM et1;

Reference:https://community.snowflake.com/s/article/Storage-Integration-authorization-error-when-using-the-EXTRACT-SEMANTIC-CATEGORIES-function-with-externally-stored-data

Upvotes: 0

Related Questions