背景

BigQueryの料金は安いと言いながらも、社内利用人数とデータ量が増えれば増えるほど、モニタリングしたほうが良いと思うので、BigQueryの課金情報をslackに送る機能を作りたいです。
ウェブ上関連の記事が多いと思いますので、ここで重要なことのみ述べたいと思います。

BigQuery課金情報をslackに送る

下記のステップがあります。
1. Google Cloud Platform (GCP)側、課金情報をエクスポートする
2. SQLを書いて、必要な情報を集計する
3. Google Apps Script (GAS)にて、SQLの実行した結果を、slackに送る機能を開発する
4. デプロイとトリガーの設定

続いて、各ステップを説明します。

1.課金情報をエクスポート

主にこの記事を参考しました。→GCP: 今月のGCP課金額をslackに自動的に書き込む←
エクスポート機能をオンにすると、BigQueryの指定されたデータセットに、GCP全ての課金情報が入れ込まれます。大体30分〜１時間ごとに新しい課金情報がどんどん追加されます。

2.SQL

ここでは、前日のBigQueryクエリ課金情報を抜き出すSQLを書きます。

１つ注意事項があります。BigQueryでは、下記の課金単位があります。Active Storage, Analysis, Long Term Storage と Streaming Insertです。また、リージョン別の課金になっています。
例えば、下記のSQLを実行すると、こんな結果になります。

-- BigQuery 課金単位
SELECT
  DISTINCT sku.description
FROM
  `<PJ名>.<Dataset名>.gcp_billing_export_v1_014465_30B3AE_52397F`
WHERE
　　service.description = 'BigQuery'
ORDER BY 
  1

公式サイトに記載されているため詳しく説明しませんが、クエリの料金とストレージの料金などがあります。→料金説明の参考←

今回は、クエリ料金しか通知を送らないので、 sku.description = 'Analysis'をWHERE文に入れます。

-- 先日のBigQuery クエリ料金を出す
SELECT
  DATE(TIMESTAMP_ADD(usage_start_time, INTERVAL 9 HOUR)) AS query_date,
  service.description,
  project.id,
  project.name,
  SUM(cost) AS cost_jpn,
  SUM(usage.amount_in_pricing_units) AS usage_tb,
  1024 * SUM(usage.amount_in_pricing_units) AS usage_gb,
  usage.pricing_unit,
  currency
FROM
  `<PJ名>.<Dataset名>.gcp_billing_export_v1_014465_30B3AE_52397F`
WHERE
  DATE(_PARTITIONTIME) >= "2019-07-01"
  AND service.description = 'BigQuery'
  AND project.id = 'gdp-glopla'
  AND sku.description = 'Analysis'
GROUP BY
  query_date,
  service.description,
  project.id,
  project.name,
  currency,
  usage.pricing_unit
HAVING 
  query_date =  DATE_ADD(DATE(TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 9 HOUR)), INTERVAL -1 DAY)
ORDER BY
  query_date

実行した結果は、こんな感じになります。

query_date	description	id	name	cost_jpn	usage_tb	usage_gb	pricing_unit	currency
2019/7/16	BigQuery	[PJ ID]	[PJ名]	106.473763	0.197759628	202.5058594	tebibyte	JPY

弊社では、昨日のBigQuery料金は、僅かな ¥106円だけです。202.5 GBのデータにクエリを実行していましたけど、びっくりするほど安いですね。

3.Google Apps Script (GAS)

いよいよGASを使います。
主にこの記事を参考しました。→GASで手軽に SlackへのKPI通知Botを作る←

上記の記事に書いてある関数runQueryを利用するので、中のSQL部分を上記のSQLに入れ替えます。
postSlackの方については、下記の部分も入れ替えます。
また、ここの rows[0].f[4].vというのは、上記の実行されたSQLの1行目の5列目の値という意味です。

  var cost_jpn = Math.round(rows[0].f[4].v * 10) / 10; // 課金
  var usage_gb = Math.round(rows[0].f[6].v * 10) / 10; // クエリ容量GB
  var usage_tb = Math.round(rows[0].f[5].v * 10) / 10; // クエリ容量TB

bodyのfieldsのほうについては、下記のような感じで入れ替えます。

                {
                    "title": "昨日の課金",
                    "value": cost_jpn + ' 円',
                    "short": true /* 二列で表示 */
                },            
                {
                    "title": "昨日の容量 (GB)",
                    "value": usage_gb + ' GB',
                    "short": true  /* 二列で表示 */
                },
                {
                    "title": "昨日の容量 (TB)",
                    "value": usage_tb + ' TB',
                    "short": true /* 二列で表示 */
                }

4.デプロイとトリガー

「編集」→「現在のプロジェクトのトリガー」に入ると、設定画面が出ます。
毎日出社したとき前日の課金情報を見たいので、下記のように設定しています。

おしまい

これで、よりBigQueryのクエリ課金状況を把握できるようになるでしょう。
ちなみに、実際のslackの通知はこんな感じになっています。

それでは、いろいろモニタリングしたい項目を集約して、slackに定期的に送るようにしましょう〜！

BigQuery課金情報をslackへの通知機能を作る

背景