Snowflake: Display Query Results in Streamlit

So far in this series I’ve discussed how to create a simple native Streamlit app in Snowflake and connecting an app to Snowflake. In this article I’ll show some additional Streamlit navigation features as well as how to populate form elements from Snowflake data.

st.set_page_config

In order for the application to use all possible screen real estate, I’ll first add the Streamlit set_page_config() function to the app. In a standalone Streamlit app, this function can be used to set the page title, favicon and add menu items such as “Help” or “Report a Bug”. For this demo, I’ll use set_page_config to set the container layout to “wide” so that the app can use as much space as possible on the screen.

#IMPORT STREAMLIT LIBRARY
import streamlit as st
#IMPORT SNOWPARK SESSION
from snowflake.snowpark.context import get_active_session

#CREATE A SESSION VARIABLE
session = get_active_session()

#UPDATE THE PAGE CONFIG SETTINGS
st.set_page_config(layout="wide")

Adding a Sidebar

For the next iteration of this app, I would like to return the results of entries in RAW_DATA.TBL_EVT_LOG and use a selection box to choose the event type to display. Since the EVT_TYPE column can be populated with multiple values dynamically loading the values from Snowflake makes the app data-driven rather than hard coded. To help with application flow, I’ll add the new selection box to a sidebar that can be collapsed if desired.

st.sidebar

Adding a sidebar menu in Streamlit is done with a single line of code and leveraging the power of Python’s “with” command and proper indentation, I can nest several other objects inside of the sidebar easily.

st.selectbox

Streamlit also provides a slick way to add a selection box to our menu sidebar with a single line of code with st.selectbox(). Additionally by setting a variable equal to the st.selectbox function, the chosen value is automagically stored in that variable. For this example, the EVT_TYPE value will be stored in a variable and then passed to a SQL Command in later step.

The values in st.selectbox can be either hardcoded or dynamically loaded from a dataframe. For this example, I’ll load a dataframe with distinct EVT_TYPE values and populate the selectbox.

Building the Sidebar

First I’ll need to query Snowflake and load the distinct EVT_TYPE values into a dataframe to use in the select box. The app runs in a different database from where TBL_EVT_LOG is stored, so I’ll need to use the fully-qualified table names in my queries for the app. As an alternative, I could create a view in the STREAMLIT_DB; however, to keep this demo focused on the app, I’ll hold off on that for now.

Once the data is loaded into a dataframe, I can use that dataframe to populate the selectbox options. The selected value from the selectbox will be stored in var_evt_type to use later for filtering the query results.

Putting all the sidebar code together looks like the following:

#ADD THE SIDEBAR AND SELECT BOX
with st.sidebar:
    #SET THE SQL COMMAND TO LOAD THE DATAFRAME
    selectbox_sql = """
        SELECT EVT_TYPE
        FROM ST_DEMO_DB.RAW_DATA.TBL_EVT_LOG
        GROUP BY EVT_TYPE;
        """
    #COLLECT THE DATA VALUES INTO A DF
    selectbox_df = session.sql(selectbox_sql).collect()

    #CREATE THE SELECT BOX; STORE SELECTED VALUE IN VAR_EVT_TYPE
    var_evt_type=st.selectbox(label="Select Event Type:",options=selectbox_df)

Querying Snowflake With Variables

Now that I have a method to populate a selectbox with the EVT_TYPE values I want to filter on, I’ll need to query the data in TBL_EVT_LOG using the var_evt_type variable in my WHERE clause. I would like to display the EVT_TYPE, EVT_DESC, EVT_TIMESTAMP and EVT_USERNAME in my app, but alias them to remove “EVT_” from the column names. To make the sql command dynamic, I’ll use Python string formatting to substitute the chosen value for EVT_TYPE from the select box into the SQL command.

Like the prior article, I’ll collect the data into a datafram and use st.dataframe to write the output to the screen. One change is the addition of use_container_width parameter which allows the dataframe to fill the entire app container. Doing so will make wider columns easier to read on screen.

#BUILD SQL COMMAND
sql = """
SELECT
    EVT_TYPE as TYPE,
    EVT_DESC as DESCRIPTION,
    EVT_TIMESTAMP as TIMESTAMP,
    EVT_USERNAME as USER    
FROM
    ST_DEMO_DB.RAW_DATA.TBL_EVT_LOG
WHERE
    EVT_TYPE = '{evt_typ}'
"""
#FORMAT SQL STRING WITH VARIABLE
sql = sql.format(evt_typ=var_evt_type)

#QUERY SNOWFLAKE
df = session.sql(sql).collect()

#WRITE TO SCREEN
st.dataframe(data=df,use_container_width=True)

With all the code updates complete, I’ll update the st.header value to “TBL_EVT_LOG Viewer” and run the app. The entire app is less than 50 lines – which includes comments and spacing for ease of reading.

#IMPORT STREAMLIT LIBRARY
import streamlit as st
#IMPORT SNOWPARK SESSION
from snowflake.snowpark.context import get_active_session

#CREATE A SESSION VARIABLE
session = get_active_session()

#UPDATE THE PAGE CONFIG SETTINGS
st.set_page_config(layout="wide")

#WRITE THE HEADER
st.header("TBL_EVT_LOG Viewer")

#ADD THE SIDEBAR AND SELECT BOX
with st.sidebar:
    #SET THE SQL COMMAND TO LOAD THE DATAFRAME
    selectbox_sql = """
        SELECT EVT_TYPE
        FROM ST_DEMO_DB.RAW_DATA.TBL_EVT_LOG
        GROUP BY EVT_TYPE;
        """
    #COLLECT THE DATA VALUES INTO A DF
    selectbox_df = session.sql(selectbox_sql).collect()

    #CREATE THE SELECT BOX; STORE SELECTED VALUE IN VAR_EVT_TYPE
    var_evt_type=st.selectbox(label="Select Event Type:",options=selectbox_df)

#BUILD SQL COMMAND
sql = """
SELECT
    EVT_TYPE as TYPE,
    EVT_DESC as DESCRIPTION,
    EVT_TIMESTAMP as TIMESTAMP,
    EVT_USERNAME as USER    
FROM
    ST_DEMO_DB.RAW_DATA.TBL_EVT_LOG
WHERE
    EVT_TYPE = '{evt_typ}'
"""
#FORMAT SQL STRING WITH VARIABLE
sql = sql.format(evt_typ=var_evt_type)

#QUERY SNOWFLAKE
df = session.sql(sql).collect()

#WRITE TO SCREEN
st.dataframe(data=df,use_container_width=True)

Streamlit also handles re-running the app automatically when a selection is changed which simplifies the code. Notice in the clip below that as the selection is changed, the query is re-executed against Snowflake and the dataframe is updated.

Conclusion

Snowflake and Streamlit both continue to impress with tools that make doing app and data development streamlined and intuitive! In this demo I covered adding some navigation features, connecting to Snowflake to run parameterized queries and displaying those results to the app.

Streamlit has a number of additional tools such as charts and graphs, maps and the list goes on. Not all features are supported natively in Snowflake yet, but I can only imagine they’re coming soon!