クエリプランとタイムライン

BigQuery のクエリジョブには、診断で使用できるクエリプランとタイミング情報が埋め込まれます。これは、他のデータベースや分析システムの EXPLAIN ステートメントなどで提供される情報に似ています。この情報は、jobs.get などのメソッドで API レスポンスから取得できます。

長期実行されるクエリの場合、BigQuery はこの統計情報を定期的に更新します。この更新は、ジョブステータスのポーリング間隔とは関係なく実行されますが、通常、更新間隔が 30 秒よりも短くなることはありません。また、実行リソースを使用しないクエリジョブ（ドライランリクエストや、キャッシュに保存された結果が提供される場合など）の場合、追加の診断情報はありませんが、他の統計情報が提供される可能性があります。

背景

BigQuery がクエリジョブを実行すると、宣言型の SQL ステートメントを実行グラフに変換し、一連のクエリステージに分割します。クエリステージは、より細かい実行ステップから構成されます。BigQuery は、高度に分散された並列アーキテクチャを利用して、このようなクエリを実行します。ステージは、多くのワーカーを同時に実行できる作業単位をモデル化しています。ステージは、高速分散シャッフルアーキテクチャを使用して相互に通信します。

クエリプランでは、並列処理に関する情報を伝えるため、作業単位とワーカーという用語を使用しています。BigQuery では、コンピューティング、メモリ、I/O リソースなど、クエリの実行に必要な複数のファセットを抽象的に表すために「スロット」という用語を使用する場合があります。ジョブ統計の概要では、この抽象的な単位に基づき、個々のクエリの totalSlotMs を表示しています。

クエリ実行のアーキテクチャでもう 1 つの重要な特性は動的である点です。つまり、クエリの実行中にクエリプランが変更される可能性があります。クエリの実行中に追加されるステージは、主にクエリワーカー全体にわたるデータ分散を向上させるために使用されます。通常、このようなクエリプランでは、ステージにパーティション再設定ステージというラベルが付きます。

クエリプランに加えて、クエリジョブは実行のタイムラインを公開します。これにより、クエリワーカー内で完了している作業単位、保留中の作業単位、アクティブな作業単位の数を確認できます。1 つのクエリの複数のステージにアクティブなワーカーが同時に存在する場合があるため、タイムラインはクエリ全体の進行状況を把握する際に有用です。

Google Cloud コンソールで情報を表示する

Google Cloud コンソールで [実行の詳細] ボタン（[結果] ボタンの近く）をクリックすると、完了したクエリのクエリプランの詳細を確認できます。

クエリプランの情報

API レスポンス内では、クエリプランはクエリステージのリストとして表されます。リスト内の各アイテムはステージごとの概要、詳細なステップ情報、ステージタイミングの分類を表します。API レスポンスに含まれていても、Google Cloud コンソールに表示されない情報もあります。

ステージの概要

各ステージには、次のような概要フィールドがあります。

API フィールド	説明
`id`	ステージの一意の数値 ID。
`name`	ステージの簡単な概要名。ステージ内の `steps` に、実行ステップの詳細が含まれます。
`status`	ステージの実行ステータス。PENDING、RUNNING、COMPLETE、FAILED、CANCELLED のいずれかになります。
`inputStages`	ステージの依存関係グラフを形成する ID のリスト。たとえば、JOIN ステージの場合、JOIN 関係の左右のデータを準備するために、2 つの依存ステージが必要になります。
`startMs`	エポックミリ秒単位のタイムスタンプ。ステージ内で最初のワーカーが開始した時間を表します。
`endMs`	最後のワーカーが実行を完了した時間を表すタイムスタンプ（エポックミリ秒単位）。
`steps`	ステージ内の実行ステップの詳細なリスト。詳細については、次のセクションをご覧ください。
`recordsRead`	ステージワーカー全体でのステージの入力サイズ（レコード数）。
`recordsWritten`	ステージワーカー全体でのステージの出力サイズ（レコード数）。
`parallelInputs`	ステージで同時に読み込み可能な作業単位の数。ステージとクエリに応じて、テーブルの列セグメントの数や、中間シャッフル内のパーティションの数を表す場合があります。
`completedParallelInputs`	ステージ内で完了した作業単位の数。クエリによっては、ステージ内のすべての入力が完了していなくても、ステージが完了する場合があります。
`shuffleOutputBytes`	クエリステージ内のワーカー全体で発生した書き込みの合計バイト数を表します。
`shuffleOutputBytesSpilled`	ステージ間で重要なデータを送信するクエリでは、ディスクベースの送信へのフォールバックが必要になる場合があります。オーバーフローされたバイトの統計情報は、ディスクにオーバーフローしたデータの量を表します。最適化アルゴリズムに依存するため、任意のクエリに対して決定論的に確定されるものではありません。

ステージごとのステップ情報

ステップとは、ステージ内でワーカーごとに実行される細分化されたオペレーションで、オペレーションの順序付きリストとして表されます。ステップは、オペレーションの詳細情報で分類されます。クエリプランのオペレーションには、次のようなカテゴリがあります。

ステップ	説明
READ	入力テーブルまたは中間シャッフルからの 1 つ以上の列の読み取り。読み取られた最初の 16 列のみがステップの詳細で返されます。
WRITE	出力テーブルまたは中間結果への 1 つ以上の列の書き込み。ステージからの HASH パーティショニングされた出力の場合は、パーティションキーとして使用される列も含まれます。
COMPUTE	式の評価や SQL 関数などのオペレーション。
FILTER	WHERE 句、OMIT IF 句、HAVING 句を実装する演算子。
SORT	並べ替えオペレーション。列のキーと並べ替えの方向が含まれます。
AGGREGATE	GROUP BY または COUNT などの集計オペレーション。
LIMIT	LIMIT 句を実装する演算子。
JOIN	JOIN オペレーション。結合の種類や、使用される列が含まれます。
ANALYTIC_FUNCTION	ウィンドウ関数（「分析関数」とも呼ばれます）の呼び出し。
USER_DEFINED_FUNCTION	ユーザー定義関数の呼び出し。

ステージごとのタイミング分類

クエリステージは、ステージのタイミング分類も、相対的形式と絶対的形式の両方で提供します。実行の各ステージは、1 つ以上の独立したワーカーによって実行された作業を表すため、情報は平均時間と最長時間の両方で提供されます。これは、特定の分類について、ステージ内のすべてのワーカーの平均的なパフォーマンスと、ロングテールの最も遅いワーカーのパフォーマンスを表します。平均時間と最長時間は、絶対的な形式と相対的な形式で表現されます。比率ベースの統計については、セグメントのワーカーによって費やされた最長時間との比率でデータが提供されます。

Google Cloud コンソールでは、相対的な表現でステージのタイミングが表示されます。

ステージのタイミング情報は次のように表示されます。

相対的なタイミング	絶対的なタイミング	割合の分子
`waitRatioAvg`	`waitMsAvg`	平均的なワーカーがスケジュール完了の待機に費やした時間。
`waitRatioMax`	`waitMsMax`	最も遅いワーカーがスケジュール完了の待機に費やした時間。
`readRatioAvg`	`readMsAvg`	平均的なワーカーが入力データの読み取りに費やした時間。
`readRatioMax`	`readMsMax`	最も遅いワーカーが入力データの読み取りに費やした時間。
`computeRatioAvg`	`computeMsAvg`	平均的なワーカーが CPU のバインドに費やした時間。
`computeRatioMax`	`computeMsMax`	最も遅いワーカーが CPU のバインドに費やした時間。
`writeRatioAvg`	`writeMsAvg`	平均的なワーカーが出力データの書き込みに費やした時間。
`writeRatioMax`	`writeMsMax`	最も遅いワーカーが出力データの書き込みに費やした時間。

連携クエリの説明

連携クエリでは、EXTERNAL_QUERY 関数を使用してクエリステートメントを外部データソースに送信できます。連携クエリには、SQL プッシュダウンと呼ばれる最適化手法が適用されます。クエリプランには、外部データソースにプッシュダウンされたオペレーション（存在する場合）が表示されます。たとえば、次のクエリを実行するとします。

SELECT id, name
FROM EXTERNAL_QUERY("<connection>", "SELECT * FROM company")
WHERE country_code IN ('ee', 'hu') AND name like '%TV%'

クエリプランには、次のステージのステップが表示されます。

$1:id, $2:name, $3:country_code
FROM table_for_external_query_$_0(
  SELECT id, name, country_code
  FROM (
    /*native_query*/
    SELECT * FROM company
  )
  WHERE in(country_code, 'ee', 'hu')
)
WHERE and(in($3, 'ee', 'hu'), like($2, '%TV%'))
$1, $2
TO __stage00_output

このプランでは、table_for_external_query_$_0(...) は EXTERNAL_QUERY 関数を表します。かっこ内には、外部データソースが実行するクエリが示されます。これに基づき、次のことがわかります。

外部データソースは、選択された 3 つの列のみを返します。
外部データソースは、country_code が 'ee' または 'hu' の行のみを返します。
LIKE 演算子はプッシュダウンされず、BigQuery によって評価されます。

比較として、プッシュダウンが行われない場合、クエリプランには次のステージのステップが表示されます。

$1:id, $2:name, $3:country_code
FROM table_for_external_query_$_0(
  SELECT id, name, description, country_code, primary_address, secondary address
  FROM (
    /*native_query*/
    SELECT * FROM company
  )
)
WHERE and(in($3, 'ee', 'hu'), like($2, '%TV%'))
$1, $2
TO __stage00_output

この場合、外部データソースは company テーブルのすべての列とすべての行を返し、BigQuery によってフィルタが実行されます。

タイムラインメタデータ

クエリタイムラインは、特定の時点での進捗状況を表し、クエリ全体の進捗状況を示すスナップショットとなります。タイムラインは一連のサンプルとして表され、以下の情報を提供します。

項目	説明
`elapsedMs`	クエリを開始してからの経過時間（ミリ秒）。
`totalSlotMs`	クエリで使用されるスロットの合計処理時間（ミリ秒）。
`pendingUnits`	スケジュールされ、実行待ちの作業単位の合計数。
`activeUnits`	ワーカーが処理しているアクティブな作業単位の合計数。
`completedUnits`	このクエリの実行中に完了した作業単位の合計数。

クエリの例

次のクエリは、シェイクスピア一般公開データセット内の行数をカウントし、「hamlet」を参照する行に結果を制限する 2 番目の条件付きカウントを持っています。

SELECT
  COUNT(1) as rowcount,
  COUNTIF(corpus = 'hamlet') as rowcount_hamlet
FROM `publicdata.samples.shakespeare`

[実行の詳細] をクリックして、クエリプランを表示します。

hamlet クエリプラン。

カラーインジケーターは、すべてのステージのすべてのステップの相対的なタイミングを表します。

実行ステージのステップの詳細を表示するには、をクリックしてステージの詳細を展開します。

hamlet クエリプランのステップの詳細。

この例では、ステージ 01 のワーカーがステージ 00 の完了を待っていた時間がセグメントの最長時間になっています。これは、ステージ 01 がステージ 00 の入力に依存しており、最初のステージがその出力を中間シャッフルに書き込むまで開始できなかったためです。

エラー報告

実行中にクエリジョブが失敗することもあります。プラン情報は定期的に更新されるため、実行グラフ内で失敗した場所を確認できます。Google Cloud コンソールでは、ステージ名の横にチェックマークや感嘆符を付けて、ステージの成功と失敗を表しています。

エラーの解釈と解決方法の詳細については、トラブルシューティングガイドをご覧ください。

API サンプルの表現

クエリプラン情報は、ジョブのレスポンス情報に埋め込まれており、jobs.get を呼び出すことで取得できます。たとえば、サンプルの hamlet クエリを返すジョブの JSON レスポンスの次の抜粋では、クエリプランとタイムライン情報の両方を示しています。

"statistics": {
  "creationTime": "1576544129234",
  "startTime": "1576544129348",
  "endTime": "1576544129681",
  "totalBytesProcessed": "2464625",
  "query": {
    "queryPlan": [
      {
        "name": "S00: Input",
        "id": "0",
        "startMs": "1576544129436",
        "endMs": "1576544129465",
        "waitRatioAvg": 0.04,
        "waitMsAvg": "1",
        "waitRatioMax": 0.04,
        "waitMsMax": "1",
        "readRatioAvg": 0.32,
        "readMsAvg": "8",
        "readRatioMax": 0.32,
        "readMsMax": "8",
        "computeRatioAvg": 1,
        "computeMsAvg": "25",
        "computeRatioMax": 1,
        "computeMsMax": "25",
        "writeRatioAvg": 0.08,
        "writeMsAvg": "2",
        "writeRatioMax": 0.08,
        "writeMsMax": "2",
        "shuffleOutputBytes": "18",
        "shuffleOutputBytesSpilled": "0",
        "recordsRead": "164656",
        "recordsWritten": "1",
        "parallelInputs": "1",
        "completedParallelInputs": "1",
        "status": "COMPLETE",
        "steps": [
          {
            "kind": "READ",
            "substeps": [
              "$1:corpus",
              "FROM publicdata.samples.shakespeare"
            ]
          },
          {
            "kind": "AGGREGATE",
            "substeps": [
              "$20 := COUNT($30)",
              "$21 := COUNTIF($31)"
            ]
          },
          {
            "kind": "COMPUTE",
            "substeps": [
              "$30 := 1",
              "$31 := equal($1, 'hamlet')"
            ]
          },
          {
            "kind": "WRITE",
            "substeps": [
              "$20, $21",
              "TO __stage00_output"
            ]
          }
        ]
      },
      {
        "name": "S01: Output",
        "id": "1",
        "startMs": "1576544129465",
        "endMs": "1576544129480",
        "inputStages": [
          "0"
        ],
        "waitRatioAvg": 0.44,
        "waitMsAvg": "11",
        "waitRatioMax": 0.44,
        "waitMsMax": "11",
        "readRatioAvg": 0,
        "readMsAvg": "0",
        "readRatioMax": 0,
        "readMsMax": "0",
        "computeRatioAvg": 0.2,
        "computeMsAvg": "5",
        "computeRatioMax": 0.2,
        "computeMsMax": "5",
        "writeRatioAvg": 0.16,
        "writeMsAvg": "4",
        "writeRatioMax": 0.16,
        "writeMsMax": "4",
        "shuffleOutputBytes": "17",
        "shuffleOutputBytesSpilled": "0",
        "recordsRead": "1",
        "recordsWritten": "1",
        "parallelInputs": "1",
        "completedParallelInputs": "1",
        "status": "COMPLETE",
        "steps": [
          {
            "kind": "READ",
            "substeps": [
              "$20, $21",
              "FROM __stage00_output"
            ]
          },
          {
            "kind": "AGGREGATE",
            "substeps": [
              "$10 := SUM_OF_COUNTS($20)",
              "$11 := SUM_OF_COUNTS($21)"
            ]
          },
          {
            "kind": "WRITE",
            "substeps": [
              "$10, $11",
              "TO __stage01_output"
            ]
          }
        ]
      }
    ],
    "estimatedBytesProcessed": "2464625",
    "timeline": [
      {
        "elapsedMs": "304",
        "totalSlotMs": "50",
        "pendingUnits": "0",
        "completedUnits": "2"
      }
    ],
    "totalPartitionsProcessed": "0",
    "totalBytesProcessed": "2464625",
    "totalBytesBilled": "10485760",
    "billingTier": 1,
    "totalSlotMs": "50",
    "cacheHit": false,
    "referencedTables": [
      {
        "projectId": "publicdata",
        "datasetId": "samples",
        "tableId": "shakespeare"
      }
    ],
    "statementType": "SELECT"
  },
  "totalSlotMs": "50"
},

実行情報を使用する

BigQuery のクエリプランでは、サービスによるクエリの実行方法に関する情報が提供されますが、サービスの管理特性のため、この情報をそのまま利用できるとは限りません。多くの最適化はサービスを使用して自動で行われます。これは、チューニング、プロビジョニング、モニタリングに、専門知識が豊富なスタッフが必要となる環境とは異なる場合があります。

クエリの実行とパフォーマンスを向上させる具体的な手段については、ベストプラクティスのドキュメントをご覧ください。クエリプランとタイムラインの統計情報は、特定のステージでのリソースの使用状況の把握に役立ちます。たとえば、JOIN ステージで入力行よりも出力行が多い場合は、クエリの前にフィルタリングが必要かもしれません。

また、タイムライン情報を見ると、特定のクエリだけが遅いのか、同じリソースを利用する別のクエリとの競合で遅いのかを識別できます。クエリの全期間を通じてアクティブな作業単位の数が限定されていても、キューに入る作業単位の数が多い場合は、同時クエリの数を減らすことで、特定のクエリの全体的な実行時間を短縮できる可能性があります。