Building the Streamlit app
1. Building the Streamlit app
For a moment, imagine that I'm a senior manager that is not very happy with the basic text-as-equal workflow I have. The workflow doesn't deal well with joins and doesn't return very helpful searches of the structured data in my database. To fix this, I spent the time to build out a semantic model and a workflow that leverages agentic AI, and it now produces efficient, effective, and accurate returns. The heavy lifting is done and let's go the last mile. The last step I need is to build a front-end and deploy the workflow to users. This will let the users ask questions in natural language without the need for someone like me to craft a SQL query for them. No more interruptions and no more constant query tweaking. Real-time answers for everyone. More time for me to focus on high-value insights that can generate value. This is where I take the generative ability of AI and bridge the gap between builders and users. This is where all of my effort pays off. The foundation is built. It's time to make this interactive so users can start using it. No one is going to open a notebook and run the backend we've built, even though our notebooks are art and people should really appreciate them. But that's not going to happen. We need a front-end, like Streamlit, to make user interaction with this workflow painless. By the end of this video, you'll have switched from the backend logic to a real-world tool that everyone can use. You'll have built a fully functional Streamlit interface that brings all of this to life, where users can ask questions of their structured data using text-to-SQL and tag. No one will need to craft their own SQL query. No one will need to ask you to craft a SQL query either. And no one will need you to interpret the SQL results. Users will be able to ask their questions and get answers through a natural language conversation directly within LLM. All we have to do now is build this into an app that can be deployed to users so that they can start asking their questions. We're going to pull in a lot of the same code we wrote in our notebook, but for time's sake, I've already put it together in a new Python file for you to use with Streamlit. Get the code from the course repo to follow along if you don't already have it. All right, here we go. Remember back to the beginning of this course when we took a quick look at this app? Now we're going to set it up and see how it deals with multi-turn conversations and is able to correctly interpret context from previous conversation turns. Before we do that, click on the repo URL and navigate to Module 3. Download the Chat with Structured Data app Python file that has all of the source code needed to build the AI app. Next, go to your Snowflake account and navigate to the Projects tab in the left panel and select Streamlit. Select the blue button on the upper right side of the screen, which reads Streamlit app, to create your app. On the pop-up, name your app. I'm going to call it Chat with Structured Data app. Choose your app location by selecting the pull-down. Choose Database and choose Schema and choose the Warehouse as Cortex Analyst Warehouse. Streamlit starts with some sample code on the left pane. If you don't happen to see this, select the Edit button on the upper right to get back to it. Copy the Python code we downloaded earlier and paste it to replace the existing code into the left pane of the Snowsight UI. Once we've created the Streamlit app and pasted in our code, we need to install the Snowflake ML Python and Tabulate libraries. We'll use these in our app. Next, at the top of the file, we'll import some packages we need, notably Streamlit, JSON, and Underscore Snowflake. We'll also import Snowflake `get_active_session()` from Snowpark Context and Complete from Cortex. We're good to go now. We'll set the Streamlit page title here, and under that, we'll set the API endpoint and timeout. These match what we did earlier. Next, we initialize the state. We check that the keys, messages, and analyst conversation exist in the Streamlit session and create them as empty lists if not. We use SessionStates here in Streamlit because the script runs from top to bottom each time there's a user interaction, so we want to make sure that these are stored. I want to point out here that we're going to track two slightly different conversation histories in SessionState. The first, named Messages, will track the natural language query and the natural language response produced at the very end. This is what we'll display in our UI. The second, called AnalystConversation, will track the user query and Cortex Analyst's response. This is what we'll send to Cortex Analyst to enable multi-turn conversations. Then, we'll create a wrapper for our Cortex Analyst API call. Critically, note that the messages we pass in the request body are the full message history stored in our SessionState. We also pass semantic model file, which points at Cortex Analyst demo, revenue time series, and the YAML semantic file. This gives Streamlit everything it needs. And set the response to extract, parse, and return the JSON in Python format. We'll pull the text and SQL from the Cortex Analyst output if it's valid and add some error handling. Now that we're building a user-facing app, we want to allow for a broader variety of user inputs. This includes inputs that successfully result in SQL like we saw in the notebook, but also cases for a Cortex Analyst response by asking the user a follow-up question. If it asks a follow-up question, the value in our Cortex Analyst response will be text, so we can include that in our parsing. When SQL is detected, we append this to the statement that we defined earlier with text and statements. If an error shape is detected, we return the status, error, and the error message could not parse the Cortex Analyst output. Next, we'll build the key method that puts everything together. This is where we'll keep track of the conversation history with Cortex Analyst in our SessionState starting with the user message. Then, we can take the user message and make a call to Cortex Analyst using our entire conversation history up to this point. When we get the response from Cortex Analyst, we'll add it to our running conversation history tracked in Streamlit SessionState. If Cortex Analyst resulted in only a follow-up question or error, we'd stop here. If we do get SQL, then we can continue on to execute the SQL. And last, we can take the executed SQL results, convert them to Markdown, and pass them to the LLM. For our LLM call, we're using Cortex Complete, and I want to point out two things. The first is that we're turning guardrails on for this response. This is just like we did in the notebook. Because we're building an enterprise app, it's better to be safe here. The second is that we're using streaming to get output tokens as soon as they're available to us. This is great for lowering the perceived latency felt by the user, and they can start reading the response before the full response is generated. Mechanically, this works by setting stream equals true and returning a generator. Then later, we can pass the generator to a built-in Streamlit method for writing streams. The last Streamlit utility we'll set up is for displaying messages. This is the function that will render our conversation history we've tracked in the messages SessionState. Now, we can set up a DisplayMessages utility to show the entire message history. Note that this is different from the ST SessionState Analyst Conversation, which is just for Cortex Analyst. And then, we'll add a Clear Conversation option for users to start over. This is also helpful when the message history grows and starts to deteriorate the quality of Cortex Analyst responses. We add the button for this so users can reset the conversation when this happens. All right, we've almost made it to the end. So, what I'm going to do here is that once I've gotten the user's input, I'll first write the user input right away and add it to the conversation history. Then, we call the big function that calls Cortex Analyst, executes SQL, and then takes the SQL results and uses them to generate a natural language response. Remember, this method produces a generator so we can stream the response. Our users are going to love this. Last, once we finish streaming, we'll take the full response and add it to our tracking of the conversation history. Let's try out a couple of related back-and-forth queries to test its multi-turn functionality. In Module 1, we asked what the highest revenue for each sales region was. Let's do that again. Again, we see the interpretation of our question, the generated SQL statement, the SQL results, and the response back in natural language. Same as last time. Now, let's try asking a follow-up question and see how the app deals with it. What if I asked, what about by product? So here, I'm asking a lot of the app. I'm expecting the app to know that I'm still talking about revenue in this question, even though I'm not explicitly stating that. I'm relying on that multi-turn ability and conversation history being retained. Looking at the reframed question, it looks like it did that. It correctly restated my question, displaying that it knows that I'm still asking about daily revenue, but that I am now looking for each product year over year. Below, I get the generated SQL statement, which I can check if I have any questions, and I can see the results of that query here, same as before. Maybe I want to ask a third question. What about the lowest performing? Again, I'm relying on the multi-turn aspect of Cortex Analyst to understand what the lowest performing thing is that I'm referring to. Let's see how it does here. We get a restated question that has interpreted correctly that I'm looking for the lowest daily revenue for each product year over year. We get the SQL and the SQL results, and I get my natural language response, which tells me which are the lowest performing product lines. That's it. Well done. You've created your own Text as SQL app and connected it to a streamlined front end that makes it easy for users to interact with and get their questions answered all in natural language. People are really going to love this. We covered a lot in this video, and I recommend going back and experimenting with repurposing this for your own use cases. This build can be used in a variety of different ways. Really, a limitless number of ways. You are all ready to get started using this for your own projects. I'm very excited thinking about the unique ways that you'll use this structure in the future. Well done. This part of your AI journey is complete, but there is lots more to come. This space is developing so fast that there's always more to learn to remain on the leading edge. But for now, we'll close off this part. I'll see you in the next video, where I'll bid you farewell for now. Thank you.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.