Accessing and setting the session state

As with other SQL statements, a CALL statement runs within a session, and inherits context from that session, such as session-level variables, current database, etc. The exact context that the procedure inherits depends upon whether the stored procedure is a caller’s rights stored procedure or an owner’s rights stored procedure.

If a caller’s rights stored procedure makes changes to the session, those changes can persist after the end of the CALL. Owner’s rights stored procedures are not permitted to change session state.

Example:

CREATE OR REPLACE PROCEDURE MYPROC()
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.9'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
  EXECUTE AS OWNER
AS
$$
def run(session):
  session.sql("ALTER SESSION SET query_tag = 'test';").collect()
  return "SUCCESS"
$$;

CALL MYPROC();
-- Stored procedure execution error: Unsupported statement type 'ALTER_SESSION'.

Execute as caller:

CREATE OR REPLACE PROCEDURE MYPROC()
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.9'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
  EXECUTE AS CALLER
AS
$$
def run(session):
  session.sql("ALTER SESSION SET query_tag = 'test';").collect()
  return "SUCCESS"
$$;

CALL MYPROC();
-- SUCCESS

Sidenote: Understanding owner’s rights and Streamlit in Snowflake apps

Streamlit apps running in Streamlit in Snowflake run with owner’s rights and follow the same security model as other Snowflake objects that run with owner’s rights.

snowflake.snowpark.Session.query_tag

session.sql("ALTER SESSION SET query_tag = 'test';").collect()
# API call
session.query_tag = "test"

we cannot get details about session collect using collect as we have nothing to return.

Proper way to set query_tag is using connection properties and get parameters using SHOW PARAMETERS

    connection_parameters = {
    "account": "test",
    "user": "test",
    "password": "test",
    "role": "test", # optional
    "warehouse": "test_wh",  # optional
    "database": "test",  # optional
    "schema": "public", # optional
    "timezone": "UTC",
    "query_tag": "test"
   }

    session = Session.builder.configs(connection_parameters).create()
    session.sql("SHOW PARAMETERS LIKE 'TIMEZONE%' IN SESSION").show()
    session.sql("SHOW PARAMETERS LIKE 'query_tag%' IN SESSION").show()

-------------------------------------------------------------------------------
|"key"     |"value"  |"default"            |"level"  |"description"  |"type"  |
-------------------------------------------------------------------------------
|TIMEZONE  |UTC      |America/Los_Angeles  |SESSION  |time zone      |STRING  |
-------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------
|"key"      |"value"  |"default"  |"level"  |"description"                                       |"type"  |
-----------------------------------------------------------------------------------------------------------
|QUERY_TAG  |test     |           |SESSION  |String (up to 2000 characters) used to tag stat...  |STRING  |
-----------------------------------------------------------------------------------------------------------

Another approach using app_name

session = Session.builder.configs(connection_parameters).app_name("test_2").create() 
session.sql("SHOW PARAMETERS LIKE 'query_tag' IN SESSION").show()

------------------------------------------------------------------------------------------------------------------
|"key"      |"value"         |"default"  |"level"  |"description"                                       |"type"  |
------------------------------------------------------------------------------------------------------------------
|QUERY_TAG  |APPNAME=test_2  |           |SESSION  |String (up to 2000 characters) used to tag stat...  |STRING  |
------------------------------------------------------------------------------------------------------------------