BigQuey の SQL チューニングや Slot 利用状況確認に BQ Visualizer を使ったら便利でした。

#Google BigQuery

#Google Cloud (GCP)

みかみ

2021.04.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

文字だけでつらつら書かれている情報よりも、図やグラフで視覚化された情報の方が理解しやすいものではないかと思います。

BQ Visualizer を使うと、BigQuery のクエリプランや Slot 使用状況などの SQL 実行結果を簡単に可視化することができます。

OSS なので GitHub からソースを取得することもできますが、ブラウザでアクセスするだけですぐに使えるサイトもあるので、BigQuery の SQL 実行結果を直感的にわかりやすく確認できて便利です。

以下のエントリでも BQ Visualizer をご紹介させていただいてます（タイムリーなネタかぶり、すみません＞＜w

それだけ使いやすい BQ Visualizer なので、是非一度お試しください！

なお、本エントリでは、JSON 形式のクエリプランを取得してから、GCP アカウント認証（BQ Visualizer にログイン）なしで、BQ Visualizer で SQL 実行結果を可視化してみます。また、SQL の Slot 利用状況がどんなふうに可視化されるかも確認してみます。

やりたいこと

BigQuery の実行結果を BQ Visuzlizer で可視化したい
BQ Visuzlizer ではどんな内容がどういうふうに可視化されるのか確認したい

BigQuery 管理コンソールから見れるクエリプラン

BigQuery では、GCP 管理コンソールからでも、クエリプランを視覚的に確認することができます。

SQL を実行してクエリプランを確認してみます。実行するのは、一般公開データセットの Github のコミット情報テーブルから件名のランキングを取得する SQL で、過去の Cloud OnAir のセッションからいただいてきました。

SELECT
    subject AS subject,
    COUNT(*) AS num_duplicates
FROM
    `bigquery-public-data.github_repos.commits`
GROUP BY
    subject
ORDER BY
    num_duplicates DESC
LIMIT 100
;

BigQuery 管理コンソールから、クエリエディタに SQL を入力して実行します。

「実行の詳細」タブをクリックすると、クエリプランを表示できます。

どんな処理ステップで、どのくらいのレコード数が処理され、どのくらいの時間がかかったのか、視覚的に確認することができます。

文字だけで表示される他のデータベースの実行計画などよりたいぶわかりやすいですが、BQ Visualizer を使うとより直感的にわかりやすくビジュアライズできます。

BQ Visualiser のビジュアライズ結果を確認

さて本題です。 BQ Visualizer にアクセスします。

BQ Visualiser

画面上部右側の「login」から GCP アカウントで OAuth2 認証すると、プロジェクトを選択してジョブリストから SQL を選択したり、ジョブIDを入力して実行済み SQL の情報を取得することができます。

詳細は以下のブログをご参照ください。

クエリプランを可視化するBigQuery Visualiserを使ってみる｜ DevelopersIO

「BQ Visualizer から GCP プロジェクトにアクセスできちゃうのはセキュリティ的にちょっと心配」という場合には、あらかじめ取得しておいた JSON フォーマットのクエリプランをアップロードすれば、GCP アカウント認証不要で同じ内容を確認することができます。

先ほど実行した SQL のクエリプランを JSON フォーマットで取得し、BQ Visualizer にアップロードして結果を確認してみます。

Step1：SQL のジョブ ID を確認

クエリプランを取得するためには対象の SQL のジョブ ID を指定する必要があります。

ジョブ ID は、BigQuery 管理コンソール下部「クエリ履歴」タブの、実行済み SQL リストから確認することができます。

リスト内の対象クエリをクリックすると、SQL クエリなどの情報と一緒にジョブ ID が表示されています。

もし、実行してからだいぶ日が経ってしまっているなどで「クエリ履歴」に表示されていない SQL を確認したい場合は、INFORMATION_SCHEMA の JOBS_BY_PROJECT ビューからも確認できます。

INFORMATION_SCHEMA を使用したジョブメタデータの取得｜ BigQuery ドキュメント

Step2：クエリプランを JSON ファイルで取得

JSON 形式のクエリプランは、以下のコマンドで取得できます。

bq --format=prettyjson show -j [ジョブID] > [出力ファイル名]

Cloud Shell から、前のセクションで確認したジョブ ID を指定したコマンドを実行し、クエリプランを JSON ファイルに出力します。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq --format=prettyjson show -j bquxjob_3418d874_178b1523f1c > select_github_repos.json
mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ ls -l | grep select_github_repos.json
-rw-r--r-- 1 mikami_yuki mikami_yuki    13644 Apr  8 12:35 select_github_repos.json

JSON ファイルには、実行した SQL の詳細情報が出力されています。

{
  "configuration": {
    "jobType": "QUERY",
    "query": {
      "destinationTable": {
        "datasetId": "_d2ad45f43c730e7b464e74355f85f966609b815f",
        "projectId": "cm-da-mikami-yuki-258308",
        "tableId": "anonae8ec2ce2c528ce98ca3bb52fb8521962bc669d8"
      },
      "priority": "INTERACTIVE",
      "query": "SELECT\n    subject AS subject,\n    COUNT(*) AS num_duplicates\nFROM\n    `bigquery-public-data.github_repos.commits`\nGROUP BY\n    subject\nORDER BY\n    num_duplicates DESC\nLIMIT 100\n;",
      "useLegacySql": false,
      "writeDisposition": "WRITE_TRUNCATE"
    }
  },
（省略）
  "statistics": {
    "creationTime": "1617882661061",
    "endTime": "1617882669673",
    "query": {
      "billingTier": 1,
      "cacheHit": false,
      "estimatedBytesProcessed": "11320100117",
      "queryPlan": [
        {
          "completedParallelInputs": "1474",
          "computeMsAvg": "474",
          "computeMsMax": "1262",
（省略）
          "writeMsAvg": "60",
          "writeMsMax": "239",
          "writeRatioAvg": 0.019193857965451054,
          "writeRatioMax": 0.0764555342290467
        },
（省略）
}

上記の JSON ファイルをローカル PC にダウンロードしました。

Step3：JSON ファイルを BQ Visualizer にアップロード

BQ Visualizer 画面「Upload from Computer」の「Select File to Upload」から、先ほどダウンロードした JSON ファイルを選択したら、「Upload」します。

すると、クエリプランが Tree 構造で可視化され、ステップごとの処理レコード数も表示されます！

画面下部の「Overview」欄は切り替え可能で、一番右の「Display Options」を選択後、プルダウンから「Hide Repartition」「Show Repartition」を切り替えることで、Tree に Repartition ステップを表示するかどうかを切り替えることができます。

Tree 表示でどの処理ステップでどのくらいの量のレコードが処理されて、次にどのステップに渡されて・・・といった処理内容がだいぶ分かりやすくなり、SQL チューニングする場合など、どこに問題があるのか解析しやすくなりました！

クエリプランの「Tree」の他にも、画面上部の「Timing」「Progress」タブから、それぞれステップごとの処理時間や処理データ量、スロット使用率などの情報をグラフで視覚的に確認できます。

BigQuery 管理コンソールの「実行の詳細」タブから確認できる内容が、BQ Visualizer の「Tree」と「Timing」タブで、より直感的にわかりやすく可視化されている感じです。

「Progress」タブでは、タイムラインに沿った Slot 使用量が確認できます。特に、予約した Slot 数に応じて料金を支払う定額プランで BigQuery をご利用の場合には、実行する SQL に対して十分な Slot 数を予約できているのか確認することができるので便利です。もしこの Slot 利用状況が常に上限に張り付いているような台形のグラフになっている場合には、Slot 数を増やせば SQL 実行速度の改善も期待できます。是非一度ご確認あれ！

また、画面下枠のタブでは、SQL 実行ステータスや実行した SQL、処理ステップ詳細情報や統計情報、実行条件などが確認できます。