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.
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