Upgrade your tables to Databricks Unity Catalog

Unity catalog provides all the features required to your data governance & security:

- Table ACL
- Row level access with dynamic view
- Secure access to external location (blob storage)
- Lineage at row & table level for data traceability
- Traceability with audit logs

Because unity catalog is added as a supplement to your existing account, migrating your existing data to the new UC is very simple.




Unity Catalog works with 3 layers:

* CATALOG
* SCHEMA (or DATABASE)
* TABLE

The table created without Unity Catalog are available under the default `hive_metastore` catalog, and they're scoped at a workspace level.

New tables created with Unity Catalog will available at the account level, meaning that they're cross-workspace.

Understanding the upgrade process

External table and Managed table

Before your upgrade, it's important to understand the difference between tables created with `External Location` vs `Managed table`

* Managed tables are table created without any `LOCATION` instruction. They use the default database storage location which is usually your root bucket `/user/hive/warehouse/your_database/your_table` (note that you can also change this property at the database level). If you drop a managed table it'll delete the actual data.
* External tables are tables with data stored to a given LOCATION. Any table created with an instruction like `LOCATION 's3a:/xxx/xxx'` is external. Dropping an External table won't delete the underlying data.

UC Access control

* UC is in charge of securing your data. Tables in UC can be stored in 2 locations:
* Saved under the UC metastore bucket (the one you created to setup the metastore), typically as Managed table. This is the recommended thing to do by default.
* Saved under an [EXTERNAL LOCATION](https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-create-location.html) (one of your S3 bucket, ADLS...) secured with a STORAGE CREDENTIAL: `CREATE EXTERNAL LOCATION location_name URL url WITH (STORAGE CREDENTIAL credential_name)`

In both case, **only the Unity Catalog should have the permission to access the underlying files on the cloud storage**. If a user can access the data directly at a file level (ex: in a workspace root bucket, with an instance profile already existing, or a SP) they could bypass the UC.

2 Upgrade options: pointing metadata to the external location or copying data to UC storage

Knowing that, you have 2 options to upgrade your tables to UC:
* **Moving the data:** If your data resides in a cloud storage and you know users will have direct file access (ex: a managed table in your root bucket or a cloud storage on which you want to keep file access), then you should move the data to a safe location in the UC (use your UC metastore default location as a recommended choice)
* **Pointing to the existing data with an External Location**: If your data is in a cloud storage and you can ensure that only the UC process will have access (by removing any previously existing direct permission on instance profile/SP), you can just re-create the table metadata in the UC with an EXTERNAL LOCATION (without any data copy)

*Notes:*
* *External tables saved in a bucket you can’t secure with UC external location (ex: saved in the workspace root storage `dbfs:/...` , or a storage with instance profile / Service Principal granting direct file access you can't remove) should be fully cloned too.*
* *Alternatively, Managed tables stored in an external bucket (this can be the case changing the default LOCATION of the DATABASE) can be upgrade using an External Location (without copying data).*

**To run this demo in your account, specify an external location below (default setting isn't open to all for privacy reason)**

Migrating a single table to UC

Let's see how your upgrade can be executed with a simple SQL query with the 2 options.

*Note: Databricks will provide a `SYNC`command to simplify these operation over the next few months: `SYNC SCHEMA hive_metastore.syncdb TO SCHEMA main.syncdb_uc2 DRY RUN`*

1/ Moving the data to the UC (ex: MANAGED table in hive_metastore)

Let's assume you have a MANAGED table in your legacy hive_metastore (which is by default under your root storage `dbfs:/xx/xx`), and you want to move this table to UC.

Because all your users have access to the root storage, your only option is to copy all the data to the UC:

* To the UC default cloud storage (recommended, that's the default for any table you create without location)
* To an external location that you have previously created.

*As this is a hard copy, any update done on the legacy table after your DEEP CLONE won't be reflected. See below for more avanced options.*

2/ Upgrade to UC using an External Location without moving data (Ex: External table in a S3 bucket)

Let's now assume that you have a table using a Location in a specific cloud storage (ex: `s3a:/my-bucket/my/table_folder`).

You can create a new External table in the UC pointing to this location, without moving the data:

* As admin, create a [STORAGE CREDENTIAL](https://docs.databricks.com/data-governance/unity-catalog/manage-external-locations-and-credentials.html) in UC and an [EXTERNAL LOCATION](https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-create-location.html) using this credential.
This will ensure only UC engine can access the cloud storage in a secure fashion.
* Create a UC table pointing to the same external location
* Make sure no user can further access the cloud storage natively (`s3a:/my-bucket/my/table_folder`, using instance profile, SP etc)

*As this is a link pointing to the data, no copy is done.*

*Note: Again, if you external location is in your root storage you should instead copy the data as users will have direct access*

Migrating a table using Databricks UI

You can use the "Data" menu on the left to upgrade a specific table and generate the above queries for you.

- Select the database you want to upgrade to UC
- Click on "Upgrade"
- Select the tables to upgrade
- Select the source destination & permissions
- Run the upgrade!

*Note: Currently, Upgrading a table to the UC using the UI only supports External location.*

Programatically migrating existing database (under `hive_metastore`) to UC

Let's see how we can take this one step further and programatically upgrade an entire database at once.

We need a script that will automatically detect the table type (External or Managed) to pick our upgrade strategy:

* Copy the data for managed location or external location in our root cloud storage
* Keep data and leverage EXTERNAL LOCATION for the other table
* Move non-delta tables to delta table
* Support view upgrade

This is implemented in the `should_copy_table` function.

The next step is to loop over the tables in the database we want to upgrade and run one of the 2 upgrade SQL queries: `DEEP CLONE` or using the `COPY LOCATION`.

*Note: Remember that you'll have to setup your `EXTERNAL LOCATION` + `CREDENTIAL` for `COPY LOCATION` to work with UC.*

Advanced upgrade script - expert mode

Let's finish with a complete upgrade script allowing for more flexibility.

We want to support the following options:

* Option to force data copy for all tables, regardless of source table (managed or external)
* Safe to run multiple times:
* Skip table if the table is already upgraded and no data have been added to the tables since the last run
* Support incremental copy option: when run multiple time, will incrementally synchronize the new data without recopying everyting (using spark streaming)
* detect any new table added
* Add table optimization on all the tables (auto compaction and optimize write)

The following script can be copy-pasted and used directly:

Migrate ALL YOUR DATABASES to UC

Let's see how to run this in parallel and make sure we move all the databases first and the the views:

Conclusion

Unity Catalog can easily be added as an addition to your workspace-level databases.

You can easily upgrade your table using the UI or with simple SQL command.

For more detailed upgrade, you can leverage a more advanced upgrade script or contact your account team for more help.