jdbcsnowflake-cloud-data-platformoozie

Snowflake cancel query when ABORT_DETACHED_QUERY=true on session level

I am using Snowflake JDBC to execute multi-statement scripts on Snowflake. My application is started by a Oozie job on a hadoop cluster(in migration phase). The requirement here is, when Oozie job is killed and there by killing the running application instance, the query that was submitted using JDBC should get cancelled by Snowflake.

I have added ABORT_DETACHED_QUERY=true to the JDBC connection url which looks like jdbc:snowflake://<account>.snowflakecomputing.com/?warehouse=<WH>&db=<DB>&schema=<SCHEMA>&ABORT_DETACHED_QUERY=true.

Even after 25 mins, the script execution is not cancelled by Snowflake. I tried to find out the underlying problems. I tried to query the session on SESSIONS view using session-id but it was not there. I also tried to query for active connections but could not find a way to do it.

So I have two queries,

  1. Is it the right way to configure ABORT_DETACHED_QUERY parameter?
  2. How do you check for active JDBC connections on Snowflake, because SHOW CONNECTIONS didn't return any connection to my application?

Also, I am using commons-dbcp BasicDataSource as datasource manager, commons-dbutils to submit query using int QueryRunner.execute(String) method.

Solution

  • This is a session parameter not a connection string parameter therefore the proper way to set it is by using an ALTER command:

    ALTER SESSION SET ABORT_DETACHED_QUERY=TRUE;

    After 5 minutes your queries should be aborted if the connectivity is lost due to abrupt termination of a session.