データエンジニアのaranです。
月日の流れは早いもので、今年の１月以来３回目の登場になります！

いきなりで、すみませんが
最近ハマっているテレビは、NHKの「みんなで筋肉体操」です。

パワーワードは「自分に甘えない！」です。

この言葉を自分に言い聞かせ、７月中旬から筋トレと１時間弱のウォーキングをしています。
２ヶ月継続したのですが、このブログを書いている時には、高校時の体重に戻りました。
次は体脂肪率も戻そうと頑張っています。

前書き

前回は、BERTについてお話しさせて頂きました。

今回は自然言語処理から離れ、データエンジニアらしく？
BigQueryのINFORMATION_SCHEMAについて調べましたので
そのお話しをさせて頂きます。

何でまた？

弊社では、特許情報を取り扱っており
ある特定の業界・分野の特許群を検索するのに、BigQueryを活用しています。

目的の特許群を検索するには、
いろいろな単語を組み合わるため、検索条件が複雑になり
トライ＆エラーを繰り返すため
BigQueryの費用がかかりがちになります。

また、社員の増加によりBigQueryの利用頻度が増えるため
更なるBigQueryのコスト増が予測でき
コストの見える化、コスト削減策の早期実施が
より重要になってきます。

そこで、2020年8月にINFORMATION_SCHEMA VIEWSが一般提供したこともあり
メタデータを可視化することで、コスト削減箇所を炙り出せるのでは？
と、淡い期待を抱き、調査することにしました。

INFORMATION_SCHEMAについて

INFORMATION_SCHEMAとは
データセット、ジョブ、テーブル、ビュー等のメタデータへのアクセスを提供する一連のビューです。

INFORMATION_SCHEMAの詳細については、
BigQueryの公式ドキュメントで確認できます。

尚、INFORMATION_SCHEMAにアスセスする場合、料金が発生します。
制約等を含め、公式ドキュメントを確認の上、アクセスすることをおすすめします。

INFORMATION_SCHEMAのアクセス方法

INFORMATION_SCHEMAには
「リージョン修飾子.INFORMATION_SCHEMA.ビュー」
でアクセスできます。

例えば、データセットのメタデータにアクセスするには、以下のSQLを実行します

SELECT * FROM `region-us.INFORMATION_SCHEMA.SCHEMATA`;

また、テーブルに関しては、リージョン修飾子の部分をデータセットIDにすることで
指定したデータセットのテーブルメタデータを取得できます。

SELECT * FROM `dataset_id.INFORMATION_SCHEMA.TABLES`;

BigQueryのコストについて

先程、INFORMATION_SCHEMAにアスセスする場合、料金が発生すると説明しましたが
BigQueryの料金について軽く触れたいと思います。

詳細については、公式ドキュメントにおまかせすることにしますが
BigQueryの料金は大きく２つから構成され

ストレージ料金
クエリ料金

ストレージ料金は、データを保持する期間とデータ量で課金され
クエリ料金は、SQL実行時に読み込んだデータ量で課金されます。

BigQueryのコスト削減方法はいろいろあると思いますが
今回は以下の２つに絞って、話をすすめたいと思います。

ストレージの削除：削除可能な不要なストレージ（データセット）を見つける
定額料金の利用：定額料金（スロット数）を見積もる

そこで、INFORMATION_SCHEMAのメタデータを使い
以下の３点を把握できるか調査しました。

データセットのレコード総数とデータ総量の把握
90日以上アクセスがないデータセットの把握
定額スロット数の把握（概算見積もり）

データセットのレコード総数とデータ総量の把握

まずは、データセットのデータ総量とレコード総数を調べてみたいと思います。
こちらを把握できれば、毎月のストレージ料金の概算
並びに、レコード数の増加による料金増加率の見積りを期待できます。

データセットのデータ総量とレコード総数は
データセットID.__TABLES__より取得できました

-- データセットのレコード総数とデータ総量を取得
SELECT
  dataset_id,
  FORMAT("%'d", SUM(row_count)) AS row_count,
  ROUND(SUM(size_bytes) /(1024 * 1024 * 1024 * 1024), 2) AS size_tb
FROM
  -- データセットIDは、環境に合わせ適宜変更して下さい
  データセットID.__TABLES__
GROUP BY
  dataset_id
;

尚、今回の調査では、１つのSQLで全データセットを集計する方法は
わかりませんでしたので
データセット分のSQLをUNION ALLでつなげた形で取得しています。
（ご存知の方いらっしゃいましたら、ぜひお教え頂ければと）

一部抜粋した形になりますが、弊社環境で利用しているデータセット一覧は
以下のようになりました
(データセット名はマスキングしております)

f:id:astamuse:20200922235235p:plain f:id:astamuse:20200922235254p:plain

尚、今回の利用しているプロジェクトは特許関連情報を管理しており
アプリのアクセスログ等のように、データ件数はそこまで多くありません。

本SQLを実行することで、用途を把握できていないデータセットや
想定以上にデータ量が多いデータセットが存在していることも
~~恥ずかしながら、~~わかりました。。

90日以上アクセスがないデータセットを洗い出す

次に、各データセットへの最終アクセス日(最終クエリ実行日)を取得して
９０日以上アクセスがないデータセット一覧を洗い出せるか
調べてみたいと思います。

こちらを把握できれば、長期保存に移行したデータセット有無
並びに、データセットの削除やCloud Storageへの移行等が検討できます。

こちらは、 INFORMATION_SCHEMA.JOBS_BY_*を利用することで
取得できました。

SELECT
  s.schema_name,
  j.latest_creation_time
FROM
  `region-us.INFORMATION_SCHEMA.SCHEMATA` AS s
LEFT JOIN (
  SELECT
    referenced_table.dataset_id AS dataset_id,
    MAX(creation_time) AS latest_creation_time
  FROM
    `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`,
    UNNEST(referenced_tables) AS referenced_table
  WHERE
    referenced_table.table_id != '__TABLES__'
  GROUP BY
    referenced_table.dataset_id ) AS j
ON
  j.dataset_id = s.schema_name
-- ジョブメタデータにアクセス履歴がない、または最終クエリ実行日より90日以上経過したデータセット
WHERE j.latest_creation_time IS NULL
  OR j.latest_creation_time < TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 90 DAY)
;

注意点として
データセットのレコード総数とデータ総量の把握で実行したSQLを対象外にするため
WHERE句に referenced_table.table_id != '__TABLES__' を追加しております。

尚、弊社環境で本SQLを実行したところ
90日以上アクセスがないデータセットが1つ
INFORMATION_SCHEMA.JOBS_BY_*にデータがない（180日以上アクセスがない）
データセットが17つありました。
(※ドキュメントでは、完了したジョブの過去 180 日間の履歴を表示との記述がありますが、弊社環境では100日程度の履歴でした)

定額スロット数の概算見積もり

最後に、定額スロット数の概算見積もりができないかを
調べてみたいと思います。

こちらを把握できれば、クエリ実行毎で課金されるオンデマンドより
定額料金への移行、並びにスロット購入数の見積りを期待できます。

スロットの詳細については、BigQueryの公式ドキュメントにおまかせします

スロット使用率については、 INFORMATION_SCHEMA.JOBS_BY_*を利用することで取得でき
公式ドキュメントのサンプルSQLを参考にし、月平均のスロット使用率を取得してみました。

SELECT
  DATE(TIMESTAMP_TRUNC(creation_time, MONTH), "Asia/Tokyo") AS month,
  COUNT(1),
  -- 月平均のスロット使用率
  SUM(total_slot_ms)
    / (1000*60*60*24* 
        -- 日付を抽出
        EXTRACT(DAY FROM 
          -- 月末日を取得
          DATE_SUB(
            -- 1ヶ月を加算
            DATE_ADD(
              -- 月初日付を取得
              DATE(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH), "Asia/Tokyo"),
              INTERVAL 1 MONTH
            ),
            INTERVAL 1 DAY
          )
        )) AS avg_slots
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
GROUP BY
  DATE(TIMESTAMP_TRUNC(creation_time, MONTH), "Asia/Tokyo")
;