DROP CONNECTION (catálogo externo)

Se aplica a: Databricks SQL Databricks Runtime 17.3 y versiones posteriores

Use el DROP CONNECTION comando para convertir un catálogo externo en un catálogo estándar en el catálogo de Unity. Después de quitar la conexión, el catálogo ya no sincroniza las tablas externas del catálogo externo. En cambio, actúa como un catálogo estándar de Unity Catalog que contiene tablas administradas o externas. El catálogo ahora está etiquetado como estándar en lugar de externo en Unity Catalog. Este comando no afecta a las tablas de su catálogo externo; afecta solo a su catálogo extranjero en el catálogo de Unity.

Requiere OWNER o MANAGE, USE_CATALOG y BROWSE permisos en el catálogo.

Syntax

ALTER CATALOG catalog_name DROP CONNECTION { RESTRICT | FORCE }

Parámetros

• catalog_name

  Nombre del catálogo externo que se va a convertir en un catálogo estándar.

• RESTRINGIR

  Comportamiento predeterminado. DROP CONNECTION RESTRICT ocurre un error al convertir el catálogo externo a un catálogo estándar si hay tablas externas o vistas externas en el catálogo.

  Para actualizar tablas externas a tablas externas administradas o externas del catálogo de Unity, consulte Conversión de una tabla externa en una tabla de catálogo de Unity administrada o Convertir una tabla externa en una tabla de catálogo de Unity externa. Para convertir vistas externas, consulte SET MANAGED (FOREIGN VIEW).

• FUERZA

  DROP CONNECTION con FORCE elimina las tablas o vistas externas restantes de un catálogo externo al transformar el catálogo en uno estándar. Este comando no elimina datos ni metadatos en su catálogo externo; solo elimina los metadatos sincronizados en Unity Catalog para crear la tabla externa.

  Advertencia

  No se puede revertir este comando. Si desea volver a federar las tablas externas en el catálogo de Unity, debe volver a crear el catálogo externo.

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 para comprobar tablas y vistas externas

Script para enumerar todas las tablas y vistas externas del catálogo federado:

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 para enumerar todas las tablas y vistas externas que están en estado de aprovisionamiento ACTIVO:

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)

Artículos relacionados

• ALTER CATALOG
• CREATE CATALOG
• DROP CATALOG
• SET ADMINISTRADO (VISTA EXTERNA)
• Conversión de una tabla externa en una tabla de catálogo de Unity administrada
• Conversión de una tabla externa en una tabla de catálogo de Unity externa