DROP CONNECTION （外部目录）

适用于： 勾选是 Databricks SQL Databricks Runtime 17.3 及更高版本

重要

此功能以公共预览版提供，目前仅适用于参与客户。要参与预览，请通过填写此表单来申请。此功能仅支持使用 Hive 元存储 (HMS) 和 Glue 联合断开外部数据目录的连接。

DROP CONNECTION使用命令将外部目录转换为 Unity 目录中的标准目录。删除连接之后，目录将不再从外部目录同步外部表。而是像一个包含托管表或外部表的标准 Unity Catalog 目录一样。目录现在在 Unity Catalog 中被标记为标准，而不是外来。此命令不会影响外部目录中的表;它仅影响 Unity 目录中的外国目录。

需要 OWNER 或 MANAGE、USE_CATALOG和BROWSE 对目录的权限。

Syntax

ALTER CATALOG catalog_name DROP CONNECTION { RESTRICT | FORCE }

参数

catalog_name

要转换为标准目录的外国目录的名称。
RESTRICT

默认行为。 DROP CONNECTION 在将外部目录转换为标准目录时，如果目录中存在任何外表或外视图，RESTRICT 将会失败。

若要转换外部视图，请参阅 SET MANAGED （FOREIGN VIEW）
力

DROP CONNECTION 将外部目录转换为标准目录时，FORCE 会删除外部目录中所有剩余的外部表或视图。此命令不会删除您外部目录中的任何数据或元数据；它只删除已同步到 Unity Catalog 中以创建外部表的元数据。

警告

无法回滚此命令。如果要将外部表联合回 Unity Catalog，则必须重新创建外部目录。

例子

-- 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.

用于检查外部表和视图的脚本

注释

在使用 DROP CONNECTION RESTRICT之前，可以使用这些 Python 脚本通过 Unity Catalog REST API 检查目录中是否有外表和视图。

用于列出联合目录中所有外部表和视图的脚本：

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.databricks.azure.cn").

    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.databricks.azure.cn"
# foreign_tables = list_foreign_uc_tables_and_views(catalog, token, workspace)
# for entry in foreign_tables:
#     print(entry)

用于列出所有处于激活预配状态的外部表和视图的脚本：

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.databricks.azure.cn").

    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.databricks.azure.cn"
# foreign_tables = list_foreign_uc_tables_and_views(catalog, token, workspace)
# for entry in foreign_tables:
#     print(entry)

Last updated on 2025-12-31

通过

DROP CONNECTION （外部目录）

Syntax

参数

例子

用于检查外部表和视图的脚本

相关文章

其他资源