Migrate PL/SQL to Databricks: SQL Scripting Procedural Language Guide

0:04Hey, today I have the pleasure to host Fabian Contaminar. Hi everyone, I'm Fontaminar. I'm a specialist solution architect in data warehousing. I joined Databricks one year ago and it's been a blast. We recently announced the public preview of SQL scripting and I wanted him to give us more information and do a demo to show you how we can get started

0:27quickly with SQL scripting. So Fabian, can you tell us more about SQL scripting, the advantages and how we can get started? Yes, absolutely. So SQL scripting is a implementation in datab bricks from uh from SQL procedural languages that were born uh probably like in the end of 80 early in Oracle with PL/SQL with cybase and Microsoft

0:51with TSQL and so on and it allows you to do things that you couldn't do in PL SQL like in one query it fits the name SQL scripting is about doing scripting with SQL and scripting is like uh you can handle data pipe, you can build a wool pipeline, you can um handle errors, you can process your data, you can do loops

1:15on arrays. It uh it opens a lot of stuff for you to do in the platform with PL SQL. So the good thing that I love with this feature is like if you're a SQL guy, it's a kind of my case and you you get onboarded to Databricks, you may be a bit afraid of doing p by spark learning a new new language, you may feel you're not relevant anymore, but

1:38it's absolutely not true. You can do most of everything in SQL and SQL scripting just add new flavor for that. So I guess SQL scripting can be a good tool to help you maybe migrate your legacy data warehouse to Databricks. Basically you can do I don't know looping for each like everything you

2:00would have done in Python easily. Now you could do the same almost the same thing in SQL. Yeah, exactly. And this is what I will show you in the demo. So SQL scripting is about having SQL compounds which we sometimes shorten to blocks where you can can run any any SQL query any SQL command. So it's not only a query, it can be a credit tableable, can

2:23be an alter table and you can uh you can do some branching you can run conditional. So let's say you want to evaluate what is in what is in in one one parameter table for example and based on the condition maybe you want to do one thing or another thing that may be completely different. Of course, if you can do it in a join, do it with a join. But if you're doing like two very

2:46different logic, maybe it's better to split it. And uh yeah, conditional branching for this is perfect. And as you said, we have we have loops also loops like for loops. So you just spawn a query and you you iterate through all the rows and for each rows you will want to do something. So again, don't replace your joints with that. This is not the goal. But sometimes to do to do uh to

3:10do some um an inflation or or some maintenance, you want to be able to do that that kind of stuff. So if you're already a SQL savvy guy, probably it's perfect. If you if you already done PL/SQL on TSQL, it's not exactly the same syntax, but you won't be lost at all. You will recognize at least the patterns. And if you if you're super savvy in PISA, it's kind of the same. So

3:33the language difference is a bit is a bit more versus PL/SQL or TSQL. So if I want to benefit or use SQL scripting what are the requirements? Yeah, you need uh a DBR 16.3 uh version minimum. So anything uh Z version or above will work with SQL scripting. Amazing. Can you show us how

3:57we can get started quickly? And for those who are watching this recording, we will share with you the notebook so you can do the same thing on your site. And we'll also add in the description of this recording the documentation so you know everything so you can understand how you can use this syntax efficiently. Absolutely. So this is a lab we've run internally in

4:22uh in data but uh it uh it seems it's uh it's nice to show the features to everyone. So as you see I'm using here uh a DBS 16.3 uh cluster. So I I just have like some uh some uh some cleaning setting variable uh setting variable commands. I don't I don't uh I don't um build on because

4:45it's not super uh important here. And here we are starting to have like the core of the product. So the core of the product of the future SQL scripting is a component statement and basically it start with a begin and it it uh it finish with the end instruction and anything between will be SQL scripting. So for here I'm doing

5:07something super simple just having a declare variable and selecting the variable. Nothing very fancy. You must your your your cell must start with a with a begin statement otherwise it won't work as you see because here I've done the declare variable before doing the

5:29before doing the begin. So this file we're working on that but currently this is a this is a state. The good thing with the component statement is that they can be uh they can be embedded one into the other. So here as you see I have one principle begin statement. I'm using a variable and I'm nesting two other two other

5:52component statement inside and this works uh perfectly. You can put some labels also on the on the compound statements all beside the outer one. The most outer one can't have can't be label today but the inner ones you can label it. it can uh it can helps to uh to navigate through your different

6:15component statements. This one works. So it's exactly mostly the same example than above. We've put some some labeling here. One thing which is very good. So this fail is failing because actually as you see I'm starting a block one but I'm finishing the block one before finishing the block two. So it's like it's

6:37like it's like reversed. So it this is not allowed in the in the SQL scripting. Yeah. And even the error is straightforward. It shows that the beginning label exactly at the end which is great. So don't need to debug a lot to find this error. Absolutely. SQL is not uh you know it's

6:59not like Python. So in the passion is not very important. I mean for the engine because this is actually a valid statement. Of course don't work like that because it's it's super messy and it's very hard to maintain but it's it's really the keywords with the begin the ends the semicolon that that pilots that navigate

7:21through everything. So this is valid but please don't work like this. But there is something you can use. If you click on the on the on this cell, you can indent this cell right down like slash indent it will yeah I think prettyify it's prettyify my bad. Pritify it will make the uh the cell you go and you can just accept it. So quick

7:44tip for to improve your productivity. Yeah I was not over thank you super amazing. Let's run some flow logic. So here what I'm doing is I'm creating a boolean variable which I call vable and basically I'm just doing like a if if

8:06against this variable. So I'm doing a first run. So in the first run you see this variable is so I'm going to block one. I'm going to block two to block two and as this is true I'm leaving the block two. So I go back straight here. So this is my output but my output is this one. If I switch

8:28the value of the of the boolean and I go to false. So you see the output is changing. Now it's block two. So why is that? So this this one now is false. We go to block one. We go to block two. This one is false. So we don't do anything. We continue here. So I'm assigning the output variable at this at this step. And I'm leaving block one. And as block two is embedded into block

8:50one, I'm going straight here and I can output the value which is now block two. We have some variables of course into component statements. Uh so we use uh we use the standard syntax which is declare variable the data type and eventually a default. So you see you have like the long way to declare a

9:14variable or the short way. So here we are inferring the data type of the script. So you're declaring your variable with declare and you're you're setting your your variables with the set command. The those variables as they have been declared inside SQL scripting they are not session variables. They are they are really like SQL scripting variables and they are dropped at the

9:36end statement here. So here if I want to print the same to the two variables values I will run for it but they have been for forgotten. So we are getting an error here. Now I will declare a variable. So you see it's the same syntax but as I don't have any begin end they are not like SQL scripting variable they are like session variables. So here

9:58I have the variable var 1 and now I'm also creating another variable var 2 and you know so this is telling because I've used the short set command and set command outside of SQL scripting is uh the one we use to put some parameters for uh for for spark. So we don't allow

10:21it outside of SQL scripting. So this is why why this one fail. But if I comment and I use the longest the longest command set variable now it works. we declare the two variables var one and v2 as session variables. So now they are existing and I can use it outside of SQL scripting but I can also use them inside SQL scripting and you you see

10:45here I'm creating two new variables with the same name inside SQL scripting but you know they they are both living together. So those two ones are the one that I just declare here inside SQL scripting and I can reference the session variable with a with a session uh with a session uh keyword here and I

11:07can get those value. So of course this is not really a good practice. Please try to be more creative with your variable names. But if for whatever reason you need to have some variables sharing the same name, this is a good way to differentiate it. And you can also create variables inside component statements. So here we are we have the

11:30same variables and above and I'm creating a new block and I'm also creating a new variables with again the same name and those variables here they are they are tied to the closest variables existing. So those one are using like the one from the blocks. So the session variables are still the

11:53same. But if I go outside of this block and I check it's it's really the same command here. We will get the value of those two variables. So those two variables weren't dropped when we declared this one. So they are living together. We are working also to extend the features to be able to reference variables using uh using the labels. Oh

12:15into your variables you can you can prefix them with with the block. So as I'm using the label block here. So v one v2 it's more clear but of course as I said the v those variables will get deleted when uh we hit the end statement. So if I try to use them outside of this block it's failing

12:37because those two variable has been dropped. So all the sequel statements that you can use in the component. So as you seen you have already done some declare and set variables. It's nice and fun but you can you can look you can do other stuff. So here I have another example where again something very simple I have a logic a

13:00branching logic if something then here I'm trying to do a create table and inserting some data otherwise I will run a a create view. So for now I'm leaving it this one to true. I'm running the SQL scripting block. Quick way of how it work. We put the true here. So we went into this part of the of the if statement. So I've put

13:23a variable which we for the table name and I I created a table with the name from this variable and I inserted like one row again with um with the value of the table and some uh with a replace of the table just to replace the t by a col. And this is

13:45what I'm displaying here. I'm just selecting call from an item derivated from from the from the variable here. If I go to false. So now we're going into the other part of the logic. And here you see I'm cutting a table. But now I'm cutting a view and I'm just selecting the data from the view. And you know

14:07this is the good thing because here I didn't change my uh my finish statement but in the middle I did two things very different. So here creating a table, inserting a row, and here creating a view all in the same uh all with one command for branching. So uh you can you can do some pretty pretty advanced stuff here. Currently in SQL scripting, we're

14:29only displaying the last result set from any select and values. So as you see here, I've put two values, but the one you get displayed is only the second one. You won't see this one. So don't don't use uh the values for for doing like log for doing like logs or or break points. This is something that

14:53will be uh that is worked on but uh I have uh I have no commitment yet when we'll be uh ready to have multiple uh multiple uh result set. when you're working with variables, you can uh you can directly apply some functions to the variables. You don't have to always do a subselect. So here I

15:15have something. So I'm creating like a basic array and you see I'm doing here I'm taking the array I'm exploding it with a select and I'm doing a bunch of operation just to change the format of the data. So I'm doing basically a sub select but you can directly use the function in in SQL scripting. So here I

15:39have no sub select. I'm directly using the transform function to change the value of my arrays. So as you see the result is the same expanding a bit but here I've done like the direct approach and here I've done like a subquery with uh exploding and uh and uh reagregating the values. So if you can

16:02stick to the direct approach it's uh it's more efficient. So branching we already see some if condition here something super basic is one strictly superior to two of course not. So we are avoiding this branch. We are avoiding this one and we're going straight to this one. So this one gets evaluated. So you can rely

16:25on the assistant to help you understand those statements. It's superis assistant. So here I'm I'm having exactly the same logic but instead of using the if statement I'm using the case statement. So this one is kind of the same but written differently different way. Exactly different way to do the

16:47case branching. So both if and K statement you can't put labels in it. So if I'm trying here and the parser will will looping. So looping is uh where you you start to do uh to do to do more more things. So we have multiple ways to do loops. So we have the loop command, the while command, the repeat command

17:10and the for command. The loop command you can uh you can uh navigate with it with the leave and iterate. So if you don't do anything it will end unlessly. So don't forget to put some exit condition. So here I have I'm doing some some looping. So I'm starting with a variable. If the variable is set to

17:32three I'm forcing an iteration but if the variable is strictly above five I'm forcing to leave the loop and otherwise I'm just incrementing my my value. So actually for vac is going here this one is false. This one is false. This one is true. I go to two again I go

17:54to three. When I go to three, I go into this branching. So I go to 13. But I still I'm iterating. So I'm bypassing all this. And when I go here now, I'm going here. I'm going to this branching and I'm leaving the loop. And this is how you're ending up with 13 as an example. While repeat. So while repeat

18:17works a bit like looping, but you have an exit condition. So for while the exit condition is at the start. So while something do something. So in this example while the counterter is inferior or equal to three just increment and once we are getting to four we are exiting the loop. So same

18:39thing with the repeat loop. So it's the same result. So as you see while is like an exclusive condition and repeat is like an exclusive condition. So for loops, this one is uh is the most powerful one, probably the one you will you will need the most because you can uh you can put queries inside the loops and uh here I have a very basic query

19:03with a select range. But you can run any query uh against your data. So again warning don't convert a join with for a for loop unless you have very very very specific reason. But on 99.99% of the case is a good uh choice to do. So here I I have some uh some logic. I won't d it. It's not super

19:26important. But basically I'm just iterating like 10 times here and doing some uh some logic at this. That's it for what I wanted to show you in the SQL scripting uh demo. There is other commands that I won't run but you can try it by yourself. And uh if you have any questions, feel free to post them into the comments uh into the comments of the

19:49YouTube video and uh we will uh we will answer it for sure. Yeah. So as Fabia mentioned, we'll upload this notebook to the GitHub repo. You can download it, use runtime 16.3 at least, play with them. If you have any question, feel free to reach out and we'll make sure to answer all your questions. Thank you so much Fabia for being a super great guest

20:12going into every single tiny detail which is amazing and uh see you soon forward to talk about our procedures Maybe.