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!

This tutorial demonstrates how to use Python to process the statistical concept of Zipf's law and demonstrates the efficiency of Python's reading and sorting large text files when processing the law. You may be wondering what the term Zipf distribution means. To understand this term, we first need to define Zipf's law. Don't worry, I'll try to simplify the instructions. Zipf's Law Zipf's law simply means: in a large natural language corpus, the most frequently occurring words appear about twice as frequently as the second frequent words, three times as the third frequent words, four times as the fourth frequent words, and so on. Let's look at an example. If you look at the Brown corpus in American English, you will notice that the most frequent word is "th

This article explains how to use Beautiful Soup, a Python library, to parse HTML. It details common methods like find(), find_all(), select(), and get_text() for data extraction, handling of diverse HTML structures and errors, and alternatives (Sel

Python's statistics module provides powerful data statistical analysis capabilities to help us quickly understand the overall characteristics of data, such as biostatistics and business analysis. Instead of looking at data points one by one, just look at statistics such as mean or variance to discover trends and features in the original data that may be ignored, and compare large datasets more easily and effectively. This tutorial will explain how to calculate the mean and measure the degree of dispersion of the dataset. Unless otherwise stated, all functions in this module support the calculation of the mean() function instead of simply summing the average. Floating point numbers can also be used. import random import statistics from fracti

This article compares TensorFlow and PyTorch for deep learning. It details the steps involved: data preparation, model building, training, evaluation, and deployment. Key differences between the frameworks, particularly regarding computational grap

Serialization and deserialization of Python objects are key aspects of any non-trivial program. If you save something to a Python file, you do object serialization and deserialization if you read the configuration file, or if you respond to an HTTP request. In a sense, serialization and deserialization are the most boring things in the world. Who cares about all these formats and protocols? You want to persist or stream some Python objects and retrieve them in full at a later time. This is a great way to see the world on a conceptual level. However, on a practical level, the serialization scheme, format or protocol you choose may determine the speed, security, freedom of maintenance status, and other aspects of the program

The article discusses popular Python libraries like NumPy, Pandas, Matplotlib, Scikit-learn, TensorFlow, Django, Flask, and Requests, detailing their uses in scientific computing, data analysis, visualization, machine learning, web development, and H

This article guides Python developers on building command-line interfaces (CLIs). It details using libraries like typer, click, and argparse, emphasizing input/output handling, and promoting user-friendly design patterns for improved CLI usability.

This tutorial builds upon the previous introduction to Beautiful Soup, focusing on DOM manipulation beyond simple tree navigation. We'll explore efficient search methods and techniques for modifying HTML structure. One common DOM search method is ex


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

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SublimeText3 Chinese version
Chinese version, very easy to use

Dreamweaver Mac version
Visual web development tools

WebStorm Mac version
Useful JavaScript development tools

Notepad++7.3.1
Easy-to-use and free code editor

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
