Databricks中如何查看Unity Catalog外部位置的依赖对象？

针对你遇到的gold_prd外部位置删除报错问题，可通过以下方法排查所有依赖对象：

1. 用Unity Catalog系统表直接查询依赖 #

执行以下SQL查询，直接获取关联该外部位置的所有对象：

SELECT 
  dependent_object_type,
  dependent_object_name,
  dependent_object_catalog,
  dependent_object_schema
FROM system.information_schema.dependent_objects
WHERE parent_object_type = 'EXTERNAL LOCATION'
  AND parent_object_name = 'gold_prd';

2. 检查目录/模式的默认存储位置关联 #

部分目录或模式的默认存储位置可能指向该外部位置，可分别查询验证：

• 检查目录：

SELECT catalog_name, storage_location
FROM system.information_schema.catalogs
WHERE storage_location IN (SELECT url FROM system.information_schema.external_locations WHERE external_location_name = 'gold_prd');

• 检查模式：

SELECT catalog_name, schema_name, storage_location
FROM system.information_schema.schemata
WHERE storage_location IN (SELECT url FROM system.information_schema.external_locations WHERE external_location_name = 'gold_prd');

3. 排查外部表与卷的存储路径 #

外部表或卷可能直接使用该外部位置的路径，通过以下查询定位：

• 外部表：

SELECT table_catalog, table_schema, table_name, storage_location
FROM system.information_schema.tables
WHERE table_type = 'EXTERNAL TABLE'
  AND storage_location LIKE CONCAT((SELECT url FROM system.information_schema.external_locations WHERE external_location_name = 'gold_prd'), '%');

• 卷：

SELECT volume_catalog, volume_schema, volume_name, storage_location
FROM system.information_schema.volumes
WHERE volume_type = 'EXTERNAL VOLUME'
  AND storage_location LIKE CONCAT((SELECT url FROM system.information_schema.external_locations WHERE external_location_name = 'gold_prd'), '%');

4. 通过Databricks UI查看依赖 #

在Databricks工作区的数据页面找到gold_prd外部位置，进入详情页后查看依赖项标签，即可直观看到所有关联对象。

内容的提问来源于stack exchange，提问作者Gam