とりあえずBigQueryのコスト管理したい人向け

## この記事の目的
- とりあえずBigQueryのコスト管理・監視を最低限やっておきたい時の備忘録
    - 記事の内容をやっておけば **「BigQueryのコスト管理・監視やってます」** と言える・・・多分。

## 前提
- 下記の各種操作ができる権限をもっているかの確認はお忘れなく
    - BigQuery
    - CloudLogging (ログルーター)
    - Lookerを使える環境があればベター
        - ベターであるので、アウトプットができる代替手段あれば問題なし

やる事とはBigQueryのクエリログを、分析用のデータセットに流し込んで分析するだけです。
<font color="red">本当に「とりあえず」なので、正確なものを求められる場合は、当記事の内容を更に掘り下げて見てもらえれば良いかと思います。</font>

## 分析用データセットをBigQueryに作る
- ログを流し込む用のデータセットを作ります
![menu.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/1c70764d-feb5-5358-405c-dafd9c5367b5.png)
- とりあえずなので単一リージョンで。
![create_dataset.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/1dbe14be-b46d-45fa-d098-b8f728d8ea09.png)


テーブルは自動で作られるので、今のところはここまで。

## ログルーター
- CloudLoggingのBigQueryクエリログを、先ほどのテーブルセットに流し込む設定をします。
![logimg.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/8a9dca70-514c-a7a2-eb8b-bb0889e45e2a.png)
- シンク名と説明は適当に。
![シンク.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/b6f14c28-367f-65cb-b4ea-57f0a2099c91.png)
- シンクの宛先は**BigQueryデータセット**で、最初に作った分析用データセットを指定する。
    - <font color="red">「パーティション分割テーブルを使用」には必ずチェックをつけましょう！</font>
        - コスト管理の為のテーブルで、無駄にコストが発生しかねない本末転倒な事になります。
![ シンク宛先.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/6a09fd27-ce7b-82e4-21a0-52df1697333d.png)
- シンクに含めるログを設定します。
![シンクログ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/b950643e-d7d4-04d9-c2f1-72bb1e32358c.png)
    - フィルタは以下の設定なら、とりあえずは実行されているクエリ情報は集められます。
        ```
        protoPayload.methodName="jobservice.jobcompleted" AND
        resource.type="bigquery_resource"
        ```
        - ログの量が多くなるので、**１クエリあたりのデータ処理バイト数が大きいものに絞る**のであれば「totalBilledBytes」のフィルタ条件を追加します。（下記は1GB以上のクエリ操作を対象とした場合）
            - ただ<font color="red">１クエリあたりのバイト数が少なくても、発行数が多すぎてコストが膨らんでいる</font>というパターンもありうるので、きちんと管理したいなら**バイト数でのフィルタリングは推奨しません。**
        ```
        protoPayload.methodName="jobservice.jobcompleted" AND
        resource.type="bigquery_resource"
        protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes>="1073741824"
        ```

ここまで設定したら、「シンクを作成」を実行

## BigQueryのデータセット確認
- ログルーターが正常に動いていれば、分析用データセットに「cloudaudit_googleapis_com_data_access」等テーブルが作成されています。
![table.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/0ed3545d-8af1-cb7a-e4e4-646cdb403b31.png)
- 　テーブル構造は以下の通りですが、RECORD型のカラムもあり<font color=red>必要な情報だけを取得するのはなかなか面倒</font>です。
![column.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/dfae9a85-66e7-0640-c8da-5d15ff165310.png)
![ログプレビュー.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/b5c3e7b3-8c11-2f42-931a-53d6fadbc348.png)


### データの確認
とりあえず重要そうな情報は、SELECTで以下の記述でカラム名を指定すればOKです。
| カラム | データ内容 |
|:-:|:-:|
|protopayload_auditlog.authenticationInfo.principalEmail| クエリを発行したユーザー(IAM) |
|protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes  | 課金対象データ処理バイト数 |
|protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query|クエリ|

例
- データ処理バイト数はMB表記にするようにしています。
- WHERE句のtimestampによる絞り込み（パーティション適用）は忘れずに！
```
SELECT
    timestamp,
    protopayload_auditlog.authenticationInfo.principalEmail AS exe_user,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query AS query,
    ROUND(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes/1024/1024, 1) as billed_mb
  
  FROM `xxxx.monitoring_log_data_set.cloudaudit_googleapis_com_data_access`
  WHERE TIMESTAMP_TRUNC(timestamp, DAY) = TIMESTAMP("2023-12-24") 
```
![result.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/762d8363-e746-a2bf-5ef3-cd5befee1e59.png)

## マテリアライズドビューの作成
素のテーブルのままだと使い勝手が悪いので、必要な情報のみをピックアップしたビューを作成します。
- 前項の「クエリ発行ユーザー」「クエリ」「データ処理バイト数」に加え、「データセット名」と「テーブル名」を追加してビューにしています。
```
CREATE MATERIALIZED VIEW `xxx.monitoring_log_data_set.view_bq_job_monitoring`
PARTITION BY DATE(timestamp)
AS 
SELECT
 timestamp,
  protopayload_auditlog.authenticationInfo.principalEmail AS exe_user,
  e.datasetId as datasetId,
  e.tableID as tableID,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query AS query,
  ROUND(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes/1024/1024, 1) as billed_mb
  FROM `xxx.monitoring_log_data_set.cloudaudit_googleapis_com_data_access`, unnest(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.referencedTables) as e
```
- これをベースにしてコストを監視するイメージです。
![ビュー.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/586124d2-ab1d-6099-9c05-76de2f916b2d.png)

### クエリ例
- ユーザー別の処理データバイト数を把握する
```
SELECT exe_user, sum(billed_mb/1024) as billed_gb FROM `xxx.monitoring_log_data_set.view_bq_job_monitoring` WHERE TIMESTAMP_TRUNC(timestamp, DAY) = TIMESTAMP("2023-12-13") group by exe_user order by billed_gb DESC
```
- ユーザー x テーブル毎の処理データバイト数を把握する
```
SELECT exe_user,tableID, sum(billed_mb/1024) as billed_gb FROM `xxx.monitoring_log_data_set.view_bq_job_monitoring` WHERE TIMESTAMP_TRUNC(timestamp, DAY) = TIMESTAMP("2023-12-13") group by exe_user,tableID ORDER by billed_gb DESC
```


## Looker Studioでのアプトプット例
### 例1.クエリ発行元を分類で分けて、それぞれの比率を出す
- 日の課金対象となっている処理データバイト数の内訳を監視します
    - この例は、**12/13以前のバッチ処理が発行したクエリが過剰なバイト数を処理していた**ので調査し、12/14以降解消した時の推移です。(ユーザー操作によるデータ量が膨らんでますが・・・)
        - 現在、何がBigQueryのコストに負担をかけているのかを一目で見ることができますね。
            - 12/16-17は土日だったので、ユーザー操作はほぼ0なのもわかります。
![looker例１.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/f7ea104e-19ae-1786-9592-3eb5186323b9.png)
#### 主な設定
- これは前項のビューを使っています

![設定１.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/43c9da12-2d9b-9736-f220-cec383413654.png)
- 「フィールドを追加」　→　「グループ」追加で、クエリの発行元をグルーピングします
    - 例の場合は、バッチと外部連携で個別のサービスアカウントを用意してBigQueryを使わせているのでこんな感じになります。
![グルーピング.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/992a69e6-3df8-d74e-b219-534e2d471c07.png)

### 例2.1クエリで処理データバイト数が一定値以上のものを抽出する
マスキングしているので見た目悪いですが、
- 「誰が」
- 「どのテーブルに」
- 「どんなクエリを発行して」
- 「どれだけの課金データ量が発生した」

という情報をまとめています。
例だとテーブル名での絞り込みもできます。
![looker.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/703531a2-a464-9950-863d-4a4102a1e5ba.png)

#### 主な設定
- これも前項と同じビューを使っていますが、1GB以上を条件とするフィルタを追加しています。
※billed_gbという項目は、「計算フィールド」でbilled_mb項目をGB表記に変換した追加したものです
![フィルタ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/69b3ea5c-e47b-6c5e-b73c-59b4deb57636.png)
![フィルタ条件.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3293400/d18c44c1-c1c3-3808-19d8-14319df90459.png)


**今回作成したビューをベースにすれば、例以外にも色々とやれる事はあるかと思います。**

## 最後に
これで「気がついたらBigQueryのコストが訳もわからずコストが跳ね上がっていた」という事態は回避できるのではと。

とはいえ、まだやれる事はあるので、この記事の内容は<font color="red">あくまでコスト管理の第一歩</font>です。
**Monitoringで一定以上の処理データバイト数のクエリが走った時にslackに通知するアラートを作る**とか、工数見合いで色々掘り下げて行ってもらえると幸いです。