DROP CONNECTION (foreign catalog)
Applies to: 
Databricks Runtime 17.3 and above
This feature is in Public Preview and is only available to participating customers at this time. To participate in the preview, apply by filling out this form. This feature only supports dropping the connection for foreign catalogs using Hive Metastore (HMS) and Glue Federation.
Use the DROP CONNECTION command to convert a foreign catalog to a standard catalog in Unity Catalog. After dropping the connection, the catalog no longer syncs foreign tables from the external catalog. Instead, it acts like a standard Unity Catalog catalog containing managed or external tables. Your catalog is now labeled as standard instead of foreign in Unity Catalog. This command doesn't impact tables in your external catalog; it impacts only your foreign catalog in Unity Catalog.
Requires OWNER or MANAGE, USE_CATALOG, and BROWSE permissions on the catalog.
Syntax
ALTER CATALOG catalog_name DROP CONNECTION { RESTRICT | FORCE }
Parameters
-
The name of the foreign catalog to convert to a standard catalog.
-
RESTRICT
Default behavior.
DROP CONNECTIONwithRESTRICTfails when converting the foreign catalog to a standard catalog if there are any foreign tables or foreign views in the catalog.To upgrade foreign tables to Unity Catalog managed or external tables, see Convert a foreign table to a managed Unity Catalog table or Convert a foreign table to an external Unity Catalog table. To convert foreign views, see SET MANAGED (FOREIGN VIEW).
-
FORCE
DROP CONNECTIONwithFORCEdrops any remaining foreign tables or views in a foreign catalog when converting the foreign catalog to a standard catalog. This command doesn't drop any data or metadata in your external catalog; it only drops the metadata synced into Unity Catalog to create the foreign table.warningYou can't roll back this command. If you want to federate the foreign tables back into Unity Catalog, you must recreate the foreign catalog.
Examples
-- Convert an existing foreign catalog using default RESTRICT behavior
> ALTER CATALOG hms_federated_catalog DROP CONNECTION;
OK
-- Convert an existing foreign catalog using FORCE to drop foreign tables
> ALTER CATALOG hms_federated_catalog DROP CONNECTION FORCE;
OK
-- RESTRICT fails if foreign tables or views exist
> ALTER CATALOG hms_federated_catalog DROP CONNECTION RESTRICT;
[CATALOG_CONVERSION_FOREIGN_ENTITY_PRESENT] Catalog conversion from UC Foreign to UC Standard failed because catalog contains foreign entities (up to 10 are shown here): <entityNames>. To see the full list of foreign entities in this catalog, please refer to the scripts below.
-- FORCE fails if catalog type isn't supported
> ALTER CATALOG redshift_federated_catalog DROP CONNECTION FORCE;
[CATALOG_CONVERSION_UNSUPPORTED_CATALOG_TYPE] Catalog cannot be converted from UC Foreign to UC Standard. Only HMS and Glue Foreign UC catalogs can be converted to UC Standard.
Scripts for checking foreign tables and views
Before using DROP CONNECTION RESTRICT, you can use these Python scripts to check for foreign tables and views in your catalog using the Unity Catalog REST API.
Script to list all foreign tables and views from the federated catalog:
import requests
def list_foreign_uc_tables_and_views(catalog_name, pat_token, workspace_url):
"""
Lists all foreign tables and views in the specified Unity Catalog.
Args:
catalog_name (str): The name of the catalog to search.
pat_token (str): Personal Access Token for Databricks API authentication.
workspace_url (str): Databricks workspace hostname (e.g., "https://adb-xxxx.x.azuredatabricks.net").
Returns:
list: A list of dictionaries containing information about the foreign tables/views.
"""
base_url = f"{workspace_url}/api/2.1/unity-catalog"
headers = {
"Authorization": f"Bearer {pat_token}",
"Content-Type": "application/json"
}
# Step 1: List all schemas in the catalog (GET request)
schemas_url = f"{base_url}/schemas"
schemas_params = {
"catalog_name": catalog_name,
"include_browse": "true"
}
schemas_resp = requests.get(schemas_url, headers=headers, params=schemas_params)
schemas_resp.raise_for_status()
schemas = schemas_resp.json().get("schemas", [])
schema_names = [schema["name"] for schema in schemas]
result = []
# Step 2: For each schema, list all tables/views and filter (GET request)
for schema_name in schema_names:
tables_url = f"{base_url}/table-summaries"
tables_params = {
"catalog_name": catalog_name,
"schema_name_pattern": schema_name,
"include_manifest_capabilities": "true"
}
tables_resp = requests.get(tables_url, headers=headers, params=tables_params)
tables_resp.raise_for_status()
tables = tables_resp.json().get("tables", [])
for table in tables:
# Use OR for filtering as specified
if (
table.get("table_type") == "FOREIGN"
or table.get("securable_kind") in {
"TABLE_FOREIGN_HIVE_METASTORE_VIEW",
"TABLE_FOREIGN_HIVE_METASTORE_DBFS_VIEW"
}
):
result.append(table.get("full_name"))
return result
# Example usage:
# catalog = "hms_foreign_catalog"
# token = "dapiXXXXXXXXXX"
# workspace = "https://adb-xxxx.x.azuredatabricks.net"
# foreign_tables = list_foreign_uc_tables_and_views(catalog, token, workspace)
# for entry in foreign_tables:
# print(entry)
Script to list all foreign tables and views that are in ACTIVE provisioning state:
import requests
def list_foreign_uc_tables_and_views(catalog_name, pat_token, workspace_url):
"""
Lists all foreign tables and views in the specified Unity Catalog.
Args:
catalog_name (str): The name of the catalog to search.
pat_token (str): Personal Access Token for Databricks API authentication.
workspace_url (str): Databricks workspace hostname (e.g., "https://adb-xxxx.x.azuredatabricks.net").
Returns:
list: A list of dictionaries containing information about the foreign tables/views.
"""
base_url = f"{workspace_url}/api/2.1/unity-catalog"
headers = {
"Authorization": f"Bearer {pat_token}",
"Content-Type": "application/json"
}
# Step 1: List all schemas in the catalog (GET request)
schemas_url = f"{base_url}/schemas"
schemas_params = {
"catalog_name": catalog_name,
"include_browse": "true"
}
schemas_resp = requests.get(schemas_url, headers=headers, params=schemas_params)
schemas_resp.raise_for_status()
schemas = schemas_resp.json().get("schemas", [])
schema_names = [schema["name"] for schema in schemas]
result = []
# Step 2: For each schema, list all tables/views and filter (GET request)
for schema_name in schema_names:
tables_url = f"{base_url}/table-summaries"
tables_params = {
"catalog_name": catalog_name,
"schema_name_pattern": schema_name,
"include_manifest_capabilities": "true"
}
tables_resp = requests.get(tables_url, headers=headers, params=tables_params)
tables_resp.raise_for_status()
tables = tables_resp.json().get("tables", [])
for table in tables:
# Use OR for filtering as specified
if (
table.get("table_type") == "FOREIGN"
or table.get("securable_kind") in {
"TABLE_FOREIGN_HIVE_METASTORE_VIEW",
"TABLE_FOREIGN_HIVE_METASTORE_DBFS_VIEW"
}
):
table_full_name = table.get('full_name')
get_table_url = f"{base_url}/tables/{table_full_name}"
tables_params = {
"full_name": table_full_name,
"include_browse": "true",
"include_manifest_capabilities": "true"
}
table_resp = requests.get(get_table_url, headers=headers, params=tables_params)
table_resp.raise_for_status()
provisioning_info = table_resp.json().get("provisioning_info", dict()).get("state", "")
if provisioning_info == "ACTIVE":
result.append(table_full_name)
return result
# Example usage:
# catalog = "hms_foreign_catalog"
# token = "dapiXXXXXXXXXX"
# workspace = "https://adb-xxxx.x.azuredatabricks.net"
# foreign_tables = list_foreign_uc_tables_and_views(catalog, token, workspace)
# for entry in foreign_tables:
# print(entry)