Tsubasa KannoIntroduction
The exciting part of this update is that it's now easy and secure to identify the Snowflake user connected to the application and customize processing for each user.
Without needing to set up a custom login mechanism or user management table, you can personalize a single application for each user in ways like:
- Changing the application display for each user
- Preparing personalized analysis dashboards for each user
- Using row access policies to get different query output results for each user (Enterprise Edition and above)
In this post, we'll create a simple ToDo list that displays individual user information.
Note: This post represents my personal views and not those of Snowflake.
Feature Overview
Goals
- Manage personal ToDo lists using a single shared app
- Use row access policy to prevent other people's ToDos from being displayed
Completed Image
User TKANNO's screen
User TARO's screen
Prerequisites
- Snowflake account
- Enterprise Edition account is required to use row access policy
Note
- Streamlit in Snowflake runs with owner privileges, so Current_Role will be the same as the Streamlit in Snowflake application role. (Therefore, it cannot be used for personalization)
Procedure
Create a table to store the ToDo list
Execute the following command from a worksheet:
-- Create ToDo list table
CREATE TABLE IF NOT EXISTS todo_list (
id INT AUTOINCREMENT,
task VARCHAR(255),
status VARCHAR(20),
due_date DATE,
completed_date DATE,
owner VARCHAR(50)
);
Create a row access policy
This policy returns rows where the owner in the todo_list table matches the current_user connected to the Streamlit in Snowflake application.
Execute the following command from the worksheet:
-- Create row access policy
CREATE ROW ACCESS POLICY IF NOT EXISTS todo_row_access_policy
AS (owner VARCHAR) RETURNS BOOLEAN ->
owner = CURRENT_USER();
Apply the row access policy
Execute the following command from the worksheet:
-- Apply row access policy
ALTER TABLE todo_list ADD ROW ACCESS POLICY todo_row_access_policy ON (owner);
This completes the worksheet operations.
Run the Streamlit in Snowflake app
Create a new Streamlit in Snowflake app and copy & paste the following code:
Line 14 is where the current user connected to the app is retrieved as a string.
import streamlit as st
from snowflake.snowpark.context import get_active_session
import pandas as pd
# Layout settings
st.set_page_config(
layout="wide"
)
# Get Snowflake session
session = get_active_session()
# Get current user
current_user = session.sql("SELECT CURRENT_USER()").collect()[0][0]
# Get ToDo list
def get_todo_list():
return session.table("todo_list").to_pandas()
# Add or update task
def upsert_task(task_id, task, status, due_date, completed_date):
due_date_sql = f"'{due_date}'" if due_date else "NULL"
completed_date_sql = f"'{completed_date}'" if completed_date else "NULL"
if task_id:
session.sql(f"""
UPDATE todo_list
SET task = '{task}', status = '{status}', due_date = {due_date_sql}, completed_date = {completed_date_sql}
WHERE id = {task_id}
""").collect()
else:
session.sql(f"""
INSERT INTO todo_list (task, status, owner, due_date, completed_date)
VALUES ('{task}', '{status}', '{current_user}', {due_date_sql}, {completed_date_sql})
""").collect()
# Delete task
def delete_task(task_id):
session.sql(f"DELETE FROM todo_list WHERE id = {task_id}").collect()
# Main function
def main():
st.title(f"{current_user}'s Personal Dashboard")
# Task list
st.subheader(f"{current_user}'s ToDo List")
todo_df = get_todo_list()
# Display header
col1, col2, col3, col4, col5 = st.columns([3, 2, 2, 2, 2])
col1.write("Task")
col2.write("Status")
col3.write("Due Date")
col4.write("Completed Date")
col5.write("Delete")
# Display task list
for _, row in todo_df.iterrows():
col1, col2, col3, col4, col5 = st.columns([3, 2, 2, 2, 2])
with col1:
task = st.text_input("task", value=row['TASK'], key=f"task_{row['ID']}", label_visibility="collapsed")
with col2:
status = st.selectbox("status", ["Pending", "In Progress", "Completed"], index=["Pending", "In Progress", "Completed"].index(row['STATUS']), key=f"status_{row['ID']}", label_visibility="collapsed")
with col3:
due_date = st.date_input("due_date", value=pd.to_datetime(row['DUE_DATE']).date() if pd.notna(row['DUE_DATE']) else None, key=f"due_date_{row['ID']}", label_visibility="collapsed")
with col4:
completed_date = st.date_input("comp_date", value=pd.to_datetime(row['COMPLETED_DATE']).date() if pd.notna(row['COMPLETED_DATE']) else None, key=f"completed_date_{row['ID']}", label_visibility="collapsed")
with col5:
if st.button("Delete", key=f"delete_{row['ID']}"):
delete_task(row['ID'])
st.experimental_rerun()
# Update database immediately if values change
if task != row['TASK'] or status != row['STATUS'] or due_date != row['DUE_DATE'] or completed_date != row['COMPLETED_DATE']:
upsert_task(row['ID'], task, status, due_date, completed_date)
st.experimental_rerun()
# Add new task
st.subheader("Add New Task")
new_task = st.text_input("New Task")
new_status = st.selectbox("Status", ["Pending", "In Progress", "Completed"])
new_due_date = st.date_input("Due Date")
if st.button("Add"):
upsert_task(None, new_task, new_status, new_due_date, None)
st.success("New task added")
st.experimental_rerun()
# Main process
if __name__ == "__main__":
main()
Conclusion
What do you think? By combining Current_User and row access policy, you can create a secure application personalized for each user with simple steps. This opens up possibilities for creating even more user-friendly applications based on your ideas.
Some advanced ideas include adding Current_User information as a signature when writing to tables via Streamlit in Snowflake, or using personalized information as context for Cortex LLM to create a personal assistant.
Please try challenging yourself with interesting uses of Current_User!
Announcements
Snowflake What's New Updates on X
I'm sharing Snowflake's What's New updates on X. Please feel free to follow if you're interested!
English Version
Snowflake What's New Bot (English Version)
https://x.com/snow_new_en
Japanese Version
Snowflake What's New Bot (Japanese Version)
https://x.com/snow_new_jp
Change History
(20240914) Initial post
