BigQuery成本监控:利用INFORMATION_SCHEMA优化数据处理

在日益增长的组织中,尤其是当许多独立的利益相关者可以访问数据时,Google BigQuery的成本监控可能是一项艰巨的任务。如果组织没有使用预留槽(固定费率定价),而是按处理的字节数计费(按需定价),成本可能会迅速失控。需要一种方法来调查或“调试”BigQuery的使用情况,以便理解:

  • 谁运行了高成本的查询
  • 确切的查询是什么
  • 这些查询何时运行(甚至可能定期运行)

以前,不得不通过Stackdriver手动设置查询日志,如文章《采取实际方法监控BigQuery成本》中所解释的。但在2019年底,BigQuery引入了作为测试功能的INFORMATION_SCHEMA视图,这些视图也包含了通过INFORMATION_SCHEMA.JOBS_BY_*视图的数据,并于2020年6月16日全面上市(GA)。

示例SQL查询

以下是一些使用INFORMATION_SCHEMA视图查询BigQuery作业的示例SQL查询:

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

工作示例

以下是监控BigQuery查询成本的工作示例SQL查询:

# 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视图中与成本监控最相关的字段,用于在当前选定项目中运行的所有作业。成本_in_dollar是通过计算total_bytes_processed以TB为单位并乘以5.00美元(截至2020年6月22日的成本)来估算的。此外,只考虑那些没有从缓存中回答的查询(参见cache_hit != true条件)。creation_time被转换为本地时区。结果被限制在过去30天内,通过WHERE子句过滤分区列creation_time。

  • 不同的视图需要不同的权限。
  • 视图是区域化的,即必须在视图规范中前缀区域(参见region-us),并且必须在该区域运行作业(例如,通过BigQuery UI中的“更多”>“查询设置”>“处理位置”)。
  • 不可能在查询中混合多个区域,因为处理位置为US的查询只能访问位置为US的资源。虽然这对于积极使用不同位置的组织来说将非常有用,但类似这样的东西是不可能的:
SELECT * FROM ( SELECT * FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION) UNION ALL ( SELECT * FROM `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
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485