Getting started with Stored procedures in Databricks SQL

0:04Hey, welcome back. We have Machu again. Hi, Machio. How are >> you? I'm doing good. Good to have you. Good to be back. >> Exactly. I'm good. Good to have you back. And we're going to be talking about a very interesting topic. Databricks introduced many many functions or features and one of them is store procedures. Yes, this is true. We have

0:26now store procedures. So yeah, there I'm really really really excited about this because this is probably one of the last major features that was missing from Databricks data warehousing capabilities and now it's it's there. So I think maybe uh maybe let's go back to what store procedure is and um and and see how the how this is important. So we introduced a little while back SQL

0:50scripting. So the ability to be able to create procedural logic using SQL. So we're using the SQL PSM standard, but most of it was up until now it was essentially client side. So the client had to create a procedure that would run it like in a notebook or it would have to do it in a in a in a client. And what a store procedure is we take

1:12that same logic, put it that same scripting logic to put it in a procedure and we can call it server side. Essentially we can say well I want to call this store procedure that does a whole bunch of things and then comes back with a bunch of results. something that's very very classic for data warehousing capabilities but that we had we were struggling to do up until now and so it's there it's in private preview actually no not private preview

1:34my mistake it's in public preview >> yeah and that's and that's uh that's good news it will be available in runtime 17 why because uh store processors are part of Apache part 4.0 know is also it's also open source. This is this is something very very important and we I in if I'm not wrong

1:56Matthew we're bringing also many other features to help in the uh migration process. We're talking about temp tables and recursive cities. >> Yes. And also multi-statement transactions. So those are all these are very very important things that that accompany. So um with this there's a

2:18whole bunch of things that we're working on um or actually actually actually in private preview that can accompany this. So the first and I think the two most important features in the context of procedural SQL are temp tables and multi-statement transactions. So multi-statement transactions is the ability to be able to do more than one

2:40statement as an acid transaction because currently the way that our asset capabilities work is just we are single statement transactions. But now with manage tables when we're using the private preview for multi-statement transaction multi table transactions we have the ability to have whole blocks like with with a begin end and roll back

3:02uh very classic approach uh that will be uh that is available to be done so that we can create an entire block of code that is does it that is atomic in its execution and if anything happens we can we can roll back. So um that's in private preview. The other element that's also there is the notion of temp table tables. That's also something that's used very frequently when we're doing ETL and data warehousing. The

3:24ability to create like temporary tables to store intermediate calculations and so that makes it a little bit easier. They get cleaned up at the end of the uh uh at the end of the script. So it just makes it a little bit easier for that logic that's also in private preview. So with store procedures, temp table and multi-statement transactions, we now have absolutely everything that is needed to be able to do procedural SQL

3:48on Databricks. So I'm really excited about this because this is a major major step. >> Yeah. So to summarize, we released SQL scripting, store pro store sort procedures, temp tables, recursive cities and multi-transaction tables. And of course we did introduce also collations which are also can be very very important regarding the usage that would be the usage and

4:12>> yeah well you mentioned your CCTS maybe I should describe what it is it's the ability to do recursively call uh essentially to be able to do uh hierarchy analysis um uh or to be able to do um let's say analysis graph analysis so that that's also bit of a of

4:34a niche but a very powerful feature that is that is often used to be able to do modeling or to be able to transverse graphs if we're model a graph on on top of a of a data warehouse. >> Can you show us quickly how we can get started with the store procedures like how find how can we define them? What would be the outcome? Uh how we can govern them?

4:56>> Sure. >> I I conveniently have a little demo for you. So the way it works is essentially so we create very so I'm doing this out of a notebook uh but we could also be uh use this out of the databicks SQL editor. I just find notebooks a lot easier to use

5:20when working with procedural SQL. That's that's my that's my thing. So um we do need a runtime 17. So this is why I've got one that's that's up with uh that's specifically for that. Um and then after that it's pretty it's pretty simple very standard approach where we just have to essentially declare the procedure. So here I'm just doing a very simple uh

5:44procedure here. I'm just creating a just calculates the area of a rectangle. I could have done that as a function but I'm also doing it as a procedure here. So this right now we can see this language is SQL. So this is an SQL. also be supporting in the near future store procedures in Python. Um, and then we can just describe it

6:07pretty. That's pretty easy. As with everything in Databricks, this becomes an a UC object. So as a unity catalog object, it becomes completely managed as part of the whole governance story. So I have it here. I can put security on it.

6:31Uh so I can put ACL's. It's it's fully governed um and usable by all the different user experience of Databricks. So uh it can be used in SQL. It could also potentially be called from notebooks. um using whatever runtime we want that supports it. So um it's it's fully integrated in terms in

6:54terms of governance. Now once I have declared it that's pretty easy. I just call it so. So here I'm just as you can see I just do a quick declaration of my variables and I just call my procedure since it calls it in here. So calls what I did here. That's pretty pretty simple. Interesting thing I can call procedure procedure. I could call a function from

7:16inside a procedure which is exactly what I'm doing here. I created a function. Um I create a procedure that calls that function. Um and then I just call it and that works works fine. So with that it just makes it a lot easier to Yeah. And it completes our story around data warehousing making it a lot easier for

7:40people who are used to using these types of objects to do it directly on on Databricks. So now we can do complete ETL or complex transformation using using SQL from from end to end using including procedural logic. So that just makes it very very easy to use

8:02SQL from end to end on Databricks and of course it makes it a lot easier uh for migrations as well because we can now bring in a lot of these objects that exist in many data warehouses as almost as is. >> That's amazing. And I think we're gonna also bring you back for um third video

8:25maybe next time about recursive cities and time tables. >> Sure. >> Yeah, we can do I'll be happy to come back. By the way, an interesting little piece of information now that we've got lake bridge out and we have stored procedure. stored procedure and SQL scripting will become the destination for code uh code migrations

8:49>> and I think we're going to also have a dedicated video to democratize the usage of lake bridge and like about all the components from the analyzer to the converter and of course data migration with lakeflow lakeflow connect and the validation to the commission the legacy that our house. Thank you, Machu. See

9:11you soon. >> Anytime. And you can call me anytime you want. >> Awesome.