获取整个组织中引用特定表的所有BigQuery查询任务

Question

问题说明

我们是一个大型组织(7000多人)，有很多BigQuery项目。我的团队拥有一套经常使用的大约250张桌子。我们意识到一些数据质量问题，但需要优先考虑哪些表是我们努力的重点。

为了确定工作的优先级，我们计划为每个表计算两个指标:

1. 每月引用该表的查询任务总数
2. 引用该表的不同目的表的总数

然而，我们被困在一个方面——如何访问整个组织中引用特定表的所有查询作业?

我们已经尝试过的

我们尝试使用以下查询来查找引用表的所有查询任务:

select count(*) from 
`project-a`.`region-qualifier`.INFORMATION_SCHEMA.JOBS
where job_type = 'QUERY'
and referenced_tables.project_id = 'project-a'
and referenced_tables.dataset_id = 'dataset-b'
and referenced_tables.table_id = 'table-c'

不幸的是，这只显示了以project-a作为计费项目启动的查询作业(错误)。

假设我们有50多个GCP项目可以执行引用我们拥有的表的查询，我们想要看到所有这些项目中的所有这些查询任务。

Answer 1

目前不可能访问整个组织中引用特定表的所有查询作业。

正如您所提到的，您可以使用如下查询来列出项目中的查询作业:

select * from `PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
where job_type = 'QUERY'

PROJECT_ID是您的Cloud项目的ID。如果未指定，则使用默认项目。您可以使用不带project-id的查询，如:

select * from `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
where job_type = 'QUERY'

更多信息请参考本文档

如果您希望该特性列出要实现的整个组织的查询作业，您可以在问题跟踪器上打开一个新的特性请求，描述您的需求。

Answer 2

事实证明，您可以通过Google Cloud Logging获得这些信息。

下面的命令提取了<DATASET_ID>中org引用表的所有查询的日志。

gcloud logging read 'timestamp >= "2022-09-01T00:00:00Z" AND resource.type=bigquery_dataset AND resource.labels.dataset_id=<DATASET_ID> AND severity=INFO'

重要的是，该命令需要从存在<DATASET_ID>的项目中运行，并且您需要roles/logging.admin角色。

值得注意的是，我无法测试INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION，它应该能做到这一点。