如何统计BigQuery数据集中所有表近365天的查询次数？

BigQuery支持统计表的查询次数，你可以通过内置视图或审计日志实现，以下是具体方法，兼容R和Python客户端：

一、使用内置视图快速查询（默认覆盖180天） #

BigQuery提供INFORMATION_SCHEMA.TABLE_USAGE_STATS视图，可直接获取表的使用统计数据，默认保留最近180天的记录。如果你的需求是180天内的数据，用这个方法最便捷：

核心SQL查询 #

SELECT
  table_catalog,
  table_schema,
  table_name,
  SUM(total_statements) AS query_count
FROM
  `your-project-id.INFORMATION_SCHEMA.TABLE_USAGE_STATS`
WHERE
  table_schema = 'your-target-dataset'
  AND last_access_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY)
GROUP BY
  table_catalog, table_schema, table_name
ORDER BY
  query_count ASC;

替换your-project-id为你的GCP项目ID，your-target-dataset为要统计的数据集名称。

二、启用审计日志获取365天完整数据 #

如果需要覆盖近365天（超过内置视图的180天限制），需要先开启BigQuery审计日志并导出到BigQuery，再查询日志数据：

1. 开启并导出审计日志 #

• 进入GCP控制台，找到你的项目，依次进入IAM与管理 > 审计日志
• 选择BigQuery服务，勾选数据访问类型的日志，设置导出目标为一个新的BigQuery数据集（例如bq_audit_logs）

2. 查询审计日志的SQL #

SELECT
  resource.labels.dataset_id AS dataset_name,
  resource.labels.table_id AS table_name,
  COUNT(*) AS query_count
FROM
  `your-project-id.bq_audit_logs.cloudaudit_googleapis_com_data_access_*`
WHERE
  resource.type = 'bigquery_table'
  AND resource.labels.dataset_id = 'your-target-dataset'
  AND proto_payload.method_name = 'google.cloud.bigquery.v2.JobService.InsertJob'
  AND proto_payload.service_data.job_insert_request.job.configuration.query IS NOT NULL
  AND TIMESTAMP(proto_payload.metadata.event_timestamp) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY)
GROUP BY
  dataset_name, table_name
ORDER BY
  query_count ASC;

替换占位符为实际项目、审计日志数据集和目标数据集名称。

三、用Python执行查询 #

使用google-cloud-bigquery库执行上述SQL：

from google.cloud import bigquery

# 初始化客户端
client = bigquery.Client(project="your-project-id")

# 替换为上面的SQL（二选一）
sql = """
SELECT
  table_catalog,
  table_schema,
  table_name,
  SUM(total_statements) AS query_count
FROM
  `your-project-id.INFORMATION_SCHEMA.TABLE_USAGE_STATS`
WHERE
  table_schema = 'your-target-dataset'
  AND last_access_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY)
GROUP BY
  table_catalog, table_schema, table_name
ORDER BY
  query_count ASC;
"""

# 执行查询并输出结果
query_job = client.query(sql)
for row in query_job.result():
    print(f"表: {row.table_schema}.{row.table_name}, 查询次数: {row.query_count or 0}")

四、用R执行查询 #

使用bigrquery库执行：

library(bigrquery)

# 配置项目ID
project_id <- "your-project-id"

# 替换为上面的SQL（二选一）
sql <- "
SELECT
  table_catalog,
  table_schema,
  table_name,
  SUM(total_statements) AS query_count
FROM
  `your-project-id.INFORMATION_SCHEMA.TABLE_USAGE_STATS`
WHERE
  table_schema = 'your-target-dataset'
  AND last_access_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY)
GROUP BY
  table_catalog, table_schema, table_name
ORDER BY
  query_count ASC;
"

# 执行查询并查看结果
query_result <- bq_project_query(project_id, sql)
usage_df <- bq_table_download(query_result)
print(usage_df)

注意事项 #

• 权限要求：执行账号需要bigquery.jobs.list权限，以及目标数据集的bigquery.tables.get权限；使用审计日志时还需要对日志数据集的读取权限。
• 数据延迟：TABLE_USAGE_STATS的数据延迟约24-48小时，审计日志的延迟通常在数小时内。
• 未被访问的表：两种方法都不会返回从未被查询过的表，这类表可直接标记为清理目标。

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