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 doable, preserving desk historical past. This text shares greatest practices and gives a step-by-step information for utilizing this usually out there (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 embody:
- Computerized optimizations that may enhance question efficiency by 20x and reduce storage prices by 50%+ (extra particulars right here).
- Streamlined information administration with automated cleanup for dropped information to avoid wasting on prices, in addition to undrop help
- 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 equivalent to automated row deletion (Auto-TTL) and row-level ingestion Zerobus ingest, in Personal Preview).
Transformed tables help 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
Characteristic | Good thing about SET MANAGED command |
|---|---|
Reduce Downtime | Hold the desk on-line and out there for reads utilizing Databricks Runtime 16.1 or above, and decrease downtime to only a few minutes for writes (or, for reads on Databricks Runtime 15.4 or beneath). |
Protect Id | 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 web. |
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 deciding on a few Unity Catalog exterior tables to transform to UC managed first, to familiarize your staff with the method, stipulations, and post-conversion steps.
For instance, you’ll be able to check out this command first on a few tables which might be completely 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) fairly than utilizing path-based entry (e.g., SELECT * FROM delta.’s3://path/to/desk’). Databricks Labs has developed UCX tooling that may provide help to discover path-based references by operating the next Databricks Labs UCX lint-local-code from an IDE terminal, to investigate your native machine’s listing code (.py or .sql information).
- 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 verify utilizing DESCRIBE HISTORY). After the conversion, Predictive Optimization will robotically 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 larger to retain full desk historical past for Delta tables. Databricks Runtime 16.1 or larger can remove reader downtime fully.
Step 3: Run the Conversion Command
Execute the conversion utilizing the next conversion command:
Observe: For tables with UniForm enabled, use SET MANAGED TRUNCATE UNIFORM HISTORY.
Step 4: Confirm the End result
After the command completes, verify 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 can even see this identical info within the ‘About this desk’ part of the Catalog Explorer.
Step 5: Submit-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 as well allow CLUSTER by AUTO, for automated liquid clustering, or verify if it’s been enabled).
Planning a Staged Journey
A profitable conversion of all tables to UC managed is a journey – adopting a phased method and planning forward will help guarantee a easy transition:
- Convert Databricks-Solely Tables: Prioritize changing tables which might be completely learn from and written to by Databricks purchasers. An experimental instrument, 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 help reads from UC managed tables, and convert these subsequent. Third-party entry will proceed working after conversion.
- Handle 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 Issues
The next particulars concerning the conversion command could also be helpful to know prematurely:
- Rollback Time Restrict: To make use of roll again security web, UNSET MANAGED have to be run on the UC managed desk inside 14 days of conversion – after that, the unique exterior information might be completely deleted to avoid wasting on storage prices.
- Time Journey Nuances: Upgrading purchasers to fifteen.4 LTS or larger could be useful. For clusters operating on Databricks Runtime 14.3 LTS or beneath or for those who use the UNSET MANAGED command to roll again, you’ll be able to solely time journey to historic commits by model quantity after conversion, not by timestamp.
- Minimized Downtime for Writers: The command is designed to attenuate downtime – writers could expertise a quick outage (estimated between 1 and 5 minutes) in the course of the last section when the desk’s location is switched to the brand new managed location.
- Short-term Delta Sharing Interruption: Delta Sharing might be briefly interrupted throughout conversion, however this can operate correctly once more as soon as the method is full.
Professional-Tip: Scaling Up with Bulk Conversion
To transform lots of or hundreds of Unity Catalog exterior tables in bulk inside a given schema, you should utilize the next easy SQL script.
Observe: This script performs stay modifications. It’s extremely beneficial to check it completely in a improvement setting 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. If you need extra management over how your information is bodily saved, you’ll be able to outline a managed storage location on the catalog or schema degree – any new managed tables created in that catalog or schema might be robotically organized in that specified location.
For pre-existing exterior tables, you’ll be able to 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, supplying you with management over the bodily structure of your information in cloud storage. Please contact your account staff to entry this characteristic in Personal Preview right now.
Changing from Exterior to Managed Tables Immediately
In only a few brief months since Public Preview, lots of of consumers have efficiently transformed hundreds of tables with SET MANAGED.
All the pieces described right here is now GA—strive it out right now and unlock the efficiency, governance, and ease of Unity Catalog Managed Tables.