Building Account Monitoring App with Streamlit in Snowflake
Streamlit in Snowflake provides a Python editor in Snowsight where you can write, edit, and run code for a Streamlit app. Combining Streamlit’s component-rich, open-source Python library with the scale, performance and security of the Snowflake platform can turn data into interactive apps.
Streamlit in Snowflake, a faster way to
# Build — Turn python scripts into web apps (Define widgets — filters, graphs, sliders and more — as variables to interact with your data and models)
# Iterate — Add, Adjust or Remove components quickly (Modify your code and see changes go live with side-by-side editor and app preview screens)
# Share — Go from build to prod in one click (Deploy Streamlit apps in scalable, reliable infrastructure — then share via URLs that leverage existing role-based access controls.)
Lets build our own Snowflake Free Trail Account Admin App on the same metrics we have build the Snowsight Dashboard for account monitoring before.
1) Getting Started with Streamlit in Snowflake
Using a free trail account (observe the account I used has 1 day left in trail) in the left navigation bar, select Streamlit.
Note — Right now Streamlit in Snowflake is a preview feature available in only AWS region accounts.
If you find Streamlit is not activated for the account, as guided go to Admin → Billing & Terms and Enable Anaconda Python packages
Acknowledge Anaconda Packages terms and continue
Go back to Streamlit in the left navigation bar, Select + Streamlit
The Create Streamlit App window opens. Enter a name for your app, select the warehouse, database and schema for your app. Select Create.
The Streamlit in Snowflake editor opens an example Streamlit app.
The Streamlit in Snowflake interface is divided into three panes:
Object sidebar: Allows you to see the databases, schemas, and views you have permissions to access.
Streamlit editor: Provides a Python editor for your Streamlit code.
Streamlit preview: Displays the running Streamlit app.
By default, only the Streamlit editor and preview panes are displayed. To change the display, use the show/hide buttons in the lower-left corner of the Streamlit in Snowflake editor.
2) Advancing with code
You can view, add, and remove external Python packages for your Streamlit app by using the Streamlit editor in Snowsight. By default, Streamlit in Snowflake installs the python, snowflake-snowpark, and streamlit packages in your environment.
Let us add plotly python package which we need for the app we would be developing.
Copy the code given below into the streamlit editor
# Import python packages
import streamlit as st
import pandas as pd
from snowflake.snowpark.context import get_active_session
from snowflake.snowpark.functions import col
import plotly.express as px
#Header
st.title(":blue[Snowflake Free Trail Account Admin App] :snowflake:")
st.subheader("This app is for basic monitoring of snowflake free trail account that is valid for 30 days with $400 worth of free usage.")
st.markdown("This app is way forward alternative to snowflake dashboard - https://medium.com/@snowflakewiki/building-dashboard-for-snowflake-account-monitoring-349b93c7ea39")
st.divider()
# Get the current credentials
session = get_active_session()
credits_used_df = session.sql
#############################################
# Days Remaining for Free Snowflake Account Expiry
trail_days_left_sql = f"select datediff(d,current_date(),end_date - 1) as days_left_in_free_trial from snowflake.organization_usage.contract_items"
trail_days_left_df = session.sql(trail_days_left_sql)
pandas_trail_days_left_df = trail_days_left_df.to_pandas()
trail_days_left_tile = pandas_trail_days_left_df.iloc[0].values
# Remaining Free Usage of given $400
remaining_usage_sql = f"select round (400 - ((select round ((storage_bytes*effective_rate), 2) as storage_cost from (select (avg(a.storage_bytes + a.stage_bytes + a.failsafe_bytes) / power(1024, 4)) as storage_bytes, b.effective_rate from snowflake.account_usage.storage_usage a join (select effective_rate from snowflake.organization_usage.rate_sheet_daily where usage_type = 'storage' and date = current_date() - 1) b on 1 = 1 where a.USAGE_DATE = current_date() -1 group by all)) + (select credits_used * effective_rate as total_credits_used from (select sum(credits_used) as credits_used, d.effective_rate from snowflake.account_usage.metering_history c join (select effective_rate from snowflake.organization_usage.rate_sheet_daily where usage_type = 'compute' and date = current_date() - 1) d on 1 = 1 group by all))), 2)"
remaining_usage_df = session.sql(remaining_usage_sql)
pandas_remaining_usage_df = remaining_usage_df.to_pandas()
remaining_usage_tile = pandas_remaining_usage_df.iloc[0].values
# Column formatting
col1, col2 = st.columns(2)
col1.metric("Days Remaining for Free Snowflake Account Expiry","{:,}".format(int(trail_days_left_tile)))
col2.metric("Remaining Free Usage of given $400",remaining_usage_tile)
#############################################
# Compute Credits Used
credits_used_sql = f"select sum(credits_used) from snowflake.account_usage.metering_history"
credits_used_df = session.sql(credits_used_sql)
pandas_credits_used_df = credits_used_df.to_pandas()
credits_used_tile = pandas_credits_used_df.iloc[0].values
# Storage Cost in $
storage_cost_sql = f"select round ((storage_bytes*effective_rate), 2) as storage_cost from (select (avg(a.storage_bytes + a.stage_bytes + a.failsafe_bytes) / power(1024, 4)) as storage_bytes, b.effective_rate from snowflake.account_usage.storage_usage a join (select effective_rate from snowflake.organization_usage.rate_sheet_daily where usage_type = 'storage' and date = current_date() - 1) b on 1 = 1 where a.USAGE_DATE = current_date() -1 group by all)"
storage_cost_df = session.sql(storage_cost_sql)
pandas_storage_cost_df = storage_cost_df.to_pandas()
storage_cost_tile = pandas_storage_cost_df.iloc[0].values
# Column formatting
col3, col4 = st.columns(2)
col3.metric("Compute Credits Used","{:,}".format(int(credits_used_tile)))
col4.metric("Storage Cost ($)",storage_cost_tile)
st.divider()
#############################################
# Credit Usage by Warehouse
wh_credits_used_sql = f"select warehouse_name,sum(credits_used) as total_credits_used from snowflake.account_usage.warehouse_metering_history group by 1 order by 2 asc"
wh_credits_used_df = session.sql(wh_credits_used_sql)
pandas_wh_credits_used_df = wh_credits_used_df.to_pandas()
fig_wh_credits_used=px.bar(pandas_wh_credits_used_df,x='TOTAL_CREDITS_USED',y='WAREHOUSE_NAME',orientation='h',title="Credit Usage by Warehouse")
fig_wh_credits_used.update_traces(marker_color='orange')
# Storage in MB
storage_mb_sql = f"select 'Database Storage' as Storage_type, avg (storage_bytes) / power(1024, 2) as Storage_in_MB from snowflake.account_usage.storage_usage where usage_date = current_date() - 1 union all select 'Stage Storage' as Storage_type, avg (stage_bytes) / power(1024, 2) as Storage_in_MB from snowflake.account_usage.storage_usage where usage_date = current_date() - 1 union all select 'Failsafe Storage' as Storage_type, avg (failsafe_bytes) / power(1024, 2) as Storage_in_MB from snowflake.account_usage.storage_usage where usage_date = current_date() - 1 union all select 'Total Storage' as Storage_type, avg (storage_bytes + stage_bytes + failsafe_bytes) / power(1024, 2) as Storage_in_MB from snowflake.account_usage.storage_usage where usage_date = current_date() - 1"
storage_mb_df = session.sql(storage_mb_sql)
pandas_storage_mb_df = storage_mb_df.to_pandas()
fig_storage_mb_df=px.bar(pandas_storage_mb_df,x='STORAGE_IN_MB',y='STORAGE_TYPE',orientation='h',title="Storage in MB")
fig_storage_mb_df.update_traces(marker_color='green')
# Container: Credit Usage & Storage
container1 = st.container()
with container1:plot1, plot2 = st.columns(2)
with plot1: st.plotly_chart(fig_wh_credits_used, use_container_width=True)
with plot2: st.plotly_chart(fig_storage_mb_df, use_container_width=True)
st.divider()
#############################################
#Credit Usage Overtime
credit_usage_overtime_sql = f"select start_time::date as usage_date, warehouse_name, sum(credits_used) as total_credits_used from snowflake.account_usage.warehouse_metering_history group by 1,2 order by 2,1"
credit_usage_overtime_df = session.sql(credit_usage_overtime_sql)
pandas_credit_usage_overtime_df = credit_usage_overtime_df.to_pandas()
fig_credit_usage_overtime_df=px.bar(pandas_credit_usage_overtime_df,x='USAGE_DATE',y='TOTAL_CREDITS_USED',color='WAREHOUSE_NAME',orientation='v',title="Credits Used Overtime", barmode="group")
st.plotly_chart(fig_credit_usage_overtime_df, use_container_width=True)
st.divider()
#############################################
# Users vs WH Usage
user_wh_usage_sql = f"select warehouse_name, user_name, count(*) as no_of_queries from snowflake.account_usage.query_history where user_name <> 'system' and warehouse_name is not null group by 1,2"
user_wh_usage_df = session.sql(user_wh_usage_sql)
pandas_user_wh_usage_df = user_wh_usage_df.to_pandas()
fig_user_wh_usage_df=px.bar(pandas_user_wh_usage_df,x='NO_OF_QUERIES',y='USER_NAME',color='WAREHOUSE_NAME',orientation='h',title="Users vs WH Usage")
st.plotly_chart(fig_user_wh_usage_df, use_container_width=True)
st.divider()
#############################################use database demo_db;select Run to update the content in the Streamlit preview pane.
You can hide the sidebar & editor panes below to see only the streamlit preview pane.
As we have multi-page Streamlit app, select a tab to view additional pages or us the navigation bar on the right.
3) Manage Streamlit apps
View — You can view the list of streamlit apps created by the snowflake role or shared to the role being used, when we select Streamlit in the left navigation bar. Select the Streamlit app you want to view and the app opens in the Streamlit in Snowflake viewer.
Edit — In Streamlit in Snowflake viewer, you can Select Edit to update the code.
Share — You can share your Streamlit app with other Snowflake users assigned to a specific role. You can additionally copy the URL to streamlit app and and send through email or text.
Rename — Select the name of the app in the upper-left corner, enter the new name in the text box and click outside the text box to commit the change.
Delete — Select the name of the app in the upper-left corner. Select Delete, and then select Delete App.
Conclusion —
With Streamlit in Snowflake effective, we may not see any advances in Snowsight Dashboards going forward. So use this as a guide to get started and build your own visual web applications using Streamlit in Snowflake.
References:
https://docs.snowflake.com/en/developer-guide/streamlit/about-streamlit
Follow and Clap if you like the content and feel free to ask if you have any questions in the comments. I will be more than happy to assist and guide you.
