この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部、池田です。
BigQueryを使う上で気になるのは料金ですが、
その大きな要素となる スロット
の使われ方をPandasで簡単に可視化してみました。
今回は実行環境として Google Colaboratory
(以下Colab)を使いました。
余談(他の方法について)
現在プレビューの機能ですが、予約スロットを使っていると、以下でより簡単にグラフとして確認ができるようです。
【 リアルタイム モニタリングに BigQuery 管理リソースグラフを使用する 】
また、以下のサービスでもBigQueryに接続してノートブックを使うことが可能なので、
同じようなことができそうです。
【 AI Platform Notebooks を使って、さくっとノートブック環境を立ち上げて BigQuery にアクセスしてみた 】
今回はGoogle ドライブに書き出したファイルをColabで読み込んでいますが、
Colabから直接BigQueryに接続することで実現もできそうです。
【 BigQuery Storage API を使用して BigQuery データを pandas にダウンロードする 】
【 ColaboratoryでBigQueryにアクセスする3つの方法 】
今回、Google ドライブ上のファイルとColabの組み合わせにしている理由は…
- BigQuery内の情報をファイルにする必要があって、ちょっと確認程度に可視化したいイメージ
- コンソールからクエリ結果を保存する時には、Google ドライブが便利そう
- ローカルやクリップボード:最大で16000行
- Google ドライブ:最大で1GB
- 予約スロットでない前提
- (ファイル保存までしてしまえば)料金を気にしなくてよい
という辺りです。
BigQuery側作業
テストクエリの実行
前回 ブログ にした
ラベル を付けておきたいので、
bqコマンド の
query
から検証用のクエリを実行しておきます。
↓こんな感じ。
クリックでコマンドを表示する/折りたたむ
nohup \
bq --location=US query \
--use_legacy_sql=false \
--use_cache=false \
--label "title:devio" \
--label "case:interactive1" \
'SELECT
a.*, b.*, c.*, d.*
FROM
`bigquery-public-data.samples.gsod` a,
`bigquery-public-data.samples.gsod` b,
`bigquery-public-data.samples.gsod` c,
`bigquery-public-data.samples.gsod` d
WHERE
a.station_number = b.station_number AND a.wban_number = b.wban_number AND a.year = b.year AND a.month = b.month AND a.day = b.day
AND a.station_number = c.station_number AND a.wban_number = c.wban_number AND a.year = c.year AND a.month = c.month AND a.day = c.day
AND a.station_number = d.station_number AND a.wban_number = d.wban_number AND a.year = d.year AND a.month = d.month AND a.day = d.day;'>> ~/devio/i1.log 2>&1 &
nohup \
bq --location=US query \
--use_legacy_sql=false \
--use_cache=false \
--batch \
--label "title:devio" \
--label "case:batch1" \
'SELECT
a.*, b.*, c.*, d.*
FROM
`bigquery-public-data.samples.gsod` a,
`bigquery-public-data.samples.gsod` b,
`bigquery-public-data.samples.gsod` c,
`bigquery-public-data.samples.gsod` d
WHERE
a.station_number = b.station_number AND a.wban_number = b.wban_number AND a.year = b.year AND a.month = b.month AND a.day = b.day
AND a.station_number = c.station_number AND a.wban_number = c.wban_number AND a.year = c.year AND a.month = c.month AND a.day = c.day
AND a.station_number = d.station_number AND a.wban_number = d.wban_number AND a.year = d.year AND a.month = d.month AND a.day = d.day;'>> ~/devio/b1.log 2>&1 &
nohup \
bq --location=US query \
--use_legacy_sql=false \
--use_cache=false \
--label "title:devio" \
--label "case:interactive2" \
'SELECT
a.*, b.*, c.*, d.*
FROM
`bigquery-public-data.samples.gsod` a,
`bigquery-public-data.samples.gsod` b,
`bigquery-public-data.samples.gsod` c,
`bigquery-public-data.samples.gsod` d
WHERE
a.station_number = b.station_number AND a.wban_number = b.wban_number AND a.year = b.year AND a.month = b.month AND a.day = b.day
AND a.station_number = c.station_number AND a.wban_number = c.wban_number AND a.year = c.year AND a.month = c.month AND a.day = c.day
AND a.station_number = d.station_number AND a.wban_number = d.wban_number AND a.year = d.year AND a.month = d.month AND a.day = d.day;'>> ~/devio/i2.log 2>&1 &
nohup \
bq --location=US query \
--use_legacy_sql=false \
--use_cache=false \
--batch \
--label "title:devio" \
--label "case:batch2" \
'SELECT
a.*, b.*, c.*, d.*
FROM
`bigquery-public-data.samples.gsod` a,
`bigquery-public-data.samples.gsod` b,
`bigquery-public-data.samples.gsod` c,
`bigquery-public-data.samples.gsod` d
WHERE
a.station_number = b.station_number AND a.wban_number = b.wban_number AND a.year = b.year AND a.month = b.month AND a.day = b.day
AND a.station_number = c.station_number AND a.wban_number = c.wban_number AND a.year = c.year AND a.month = c.month AND a.day = c.day
AND a.station_number = d.station_number AND a.wban_number = d.wban_number AND a.year = d.year AND a.month = d.month AND a.day = d.day;'>> ~/devio/b2.log 2>&1 &
BigQueryの一般公開データセット
の気象情報の gsod
サンプルテーブルを適当に結合して(もっと実務的なクエリが良かったのですが…)、
キャッシュ無しで通常1分弱ほどかかるように調整しました。
そのクエリを インタラクティブ(オンデマンド)クエリとバッチクエリ
として、それぞれ2本ずつ nohup
でほぼ同時に実行しています。
テストクエリの結果(ジョブ情報)
↓のクエリで INFORMATION_SCHEMA
から
ジョブの情報
( JOBS_BY_PROJECT
ビュー)を参考までに簡単に確認します。
SELECT
labels[SAFE_OFFSET(0)].value AS title, labels[SAFE_OFFSET(1)].value AS `case`,
ROUND(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) / 1000, 1) AS time_sec,
ROUND(total_slot_ms / TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS avg_slots,
total_slot_ms, total_bytes_billed, cache_hit,
(error_result IS NOT NULL) AS has_error,
creation_time, job_id, priority, start_time, end_time,
state, total_bytes_processed,
REGEXP_REPLACE(LEFT(query, 100), "\r?\n", " ") AS query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
creation_time >= "2021-04-09 09:00:00" -- UTC, partition
AND labels[SAFE_OFFSET(0)].value = "devio"
ORDER BY creation_time DESC;
↓結果
+-------+--------------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+------------------------------------------------------------------------------------------------------+
| title | case | time_sec | avg_slots | total_slot_ms | total_bytes_billed | cache_hit | has_error | creation_time | job_id | priority | start_time | end_time | state | total_bytes_processed | query |
+-------+--------------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+------------------------------------------------------------------------------------------------------+
| devio | batch1 | 56.2 | 162.0 | 9111267 | 17291018240 | false | false | 2021-04-09 09:34:35 | foo | BATCH | 2021-04-09 09:36:11 | 2021-04-09 09:37:07 | DONE | 17290009238 | SELECT a.*, b.*, c.*, d.* FROM `bigquery-public-data.samples.gsod` a, `bigquery-publ |
| devio | interactive2 | 52.0 | 167.0 | 8675341 | 17291018240 | false | false | 2021-04-09 09:34:35 | bar | INTERACTIVE | 2021-04-09 09:34:35 | 2021-04-09 09:35:27 | DONE | 17290009238 | SELECT a.*, b.*, c.*, d.* FROM `bigquery-public-data.samples.gsod` a, `bigquery-publ |
| devio | batch2 | 51.1 | 183.0 | 9363212 | 17291018240 | false | false | 2021-04-09 09:34:35 | hoge | BATCH | 2021-04-09 09:35:36 | 2021-04-09 09:36:27 | DONE | 17290009238 | SELECT a.*, b.*, c.*, d.* FROM `bigquery-public-data.samples.gsod` a, `bigquery-publ |
| devio | interactive1 | 57.7 | 152.0 | 8778882 | 17291018240 | false | false | 2021-04-09 09:34:35 | fuga | INTERACTIVE | 2021-04-09 09:34:35 | 2021-04-09 09:35:33 | DONE | 17290009238 | SELECT a.*, b.*, c.*, d.* FROM `bigquery-public-data.samples.gsod` a, `bigquery-publ |
+-------+--------------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+------------------------------------------------------------------------------------------------------+
所要時間 time_sec
は4本とも1分弱で、
ジョブの作成時間 creation_time
が同じなのに、
start_time
や end_time
にばらつきがありますね。
ジョブのタイムラインの取得と加工
前節では、ジョブごとの情報でざっくり見ましたが、
各ジョブの1秒ごとの挙動を JOBS_TIMELINE_BY_PROJECT
ビューから取得して、
可視化しやすいように加工します。(加工は別にPnadas側でやっても良いです。)
【 INFORMATION_SCHEMA を使用したタイムスライスごとのジョブのメタデータの取得 】
↓こんな感じで取得&加工。
SELECT *,
ROUND(SUM(period_slots) OVER (PARTITION BY job_id) / time_sec) AS avg_slots -- 平均使用スロット
FROM (SELECT job.title, job.case, tl.job_id,
TIMESTAMP_DIFF(tl.period_start, MIN(tl.period_start) OVER (PARTITION BY job.title), SECOND) AS elapsed_sec, -- 初めのジョブが開始してからの経過時間
tl.period_slot_ms / 1000 AS period_slots, -- この期間の使用スロット
tl.state,
TIMESTAMP_DIFF(MAX(tl.job_end_time) OVER (PARTITION BY tl.job_id),
MIN(tl.job_start_time) OVER (PARTITION BY tl.job_id), MILLISECOND) / 1000 AS time_sec, -- 合計処理時間
ROUND(MAX(tl.period_slot_ms) OVER (PARTITION BY tl.job_id) / 1000) AS max_slots -- 最大使用スロット
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT tl,
(SELECT DISTINCT labels[SAFE_OFFSET(0)].value AS title, labels[SAFE_OFFSET(1)].value AS `case`, job_id
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE labels[SAFE_OFFSET(0)].value = 'devio') job
WHERE tl.job_id = job.job_id)
ORDER BY title, `case`, elapsed_sec;
時刻だと分かりにくいので、経過時間として加工しています。
JOBS_TIMELINE_BY_PROJECT
ビューはラベル情報を持たないので、
前述の JOBS_BY_PROJECT
ビューと結合しています。
(ラベルを使っていない場合は、結合せずに job_id
をキーとして見ていけば良いはずです。)
↓結果(冒頭一部)
+-------+--------+--------------------------------------------+-------------+--------------+---------+----------+-----------+-----------+
| title | case | job_id | elapsed_sec | period_slots | state | time_sec | max_slots | avg_slots |
+-------+--------+--------------------------------------------+-------------+--------------+---------+----------+-----------+-----------+
| devio | batch1 | foo | 0 | 0.0 | PENDING | 56.211 | 935.0 | 162.0 |
| devio | batch1 | foo | 1 | 0.0 | PENDING | 56.211 | 935.0 | 162.0 |
| devio | batch1 | foo | 2 | 0.0 | PENDING | 56.211 | 935.0 | 162.0 |
| devio | batch1 | foo | 3 | 0.0 | PENDING | 56.211 | 935.0 | 162.0 |
| devio | batch1 | foo | 4 | 0.0 | PENDING | 56.211 | 935.0 | 162.0 |
| devio | batch1 | foo | 5 | 0.0 | PENDING | 56.211 | 935.0 | 162.0 |
| devio | batch1 | foo | 6 | 0.0 | PENDING | 56.211 | 935.0 | 162.0 |
| devio | batch1 | foo | 7 | 0.0 | PENDING | 56.211 | 935.0 | 162.0 |
| devio | batch1 | foo | 8 | 0.0 | PENDING | 56.211 | 935.0 | 162.0 |
| devio | batch1 | foo | 9 | 0.0 | PENDING | 56.211 | 935.0 | 162.0 |
…
経過時間 elapsed_sec
時点での、スロット使用数 period_slots
が取得できそうですね。
(batch1は処理開始が遅いので、冒頭はスロットが0で続く。)
このクエリの結果をGoogle ドライブに保存します。
マイドライブ直下にフォルダと共に出力されました。
この結果をColabで参照していきます。
Colab側作業
実装
コードの記入エリアを追加しながら書いていきます。
ノートブックに Google ドライブをマウント 。
[1]
from google.colab import drive
drive.mount('/content/drive')
(出力されたURLから取得したコードを入力してやる必要があります。)
前章で出力したファイルを読み込む。
[2]
import pandas as pd
file_dir = "{Google ドライブ上のパス}"
file_path = file_dir + "{Google ドライブ上のファイル名}.csv"
df = pd.read_csv(file_path,
header=0, sep=",", encoding="utf-8")
df.info()
case
列ごとに折れ線グラフとして、
スロット使用数 period_slots
と平均のスロット使用数 avg_slots
を出力。
[3]
from matplotlib import pyplot as plt
cases = df["case"].unique()
print("cases:", cases)
plt.style.use("ggplot")
for c in cases:
plt.figure()
df[df["case"] == c].plot(kind="line", title=c,
x="elapsed_sec",
y=["period_slots", "avg_slots"])
plt.savefig(file_dir + "{}_line.png".format(c))
plt.close('all')
ピボットして、積み上げの折れ線グラフ(面グラフ)として出力。
[4]
pivot = df.pivot(index="elapsed_sec", columns="case")["period_slots"]
pivot.info()
titles = df["title"].unique()
plt.figure()
pivot.plot.area(title=titles[0], linewidth=0)
plt.savefig(file_dir + "{}_lines.png".format(titles[0]))
plt.close("all")
Google ドライブのマウントを解除。
[5]
drive.flush_and_unmount()
↑こんな感じで実装すると、各グラフがGoogle ドライブに出力されます。(結果画像は次章。)
↓ちなみに、plt.close("all")
しなければ、Colab上で表示もできます。
結果
前章までの作業で可視化してみた結果がこんな感じです。
内容が同じクエリなので、どれも同じような波形(スロットの使われ方)ですね。
積み上げてみると、今回の検証では、
「インタラクティブクエリが並列で動いた」あとに「バッチクエリが逐次で動いた」
というような挙動であったことが分かりますね。
(今回はサンプルやバリエーションが少ないのと、考察は本題ではないのでこれくらい。)
おわりに
予約スロットで フェア スケジューリング
の動きなどを可視化したら楽しそうですね。
やっぱりColab便利です。
関連情報/参考にさせていただいたページ
- BigQuery
- その他
- 他の方法