SET MANAGED: Convert External Tables with Predictive Optimization

0:04Hey folks, today I'm going to talk about the new magic command called set managed. This command provide a seamless mechanism to convert UC external tables to UC managed while minimizing downtime handling concurrent rights maintaining table configuration and preserving table history. But why should I convert to UC managed tables? Well, first of all,

0:27you're going to benefit from predictive optimization. It's where Databricks optimize your data layout and compute using AI. So, you don't need to manually handle operations such as optimize, vacuum, and analyze. You can benefit from automatic liquid clustering where the system will intelligently select clustering keys and as query patterns, Databricks automatically update

0:50clustering keys to improve performance and lower the cost. You're going to also benefit from metadata caching. the system will benefit from a query performance by minimizing requests to the transaction logs stored in in the cloud. Last but not least, if you drop a manage shape, datab bricks deletes the data in the cloud storage after 8 days, reducing storage cost. While for an external table, you must manually go to

1:13the store your storage bucket and delete those files. Now, let's move to the to my notebook. I'm going to create my catalog. Then I'm going to create a table called customers which going to be in an external location. I'm going to generate some fake data. Then insert this data inside my my external table. We can have a look at this the table. We

1:36have many many columns over there. Let me run now the describe extended. So as you can see this is the schema of my table. And if you have a look at the type it's an external table. Now let me let's say update my my my table whenever I have the like set the loyalty points to 99 99 9,999 whenever I have the customer ID equal to

2:00one or two. If I run now the describe history you're going to see all the steps I all the operation applied on the top of this table. Now what I'm going to do, I'm going to use my set manage command to convert this table from external to a UC manage one. For the purpose of this example, I'm using an interactive cluster, but I would

2:22recommend to use the job cluster to run this this command which can help you lower the cost. And you see it was fast. But again, because I have only few data in my table, I think I have 1 million rows. Now if I run the describe extended I have the same schema but now the table is managed which is good news. I went

2:45from an external table to a managed to a managed table and if I as you can see nothing changed. I have the same catalog, the same schema, the same name which is important which means I no longer need to go and update all different notebooks uh to keep my workflows running and I also I was also able to keep my the same history like uh

3:07you see that when I created the table when I did the insert and the and the update and keep in mind once you move your your table or you convert your table from an external to UC manage make sure to enable predictive optimization at the catalytic level so you can benefit from the enhancement I mentioned at the beginning of this episode. I hope

3:29it was useful. Make sure to subscribe and see you soon.