BigQueryのクエリ費用を抽出する

## 請求額をリアルタイムに把握したい
Google Analytics 360やGoogle Analytics 4でBigQueryからデータを抽出する機会は増えていて、従量課金ということもあり任意の時点でのクエリの費用をリアルタイムに把握したいこともあると思います。
そこで、ここではロギングの監査ログをBQテーブルとして出力し、クエリに掛かった費用を抽出してみます。


## ログ ルーティング シンクの作成

[ロギング](https://console.cloud.google.com/logs/query)にてシンクを作成します。（キャプチャは新UIになっているため、人によっては同じ画面が表示されないかもしれません）

### シンクの詳細
シンク名と説明を設定します。

![ログ-ルーティング-シンクの作成-–-ロギング-–-ataru-storage-–-Google-Cloud-Platform.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/87061f6b-360e-d1ff-3c21-d3c073e218a8.png)

### シンクの宛先
シンクサービスをBigQuery データセットを選択し、BigQueryデータセットを任意のデータセットで選択します。日付別シャード化テーブルとして作成することもできるようです。

![ログ-ルーティング-シンクの作成-–-ロギング-–-ataru-storage-–-Google-Cloud-Platform (1).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/085c4b00-da06-a45d-31f4-dd7474634629.png)

### シンクに含めるログの選択

任意のログに絞りたい場合は包含フィルタを作成します。
ここでは`resource.type`に`bigquery_resource`を指定しています。

![ログ-ルーティング-シンクの編集-–-ロギング-–-ataru-storage-–-Google-Cloud-Platform.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/c8e67dfc-ac98-cf9f-2845-876a787a850e.png)


### シンクに含めないログの選択

任意のログを場外したい場合には除外フィルタを作成します。

![ログ-ルーティング-シンクの作成-–-ロギング-–-ataru-storage-–-Google-Cloud-Platform (3).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/8db90822-1062-9aa0-208b-c19867c5ce46.png)

以上の設定で、指定されたBigQueryテーブルへ BigQueryの監査ログが出力されていくことになります。

## SQLで抽出する

以下のクエリは任意の期間(直近半年)でのprincipalEmailごとの推定費用となります。

ロギングで作成したデータセットに `cloudaudit_googleapis_com_data_access`というテーブルが作成されているのでFROM句にこれを指定します。

```sql
DECLARE
  PRICING FLOAT64 DEFAULT 5.0;
CREATE TEMP FUNCTION
  START_DATE() AS (FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)));
CREATE TEMP FUNCTION
  END_DATE() AS (FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)));
WITH
  raw_data AS (
  SELECT
    protopayload_auditlog.authenticationInfo.principalEmail AS principalEmail,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent AS jobCompletedEvent
  FROM
    `project_name.dataset_name.cloudaudit_googleapis_com_data_access_*`
  WHERE
    _TABLE_SUFFIX BETWEEN START_DATE()
    AND END_DATE()
)
SELECT
  principalEmail,
  FORMAT('%9.2f', PRICING * (SUM(jobCompletedEvent.job.jobStatistics.totalBilledBytes) / POWER(2, 40))) AS Estimated_USD_Cost
FROM
  raw_data
WHERE
  jobCompletedEvent.eventName = 'query_job_completed'
GROUP BY
  principalEmail
ORDER BY
  Estimated_USD_Cost DESC
```

`PRICING`の変数については、クエリを発行するプロジェクトのリージョンによって価格設定が異なるので任意の値を設定します。例えば米国リージョンは＄5/TBで東京リージョンは＄6/TBとなります。
1TBは1Byteの`2の40乗`となるため、`POWER(2,40)`で計算した指数で割り算して(ByteをTBにして)、`PRICING`を掛けることになります。

`STARTDATE` は任意の開始期間を設定します。今回の例では「180日前から当日まで」という抽出範囲となります。

以上のクエリを保存しておけば、WEBコンソールからいつでも呼び出せるようになります。

![スクリーンショット 2021-02-01 18.03.05（2）.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/9f3bc5ac-6288-e2f9-686e-5928ceb833b9.png)


## 結果
クエリを発行すると以下のように、principalEmailごとの米国ドルコストを抽出することができました。hoge@mediba(仮)は半年間で333ドル分のクエリを発行していたようです。

![BigQuery-–-ataru-storage-–-Google-Cloud-Platform.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/3264b468-6044-6171-22b4-515f625ea9d1.png)

※ここでは毎月の無料枠(1TB)の費用を考慮していません。

今回は利用していませんが、`jobCompletedEvent.job.jobConfiguration.query.query` には発行されたクエリも記述されているため、個別のクエリと費用を抽出することも可能です。

## 終わりに
ロギングを利用すればリアルタイム連携できるようになりますので、例えばCloud Pub/Subを中継して「クエリの発行が発生する度にメールアドレス・推定費用・発行クエリを任意のHTTPエンドポイントに投げる」なんてこともできます。Slack連携が捗りそうですね。