Databricks SQL: catalog of each query

[noimeta]

Currently, we are migrating from hive metastore to UC.

We have several dashboards and a huge number of queries whose catalogs have been set to hive_metastore and using <db>.<table> access pattern.

I'm just wondering if there's a way to switch catalogs of all queries in one go? Switching catalog of each query one by one needs tons of work. Also, it would be best if we can switch back and forth, so that we can make sure that everything looks correct.

[AdrianLobacz]

Hi, check this link, its documentation about UC and hive metastore

https://docs.databricks.com/data-governance/unity-catalog/hive-metastore.html#language-sql

[noimeta]

Thank you. I did check the link.

I know that we can explicitly set the catalog name in a query. Anyway, my point is I don't want to manually edit each query one by one.

[youssefmrini]

There is a configuration that you can use to define the default catalog and you can have as many sql endpoints as catalogs. It will makes your life much easier.

[noimeta]

That sounds very promising.

Could you please pin point how to do that? Is there any document that I can follow?

[NateAnth · Accepted solution]

Please see the link here.

This would be the setting to set on non-SQL Warehouse Databricks Clusters

spark.databricks.sql.initial.catalog.name

To change for the all of the SQL Warehouses in a workspace you would use:

databricks unity-catalog metastores assign --workspace-id 1234567890123456 \
                                       --metastore-id 12a345b6-7890-1cd2-3456-e789f0a12b34 \
                                       --default-catalog-name my_catalog

[noimeta]

Thank you so much. The unity-catalog CLI is working for me

[h_h_ak]

May you can also have a look here, if you need hot fix 🙂 

https://github.com/databrickslabs/ucx