How to pass session parameters with python to snowflake?

Question

The below code is my attempt at passing a session parameter to snowflake through python. This part of an existing codebase which runs in AWS Glue, & the only part of the following that doesn't work is the session_parameters.

I'm trying to understand how to add session parameters from within this code. Any help in understanding what is going on here is appreciated.

      sf_credentials = json.loads(CACHE["SNOWFLAKE_CREDENTIALS"])                                                                                                                                                                                           
      CACHE["sf_options"] = {                                                                                                                                                                                                                               
          "sfURL": "{}.snowflakecomputing.com".format(sf_credentials["account"]),                                                                                                                                                                           
          "sfUser": sf_credentials["user"],                                                                                                                                                                                                                 
          "sfPassword": sf_credentials["password"],                                                                                                                                                                                                         
          "sfRole": sf_credentials["role"],                                                                                                                                                                                                                 
          "sfDatabase": sf_credentials["database"],                                                                                                                                                                                                         
          "sfSchema": sf_credentials["schema"],                                                                                                                                                                                                             
          "sfWarehouse": sf_credentials["warehouse"],                                                                                                                                                                                                       
          "session_parameters": {                                                                                                                                                                                                                           
              "QUERY_TAG": "Something",                                                                                                                                                                                                                    
          }                                                                                                                                                                                                                                                 
      }

In AWS Cloudwatch, I can find the parameter was sent with the other options. In snowflake, the parameter was never set.

I can add more detail where necessary, I just wasn't sure what details are needed.

Can you try removing the trailing comma in "QUERY_TAG": "Something", and also try passing other properties from https://docs.snowflake.com/en/sql-reference/parameters.html . Once you set them try fetching them back as shown in https://github.com/snowflakedb/snowflake-connector-python/blob/c156e628fbfb5eaa792e8882bf175be7b1055689/test/test_session_parameters.py — Prabhakar Reddy, Sep 03 '20 at 02:55
@PrabhakarReddy. Thanks for the suggestions. Trailing comas are a supported feature in python3 & makes several things easier, [see this question for more info on that](https://stackoverflow.com/questions/11597901/why-are-trailing-commas-allowed-in-a-list). I tested other session parameters, but none were working with the format above. — Chris McCullough, Sep 08 '20 at 14:18

score 0 · Accepted Answer · answered Sep 04 '20 at 16:45

It turns out that there is no need to specify that a given parameter is a session parameter when you are using the Spark Connector. So instead:

      sf_credentials = json.loads(CACHE["SNOWFLAKE_CREDENTIALS"])                                                                                                                                                                                           
      CACHE["sf_options"] = {                                                                                                                                                                                                                               
          "sfURL": "{}.snowflakecomputing.com".format(sf_credentials["account"]),                                                                                                                                                                           
          "sfUser": sf_credentials["user"],                                                                                                                                                                                                                 
          "sfPassword": sf_credentials["password"],                                                                                                                                                                                                         
          "sfRole": sf_credentials["role"],                                                                                                                                                                                                                 
          "sfDatabase": sf_credentials["database"],                                                                                                                                                                                                         
          "sfSchema": sf_credentials["schema"],                                                                                                                                                                                                             
          "sfWarehouse": sf_credentials["warehouse"],                                                                                                                                                                                                                                                                                                                                                                                                                                
          "QUERY_TAG": "Something",                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
      }

Works perfectly.

I found this in the Snowflake Documentation for Using the Spark Connector: Here's the section on setting Session Parameters

How to pass session parameters with python to snowflake?

1 Answers1