How to Connect Streamlit to Snowflake

Streamlit is a powerful Python library for building interactive web applications, and Snowflake is a robust cloud data platform. Connecting the two allows you to create data-driven apps with real-time access to your Snowflake data warehouse. This guide walks you through the steps to set up a secure connection between Streamlit and Snowflake.

Prerequisites

Before you begin, ensure you have:

• A Snowflake account with access to a database, schema, and table.

• Python 3.11 installed on your system.

• A Streamlit app environment set up.

• Snowflake credentials (account name, username, password, or other authentication details).

• Basic knowledge of Python and SQL.

Step 1: Create sample dataset in snowflake

CREATE DATABASE PETS;

CREATE TABLE MYTABLE (
    NAME    varchar(80),
    PET    varchar(80)
);

INSERT INTO MYTABLE VALUES ('Mary', 'dog'), ('John', 'cat'), ('Robert', 'bird');

SELECT * FROM MYTABLE;

Step 2: Create a python virtual environment install required libraries

To connect Streamlit to Snowflake, you'll need the snowflake-snowpark-python library for database connectivity and streamlit for the app interface. Install them using pip:

python3.11 -m venv venvsnowst 
source venvsnowst/bin/activate
pip install snowflake-snowpark-python streamlit

Step 3: Set Up directories and files

For security, avoid hardcoding credentials in your code. Instead, use environment variables or Streamlit's secrets management.

touch streamlit_app.py
mkdir .streamlit
touch .streamlit/secrets.toml

Step 4: Set Up directories and files

Add the following code to your streamlit_app.py

import streamlit as st
from snowflake.snowpark import Session

st.title('❄️ How to connect Streamlit to a Snowflake database')

# Establish Snowflake session
@st.cache_resource
def create_session():
    return Session.builder.configs(st.secrets.snowflake).create()

session = create_session()
st.success("Connected to Snowflake!")

# Load data table
@st.cache_data
def load_data(table_name):
    ## Read in data table
    st.write(f"Here's some example data from `{table_name}`:")
    table = session.table(table_name)

    ## Do some computation on it
    table = table.limit(100)

    ## Collect the results. This will run the query and download the data
    table = table.collect()
    return table

# Select and display data table
table_name = "PETS.PUBLIC.MYTABLE"

## Display data table
with st.expander("See Table"):
    df = load_data(table_name)
    st.dataframe(df)

## Writing out data
for row in df:
    st.write(f"{row[0]} has a :{row[1]}:")

Let's break down this Streamlit-Snowflake integration into four key steps:

Step A: Import Necessary Libraries and Set Up Streamlit Title

• The script starts by importing streamlit for UI elements and Session from snowflake.snowpark for database interaction.

• A title is added using st.title() to introduce the app.

Step B: Establish a Connection to Snowflake

• The function create_session() initializes a Snowflake session using credentials stored in st.secrets.snowflake.

• The @st.cache_resource decorator ensures the session persists across multiple app reruns, improving efficiency.

• Once connected, st.success() displays a confirmation message.

Step C: Load and Process Data from Snowflake

• load_data(table_name) retrieves data from a specified Snowflake table.

• Inside this function:

  • Data is fetched using session.table(table_name).

  • A computation is performed (table.limit(100)) to restrict results to 100 rows.

  • table.collect() executes the query and downloads the data.

• The function is wrapped in @st.cache_data, meaning results are cached for performance optimization.

Step D: Display Data in Streamlit UI

• The table name (PETS.PUBLIC.MYTABLE) is defined.

• Using st.expander(), an expandable section is created to show the table.

• st.dataframe(df) presents data in a structured format.

• Finally, the script iterates through the rows and dynamically formats the output using emojis (st.write(f"{row[0]} has a :{row[1]}:")).

Step 5: Configuring access credentials using secrets management

Now, let's add the following content to the .streamlit/secrets.toml file we created earlier

[snowflake]
user = "enter_your_password_here"
password = "enter_your_password_here"
account = "enter_your_account_key_here"

Step 6. Launching the Streamlit app

"With streamlit_app.py and .streamlit/secrets.toml set up, it's time to return to the terminal and launch the Streamlit app

streamlit run streamlit_app.py

Final Web UI:

Conclusion:

Connecting Streamlit to Snowflake enables you to build powerful, data-driven web applications with minimal effort. By following these steps, you can securely query your Snowflake data and present it in an interactive Streamlit interface. Experiment with visualizations and user inputs to create engaging apps tailored to your data needs.

Reference:

https://www.youtube.com/watch?v=SgWxkAdjK78