topic Re: How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate in Qlik Replicate

How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate

DataGuyAtHeart — Mon, 29 Mar 2021 20:35:20 GMT

For Change jobs where Snowflake is the target, I am getting:

RetCode: SQL_ERROR SqlState: 08001 NativeError: 390114 Message: Authentication token has expired. The user must authenticate again. [1022502] (ar_odbc_stmt.c:4614)

I believe I need a way to set sessions Keep Alive in order to avoid this. How do I configure this for a Snowflake connection?

Thanks!

Re: How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate

lyka — Mon, 29 Mar 2021 21:35:19 GMT

Hello,

For the error:

RetCode: SQL_ERROR SqlState: 08001 NativeError: 390114 Message: Authentication token has expired. The user must authenticate again. [1022502] (ar_odbc_stmt.c:4614)

I suggest adding the below value to the ODBC connection string for the snowflake target endpoint:

CLIENT_SESSION_KEEP_ALIVE=true;ABORT_DETACHED_QUERY=true;CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=900;

And the following to the user:

Please change the setting for the snowflake user properties, NOTE that this is not set in replicate but on Snowflake end:

alter user user_name set CLIENT_SESSION_KEEP_ALIVE = TRUE;

alter user user_name set ABORT_DETACHED_QUERY = TRUE;

alter user user_name set CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY = 900;

Some links for your reference:

https://docs.snowflake.net/manuals/sql-reference/parameters.html#client-session-keep-alive

https://docs.snowflake.net/manuals/user-guide/odbc-parameters.html#connection-parameters

Thanks

Lyka

Re: How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate

DataGuyAtHeart — Tue, 30 Mar 2021 11:54:34 GMT

Thanks for all of the great info! I will try it today.

Re: How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate

DataGuyAtHeart — Wed, 31 Mar 2021 18:26:40 GMT

This worked like a champ - thanks again @lyka

Re: How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate

alla2 — Wed, 13 Apr 2022 15:19:59 GMT

@lyka Can you give me where to add this ?

CLIENT_SESSION_KEEP_ALIVE=true;ABORT_DETACHED_QUERY=true;CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=900;

Re: How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate

SwathiPulagam — Wed, 13 Apr 2022 15:22:34 GMT

Hi @alla2 ,

Please add the below mentioned value to snowflake endpoint-->Advanced-->Additional ODBC connection properties: CLIENT_SESSION_KEEP_ALIVE=true;ABORT_DETACHED_QUERY=true;CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=900;

Thanks,

Swathi

Re: How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate

alla2 — Wed, 13 Apr 2022 15:30:09 GMT

don't see Additional ODBC connection properties in Additional tab

Re: How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate

shashi_holla — Wed, 13 Apr 2022 15:36:54 GMT

If your endpoint is Snowflake on Google then might not see it.

It's there for Snowflake on Azure and AWS:

Thank you,

Re: How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate

alla2 — Wed, 13 Apr 2022 15:39:09 GMT

Thanks Shashi, any idea how to do this for Google too ?

Re: How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate

SwathiPulagam — Wed, 13 Apr 2022 15:55:36 GMT

hi @alla2 ,

For Snowflake Google endpoint please follow the below steps:

Go to Snowflake endpoint-->Advanced-->Internal parameter-->add this additionalConnectionProperties and provide the below string as value: CLIENT_SESSION_KEEP_ALIVE=true;ABORT_DETACHED_QUERY=true;CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=900;

Thanks,

Swathi