💽

[Google Cloud]Data StudioがBigQueryへ発行しているクエリを探る

2021/05/04に公開

Google Cloud

BigQuery

Google Data Studio

tech

Google CloudのAlways FreeでMLBデータ分析を行うための基盤を作るんだ！と、作り上げた物をまとめたブログを先日Zennで書きました。

ダッシュボードまで仕上がって、しかも無料！

とても最高なのですが、、ふと、とあることが気になってきました。

「Google Data StudioでBigQueryにつないでいるけど、どんなクエリが発行されているんだ、、」

Data Studioが "BigQuery as Always Free" を台無しにするかもしれない

BigQueryのAlways Free条件は、下記でアナウンスされています。

MLBデータ分析基盤におけるAlways Freeの条件は、ストレージとクエリ実行時のデータ量の2点。
うち、ストレージは2.5GBぐらいなので大丈夫として、1TB/月のリミットがあるクエリがユーザーとして気になってきます。

現在、Data Studioでダッシュボード化している対象データは2021年シーズンだけに絞ったViewで、元テーブルはシーズンでパーティショニングもしているので、Viewアクセスの度にREADされるデータ量は大体85MiBぐらいに抑えています（フルスキャンだと2GBぐらい行っちゃうので、、）。が、気になるのは、、

どんなクエリが
どれだけ発行されているのか

と言う点。理不尽にパフォーマンスが悪いクエリが発行されていたり、クエリがありえない量発行されていたり、、とかが起こると、1TBと言う死守ラインを超えてしまうリスクが生じてしまいます。

このあたりは、ちゃんと抑えておかないと、この先おちおちダッシュボードも作れません。なので、Data Studioで発行されているクエリがどういった物なのか、調査することにしました。

実行クエリの調査方法

Cloud Loggingからアクセスログを取得

Google Cloudのコンソールから左サイドメニュー「ロギング」へ行くと、現在のプロジェクトで回しているプロダクトのログを一元的にチェック出来ます。

一元的にストックしているログをフィルタリングして、BigQueryへのクエリ実行ログを拾います。フィルタリング用のクエリをこんな具合で書いて、レッツフィルタリング。

resource.type=("bigquery_dataset" OR "bigquery_project") AND
logName:"cloudaudit.googleapis.com"

事前に調査用にダッシュボードアクセスを行なっていたので、時間から対象のログを絞れました。割とログがあるな、、と、この時点ではドキドキしています。

アクセスログから実行クエリを抜き出す

ドキドキしていましたが、結果としては杞憂でした。
アクセスログを見ると、BigQueryへの実行リクエストのキューイング（jobInsertion）と、キューされたリクエストの状態変更（jobChange）と、1クエリにつき2種ロギングされているらしいです。なので、実行クエリの取得では、jobInsertion のログだけを見て、対象のデータを抜いてくれば良さそうです。

キャプチャのログをダウンロードした上で、Pythonでパースしました。

import json

with open("bigquery-logs.json", "r") as f:
    logs = json.load(f)
    
# クエリ取得
qs = []
for log in logs:
    metadata = log["protoPayload"]["metadata"]
    if "jobInsertion" in metadata:
        qs.append(metadata["jobInsertion"]["job"]["jobConfig"]["queryConfig"]["query"])
	
# printする
for q in qs:
    print(q)
    print("---")

こんな具合でした（プロジェクト名等はマスキング）。

SELECT * FROM (
SELECT COUNT(1.0) AS clmn100000_ FROM (
SELECT * FROM (
SELECT t0.pitch_type AS clmn0_ FROM `dummy.hoge.fuga` t0
) WHERE ((clmn0_ NOT IN ("FF", "SL", "SI", "CH", "CU", "FC", "KC", "FS") OR (clmn0_ IS NULL)) AND (NOT (clmn0_ IS NULL)))
)
) LIMIT 20000000
---
SELECT * FROM (
SELECT clmn0_, COUNT(1.0) AS clmn100000_ FROM (
SELECT t0.pitch_type AS clmn0_ FROM `dummy.hoge.fuga` t0
) GROUP BY clmn0_
) LIMIT 20000000
---
SELECT * FROM (
SELECT COUNT(DISTINCT clmn92_) AS clmn100000_, COUNT(DISTINCT clmn5_) AS clmn100001_, COUNT(1.0) AS clmn100002_ FROM (
SELECT t0.batter_name AS clmn92_, t0.player_name AS clmn5_ FROM `dummy.hoge.fuga` t0
)
) LIMIT 20000000
---
SELECT * FROM (
SELECT clmn0_, clmn5_, AVG(clmn2_) AS clmn100000_ FROM (
SELECT t0.pitch_type AS clmn0_, t0.player_name AS clmn5_, t0.release_speed AS clmn2_ FROM `dummy.hoge.fuga` t0
) GROUP BY clmn0_, clmn5_
) LIMIT 20000000
---
SELECT * FROM (
SELECT clmn18_, COUNT(1.0) AS clmn100000_ FROM (
SELECT t0.p_throws AS clmn18_ FROM `dummy.hoge.fuga` t0
) GROUP BY clmn18_
) LIMIT 20000000
---
SELECT * FROM (
SELECT clmn0_, clmn5_, AVG(clmn100001_) AS clmn100000_ FROM (
SELECT clmn0_, clmn5_, ABS(clmn27_) AS clmn100001_ FROM (
SELECT t0.pfx_x AS clmn27_, t0.pitch_type AS clmn0_, t0.player_name AS clmn5_ FROM `dummy.hoge.fuga` t0
)
) GROUP BY clmn0_, clmn5_
) LIMIT 20000000
---
SELECT * FROM (
SELECT clmn92_, AVG(clmn53_) AS clmn100000_ FROM (
SELECT * FROM (
SELECT t0.batter_name AS clmn92_, t0.launch_speed AS clmn53_ FROM `dummy.hoge.fuga` t0
) WHERE (NOT (clmn92_ IS NULL))
) GROUP BY clmn92_
) LIMIT 20000000
---
SELECT * FROM (
SELECT clmn0_, clmn5_, AVG(clmn100001_) AS clmn100000_ FROM (
SELECT clmn0_, clmn5_, ABS(clmn28_) AS clmn100001_ FROM (
SELECT t0.pfx_z AS clmn28_, t0.pitch_type AS clmn0_, t0.player_name AS clmn5_ FROM `dummy.hoge.fuga` t0
)
) GROUP BY clmn0_, clmn5_
) LIMIT 20000000
---
SELECT * FROM (
SELECT clmn92_, AVG(clmn54_) AS clmn100000_ FROM (
SELECT * FROM (
SELECT t0.batter_name AS clmn92_, t0.launch_angle AS clmn54_ FROM `dummy.hoge.fuga` t0
) WHERE (NOT (clmn92_ IS NULL))
) GROUP BY clmn92_
) LIMIT 20000000
---
SELECT * FROM (
SELECT clmn76_, clmn92_, clmn8_, clmn1_, clmn77_, SUM(clmn52_) AS clmn100000_ FROM (
SELECT * FROM (
SELECT t0.at_bat_number AS clmn76_, t0.batter_name AS clmn92_, t0.events AS clmn8_, t0.game_date AS clmn1_, t0.hit_distance_sc AS clmn52_, t0.pitch_number AS clmn77_ FROM `dummy.hoge.fuga` t0
) WHERE ((NOT (clmn8_ IS NULL)) AND (NOT (clmn92_ IS NULL)))
) GROUP BY clmn76_, clmn92_, clmn8_, clmn1_, clmn77_
) LIMIT 20000000
---

このログからは、こんなことが分かりました。

Data Studioはメモリ上にデータをガサッと抜いた上で、グラフ毎に加工処理が走っている訳で無く、グラフ単位に最適化されたクエリが独立して走っている
作られているクエリ自体は、特別理不尽とも思わない
クエリは集計された結果をMAX2000万件リターンする。つまり、2000万件以内に収まるクエリになるべく条件を絞らないと、単にクエリでなめるデータ量だけがかさんでしまう、、

クエリでなめるデータ量を見ると、カラム数が異なるのでブレはありますが、1回の実行で大体5〜8MiBを食うぐらいと、こぢんまりしたボリュームでした。シンプルにクエリが全て8MiBをなめるにしても80MiBなので、1TB/月の上限に行くには大体12000回クエリを走らせないと行かないぐらいです。

まとめ

Data Studio上のダッシュボードは、現在はこんな具合ですが、ここをよりリッチにしようとすると、実行されるクエリがかさんでいくぞ、と言うことが分かりました。

データとしては1日2MBずつ増えているので、その分Data Studio経由でのクエリも重くなっていきますが、、ただ、完全プライベート（非公開）かつ、1日1回見れればOKと言うダッシュボードなので、Data Studio単体で理不尽な程データ量を食うことはほぼ無さそうです。

大丈夫そうだ！スッキリ！と言う訳で、しばらくは快眠が出来そうです。