SNOW-1916889: Expose richer query status (like the Python SDK)

[haggholm]

What is the current behavior?

A Snowflake Statement object has a getStatus() method, but currently, if the published types are correct, it only exposes two states:

export enum StatementStatus {
    Fetching = "fetching",
    Complete = "complete",
}

(I am assuming that the published types are correct, and these are indeed the only statuses I've observed. But if I'm wrong, I'd suggest that this ticket is still valid though as a request to improve the .d.ts types, docs, &c.)

What is the desired behavior?

The Python library (going by the API docs) exposes a much richer set of status constants, including RESUMING_WAREHOUSE and QUEUED, so I am assuming that the server interfaces already have access to more information. It would be nice if we could check for these through the Node connector.

It would be especially nice if the application could subscribe to push notifications rather than needing to poll, like

connection.execute({
  complete: (err) => { /* ... */ },
  onStatusChange: (newStatus, oldStatus) => { /* ... */ },
});

Simply allowing me to query for the other status options would be a huge help, though it would be nice to leave it to the driver to make sure no state transitions are missed by too-infrequent polling. (I'm particularly interested to know if a query leaves a waiting state like RESUMING_WAREHOUSE or QUEUED and enters an active state, to better measure duration of actual queries.)

How would this improve snowflake-connector-nodejs?

Currently it's often difficult to accurately diagnose performance problems, especially if operating as a SaaS platform without direct access to a customer's Snowflake environment and Snowsight UI. Is a query slow to complete because it's inefficient, because the customer's warehouse is backed up and the query is stuck in a queued state, because execution is slow, because result streaming is slow…? A fuller picture of query status would make it much easier to address performance problems.

References, Other Background

[sfc-gh-dszmolka]

hi - thanks for submitting this enhancement suggestion. Given the current workload of the team, to set expectations, it's not realistic to have this new capability to implement push notifications for at least 3 months, at the minimum, but realistically even more.

So let's see what we have today, maybe it already helps you. We have QueryStatus (https://github.com/snowflakedb/snowflake-connector-nodejs/blob/v2.0.2/index.d.ts#L194)

export type QueryStatus = "RUNNING" | "ABORTING" | "SUCCESS" | "FAILED_WITH_ERROR" | "ABORTED" | "QUEUED" | 
"FAILED_WITH_INCIDENT" | "DISCONNECTED" | "RESUMING_WAREHOUSE" | "QUEUED_REPARING_WAREHOUSE" | 
"RESTARTED" | "BLOCKED" | "NO_DATA";

which already does include the statuses you need, and more. QueryStatus should be exposed through connection.getQueryStatus(queryId) (https://github.com/snowflakedb/snowflake-connector-nodejs/blob/v2.0.2/lib/connection/connection.js#L587), and you can see an example of using it in the Snowflake documentation at https://docs.snowflake.com/en/developer-guide/node-js/nodejs-driver-execute#execute-queries-asynchronously

I'm aware polling for query status is way less elegant than just being able to subscribe to it and get it automatically whenever it changes, but maybe it could still help your use case. What do you think, could this be helpful for you?

[sfc-gh-dszmolka]

Sorry, i almost missed this part:

to better measure duration of actual queries.

If this is the ultimate goal for the request, we might already have some kind of solution. Could you please look at our Query History docs at https://docs.snowflake.com/en/sql-reference/account-usage/query_history, and see if this is something which would fulfil the needs? We do have detailed statistics for various query life stages (queued_provisioning_time, compilation_time, execution_time and so on, a lot actually) so it should be possible in a way, to analyze individual queries' performance and time spent in each phase, even retroactively.
Let me know please if this helps.

[haggholm]

I did look into using the getQueryStatus() method, but that appears to require the use of async execution, which would require modifying (many layers of) utility code because as far as I can tell, async operation only works for some queries. For example, anything with describeOnly: true appears to fail, and a number of other operations do as well as they don't return values, or somehow have invalid query ids, &c.

You might reasonably object that it makes no sense to do all this inspection for describeOnly queries, but that's the difficulty of retrofitting this into a codebase that interfaces with the driver through layers of utilities: I know that at least some query patterns will break, but I'm not sure exactly which or why, so this would only work for greenfield areas of the project.

(Is there a clear picture of what, exactly, won't work the same with async execution s.t. it can be anticipated and dealt with? At the moment it seems unclear.)

If this is the ultimate goal for the request, we might already have some kind of solution. Could you please look at our Query History docs at https://docs.snowflake.com/en/sql-reference/account-usage/query_history, and see if this is something which would fulfil the needs?

I suspect it won't (though I very much appreciate the helpful suggestions), as (a) I'm dealing with a SaaS product where I'm not sure we'll have permission to read some system views, and (b) the docs mention a latency of up to 45 minutes, which isn't acceptable for our use case.

[sfc-gh-dszmolka]

got you, thank you for all these background information. about getQueryStatus i'm not entirely convinced that it would always require async query execution - i mean, it is the base use-case for this function to

• send in queries async
• poll how the query is doing (with getQueryStatus)
• when the query is finished running, retrieve and consume results

but i guess it could be tested how it interacts with queries sent in synchronously, inside and outside of the particular Session where the sync query is running right now (there might be differences)

QUERY HISTORY has an instantenous version too, please see table function https://docs.snowflake.com/en/sql-reference/functions/query_history, so two options

• instant, works on current metadata - information_schema.query_history table function (limited to relatively small resultset, as it works on live metadata, not recommended to be used for queries expected to return >10k rows. Better fitting for realtime status)
• has delay, works on historical data, is available up to 1 year - account_usage.query_history view. It's meant backing retroactive analysis & report queries, indeed not realtime but you don't have a limitation of needing to retrieve a small resultset either. Better fitting for analytics.

Can you perhaps test out the table function and see if it works for you?

Of course even if getQueryStatus would work with sync queries, and if the table function query_history works for you analyzing query stats near-realtime, they don't implement having the query statuses coming as Event so a consumer could subscribe to it. I now passed this request to the relevant team, to consider it for future plans (no timeline attached)

[haggholm]

i guess it could be tested how it interacts with queries sent in synchronously, inside and outside of the particular Session where the sync query is running right now (there might be differences)

That's what I initially wanted to do, but I got the impression that it couldn't be done. The question is, how do I get a query id? In the documentation, examples always seem to check for stmt.getQueryId() in the statement passed to the complete() callback.

Of course I can call getQueryId() on the statement returned directly from snowflake.execute({ /* ... */ }), but the query id always seems to be undefined, at least until the callback is invoked…which of course only happens when execution is done and it's too late to poll for interesting things like whether the query is queued. I'd like to be able to tell if my query is queued while it's still queued.

Am I missing anything? This would be my preferred solution (short of new hooks), if only it worked, which…currently, it doesn't.

Can you perhaps test out the table function and see if it works for you?

The table function may work, assuming it doesn't have significant latency. Two questions:

• Are queries against information_schema treated specially? E.g., are they exempt from queueing and similar? (It wouldn't do much good to attempt to check if a query is queued, if the status check query may also end up waiting in a queued state.)
• Are the data always available to the session/user who issued a query, or should I worry that some configurations might potentially be missing permission to read the query history?