CODEWITHSUNDEEP
pythonpython-3.xaws-lambdasnowflake-cloud-data-platform
Enrai
Enrai

Reputation: 593

Asynchronous query execution in Snowflake: SQL execution canceled

I am using Snowflake Database-as-a-service to store and process our data. Due to handling huge amounts of data, I want to run a query, get the query ID and let it the query execute asynchronously. Another part of the system will monitor the status of the query by checking the query history table using that query ID.

I am using the Snowflake Python Connector.

Here is a sample of what I have so far:

from __future__ import print_function
import io, os, sys, time, datetime
modules_path = os.path.join(os.path.dirname(__file__), 'modules')
sys.path.append(modules_path)

import snowflake.connector

def async_query(data):
    connection = snowflake.connector.connect(
        user=data['user'],
        password=data['password'],
        account=data['account'],
        region=data['region'],
        database=data['database'],
        warehouse=data['warehouse'],
        schema=data['schema']
    )

    cursor = connection.cursor()
    cursor.execute(data['query'], _no_results=True)
    print(cursor.sfqid)

    return cursor.sfqid

This piece of code seems to be working, i.e I am getting the query ID, but there is one problem - the SQL query fails with error "SQL execution canceled." in Snowflake. If I remove the _no_results=True parameter, the query works well, but then I have to wait it to complete, which is not the desired behaviour.

Any ideas what is causing the "SQL execution canceled" failure?

A little bit of more info: The reason why I don't want to wait for it, is that I am running the code on AWS Lambda and Lambdas have a maximum running time of 5 minutes.

Upvotes: 6

Views: 6589

Answers (1)

Sig
Sig

Reputation: 112

If _no_results=True is not specified, the execution is synchronized, so the application has to wait for the query to finish. If specified, the query becomes async, so the application will continue running, but the destructor of connection will close the session in the end, and all active queries will be canceled. It seems that's the cause of "SQL execution canceled".

AWS lambda limits the execution time to 5 min, so if the query takes more than the limit, it won't work.

Btw _no_results=True is an internal parameter used for SnowSQL, and its behavior is subject to change in the future.

Upvotes: 3

Related Questions