2

I'm using (NodeJs Snowflake Driver lib) - snowflake-sdk and wanted to ask: is there a way to execute Snowflake 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 using async:true (see here) - this is an url parameter but it’s not allowed in NodeJs snowflake driver library.

This is the snippet from my code:

  async executeStatement(params: ExecuteProcedureParams, connection: sf.Connection): Promise<ExecutionResult> {
    const procedureCommand = `${params.sqlCommandText};`;
    return new Promise((resolve, reject) => {
      try {
        connection.execute({
          sqlText: procedureCommand,
          binds: params.sqlCommandParams ?? undefined,
          complete: (err: Error, stmt: sf.Statement, rows: any) => {
            if (err) {
              reject(err);
            } else {
              console.log('Number of rows produced: ' + rows?.length);
              resolve({
                requestId: stmt.getRequestId(),
                executedSqlText: stmt.getSqlText(),
                rows,
              });
            }
          },
        });
      } catch (err) {
        console.log(err);
      }
    });
  }

I would like executeStatement to be non-blocking for heavy statements if I choose to run connection.execute({...}); in async mode.

And 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

Maybe someone could recommend an approach to achieve this asynchronous behavior?

Giedrius Valančius
  • 21
  • 3
  • 1
    Maybe you can have a function to run your statement in an Async way like this [heartbeatAsync](https://github.com/snowflakedb/snowflake-connector-nodejs/blob/master/lib/connection/connection.js#L117-L134) ? – Sergiu Oct 08 '21 at 12:22
  • Thanks for the reply @Sergiu. I guess the problem with this approach would be the fact that the Promise like this would be resolved only some time in the future when the actual statement execution is done (and that could be a relatively long time). My goal here is to start execution and almost immediately end the whole Node process without unresolved Promises (I would use one AWS Lambda for starting statement execution and completely different lambda as a status checker - step function approach). – Giedrius Valančius Dec 15 '21 at 09:06
  • did you ever manage to make this work? but anyway thanks for your example at least it helped me with something I was strugling – Miguel Costa Jan 27 '22 at 16:14
  • 1
    Hey, @MiguelCosta. I didn't solve it yet (as far as I would like to...). Until this async behaviour is provided by the library maintainers I'm starting and waiting for heavy snowflake executions to end from an ECS container (instead of a lambda). I also opened this as a request in the official github repo (snowflake-connector-nodejs), see here: [link to open issue](https://github.com/snowflakedb/snowflake-connector-nodejs/issues/208) <- repo owners already saw it but no progress on solving this yet – Giedrius Valančius Jan 31 '22 at 07:43
  • Unfortunately the snowflake nodejs client does not support this - my solution is to use the API directly to pass in necessary parameters. – Leo Romanovsky Dec 09 '22 at 23:45

0 Answers0