如何在BigQuery中列出创建视图所依赖的所有表？

MSSQL中的实现方式 #

在MSSQL/TSQL中，可通过以下语句直接列出视图所依赖的用户表：

select distinct schema_name(v.schema_id) as schema_name,
   v.name as view_name,
   schema_name(o.schema_id) as referenced_schema_name,
   o.name as referenced_entity_name,
   o.type_desc as entity_type
from sys.views v
join sys.sql_expression_dependencies d
 on d.referencing_id = v.object_id
 and d.referenced_id is not null
join sys.objects o
 on o.object_id = d.referenced_id
where o.type_desc = 'USER_TABLE' 
 order by schema_name,
      view_name;

BigQuery的简便实现方案 #

在BigQuery中，无需手动解析视图DDL，可使用INFORMATION_SCHEMA.VIEW_DEPENDENCIES视图直接获取视图的依赖关系，筛选出用户表类型的依赖即可：

SELECT
  referencing_schema_name AS schema_name,
  referencing_view_name AS view_name,
  referenced_schema_name,
  referenced_table_name AS referenced_entity_name,
  'USER_TABLE' AS entity_type
FROM
  `your-project-id`.INFORMATION_SCHEMA.VIEW_DEPENDENCIES
WHERE
  referenced_entity_type = 'TABLE'
ORDER BY
  schema_name, view_name;

说明 #

• 替换语句中的your-project-id为实际的项目ID；
• 若需查询特定数据集的视图依赖，可在WHERE子句中添加referencing_dataset_name = 'your-dataset-name'进行过滤。

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