Tracking SiS Application Usage: The GA Update

Credit: This image was created with the assistance of DALL·E 2.

Are you looking to track Streamlit-in-Snowflake application usage for your Snowflake account? I solved this very problem with a hacky, interim solution back in March 2023, but, since SiS went GA in December 2023, I decided to update my solution.

In this post, you’ll learn:

1. How to create an SIS_USAGE table
2. How to add usage tracking within your SiS applications
3. How to identify the number of times a user has used an SiS application

What’s Changed

As SiS has transitioned from PrPr to PuPr to GA, there have been changes to certain actions within an SiS app, such as the following:

• CURRENT_SESSION() — returns None
• CURRENT_USER() — returns None
• SHOW commands — returns empty

Since my old article leveraged the CURRENT_SESSION() and CURRENT_USER() commands that are now disabled, it made sense to update my approach with a new article.

Assumptions

For this example walkthrough to work, the following assumptions have been made:

• The Snowflake account is hosted on either AWS or Azure and is not PrivateLink.
• The user has access to a role with the right privileges to create SiS applications.
• The user has access to a role that is the owner of one or more SiS apps.

How to create an interim SIS_USAGE table

▹ 1. In Snowsight, create a new SQL worksheet and set the appropriate role, database, schema, and warehouse context.

▹ 2. In the SQL worksheet, create the SIS_USAGE table:

Here is the SQL query text: CREATE TABLE IF NOT EXISTS STREAMLIT_DB.SIS.SIS_USAGE(TS TIMESTAMP_LTZ, STREAMLIT_APP STRING, USER_NAME STRING);

How to add usage tracking within your SiS applications

▹ 1. In the Snowsight UI, switch the role context to the SiS app owner (APPDEV in this example) and navigate to the Streamlit Apps section.

▹ 2. In the Streamlit Apps section, create a new SiS app or click on an existing SiS app.

▹ 3. In the SiS app’s Python worksheet, add a get_usage() function to execute an INSERT INTO statement for the SIS_USAGE table:

A get_usage() function is cached with st.cache_data().

(Note: In this case, ‘EXAMPLE_SIS_APP’ would be manually changed to the SiS app’s name.)

▹ 4. In the SiS app’s Python worksheet, add the following code to define a main() function that will run the get_usage() function when it is called:

How to identify the number of times a user has used an SiS application

▹ 1. In your current role, run the SiS application.

▹ 2. Then, switch between SiS applications where this approach has been set up.

▹ 3. After clicking through one or more SiS applications that your current role has access to, return to the SQL worksheet and run the following query:

SELECT TS, “STREAMLIT_APP” (concatenated cols), USER_NAME FROM STREAMLIT_DB.SIS.SIS_USAGE;

Expectant Output:

The query result displays a row of output for the following cols: TS, STREAMLIT_APP, and USER_NAME.

▹ 4. Since SiS now GA for AWS and Azure, running the CURRENT_ROLE() function will return None. Regardless of whether I’m in a role that is the SiS application owner or the SiS application user, an SiS application runs with the owner’s rights by default. This implies that retrieving CURRENT_ROLE() as ROLE_NAME will not provide accurate information for tracking purposes.

▹ 5. With that in mind, let’s leverage the table to identify the number of times a user has interacted with each SiS application:

SQL query counts STREAMLIT_APP and groups by DB_NAME, SCH_NAME, STREAMLIT_APP, and USER_NAME.

Expectant Output:

The SQL query result shows a row where ANONYMOUS user has used the EXAMPLE_SIS_APP twice.

▹ 6. Using the same table, let’s track what time of day users are interacting with each SiS application:

The SQL query enables you to track what time of day users are interacting with SiS apps.

Expectant Output:

The EXAMPLE_SIS_APP has had 2 morning sessions.

Wrapping up

Thank you for reading my post! To recap, I provided an updated step-by-step walkthrough of an SiS application usage tracking approach. If you have any questions, please post them in the comments below or contact me on LinkedIn.

Code Repo: emmynguyen/sis-usage-tracking (github.com)

Happy Streamlit-ing in Snowflake! 🎈