Streamlit in Snowflake: Read and write from Google sheet (Part 3)

I have been looking for a fun project to run on Streamlit for a while and I think this is a good one to test out Streamlit in Snowflake.

I have been looking for a fun project to run on Streamlit for a while and I think this is a good one to test out Streamlit in Snowflake.

UI

This is a simple UI that allows you to:

• Read from a table in Snowflake and dump the result into Google sheet.

• Write data from Google sheet into Snowflake table

code: 

# Import python packages
import streamlit as st
from snowflake.snowpark.context import get_active_session

# Get the current credentials
session = get_active_session()

# Write directly to the app
st.title("Google sheet data pipeline")


with st.expander("Write to google sheet"):
    st.write("""
        Write data to google sheet from snowflake table
        """)
    with st.form("write"):
        table = st.text_input(
            "Table name (enter the full table name):",
            placeholder="Table name",
        )
        spreadsheet_id = st.text_input(
            "Google sheet ID: ",
            placeholder="Google sheet ID",
        )
        
        sheet_range = st.text_input(
            "sheet range(sheet1!A:Z): ",
            placeholder="sheet range",
        )
        submitted = st.form_submit_button("Submit")
        if submitted:
            result = session.sql(f'''
                CALL SAMPLE_DATA.TPCH_SF1.WRITE_TO_GSHEET('{table}', '{spreadsheet_id}', '{sheet_range}')
                ''')
            print(result.collect())
            

with st.expander("Read from google sheet to snowflake table"):
    st.write("""Export data from google sheet to snowflake""")
    with st.form("read_sheet"):

        spreadsheet_id = st.text_input(
            "Google sheet ID: ",
            placeholder="Google sheet ID",
        )
        
        sheet_range = st.text_input(
            "sheet range: ",
            placeholder="sheet range",
        )
        table = st.text_input(
            "Enter destination table name",
            placeholder="Table name",
        )
        database = st.text_input(
            "Enter destination database name",
            placeholder="database",
        )
        schema = st.text_input(
            "Enter destination schema name",
            placeholder="schema",
        )        

        submitted = st.form_submit_button("Submit")
        if submitted:
            result = session.sql(f'''
                CALL SAMPLE_DATA.TPCH_SF1.READ_SHEET('{spreadsheet_id}', '{sheet_range}', '{table}', 
            '{database}', '{schema}')
                ''')
            print(result.collect())

Code breakdown:

Import packages:

• Streamlit is imported to create the web application interface.

• snowflake.snowpark.context provides access to the Snowflake session and SQL execution capabilities.

Establish Snowflake Connection:

• get_active_session() retrieves the active Snowflake session, enabling interaction with the database.

Write to Google Sheet Section:

with st.expander("Write to google sheet") create a section that the user can toggle for section visibility.

with st.form("write") create form, the form has 3 fields table, spreadsheet_id, and sheet_range. Once the form is filled the user clicks on the submit button to trigger WRITE_TO_GSHEET procedure.

Call WRITE_TO_GSHEET procedure:

This is where we use the collected fields from the form to send requests to Google Sheets API. We have a procedure created and it accepts 3 arguments.

result = session.sql(f'''
                CALL SAMPLE_DATA.TPCH_SF1.WRITE_TO_GSHEET('{table}', '{spreadsheet_id}', '{sheet_range}')
                ''')

Read from Google Sheet Section:

This section reads from Google Sheets and dumps the data into a snowflake table.

with st.expander("Read from Google sheet to Snowflake Table"), create a section that the user can toggle for section visibility.

with st.form("read_sheet"); create form, the form has 3 fields spreadsheet_id, sheet_range, table, database, and schema. Once the form is filled the user clicks on submit button to trigger READ_SHEET.

Call READ_SHEET function:

This is where we use the collected fields from the form to send request to Google Sheets API. We have a procedure created and it accepts 5 arguments.

spreadsheet_id: this is the id of the Google sheet you are reading from.

sheet_range: specifies sheet and range. The format could be sheet1!A:Z or A22:Z22 depending on your use case.

table: The name of the table you want to write to on Snowflake. Note: if the table already exists, this function overwrites the table.

schema: Destination schema in Snowflake.

Database: Destination database in Snowflake.

Once the submit button is clicked the function is called and the code starts to execute.

result = session.sql(f'''
                CALL SAMPLE_DATA.TPCH_SF1.READ_SHEET('{spreadsheet_id}', '{sheet_range}', '{table}', 
            '{database}', '{schema}')
                ''')

You can get creative with the UI by adding spinner for a better user experience, and add options to overwrite, and append.

Demo:

Closing note:

Key Points from the tutorial:

• Highlight the steps required to set up Google Oauth credentials.

• Guide users on obtaining necessary Snowflake credentials and stored procedure set up.

• Guide users on how to create external network access and integrations in Snowflake

• Explain the role of Snowflake stored procedures in handling data transfer.

• Guide users on how Google API can be used to perform basic CRUD operations

• Emphasize the integration of Streamlit for a user-friendly interface.

Thanks for reading this series and I hope it’s helpful.

Full code: https://github.com/hammedb197/google_sheet_snowflake

Reference :

• https://developers.google.com/sheets/api/guides/concepts

• https://www.snowflake.com/en/data-cloud/overview/streamlit-in-snowflake/