SNOW-1569617: Allow multi statement queries to stream the result of the final query

[cleve-fauna]

What is the current behavior?

Currently, if you use a multi statement query such as:

 const sql = `
    SELECT 'first query';

    SELECT 'second query';
  `;
  const selects = connection.execute({
    sqlText: sql,
    streamResult: true,
    parameters: { MULTI_STATEMENT_COUNT: 2 }
  });
  for await (const row of stagedQueryLogIntegrations.streamRows()) {
    console.log(row);
  }
}

You will get a stream of the results of the first query. In this case 'first query' will be streamed to you.

What is the desired behavior?

Have the ability to stream the result of the second query, such that

 const sql = `
    SELECT 'first query';

    SELECT 'second query';
  `;
  const selects = connection.execute({
    sqlText: sql,
    streamResult: true,
    parameters: { MULTI_STATEMENT_COUNT: 2 }
  });
  for await (const row of stagedQueryLogIntegrations.streamRows()) {
    console.log(row);
  }
}

logs 'second query'.

How would this improve snowflake-connector-nodejs?

This would greatly improve the usability of the connector. For example, there are cases I need to set a session variable in the first query, and then query a view that relies on the session variable in the second. Presently, I cannot use the streamRows feature of the driver to do this and consume the results of the view query.

Here is an example:

 const sql = `
    SET results_after_date = '2024-01-01';

    SELECT * from my_view;
  `;
  const selects = connection.execute({
    sqlText: sql,
    streamResult: true,
    parameters: { MULTI_STATEMENT_COUNT: 2 }
  });
  for await (const row of stagedQueryLogIntegrations.streamRows()) {
    console.log(row);
  }
}

The stream will be empty as it will contain the output of the session variable setting. Instead, I'd like to see the result of the view.

I can work around this by using the more verbose event syntax, looking something like this in this case:

  await new Promise<void>((resolve, reject) => testConnection.execute({
    sqlText: "SET results_after_date='2024-01-01';",
    complete: (err) => {
      if (err) {
        reject(err);
      }
      testConnection.execute({
        'SELECT * from my_view',
        complete: (err, _, rows) => {
          if (err) {
            reject(err);
          }
          console.log(rows);
          resolve();
        }
      });
    }

References, Other Background

It would also be great if the fact that streamRows returns the results of the first query was documented. Had to plumb through forums and experiments on my local machine to figure this out.

What is your Snowflake account identifier, if any?

FAUNA-GK58919

[sfc-gh-dszmolka]

hi and thank you for submitting this enhancement request; the team will consider it for future plans.

If you wish to help us prioritize amongst feature requests and this capability is important for you (looks like it!), do reach out to your Snowflake account team please and let them know of this request. They can use the appropriate channels (other than a github issue) to ensure resources are dedicated to the enhancement according to its importance. Thank you in advance!

edited the comment to ask if the documentation for multi-statement query, which has example for determining if there's any more results to consume from the next query, is helpful at all ?
It is true we don't explicitly document that by default the result of the first query is returned, however we do document how to retrieve the result of each sub-queries.

[sfc-gh-dszmolka]

a month passed now without a response, so closing this now. if you happen to need any further help, comment please and we can reopen