如何获取全组织内引用特定表的所有BigQuery查询作业？

问题描述 #

我们是拥有7000+员工的大型组织，旗下有多个BigQuery项目。团队负责约250个高频使用的表，目前存在数据质量问题，需要优先确定整改重点。

为明确优先级，我们计划为每个表统计两个核心指标：

• 每月引用该表的查询作业总次数
• 引用该表的不同目标表总数

但现在遇到了难题：怎么才能获取整个组织内所有引用特定表的查询作业？

我们尝试过用INFORMATION_SCHEMA.JOBS查询，但只能拿到以目标表所在项目为计费项目的查询作业，无法覆盖组织内另外50+可能引用该表的GCP项目。

解决方案 #

1. 利用BigQuery审计日志（推荐） #

组织内所有BigQuery查询都会生成审计日志，这是跨项目追踪表引用的最可靠方式：

• 先确保组织已开启Data Access Audit Logs里的bigquery.googleapis.com/data_access日志，覆盖所有相关项目。
• 通过Cloud Logging把这些日志导出到一个集中的BigQuery数据集。
• 然后用下面的SQL查询导出的日志表，筛选出引用目标表的所有查询：

SELECT
  proto_payload.service_data.job_insert_request.job.job_id,
  proto_payload.service_data.job_insert_request.job.project_id AS query_project,
  timestamp AS query_time,
  proto_payload.service_data.job_insert_request.job.configuration.query.destination_table AS target_table,
  proto_payload.service_data.job_insert_request.job.configuration.query.query
FROM
  `your-audit-log-project.your-dataset.cloudaudit_googleapis_com_data_access_*`
WHERE
  proto_payload.method_name = 'jobservice.insert'
  AND EXISTS (
    SELECT 1
    FROM UNNEST(JSON_EXTRACT_ARRAY(proto_payload.service_data.job_insert_request.job.configuration.query.referenced_tables)) rt
    WHERE JSON_VALUE(rt, '$.projectId') = 'project-a'
      AND JSON_VALUE(rt, '$.datasetId') = 'dataset-b'
      AND JSON_VALUE(rt, '$.tableId') = 'table-c'
  )
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)) 
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())

这个查询能覆盖所有项目发起的引用查询，还能直接提取目标表信息，方便你统计那两个指标。

2. 脚本遍历所有项目的INFORMATION_SCHEMA.JOBS #

如果没法用审计日志，可以写个脚本遍历所有50+项目的区域级INFORMATION_SCHEMA.JOBS视图，汇总结果：

• 用Python的BigQuery客户端库，遍历每个项目的所有使用区域。
• 对每个项目区域执行查询，合并结果。示例代码：

from google.cloud import bigquery

client = bigquery.Client()
# 替换成你的目标表信息和项目、区域列表
TARGET_PROJECT = "project-a"
TARGET_DATASET = "dataset-b"
TARGET_TABLE = "table-c"
PROJECT_LIST = ["project-1", "project-2", ...]
REGION_LIST = ["US", "EU", "asia-southeast1", ...]

total_query_count = 0
distinct_target_tables = set()

for project in PROJECT_LIST:
    for region in REGION_LIST:
        try:
            query = f"""
                SELECT
                    COUNT(*) AS job_count,
                    ARRAY_AGG(DISTINCT CONCAT(destination_table.project_id, '.', destination_table.dataset_id, '.', destination_table.table_id)) AS target_tables
                FROM
                    `{project}.{region}.INFORMATION_SCHEMA.JOBS`
                WHERE
                    job_type = 'QUERY'
                    AND EXISTS (
                        SELECT 1
                        FROM UNNEST(referenced_tables) rt
                        WHERE rt.project_id = '{TARGET_PROJECT}'
                          AND rt.dataset_id = '{TARGET_DATASET}'
                          AND rt.table_id = '{TARGET_TABLE}'
                    )
                    AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 MONTH)
            """
            result = client.query(query).result()
            for row in result:
                total_query_count += row.job_count
                distinct_target_tables.update(row.target_tables)
        except Exception as e:
            print(f"跳过项目{project}区域{region}：{str(e)}")

print(f"当月总引用查询次数：{total_query_count}")
print(f"引用该表的不同目标表数量：{len(distinct_target_tables)}")

注意：这种方法需要你有所有目标项目的BigQuery数据查看权限，而且遍历多项目多区域的效率不高，适合项目数量不多的情况。

3. 用BigQuery Usage Insights（企业版功能） #

如果你的组织用了BigQuery Enterprise Plus层级，直接用Usage Insights就能在控制台查看表的跨项目引用数据，包括查询次数和关联的目标表，不用自己写代码查。

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