Getting started with recursive CTE in Databricks SQL

0:04Hey, today Fabia is back. Last time he did a session about SQL scripting that was like a ma this video was a massive success. It's still trending like two months after which is awesome. And he's back to talk about a very interesting feature and well known by uh that are housing falls. It's called recursive

0:26cities. Hi Fabian. >> Hi Yousef. Thanks for having me back again today. Uh we will be talking about recursive city. Uh they have just made it to Databricks. Uh when I joined Databricks one year ago uh I found the lack of recursivity uh to be quite a gap in our product and I'm very happy that uh

0:48that our team managed to fill that gap uh very quickly. Recursivity are very important in SQL because it's the features that make SQL a touring complete language. uh which means you could probably solve any uh any uh complex algorithm with SQL. Of course, it's not adapted for everything, but it opens up some of your perspective.

1:12Uh to illustrate this, I will show you three examples uh on recursive. One very classic, how to drill down Yi. Uh another one uh bit more fun how to evaluate um to evaluate a formula of a string uh letter one a sessionization about giving uh giving ids to like a

1:36navigation on a website with a different rule set and one of those can only be solved with recursive city. So let me share my screen. I will show you those examples. So the first one is a very uh very classical yashi. So it's the data from a from a a table that that lives on Oracle since forever. So it's

1:58very uh very basic stuff. So I can create the table and insert some values. So basically I'm putting an employee name and the name of his manager and we see king being the king has no manager. uh we are going to run the recursive query about it just to display all the change of management for every

2:20employee. So when you are doing a recursive query you need to start with an anchor. Uh usually it's one row but it doesn't have to be so it very depending of what you want to do here. So here the start of our recussion will be will be king who has no manager and I'm creating a pass. So it's uh I'm putting I'm using this as an array. In

2:43other system you could you could have done string concatenate conc concatenation here just building the anchor here. What I'm doing within the union is I am reusing uh the recursive city inside it its own definition and I'm performing a join just to get the new information from uh

3:07the manager to its next management and we are here we are doing some stuff we are appending all the all the manager names uh in uh in place. So when I am running this query, we have the two rows that you have in the two columns sorry that you have in the table with the employee and the manager but we have also all the line of management

3:30here. So if we take Alen for example so Alen is managed by black but now from here we can also see that black is managed by king. Um if we go all the way down we can get to Adams. So Adams is managed by Ford by Scott sorry. And we can see that Scott is managed by Jones who is managed by King. So we have all the all the chain of management uh

3:54within this pass. So this one is very very useful when you are doing uh accounting you have like nested accounts uh you are doing so um hierarchical data. So employee manager is a is a great example. Uh you can also imagine when you have uh you have a different e equipment that are tied together. Uh so

4:17super valuable. This this example is basic but it's really to show you the idea. Uh I have another example which is uh which is a bit more fun. So it's very basic. Here I'm putting a formula in a variable and the formula is uh is passed as a string. And basically again very simple I'm uh I'm doing one number one

4:40operation one number everything is a everything is an integer I don't need to cast something >> but what I want to do here I want to try to evaluate all this so in the anchor part of my city what I'm doing I'm just taking my rule formula because I need to keep track of where I am but I'm just taking the first number so it will be my

5:02initialization point then I will be adding um I will be adding uh new rows to this output uh depending on the character I will be uh I will be checking. So you see I've put a variable which is called level that help me to keep track or of where I am in my formula and um

5:25basically I will handle them two by two. So that's why you you have a level times two here. The first one will be to determine what would be the operator and the second one what would be the operand. So it allows me basically to convert something very stringy to like proper proper expression and increasing of course my level. So what I will be running this query. So

5:49okay so I I have like all my characters. We can we can make a quick check. 6 * 3 is 18. 18 divided by 2 is 9. 9 * 9 is 91 - 477 + 7 84. So that that's the good uh the correct answer to my formula. The last example is a bit more

6:12complex. I'm here I'm creating like imagine you are you have having some some web blogs. You only have very uh very basic information about actions and a time stamp. Here I will show you the content of my uh of my table. It's very generic. So basically I have 20 uh 20 20 rows and I've added I have an ID

6:36from 1 to 20. Basically the ID you see it's also the minutes of my time stamp and just for the first six row I am I am at the 11th hour. For the next 10 rows I I am at noon and for the last four rows I am at 1 pm. Uh so first rule we will define a session that ends after 5 minutes of

6:59inactivity. Uh this one we don't need actual actually recursive C2 to solve it. Uh imagine so you're you're browsing a page and yeah your your session will will log you out after a certain time of inactivity. uh here we are using only window function. So basically I am ordering my data set by time stamp and I'm checking if the differences between my current

7:24row versus the president is over 5 minutes or not and I have just also a case with the n just to handle the first row. If all this is true then I am evating this expression as a boolean either true or false and converting the boolean to tiny int give give me zero or one and then I just need to aggregate this series of zero or one to have my

7:46session ID. This will become my output and we can see it's okay. So the session ID are already matching the hours. So all the ID here because I have always less than five minutes between two actions. So this is session one. Here I have a 1 hour break. This is session two. And you get the idea. This is session three. Okay. Perfect. Now let's

8:08change a bit the rule of the game. Now what we want to do, we want to evaluate the session the session based on the start of the session. So imagine you are like um you are putting uh putting uh items in a cart. Usually you have a um you have a set time to validate or continue that cart. Uh so

8:30here we need we can't really solve that solve this with window function. Uh because the first session is very easy because you you have it's the first time it's the first time of your uh of your series. So it's very easy to get it. But the second session you're not sure because you can add plus five but you don't know exactly when that will fall

8:53off. So it's very hard to determine when the second session is starting. So to cover this we are using a recursivity here. So what I am what I'm putting in my unsure I'm just starting with the with the first rows. I can clear this here. So I'm just starting with the first rows and I will uh I will check against the table for the next row. So

9:16it's ready ID equal ID + 1. And basically the logic is here. I'm I will keep track of my the starting of my time stamp and I will update it only if my current time stamp is superior to my session starting time stamp plus 5 minutes. If as not the case I'm not touching that that column and if it's the case I will update that columns. And

9:38it's the same for the session ID. If I did update I will add one otherwise I'm adding zero. And when I'm running this query, we'll see the real set now will be very very different. So we can see here the same five minutes are grouped together in session one. And this is the starting of the session. But here when I I I'm at the six uh six

10:01minutes. So the difference is becoming uh above 5 minutes. So I am creating a new point here and I'm doing a new session. Here the session is I have more than one hour. So I have another session. But even here if everything is concurrent by one minute same a bit the same for ID1. We still put here a stop

10:24and we are reaching 5 minutes. So now we are updating the new session session beginning uh starting time and we are adding a new session ID. And for to solve this you really need need recursivity. And that's it for my example. So one very basic, one a bit more fun and the latest one a bit more complex but I I think it has it has some

10:47values for for my peers. >> And the good news that those notebooks will be available in the uh GitHub repo and which means everyone can just go and try it and see how how it works. >> Absolutely. >> Thank you. Thank you so much Fabian. And uh see you soon. >> Thank you Sev. Talk to you soon. My