Apps - Streamlit in Snowflake - Part II
1. Apps - Streamlit in Snowflake - Part II
We left off just when things were starting to get interesting. We were about to define the two functions that run the SQL queries to pull the data we need. Let’s talk about that now. First we’ll hide the app for a moment so we can see the code full screen. Okay, for the first function, you give it a list of city names, and a start year and an end year, and it will calculate the total order value for each day in that range for each of those cities. You’ll notice that it generates the SQL dynamically – you can see city_names, start_year, and end_year inside the curly brackets. Then it converts the result to pandas, and returns that plus the text of the SQL query that you used to generate that data. The next function – get_unique_cities – is much simpler. It takes no arguments, and we’ll use it later to generate the city dropdown menu. Each of these two functions has an st.cache.data operator above it, which just means that streamlit will pay attention to whether you’ve already run that function for that particular set of inputs before, and if you have, it will pull the results out of cache so you don’t have to run it again unnecessarily. It’s very smart. Cool, so believe it or not, we’re almost done with this app. It’s less than 100 lines of code total. The next function, get_city_sales_chart, takes in a dataframe, and uses the Altair library to generate the main chart we see in the app. And I’ll pull up the app so you can see that again. You can see that it creates a line with “tooltip=True,” and that lets us hover over the chart and see the corresponding values. And among other things, it’s specifying the labels for the axes. And the last function here is just one that helps us format the SQL output so it looks nice. Okay, so so far, we haven’t actually run these functions. We’ve only created their logic. So that’s about to change. So this first_col, second_col line separates what comes next into two columns. And to specify what should go in each of these invisible columns we’ve created for layout purposes, we have two “with” statements. The first one supplies what should happen in the first column, which is the one that has a slider (you can see st dot select_slider). This saves the values you get from that slider as start_year and end_year, and those are the values we’ll feed into the get_city_sales_data function we created above when it actually comes time to call it. The second column is the multiselect that gives us back the list of cities we want to query data for, and that will also be an input into the get_city_sales_data function. (You need that so the app knows which cities you’re looking for data for! The app can’t read your mind.) And any text in the app you can find here in the code – Like the text above the slider, and the text above the multiselect. So let’s jump down and look at this sales_data, sales_sql line. This is where we actually call get_city_sales_data, and get back the dataframe we’re going to use to make our chart! And finally, we create three tabs – chart_tab, dataframe_tab, and query_tab – that correspond to each of the tabs we can toggle through on the app. And then we assign content to each tab – the altair chart, the dataframe, and the formatted sql. So I don’t expect you to have absorbed all or even much of that, but I want to pause for a moment to reflect on the fact that in fewer than 100 lines of Python (including our import statements), we created this app. It has layouts that change dynamically if I narrow the window. It has a slider and a multiselect that the chart below dynamically takes as input to refresh. And I’ll note that this multiselect is pretty sophisticated. It offers suggestions as you’re typing. You can use it as a dropdown. Our app has multiple tabs we can toggle between. We can hover over the chart to see the underlying values. This is pretty impressive. And that doesn’t even talk about the scale. That orders_v view that we’re querying has more than 690 million rows, and we can generate these app results *fast*. That’s because the Streamlit code is integrated into Snowflake itself, and Snowflake scales so well. So cool. To recap, here’s what we covered One, open-source Streamlit and Streamlit in Snowflake. Two, the Streamlit app gallery. Three, the different parts of the Streamlit in Snowflake UI. Four, how to create a session object using get_active_session from snowlake.snowpark. Five, the basics of the st.cache_data operator. Six, how to run a SQL query using session.sql. And seven, a bunch of streamlit fundamentals, like creating a title with st.title, outputting text with st.write, creating a divider with st.divider, creating columns with st.columns, creating a slider with st.select_slider, creating a multiselect with st.multiselect, and creating tabs with st.tabs I do not expect you to memorize all of these details – Instead, my hope is that you’ll emerge from this video feeling like you’re not many steps away from being able to build really cool apps with Streamlit in Snowflake. And this might be too much to hope for, but I’d also love it if you came away from this feeling that the Streamlit end result and creation process can be very beautiful.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.