Personalize for each user with Streamlit in Snowflake (SiS)

Introduction

At the end of July 2024, Current_User and Row Access Policy became available in Streamlit in Snowflake.

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

Original Japanese Article

https://zenn.dev/tsubasa_tech/articles/a23029dfe97c46

The above is the detailed content of Personalize for each user with Streamlit in Snowflake (SiS). For more information, please follow other related articles on the PHP Chinese website!