How to Use Temporary Tables in Databricks SQL

0:04Hi folks, today I have the pleasure to host for well I stopped counting Fabian Kamina. He's a rock star and today he'll be talking about temporary tables in DBSQL. Hi Fabia. >> Hi Ysef. Thanks for having for having me once again here. I think it's the fourth time so I'm still counting but uh it's not the last time for sure.

0:26So I'm curious what's the difference between using a view and a temporary table? >> Yeah, basically it's about materialization. In a view you are doing a definition that you can use and reuse. So it's nice. It has a lot of use case. View are super popular. But sometimes you need to materialize the data because maybe it's a complex calculation that

0:49you will need to run multiple time. So instead of redoing the compute end time you would do once store the result into a time table and then reuse it in your session as long as you want. >> So to understand well temperate so temporary table is I mean is persisted. So in order to drop the data should I run a specific command is it tied to a

1:11session or is it tied to a compute which means if I if I turn off my compute then I'll lose the uh the temp table. How how does it work? Yeah, absolutely. Temporary tables are session scoped a bit like the SQL variables in in DBSQL. So if you switch compute, obviously you have a new session, but even in the same compute, you can start a new session and your item will drop.

1:34Actually, I have a little demo for you. Maybe it's time to start doing the show. So this is the temporary uh table script. Basically, you have we have a new keyword which is temporary. So we can just create the temporary table. It's exactly the same syntax than with a classical table except for the moment we don't support liquid

1:57clustering but it's on the road map. So it will uh it will learn very soon. You see and now I have a temp table. I have the okay. Please note that you can't create or replace a temporary table. If I run this command I will get an error. So if you need in the same session to change your temporary table, you need to use the command drop the temporary table. You see? Okay. So I've dropped it. It's okay. It's no longer

2:19accessible. So let me show you temporary table. So I'm recetting it because I've just dropped it. Let's insert some data and let's check the data output from this command. So I just put one row with ID one. It's a it's identity with a time stamp and a value. Okay, it's a table. So if if you want you can run any

2:41operation you you would be running against the table. You can run insert, update, delete, merge, select. So here I'm showing you uh just a quick update and the result is 11. This time as I told you it's scoped in the session. So here on the compute you can see if I'm save the compute I can I can press the new session button. And if I do this and I just run the same select star command,

3:05now it's failing because the s it's a new session. So my my object does not exist again. Um a big topic which is important. Uh yesterday we talked about multi-statement transaction and you can't really run a DDL inside MST and it's also the case for for um temporary table. So if I want to run this command,

3:28it's not supported. But what you can do actually you can create a temporary table outside of the multi-statement transaction. So this will work and then inside a statement it will work. Oh it doesn't work. Why doesn't why doesn't doesn't it work? Because we saw yesterday you need to put a specific

3:50parameter. >> Exactly. Man the >> exactly >> catalog manage. Yeah. >> And it's the same parameter for temporary table. So don't forget to add this parameter data feature catalog manage equals to supported and then now I can run operation um in my temporary table inside the multi-statement transaction and that's it.

4:12>> Can you give me an example on when should I use uh in a specific use case uh temp tables? >> Yeah uh so uh let's say you are creating a complex indicator. So you are running a logic and you will reuse this data set multiple time. So just create your result use the temporary table multiple time and at the end of the session you

4:35don't even need to drop the table. It will uh it will do it automatically for you. >> Okay that's perfect. It's crystal clear. Um, as Fabia mentioned, it's now running on DBS SQL, which means it's using SQL warehouses, but it's coming soon to serverless compute and and classic clusters as well. >> Absolutely. >> Thank you so much and see you soon.

4:57>> Thanks everyone. Cheers. In lake >> drives data sees lake flow streams the flow iceberg and delta lake tables a glow unity catalog governs lake base o textql mosa and ka prolong you quitting co-host light the fire dream scale higher and higher next drop