asynchronous stored procedure in snowflake

Question

I need to have stored procedure in snowflake that has to be executed in asynchronous mode .so if call is made is to the stored procedure ,it should immediately return queryid and close the session. The actual execution should happen independently. I tried implementing the wrapper stored procedure that calls asynchronous stored procedure but doesn't seem to work.

If I understand well your request you don't need the stored procedure to be async in this case. What you want is probably a client that supports async call. Is this correct? Several Snowflake connectors do support async calls. — Sergiu

Gokhan Atil Gokhan Atil · Accepted Answer · 2021-03-20T14:40:04

You can execute a stored procedure asynchronous from your Java app:

https://docs.snowflake.com/en/user-guide/jdbc-using.html#examples-of-asynchronous-queries

I created a simple stored procedure which waits 5 seconds and then return "Success". Here is a sample Java code to call the stored procedure, and get the query ID (while the stored procedure is still working):

public static void main(String[] args) throws SQLException, InterruptedException {

    try (Connection con = getConnection()) {

        try (var statement = con.createStatement()) {

            var sql_command = "CALL TEST_SP()";
            var resultSet = statement.unwrap(SnowflakeStatement.class).executeAsyncQuery(sql_command);
            var queryID = resultSet.unwrap(SnowflakeResultSet.class).getQueryID();


            System.out.printf("Calling TEST_SP() - Query ID is %s%n", queryID);
            var queryStatus = resultSet.unwrap(SnowflakeResultSet.class).getStatus();
            while (queryStatus == QueryStatus.RUNNING || queryStatus == QueryStatus.RESUMING_WAREHOUSE) {
                Thread.sleep(1000); // 1000 milliseconds.
                System.out.printf( "Waiting for 1 second...\n");
                queryStatus = resultSet.unwrap(SnowflakeResultSet.class).getStatus();

            }
            if (resultSet.next()) System.out.println(resultSet.getString(1));
        }
    }
}

Output:

Calling TEST_SP() - Query ID is 019b08ac-32c3-3514-0000-b4550004911a
Waiting for 1 second...
Waiting for 1 second...
Waiting for 1 second...
Waiting for 1 second...
Waiting for 1 second...
Success

Of course, you don't need to wait until the end of the stored procedure, this code just shows that how the stored procedure is executed asynchronously.

asynchronous stored procedure in snowflake

1 Answers