Streamlit in Snowflake (SiS): Identify SiS App Creators

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

Are you looking to identify which users have created Streamlit in Snowflake (SiS) applications in a given Snowflake account? In this article, I will share the approach I took.

Assumptions

For this example, 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 can see all STREAMLIT objects in a Snowflake account (i.e. ACCOUNTADMIN).
• The user has access to a role that has access to the SNOWFLAKE database and can query the following ACCOUNT_USAGE view: QUERY_HISTORY.
• The Snowsight UI’s SQL worksheet is being used to store and execute the queries provided throughout the article.
• This walkthrough assumes the SQL worksheet already set up the proper role, database, schema, and warehouse contexts.

QUERY_HISTORY View

In the SNOWFLAKE database, there is the ACCOUNT_USAGE schema with the QUERY_HISTORY view that captures “Snowflake query history by various dimensions (time range, session, user, warehouse, etc.) within the last 365 days (1 year)” (Snowflake).

Querying QUERY_HISTORY

To get a familiarity with how many columns and what type of information is provided by the QUERY_HISTORY view, run the following SQL query in a SQL worksheet:

SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
LIMIT 1;

Of the columns in the QUERY_HISTORY view, the ones that are worthwhile to keep are QUERY_TEXT, QUERY_TYPE, USER_NAME, and ROLE_NAME. Run the following SQL query:

SELECT QUERY_TEXT, QUERY_TYPE, USER_NAME, ROLE_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
LIMIT 1;

Identifying Streamlit Object Names

Knowing that SiS applications are created either programmatically or through the Snowsight UI, here are the potential query text starters:

• CREATE STREAMLIT
• CREATE OR REPLACE STREAMLIT

Now, restructure the SQL query to include a WHERE clause where the query text starters are being searched for in the QUERY_TEXT column and then run it:

SELECT QUERY_TEXT, QUERY_TYPE, USER_NAME, ROLE_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TEXT LIKE 'create STREAMLIT%' 
  OR QUERY_TEXT LIKE 'CREATE OR REPLACE STREAMLIT'
  AND EXECUTION_STATUS = 'SUCCESS';

Query Result:

Temporary Table Creation

With the revised query that leverages the QUERY_HISTORY view, create a temporary table:

CREATE TEMPORARY TABLE IF NOT EXISTS CREATE_ST_QUERY_TBL (QUERY_TEXT STRING, QUERY_TYPE STRING, USER_NAME STRING, ROLE_NAME STRING)
AS
SELECT QUERY_TEXT, QUERY_TYPE, USER_NAME, ROLE_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TEXT LIKE 'create STREAMLIT%' OR QUERY_TEXT LIKE 'CREATE OR REPLACE STREAMLIT' AND EXECUTION_STATUS = 'SUCCESS';

After table creation, query the temporary table to validate it contains everything that’s needed:

SELECT * FROM CREATE_ST_QUERY_TBL;

SHOW STREAMLITS Command

Assuming a user is leveraging the ACCOUNTADMIN role, the SHOW STREAMLITS command enables the role to “[list all] the Streamlit objects” in the Snowflake account (Snowflake Documentation).

Execute the Command

Run the following command:

SHOW STREAMLITS;

-- Another option that also works for ACCOUNTADMIN (may work with other powerful custom roles)
-- SHOW STREAMLITS IN ACCOUNT;

Retrieve Last Query ID

The LAST_QUERY_ID “returns the ID of a specified query in the current session”, which should be the “SHOW STREAMLITS;” query (Snowflake Documentation). Run the following context function:

SELECT LAST_QUERY_ID();

Validate the Query ID

To see if the query results are in line with what you’d expect, run the following SQL query:

SELECT * FROM TABLE(RESULT_SCAN('<query_id>'));

Temporary Table Creation

If the Query ID and its results are correct, then create a temporary table:

CREATE TEMPORARY TABLE STREAMLITS_TBL
AS
SELECT * FROM TABLE(RESULT_SCAN('<query_text>'));

After table creation, query the temporary table to validate it contains everything that’s needed:

SELECT * FROM STREAMLITS_TBL;

Identify SiS App Creators

Column Selection

From the STREAMLITS_TBL, here are the relevant columns to include in the SQL query: “database_name”, “schema_name”, “name”, and “title”.

SELECT
    "database_name",
    "schema_name",
    "name",
    "title"
FROM STREAMLITS_TBL;

From the CREATE_ST_QUERY_TBL, here are the relevant columns to include in the SQL query: USER_NAME and ROLE_NAME.

SELECT
  USER_NAME,
  ROLE_NAME
FROM CREATE_ST_QUERY_TBL;

Finding Distinct Matches Between Tables

To avoid duplicate records of a SiS application object, use the DISTINCT key word in the SELECT statement.

Write a JOIN operation between the STREAMLITS_TBL n and CREATE_ST_QUERY_TBL q that happens via the q.QUERY_TEXT and n.“name” columns.

To retrieve the matches between the two tables, use the LIKE operator and the ‘||’ operator to construct a search pattern to check if the QUERY_TEXT column contains the NAME value anywhere in its string value.

Query

To identify which users have created which SiS applications, run the following SQL query:

-- Note: Tables created from SHOW commands require the use of quotations
-- when referencing specific column names.

SELECT DISTINCT
    n."database_name" AS "DATABASE",
    n."schema_name" AS "SCHEMA",
    n."name" AS "NAME",
    n."title" AS "SIS_APP_NAME",
    q.USER_NAME AS "SIS_APP_CREATOR",
    q.ROLE_NAME AS "OWNER_ROLE"
FROM
    STREAMLITS_TBL n
JOIN
    CREATE_ST_QUERY_TBL q
ON
    q.QUERY_TEXT LIKE '%' || n."name" || '%';

Query Result:

In this Snowflake account, the ENGUYEN user used the SYSADMIN role to create the SiS application called TEST within the PUBLIC schema of the EXAMPLE database.

Wrapping up

Thank you for reading my post! To recap, I provided a step-by-step walkthrough of identifying SiS application creators.

If you have any questions, please post them in the comments below or contact me on LinkedIn.

Happy Streamlit-ing in Snowflake! ❄️