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 docs.snowflake.com/en/sql-reference/parameters.html . Once you set them try fetching them back as shown in github.com/snowflakedb/snowflake-connector-python/blob/… — Prabhakar Reddy
@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. I tested other session parameters, but none were working with the format above. — Chris McCullough

Chris McCullough Chris McCullough · Accepted Answer · 2020-09-04T16:45:19

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 Answers