The new snowflake connector is by default altering the session to set multi_statement_count = 1

Question

I was using script activity to execute multiple statements in Snowflake using ADF. The new snowflake connector is not able to run Snowsql statements in one script activity separated by semicolon.

In the Warehouse, when I check for query history, below statement is being executed with new Snowflake Connector.

alter session set multi_statement_count = 1; This is blocking to run multiple SnowSQL statements in one script activity.

The old Snowflake connector executes alter session set multi_statement_count = 0 by default supporting to execute multiple snowsql statements in one script activity.

I tried running the statement alter session set multi_statement_count = 0 ; select * from A; select * from B; all together in one script activity using the new snowflake connector but it still fails.. I am not sure how to get around this issue.

I definitely need to be able to run multiple Snowsql statements in one script activity.

Answer 1

@Raghu Gorla

Welcome to Microsoft Q&A platform and thanks for posting your question.

It seems like the new Snowflake connector has a limitation where it sets multi_statement_count = 1 by default, which prevents running multiple SnowSQL statements in one script activity. This is different from the old Snowflake connector which sets multi_statement_count = 0 by default, allowing multiple SnowSQL statements to run in one script activity.

Here are a few potential workarounds you could consider:

1. Stored Procedure: You could wrap all your SQL statements within a stored procedure. Then, in the ADF Script Activity, you would only need to call your stored procedure.
2. num_statements Parameter: According to the Snowflake Connector for Python release notes, multi-statement query execution is now supported through cursor.execute and cursor.executemany. An additional argument, num_statements, can be provided to execute to use this parameter at the statement level.

Hope this helps. Do let us know if you any further queries.