The brand new SET MANAGED command gives a seamless mechanism to convert UC exterior tables to UC managed tables whereas minimizing downtime, dealing with concurrent writes, sustaining desk configurations, and, the place potential, preserving desk historical past. This text shares greatest practices and gives a step-by-step information for utilizing this usually obtainable (GA) command:
Why Convert to UC Managed Tables?
With Unity Catalog because the supply of fact, managed tables unlock distinctive capabilities that improve efficiency, governance, and ease of use—with out vendor lock-in.
Key benefits embrace:
- Automated optimizations that may enhance question efficiency by 20x and reduce storage prices by 50%+ (extra particulars right here).
- Streamlined information administration with computerized cleanup for dropped information to avoid wasting on prices, in addition to undrop assist
- Enhanced governance with information lineage, fine-grained entry controls, and safer desk entry with Unity Catalog supervision over all reads and writes
- A basis for future capabilities resembling computerized row deletion (Auto-TTL) and row-level ingestion Zerobus ingest, in Non-public Preview).
Transformed tables assist reads from any third-party shopper (see right here for extra particulars).
How can the SET MANAGED Conversion Command Assist?
The SET MANAGED command makes conversion from exterior to managed tables simpler
|
Function |
Advantage of SET MANAGED command |
|---|---|
|
Decrease Downtime |
Maintain the desk on-line and obtainable for reads utilizing Databricks Runtime 16.1 or above, and decrease downtime to just some minutes for writes (or, for reads on Databricks Runtime 15.4 or beneath). |
|
Protect Identification |
The desk’s title, permissions, tags, and settings for all tables, and desk historical past (for Delta tables) are all retained. |
|
Deal with Concurrency |
The SET MANAGED command safely handles concurrent writes that will happen in the course of the conversion. |
|
Roll Again |
One other command referred to as UNSET MANAGED allows roll again of a transformed desk again to UC exterior inside 14 days, as a security internet. |
How Do I Convert from Exterior to Managed Tables?
A Practitioner’s Step-By-Step Information for Conversion
The SET MANAGED command makes desk conversion simple. In a step-by-step information, we have outlined key suggestions to make sure a easy transition from exterior to managed tables.
Step 1: Choose Exterior Tables to Convert
Start by choosing a few Unity Catalog exterior tables to transform to UC managed first, to familiarize your workforce with the method, conditions, and post-conversion steps.
For instance, you possibly can check out this command first on a few tables which might be solely learn and written to by Databricks purchasers (see planning a staged journey).
Step 2: Pre-Flight Guidelines
Examine that your ecosystem of desk readers and writers are prepared for change. For every chosen UC exterior desk and its related workloads, you’ll need to:
- Replace to make use of Identify-Based mostly Entry: Examine your jobs, notebooks, and queries to make sure they entry the desk utilizing its three-part title (catalog.schema.desk) slightly than utilizing path-based entry (e.g., SELECT * FROM delta.’s3://path/to/desk’). Databricks Labs has developed UCX tooling that may allow you to discover path-based references by operating the next Databricks Labs UCX lint-local-code from an IDE terminal, to research your native machine’s listing code (.py or .sql recordsdata).
- Cancel all Upkeep Jobs: To stop conflicts, guarantee no OPTIMIZE, ZORDER, or CLUSTER BY jobs are operating or scheduled to run on the desk in the course of the conversion course of, in the event that they exist (can test utilizing DESCRIBE HISTORY). After the conversion, Predictive Optimization will routinely deal with optimization jobs.
- [Optional] Improve Databricks Runtime Variations: All Databricks clusters studying from or writing to the desk ought to ideally be on Databricks Runtime 15.4 LTS or greater to retain full desk historical past for Delta tables. Databricks Runtime 16.1 or greater can remove reader downtime totally.
Step 3: Run the Conversion Command
Execute the conversion utilizing the next conversion command:
Be aware: For tables with UniForm enabled, use SET MANAGED TRUNCATE UNIFORM HISTORY.
Step 4: Confirm the End result
After the command completes, affirm that the conversion was profitable by checking the desk’s metadata.
Within the output of this command, the “Sort” property ought to now show as “MANAGED”. You may also see this identical data within the ‘About this desk’ part of the Catalog Explorer.
Step 5: Put up-Conversion Housekeeping
After a profitable conversion, full these last steps to make sure a easy transition:
- Restart streaming learn or write jobs that use the desk if any have paused
- Carry out practical testing by operating key queries to make sure all readers and writers are working as anticipated on the newly managed desk
- Affirm that Predictive Optimization is now enabled for the desk to start benefiting from automated upkeep (you may also allow CLUSTER by AUTO, for computerized liquid clustering, or test if it’s been enabled).
Planning a Staged Journey
A profitable conversion of all tables to UC managed is a journey – adopting a phased strategy and planning forward might help guarantee a easy transition:
- Convert Databricks-Solely Tables: Prioritize changing tables which might be solely learn from and written to by Databricks purchasers. An experimental device, Entry Insights, can be utilized to assist establish tables with solely “Databricks readers and writers” vs. “Non-databricks readers” or “Non-databricks writers”.
- Convert Tables with Supported Exterior Instruments: Decide which tables are accessed by third-party instruments which additionally natively assist reads from UC managed tables, and convert these subsequent. Third-party entry will proceed working after conversion.
- Tackle Advanced Instances Final: For tables accessed with unsupported legacy instruments—plan to make use of options like Compatibility Mode for reads. The place third-party writes are required, re-create these tables and allow writes to those UC managed tables in Preview Preview.
Extra Concerns
The next particulars relating to the conversion command could also be helpful to know prematurely:
- Rollback Time Restrict: To make use of roll again security internet, UNSET MANAGED have to be run on the UC managed desk inside 14 days of conversion – after that, the unique exterior information shall be completely deleted to avoid wasting on storage prices.
- Time Journey Nuances: Upgrading purchasers to fifteen.4 LTS or greater will be useful. For clusters operating on Databricks Runtime 14.3 LTS or beneath or in the event you use the UNSET MANAGED command to roll again, you possibly can solely time journey to historic commits by model quantity after conversion, not by timestamp.
- Minimized Downtime for Writers: The command is designed to reduce downtime – writers could expertise a short outage (estimated between 1 and 5 minutes) in the course of the last part when the desk’s location is switched to the brand new managed location.
- Non permanent Delta Sharing Interruption: Delta Sharing shall be quickly interrupted throughout conversion, however this can perform correctly once more as soon as the method is full.
Professional-Tip: Scaling Up with Bulk Conversion
To transform tons of or hundreds of Unity Catalog exterior tables in bulk inside a given schema, you should utilize the next easy SQL script.
Be aware: This script performs dwell modifications. It’s extremely beneficial to check it totally in a growth atmosphere earlier than operating it in manufacturing.
Controlling Your Information’s Bodily Location
Unified Catalog (UC) managed tables reside in customer-managed storage and are accessible by way of open catalog APIs. In order for you extra management over how your information is bodily saved, you possibly can outline a managed storage location on the catalog or schema stage – any new managed tables created in that catalog or schema shall be routinely organized in that specified location.
For pre-existing exterior tables, you possibly can set a managed storage location, then use the SET MANAGED command to transform them to UC managed tables. Throughout conversion, the system respects the managed location you’ve outlined, providing you with management over the bodily structure of your information in cloud storage. Please contact your account workforce to entry this function in Non-public Preview in the present day.
Changing from Exterior to Managed Tables Immediately
In just some brief months since Public Preview, tons of of shoppers have efficiently transformed hundreds of tables with SET MANAGED.
All the pieces described right here is now GA—strive it out in the present day and unlock the efficiency, governance, and ease of Unity Catalog Managed Tables.