Complex procedural logic with stored procedures
1. Complex procedural logic with stored procedures
Earlier, you used UDFs to perform very specific computations. They helped us perform conversions between units of measurement, and they scaled really well when we used them to derive new columns in a view. UDFs excel at this, but when you want to go beyond specific computations, and perhaps capture and reuse more complex logic, then you might want to consider a stored procedure over a UDF. Before getting into the details of stored procedures, I'm sure you might be wondering, what exactly constitutes more complex logic? Well, the exact specifics will of course depend on your use case, but generally speaking, stored procedures might, for example, execute a series of operations that could include multiple SQL statements, transactional logic, or other complex workflows. For example, a stored procedure could be used to process an order, update inventory, generate reports, or maybe execute some other specific critical business logic. But the gist is that stored procedures are a set of instructions that typically contain logic that is more complex and broader in scope than a user-defined function. Stored procedures can even call previously defined UDFs within them, in case you need to leverage a UDF within the logic you're writing in the procedure. The pattern for working with stored procedures is similar to the pattern for working with UDFs. You define the procedure and write its definition in one of the supported languages, SQL, JavaScript, Python, Java, or Scala. You can then call the stored procedure using the call syntax and specify the name of the procedure. You can also write and call stored procedures directly within Snowflake, or in a development environment that supports one of the languages that I mentioned earlier. Follow along with me to create a stored procedure that we'll use in our pipeline. We'll use it to process the stream that we created previously. Now is a good time to pause the video and make sure you're logged into your Snowflake account. Navigate to the orders header sprocs SQL file in the module 3 folder. Copy the entire file and paste it into a new SQL worksheet. Let's first understand what this stored procedure will do. It's going to process the order header stream that we created earlier that tracks changes to the order header table. It'll specifically query the stream, determine if any orders occurred in Hamburg, and if so, return the total sales amount for those orders that day. It'll also write these results to a new table. We want to create this because in addition to monitoring the weather, our analyst teammates now want to keep a fresh eye on the sales there as well. We create the stored procedure using create or replace procedure and specify a path and a name. You'll know that the procedure lives in the raw POS schema alongside the stream that's also in that schema. We specify a few parameters that are relevant to the stored procedure's definition. We specify that it will return a string value. We'll specifically return a success message to signal that the stored procedure executed successfully. We're going to use Snowpark for Python to write the logic, so we know that the language is in Python. We specify a Python runtime, in this case, 310. We then specify the handler, and this is just a reference to the function containing the logic, which you can quickly see down here. And finally, we'll specify any Python package that we'll need in our logic. We're using Snowpark for Python, so we'll indicate here Snowflake, Snowpark, Python. Just like UDFs, the magic is in between these dollar sign delimiters. This is where we can write all of our Python code. Our logic is mostly in the process order header stream function. We do a few main things in this function. We query the stream and look at all new inserts that were recorded against the order header table. We then look up whether any of the new orders occurred in the city of Hamburg, and we do this by looking up the location of the order using location ID. If there were sales recorded, we sum the sales and then write the total sales for that day to a new table. Okay. Run the SQL statements that set your context, and then run the block of SQL that creates the stored procedure. Now let's test out our stored procedure. Let's insert some dummy order data for a location in Hamburg. Run the insert into statement. Next, run the select statement to verify the insert. All right, there it is. Finally, call the stored procedure. You can see the syntax for this here. Let's go ahead and run the last line of SQL. And there it is, 4580. And we know this value is correct because it matches the value in the dummy data that we inserted. Super cool. To take this even further, you could imagine us automating this stored procedure to execute, say, every 24 hours. This way, we could keep track of daily sales in Hamburg easily. We haven't learned about this type of automation yet, but we will in an upcoming module. Stored procedures are incredibly powerful. They make it easy to take large swaths of logic, place them in a centralized location, and then reuse them as needed. It's incredibly common to use them to perform aggregations, to update records, perform complex procedural logic, and more. I mentioned this briefly earlier, but stored procedures become even more powerful when you start automating them. We'll learn about that sort of automation in an upcoming module. For now, let's recap what you learned. You learned that stored procedures are used to store and define complex logic that can be easily reused at scale. You learned that stored procedures can be written in one of several supported languages. And you learned that you can call stored procedures using the call syntax. Up next, let's take a look at how to process data even faster with dynamic tables.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.