How to execute multiple asynchronous queries in Snowflake without it's interferring each other?

Question

I would like to execute multiple asynchronous queries. However, it keeps interferring each other SQL commands.

For example, query A need to use schema A and query B need to use schema B but the command to use schema B is triggered then query A is run after it which cause query A to fail.

Sample query A:

1. USE SCHEMA TEST;
2. SELECT * FROM TABLE_A;

Sample query B:

1. USE SCHEMA TEST_2;
2. SELECT * FROM TABLE_B;

What happened in my case when triggered both of them asynchronously in the same connection is that it ran like this:

1. USE SCHEMA TEST;
2. SELECT * FROM TABLE_B;

which cause query to fail because TABLE_B doesn't exists in schema TEST.

Does the snowflake any way to trigger multiple asynchronously queries without it interferring each other? Other than connect, run the query and the disconnect again.

Answer 1

The problem seems to be that you are reusing a connection, and connections have a state. That state changes every time you set variables or USE SCHEMA.

Possible solutions:

• Don't re-use a connection. Instead create a new connection for each new asynchronous set of queries. Then you can set different schemas within.

• If you want to use the same connection, don't change state with USE SCHEMA. Instead hard-code the schema into the query, as in from db.schema.table.

• Use the SQL API that happens to be stateless: https://docs.snowflake.com/en/developer-guide/sql-api/index.html

For further help please open new questions, and make sure to share more code and what connector and language you are using.