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!

The reasons why Python scripts cannot run on Unix systems include: 1) Insufficient permissions, using chmod xyour_script.py to grant execution permissions; 2) Shebang line is incorrect or missing, you should use #!/usr/bin/envpython; 3) The environment variables are not set properly, and you can print os.environ debugging; 4) Using the wrong Python version, you can specify the version on the Shebang line or the command line; 5) Dependency problems, using virtual environment to isolate dependencies; 6) Syntax errors, using python-mpy_compileyour_script.py to detect.

Using Python arrays is more suitable for processing large amounts of numerical data than lists. 1) Arrays save more memory, 2) Arrays are faster to operate by numerical values, 3) Arrays force type consistency, 4) Arrays are compatible with C arrays, but are not as flexible and convenient as lists.

Listsare Better ForeflexibilityandMixdatatatypes, Whilearraysares Superior Sumerical Computation Sand Larged Datasets.1) Unselable List Xibility, MixedDatatypes, andfrequent elementchanges.2) Usarray's sensory -sensical operations, Largedatasets, AndwhenMemoryEfficiency

NumPymanagesmemoryforlargearraysefficientlyusingviews,copies,andmemory-mappedfiles.1)Viewsallowslicingwithoutcopying,directlymodifyingtheoriginalarray.2)Copiescanbecreatedwiththecopy()methodforpreservingdata.3)Memory-mappedfileshandlemassivedatasetsb

ListsinPythondonotrequireimportingamodule,whilearraysfromthearraymoduledoneedanimport.1)Listsarebuilt-in,versatile,andcanholdmixeddatatypes.2)Arraysaremorememory-efficientfornumericdatabutlessflexible,requiringallelementstobeofthesametype.

Pythonlistscanstoreanydatatype,arraymodulearraysstoreonetype,andNumPyarraysarefornumericalcomputations.1)Listsareversatilebutlessmemory-efficient.2)Arraymodulearraysarememory-efficientforhomogeneousdata.3)NumPyarraysareoptimizedforperformanceinscient

WhenyouattempttostoreavalueofthewrongdatatypeinaPythonarray,you'llencounteraTypeError.Thisisduetothearraymodule'sstricttypeenforcement,whichrequiresallelementstobeofthesametypeasspecifiedbythetypecode.Forperformancereasons,arraysaremoreefficientthanl

Pythonlistsarepartofthestandardlibrary,whilearraysarenot.Listsarebuilt-in,versatile,andusedforstoringcollections,whereasarraysareprovidedbythearraymoduleandlesscommonlyusedduetolimitedfunctionality.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Zend Studio 13.0.1
Powerful PHP integrated development environment

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SublimeText3 Chinese version
Chinese version, very easy to use

Atom editor mac version download
The most popular open source editor
