クエリプランの説明

BigQuery では、完了したクエリの実行プラン(または単にクエリプラン)に関する診断情報が提供されます。この機能は、クエリプランの各ステップに関するメタデータを提供する点が、他のいくつかのクエリエンジンで使用できる EXPLAIN ステートメントと似ています。この情報を使用してクエリのパフォーマンスを改善できることがよくあります。

BigQuery では、クエリプランの説明を見るために EXPLAIN ステートメントを使用する必要はありません。実際、BigQuery には EXPLAIN ステートメントはありません。BigQuery では、クエリが完了した後でウェブ UI または API を使用してクエリプランを利用できます。

ウェブ UI を使用する場合は、[Results] ボタンの隣にある [Explanation] ボタンをクリックすることで、完了したクエリのクエリプランを表示できます。

BigQuery ウェブ UI の [説明] ボタンのスクリーンショット

BigQuery API を使用する場合は、jobs.get レスポンスの本文で完了したクエリのクエリプランを確認できます。クエリプランの情報はクエリ ジョブリソースに自動的に添付されて、7 日間保持されます。

クエリプランではクエリが一連のステージとして記述されています。各ステージは複数のステップで構成され、各ステップではデータソースからの読み取り、入力に対する一連の変換の実行、後のステージ(または最終結果)への出力の排出が行われます。

使用可能なメタデータ

クエリプランの各ステージでは次の情報を使用できます。

API の JSON 名 ウェブ UI 名 説明
id Stage x ステージの(プラン内で)一意な整数 ID。
recordsRead Input ステージによって読み取られた行(トップレベル レコード)の数。
recordsWritten Output ステージによって書き込まれた行(トップレベル レコード)の数。

相対的なタイミング

クエリプランの各ステージにおける次の割合もわかります。

API の JSON 名 ウェブ UI* 割合の分子 **
waitRatioAvg waitRatioAvg の暗黄色のバー 平均的なワーカーがスケジュール完了の待機に費やした時間。
waitRatioMax waitRatioMax の黄色のバー 最も遅いワーカーがスケジュール完了の待機に費やした時間。
readRatioAvg readRatioAvg の暗紫色のバー 平均的なワーカーが入力データの読み取りに費やした時間。
readRatioMax readRatioMax の紫色のバー 最も遅いワーカーが入力データの読み取りに費やした時間。
computeRatioAvg computeRatioAvg の暗いオレンジ色のバー 平均的なワーカーが CPU のバインドに費やした時間。
computeRatioMax computeRatioMax のオレンジ色のバー 最も遅いワーカーが CPU のバインドに費やした時間。
writeRatioAvg writeRatioAvg の暗青色のバー 平均的なワーカーが出力データの書き込みに費やした時間。
writeRatioMax writeRatioMax の青色のバー 最も遅いワーカーが出力データの書き込みに費やした時間。

* ラベル "AVG" と "MAX" は図だけのものであり、実際のウェブ UI には表示されません。

** すべての割合の分母は同じで、すべてのセグメントのすべてのワーカーによって費やされた最長の時間です。

たとえば、Shakespeare パブリック データセットの中で、「Hamlet」に関連する行の数を数える次のような簡単なクエリを実行します。

SELECT COUNT(*)
FROM [publicdata:samples.shakespeare]
WHERE corpus = 'hamlet'

[Explanation] ボタンをクリックすると、クエリプランに関する次のような情報が表示されます。

ハムレットの検索結果の展開されていないウェブ UI のスクリーンショット

この例では、平均の割合はすべて最大の割合と同じです。また、Stage 1 の waitRatioMax の値がすべてのセグメントで最長であったため、それが表示されるすべての割合に共通の分母になります。

ステップのメタデータ

各ステージは複数のステップで構成されます。クエリプランには、ステージの間に実行された各ステップについての情報が含まれます。次の表では、ステージに含まれる可能性があるステップについて説明します。

ステップ 説明
READ 入力テーブルまたは中間結果からの 1 つ以上の列の読み取り。
WRITE 出力テーブルまたは中間結果への 1 つ以上の列の書き込み。ステージからの HASH パーティショニングされた出力の場合は、パーティション キーとして使用される列も含まれます。
COMPUTE ほとんどの式の評価に対するキャッチオール、および組み込み関数の呼び出し。
FILTER WHERE 句、OMIT IF 句、HAVING 句を実装する演算子。
SORT 列のキーと並べ替えの方向などの並べ替えオペレーション。
AGGREGATE GROUP BY などの集計。一般に、複数のステージに分かれています(すべての中間結果をまとめるために、ワーカーごとおよび最終パスに 1 回)。
LIMIT LIMIT 句を実装する演算子。
JOIN 結合の種類および使用されている列などの JOIN オペレーション。
ANALYTIC_FUNCTION 分析関数の呼び出し(「ウィンドウ関数」とも呼ばれます)。
USER_DEFINED_FUNCTION ユーザー定義関数の呼び出し。

ウェブ UI でステップのメタデータを表示するには、ステージの三角形をクリックします。 次の例では、Stage 1 の展開された結果を示します。このクエリには、READ、AGGREGATE、WRITE のステップが含まれます。

ハムレットの検索結果の展開されたウェブ UI のスクリーンショット

WRITE ステップは Stage 1 の出力を __stage1_output という名前の識別子に書き込んだことに注意してください。これらの識別子を使用すると、ステージで生成されたデータ、およびそのデータが後のステージで使用されたタイミングを、簡単に追跡できます。

BigQuery API とクエリプランの説明

クエリプランの情報はジョブリソースに自動的に添付されて、7 日間保持されます。その期間内に jobs.get() を呼び出すと、JSON のレスポンス オブジェクトの queryPlan にクエリプランの情報が格納されます。クエリプランの説明を含む JSON レスポンス オブジェクトの例を次に示します。

{
 "kind": "bigquery#job",
 "id": "<ID>",
 "jobReference": {
   ...
 },
 "configuration": {
   ...
 },
 "status": {
  "state": "DONE"
 },
 "statistics": {
   ...
  "query": {
   "totalBytesProcessed": "1125284696816",
   "cacheHit": false
   "queryPlan": [
    {
     "steps": [
      {
       "description": "READ corpus, word_count FROM bigquerytestdefault:samples.shakespeare"
      },
      {
       "description": "AGGREGATE QUANTILES(word_count) AS f0_ GROUP BY corpus\n"
      },
      {
       "description": "WRITE corpus, f0_ TO TABLE __R0"
      }
     ],
     "recordsRead": "164656",
     "recordsWritten": "42"
    },
    {
     "steps": [
      {
       "description": "READ corpus, f0_ FROM __R0 AS samples.shakespeare\n"
      },
      {
       "description": "AGGREGATE QUANTILES(f0_) AS f0_ GROUP BY corpus\n"
      },
      {
       "description": "WRITE corpus, f0_ TO __root_union0"
      }
     ],
     "recordsRead": "42",
     "recordsWritten": "42"
    }
   ],
   }
  },
}

結果を解釈する

平均時間と最大時間が大きく異なる

データには偏りのある分布が存在することがあります。理想的には、データがすべての個別ワーカーに均等に分布していれば、同時に終了できます。 データの分布に偏りがあると、一部の負荷が高いワーカーが他のワーカーに追いつくまでの間、実行時間が必要以上に長くなる可能性があります。

たとえば、ウェブ UI では、平均読み取り時間と最大読み取り時間の違いが次のようになることがあります。

読み取り時間の平均と最大の差を示す紫色のバー

BigQuery はこの種の偏りに自動的に対処しようとしますが、偏りが非常に大きく、非常に多くのデータが 1 つのバケットに集中した場合、不可能なことがあります。

一般的な原因
NULL 値、空の値、デフォルト値を含む JOIN、GROUP BY、PARTITION。 データの分布における自然な偏り。たとえば、GROUP BY over Country (顧客の大多数が 1 つの国の場合)や、HTTP リファラー(対数の可能性)。
トラブルシューティング
中間結果を TOP COUNT ステートメントにフィードして、最も一般的な値がキーに対してどのように分布しているかを確認します。
解決策
偏っている値が必要ない場合は、可能な限り早い段階でフィルタを使ってそれを除外します。

可能であれば 2 つの異なるクエリを用意し、1 つは偏りのあるキーだけに対して実行し、もう 1 つは偏りのあるキーを除く他のすべてのデータに対して実行します。

新しいキーを追加して、偏りのあるデータをさらに小さい部分に再分割します。 再分割したグループを再結合するときは注意が必要です。集計演算の中には簡単な構成を備えていないものがあります(例: SUM と AVG の違い)。 たとえば、次のようなステートメントがあるものとします。

SELECT ... GROUP BY a

これは次のように変換します。

SELECT ... FROM (SELECT ... (GROUP by a, b) GROUP BY a)

大部分の時間が中間ステージからの読み取りに費やされた

前のステージが予想より多くのデータを生成している可能性があります。

クエリの早い段階で一部のレコードを除外するか、またはより制限の厳しい JOIN オペレーションを使用します。

大部分の時間が入力テーブルからの読み取りに費やされた

大部分の時間がテーブルからの読み取りに費やされるのは、クエリの理想的な状態です。なぜなら、これは他のすべてのオペレーションが、入力データの読み取りに要した基本コストより低コストであったことを意味するからです。

もちろん、必要なデータだけを読み取るようにすることで、クエリのパフォーマンスをさらに改善できます。頻繁に使用するテーブルであれば、小さいテーブルに分割することも検討してください。たとえば、データを日付単位のテーブルに保持し、特定の日付範囲をクエリするということがよく行われます(前の四半期、最後の 7 日間など)。

詳しくは、テーブル ワイルドカード関数をご覧ください。

大部分の時間がスケジュールの待機に費やされた

作業量が、すぐにスケジュール可能な量を上回っている可能性があります。

しばらくお待ちください。時間の制約が厳しくないジョブであれば、そのままスケジュールしておけば、リソースが空き次第実行されます。

大部分の時間が出力への書き込みに費やされた

入力から読み取られたデータより多くのデータを放出している場合は、これは予期された状態です。

出力する前に一部のデータを除外することを検討してください。

大部分の時間が中間ステージへの書き込みに費やされた

大部分の時間が中間ステージからの読み取りに費やされたをご覧ください。

大部分の時間が CPU にバインドされたタスクに費やされた

これは、I/O よりデータの変換と処理に多くの時間が使われていることを示しています。複雑なクエリの場合は珍しいことではありませんが、CPU の使用量が High-Compute 階層に達するほど高い場合は、パフォーマンスを改善してコストを節約できる可能性があることを意味します。

一般的な原因
クエリでは多くの複雑な式を評価していますか。ユーザー定義関数や JSON/Regex の評価は、特にコストがかかる可能性があります。
解決策

可能な限り早い段階でフィルタリングを行って、複雑な式が適用されるデータの量を減らします。

頻繁に実行するクエリの場合、式の一部を事前に計算し、毎回再評価する必要がないようにします。

Stage 1 でのデータスキュー

イベントの GDELT データベースは、サイズが異なる複数のファイルに格納されています。このデータの偏りは、クエリの最初のステージにおけるテーブル読み取りの平均時間と最大時間の差異として認識できます。

たとえば、国別にイベントをカウントする次の簡単なクエリを使うと、Stage 1 の最大読み取り時間は平均より数倍長く、最大計算時間は平均の約 2 倍であることがわかります。

SELECT
  ActionGeo_CountryCode,
  COUNT(1) AS count
FROM [gdelt-bq:full.events]
GROUP BY 1
ORDER BY 2 DESC;

Stage 1 でのデータの偏りの結果を示すウェブ UI のスクリーンショット

BigQuery によるテーブルのパーティショニングをユーザーは制御できないため、Stage 1 でのデータの偏りを緩和するためにできることはあまりありませんが、データの偏りがクエリの後半のステージで発生する場合は、より多くのキーで結合またはグループ化を行ってデータを細かくパーティショニングすることにより、偏りを減らせることがよくあります。

パフォーマンス向上のためにフィルタをプッシュダウンする

次のクエリでは結合オペレーションの後でフィルタを適用していますが、この場合は、フィルタが結合の入力の 1 つにだけ関係しているため、効果的ではありません。

SELECT
  ToHuman.HumanName,
  COUNT(1) AS count
FROM [gdelt-bq:full.events] AS Events
JOIN [gdelt-bq:full.crosswalk_geocountrycodetohuman] AS ToHuman
ON Events.ActionGeo_CountryCode = ToHuman.FIPSCC
WHERE Events.Year > 2010
GROUP BY 1
ORDER BY 2 DESC;

この場合は、フィルタを実行スタックの下方に移動して、結合オペレーションの前にフィルタが適用されるようにすることで、パフォーマンスを改善できます。 次のクエリの実行プランでわかるように、BigQuery では結合をまたがる下方へのフィルタの移動は自動的には行われません。フィルタ条件 Events.Year > 2010 は結合への入力の 1 つにだけ依存しますが、フィルタリングは内側の結合の後で行われます。

Stage 2 でのデータの偏りの結果を示すウェブ UI のスクリーンショット

パフォーマンスを改善するには、フィルタを適用することでより多くのデータが除外されるよう、結合の前でフィルタリングが行われるようにクエリを修正できます。次の例では、クエリを修正して、フィルタを結合オペレーションの前のサブクエリに移動しています。

SELECT
  ToHuman.HumanName,
  COUNT(1) AS count
FROM (SELECT *
      FROM [gdelt-bq:full.events]
      WHERE Year > 2010) AS Events
JOIN [gdelt-bq:full.crosswalk_geocountrycodetohuman] AS ToHuman
ON Events.ActionGeo_CountryCode = ToHuman.FIPSCC
GROUP BY 1
ORDER BY 2 DESC;

Stage 2 でのデータの偏りを修正した後の結果を示すウェブ UI のスクリーンショット

外出先でもリソースをモニタリング

Google Cloud Console アプリを入手して、プロジェクトの管理にお役立てください。

フィードバックを送信...

BigQuery のドキュメント