データエンジニアの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でつなげた形で取得しています。
（ご存知の方いらっしゃいましたら、ぜひお教え頂ければと）
一部抜粋した形になりますが、弊社環境で利用しているデータセット一覧は
以下のようになりました
(データセット名はマスキングしております)
尚、今回の利用しているプロジェクトは特許関連情報を管理しており
アプリのアクセスログ等のように、データ件数はそこまで多くありません。
本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") ;
尚、弊社環境で、本SQLを実行したところ
以下のようなになりました。
（棒グラフがクエリ実行数、線グラフがスロット使用率）
6月は、他の月よりクエリ実行は少ないが
データ量が多いテーブルへのアクセスが多く
スロット使用率が多いことがわかりました。
最後に
INFORMATION_SCHEMAへの単純なSQLでも
メタデータの把握・可視化ができ、コスト削減できる箇所が
ある程度見えました。
今回は、単純な単位での集計でしたが
更に時間帯別やテーブル単位やユーザ別での集計・分析も
必要になります。
今後もINFORMATION_SCHEMAを使って
更なるメタデータの把握に努めたいと思います。
それ報告はまたの機会に。
アスタミューゼでは、エンジニア・デザイナーを募集中です。
ご興味のある方は遠慮なく採用サイトからご応募願います。是非、お待ちしています。
