监控 SQL

BigQuery：通过 INFORMATION_SCHEMA 监控查询费用

Pascal Landau

0/5 (0投票)

2022 年 5 月 2 日

MIT

2分钟阅读

4776

调试 BigQuery 使用情况

本文首先发表于 https://www.pascallandau.com/ 的 BigQuery：通过 INFORMATION_SCHEMA 监控查询费用。

在 Google BigQuery 中进行成本监控可能是一项艰巨的任务，尤其是在一个不断壮大的组织中，并且有许多（独立的）利益相关者可以访问数据。如果您的组织没有使用预留槽位（固定费率定价），而是按处理的字节数计费（按需定价），成本可能会迅速失控，我们需要通过某种方式来调查或“调试” BigQuery 的使用情况，以便了解

谁运行了高成本的查询
什么是确切的查询
何时运行了这些查询（它们甚至可能定期运行）

以前，我们必须手动设置查询日志记录，通过 Stackdriver，如文章采取务实的方法来监控 BigQuery 成本所述，但 2019 年底，BigQuery 引入了 INFORMATION_SCHEMA 视图作为 beta 功能，其中还包含有关 BigQuery 作业的数据，通过 INFORMATION_SCHEMA.JOBS_BY_* 视图，并在 2020-06-16 获得全面发布 (GA)。

示例

SELECT 
  creation_time,
  job_id,
  project_id,
  user_email,
  total_bytes_processed,
  query
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER

SELECT * FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

SELECT * FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION

工作示例

# Monitor Query costs in BigQuery; standard-sql; 2020-06-21
# @see http://www.pascallandau.com/bigquery-snippets/monitor-query-costs/

DECLARE timezone STRING DEFAULT "Europe/Berlin";
DECLARE gb_divisor INT64 DEFAULT 1024*1024*1024;
DECLARE tb_divisor INT64 DEFAULT gb_divisor*1024;
DECLARE cost_per_tb_in_dollar INT64 DEFAULT 5;
DECLARE cost_factor FLOAT64 DEFAULT cost_per_tb_in_dollar / tb_divisor;

SELECT
 DATE(creation_time, timezone) creation_date,
 FORMAT_TIMESTAMP("%F %H:%I:%S", creation_time, timezone) as query_time,
 job_id,
 ROUND(total_bytes_processed / gb_divisor,2) as bytes_processed_in_gb,
 IF(cache_hit != true, ROUND(total_bytes_processed * cost_factor,4), 0) as cost_in_dollar,
 project_id,
 user_email,
FROM 
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE
  DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) and CURRENT_DATE()
ORDER BY
  bytes_processed_in_gb DESC

此查询将从 INFORMATION_SCHEMA.JOBS_BY_USER 视图中选择与成本监控相关的最有趣的字段，这些字段用于在当前选定项目中在 US 地区运行的所有作业。
cost_in_dollar 通过计算 Terabyte 中 total_bytes_processed 并将结果乘以 5.00 美元来估算（这对应于截至 2020-06-22 的成本）。此外，我们仅考虑未从缓存中回答的查询的成本（参见 cache_hit != true 条件）。
creation_time 转换为我们的本地时区。
通过使用 WHERE 子句过滤分区列 creation_time，结果限制在过去 30 天内。
随意将 JOBS_BY_PROJECT 替换为 JOBS_BY_USER 或 JOBS_BY_ORGANIZATION

在 BigQuery 上运行

在 BigQuery UI 中打开

注释

在使用 INFORMATION_SCHEMA 视图时，我遇到了一些小问题

不同的视图需要不同的权限。
这些视图是区域化的，也就是说，我们必须在区域前缀（参见视图规范中的 region-us）并在 该区域 运行作业（例如，从 BigQuery UI 通过更多 > 查询设置 > 处理位置）
不可能在查询中 混合多个区域，因为处理位置为 US 的查询只能访问位置 US 中的资源。尽管这对于积极使用不同位置的组织来说非常有帮助，但这是不可能的
```
SELECT * FROM 
(SELECT * `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION)
UNION ALL
(SELECT * `region-eu`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION)
```
数据目前仅保留过去 180 天。
JOBS_BY_USER 视图似乎根据电子邮件地址“匹配”用户。我的用户电子邮件地址是 @googlemail.com 地址；在用户列中，它存储为 @gmail.com。因此，当我使用 JOBS_BY_USER 时，我没有得到任何结果。
JOBS_BY_USER 和 JOBS_BY_PROJECT 默认将使用当前选定的项目。可以通过以下方式指定不同的项目（例如，other-project）
```
SELECT * FROM `other-project.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
```
完整的 query 对于 JOBS_BY_ORGANIZATION 不可用。

由于技术限制，本文的字数限制为 40000 个字符。在 BigQuery：通过 INFORMATION_SCHEMA 监控查询费用阅读全文