SNOW-1858342: Support other session parameters

[countergram]

The JDBC docs note that any session parameter can be included in a connection string: https://docs.snowflake.net/manuals/user-guide/jdbc-configure.html#other-parameters

This would be useful in the Node.js connector as well. E.g. we try to keep query_tags on as many of our workloads as possible and would would like to make it part of all of our connection params by default.

[owlas]

I was about to open a separate issue for configuring the timezone just like the snowflake python api client.

Would that come under this same issue @sfc-gh-jfan ? Or should I open a new issue specifically for timezone support?

[hanliao]

I'm also curious about is there a set time to add supports to QUERY_TAG? We do hope to use tagging for some internal efforts.

[sfc-gh-dszmolka]

hi; thank you for submitting this issue. this has been brought to attention of the dev team to look at and prioritise if found feasible. thank you for bearing with us !

[shaikatz]

Please! it's highly needed!

[manishprivet]

@sfc-gh-dszmolka This is really being a blocker for us here as well. We have our application in node, but our queries require the week_start parameter value change. I understand that it is possible to do it in python

[sfc-gh-dszmolka]

thank you for letting us know about the business impact of the lack of capability. i now relayed it again to the driver team.

please note, we absolutely cannot commit any timelines to any enhancement in the open-sourced driver - however if you are a Snowflake customer, you do have a quite powerful option at your disposal, and that's letting your account executive know of how important this is for you. they can usually give some traction to requests. i'm not very happy to having relay this message, but wanted to set the expectations here.

but as mentioned, i again reiterated with the team how important this request is, hopefully they can put it on the timeline in the foreseeable future.

[sfc-gh-dszmolka]

a possible workaround which might help getting some of you unblocked, or at least something to consider. with a fairly recent version of the driver (1.6.18) we now support multi-statement queries. Documentation can be found here.

The idea here for this workaround is to:

1. enable multi-statement for your query
2. as the first statement in the multi-statement query, issue ALTER SESSION SET <session_parameter> = <desired_value>
3. run the rest of the usual queries.

This way, the queries should run with the desired session parameter, which is not yet supported directly in the connection string. Should be applicable for any session parameter, not just QUERY_TAG for which this Issue was originally opened

[hanliao]

@sfc-gh-dszmolka thanks for the suggestion but i'm wondering will it create additional credit cost to issue a new query every time? I also tried multi-statement queries but somehow I cannot get it work since it was complaining "stmt.hasNext()" and "stmt.NextResult()" don't exist on type. For more context I am using [email protected] and @types/[email protected] since snowflake-sdk lacks official typescript typings support, which is such a pity.

[sfc-gh-dszmolka]

@hanliao this is a very valid question. I don't believe the costs should increase or at least not significantly, since the extra queries (ALTER SESSION..) introduced as the workaround to not use compute (warehouse) at all, but the cloud services layer instead. Also the latter is not billed unless it exceeds the 10% of the compute usage you are already using, see How Are Costs Incurred?

Of course the best way is to test it e.g. for a day or week and see how it goes for you.

About the multi-statement queries example: the examples (without typings support, so the core snowflake-sdk) do work, both of them, tested them myself. How to tackle them with @types/snowflake-sdk, perhaps the maintainers or the users of that can help. Perhaps it needs adjustments, but I'm not sure.

[hanliao]

@sfc-gh-dszmolka right, for multi-statement queries, what I mean is that this package doesn't have native typings support so I have to either writing my own typings files and maintain them, or use an existing third party typings package which lacks Snowflake support, since our service uses TypeScript.

[theodoresiu]

Regarding query_tags passing them in as parameter in a query option will work. See

snowflake-connector-nodejs/lib/connection/statement.js

Line 1229 in ef1a7d7

json.parameters = statementContext.parameters;

and the subsequent API POST request in the code