SNOW-502598 Unable to execute statements in async mode (add async query execution capabilities)

[gievln]

Please answer these questions before submitting your issue. Thanks!

1. What version of NodeJS are you using (node --version and npm --version)?
   node: v14.18.0
   yarn: 1.22.10

2. What operating system and processor architecture are you using?
   For Prod env: AWS Lambda
   For local testing:

Distributor ID: Ubuntu
Description:    Ubuntu 20.04.3 LTS
Release:        20.04
Codename:      focal

3. What are the component versions in the environment (npm list)?

"snowflake-sdk": "1.6.3",
"@types/snowflake-sdk": "1.6.1"

Hey, is there a way to execute statements in async manner so that after starting a heavy procedure I would be able to monitor its status using snowflake's query_history() calls?
Looks like snowflake-connector-nodejs doesn't allow to set async execution parameter to the inner SnowFlake API call on statement execution…
In general Snowflake REST API allows to execute sql statement in async mode async:true (see here) - this is an url parameter but it’s not allowed in NodeJs snowflake driver. I believe this is the place where we should be able to set async param:

...
url: Url.format(
        {
          pathname: '/queries/v1/query-request',
          search: QueryString.stringify(
            {
              requestId: statementContext.requestId,
              async: statementContext.async // <--- NEW LINE THAT DOESN'T EXIST AT THE MOMENT
            })
}),

source code is here: https://github.com/snowflakedb/snowflake-connector-nodejs/blob/master/lib/connection/statement.js#L1134

Is there a plan to introduce this async flag support in snowflake-connector-nodejs? Or maybe someone could recommend other approach to achieve this asynchronous behavior?

[bjacques]

I see that async queries are available for the Python driver, but not Nodejs

[TomNachman]

will there be any support for NodeJS driver in the future for this?

[sfc-gh-dszmolka]

hi, we do have plans to support it in the future. unfortunately at this moment, i do not have any information on the timeline.

[sfc-gh-dszmolka]

reopened to be able to properly track the request

[mbrookson]

I'd really like to see this feature added. I'm importing about 1.3 million records as a stream, batching the records in an array and then processing the. Since this is not async, each invocation of the streamRows.on('data') callback is not able to wait for the previous to finish processing. Essentially this means ALL records are being streamed in but I cannot await each batch, causing an out of memory exception.

Ideally I'd like the callback to be async and the SDK pause streaming in data from Snowflake until the promise has resolved. I think this would prevent the constant stream of data that's causing a memory issue.

[sfc-gh-dszmolka]

Thank you all so much for bearing with us ! Got some good news: work is ongoing on this one, will update this comment once it gets into review.

[sfc-gh-dszmolka]

PR: #672

[sfc-gh-dszmolka]

PR #672 is merged and will be part of the upcoming October's release (hoping to release very soon)

[sfc-gh-dszmolka]

node.js driver version 1.9.1 released with the fix and is available on npm. thank you all for bearing with us !