如何查询BigQuery表的GCP Dataplex标签值及缺失标签的表数量？

BigQuery的表标签元数据不在常规的INFORMATION_SCHEMA视图里，得用专门的系统视图BQ.INFORMATION_SCHEMA.TABLE_TAGS来获取标签信息，以下是具体实现方法：

1. 查询指定项目/数据集的表标签 #

用下面的SQL可以获取目标范围的表标签数据：

SELECT
  table_catalog AS project_id,
  table_schema AS dataset_id,
  table_name,
  tag_key,
  tag_value
FROM
  `你的项目ID.BQ.INFORMATION_SCHEMA.TABLE_TAGS`
-- 可选：限定特定数据集
WHERE table_schema = '目标数据集ID'

2. 统计缺少特定标签值的表数量 #

假设你要统计缺少environment:production标签组合的表数量，思路是先获取所有物理表的列表，再排除已包含该标签的表，最后计数：

WITH all_tables AS (
  SELECT
    table_catalog,
    table_schema,
    table_name
  FROM
    `你的项目ID.*.INFORMATION_SCHEMA.TABLES`
  WHERE
    table_type = 'BASE TABLE' -- 仅统计物理表，排除视图
),
qualified_tables AS (
  SELECT
    table_catalog,
    table_schema,
    table_name
  FROM
    `你的项目ID.BQ.INFORMATION_SCHEMA.TABLE_TAGS`
  WHERE
    tag_key = 'environment'
    AND tag_value = 'production'
)
SELECT
  COUNT(*) AS missing_tag_table_count
FROM
  all_tables
LEFT JOIN
  qualified_tables
USING (table_catalog, table_schema, table_name)
WHERE
  qualified_tables.table_name IS NULL

如果只需要统计单个数据集，把all_tables里的*.INFORMATION_SCHEMA.TABLES改成目标数据集ID.INFORMATION_SCHEMA.TABLES即可。

注意事项 #

• 需确保账号拥有bigquery.tables.getIamPolicy权限，否则无法访问标签数据
• 项目/数据集级别的继承标签也会被包含在TABLE_TAGS视图中

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