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

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

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

続いて、各ステップを説明します。
##### 1.課金情報をエクスポート
主にこの記事を参考しました。→[GCP: 今月のGCP課金額をslackに自動的に書き込む](https://ex-ture.com/blog/2017/11/06/gcp-billing-to-slack/)←
エクスポート機能をオンにすると、BigQueryの指定されたデータセットに、GCP全ての課金情報が入れ込まれます。大体30分〜１時間ごとに新しい課金情報がどんどん追加されます。

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

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

```sql
-- BigQuery 課金単位
SELECT
  DISTINCT sku.description
FROM
  `<PJ名>.<Dataset名>.gcp_billing_export_v1_014465_30B3AE_52397F`
WHERE
　　service.description = 'BigQuery'
ORDER BY 
  1
```
![BigQuery_-_GDP_glopla_-_Google_Cloud_Platform.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/200629/0b780df9-d729-27c4-35a4-91ed0412ad92.png)

公式サイトに記載されているため詳しく説明しませんが、クエリの料金とストレージの料金などがあります。→[料金説明の参考](https://cloud.google.com/bigquery/pricing?hl=ja#storage)←

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

```sql
-- 先日の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を作る](https://qiita.com/s_yaginuma/items/26715f776fe6e8c6c969)←

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

```javascript
  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のほうについては、下記のような感じで入れ替えます。

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

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

<img width="574" alt="Apps_Script_-_プロジェクト_トリガー-2.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/200629/8a0c7230-7c2a-da41-2f35-7600b9c84410.png">


# おしまい
これで、よりBigQueryのクエリ課金状況を把握できるようになるでしょう。
ちなみに、実際のslackの通知はこんな感じになっています。
<img width="462" alt="Slack_-_GLOBIS.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/200629/4ee15dcd-06fa-08b7-6b95-8dc478f973b4.png">
それでは、いろいろモニタリングしたい項目を集約して、slackに定期的に送るようにしましょう〜！