SQL アンチパターンの回避

以下のおすすめの方法では、BigQuery のパフォーマンスに影響するクエリ アンチパターンの回避に関するガイダンスを提供します。

自己結合

おすすめの方法: 自己結合を回避します。代わりに、ウィンドウ関数を使用してください。

通常、自己結合は、行依存の関係をコンピューティングするために使用します。自己結合を使用すると、出力行の数が 2 倍になる可能性があります。このように出力データが増加した場合は、パフォーマンスが低下することがあります。

クエリによって生成される追加のバイト数を減らすには、自己結合の代わりに、ウィンドウ(分析)関数を使用します。

データスキュー

おすすめの方法: クエリがいくつかの値に大きく偏ったキーを処理する場合は、できるだけ早い段階でデータをフィルタ処理します。

パーティション スキューは、データスキューとも呼ばれ、サイズがバラバラのパーティションにデータが分割された状態です。これにより、スロット間で送信されるデータの量が不均衡になります。スロット間でパーティションを共有することはできません。そのため、1 つのパーティションが特別に大きい場合は、サイズの大きいパーティションを処理するスロットが遅くなったり、クラッシュしたりする可能性があります。

パーティション キーのある値の出現回数が他の値に比べて多い場合、そのパーティションが大きくなります。たとえば、guest または null の入力が頻繁に発生する user_id フィールドでグループ分けした場合です。

スロットのリソースが多すぎると、resources exceeded エラーが発生します。スロットのシャッフル制限(メモリ圧縮の 2 TB)に達した場合も、シャッフルがディスクに書き込まれ、パフォーマンスがさらに低下します。定額制のお客様は、割り当てられるスロットの数を増やすことができます。

クエリプランの説明を調べて、平均コンピューティング時間と最大コンピューティング時間に大きな差がある場合は、データが偏っている可能性があります。

データの偏りから生じるパフォーマンス問題を回避するには:

  • APPROX_TOP_COUNT などの近似集計関数を使用して、データが偏っているかどうかを判断します。
  • できるだけ早い段階でデータをフィルタ処理します。

不均衡な結合

JOIN 句を使用した場合も、データスキューが発生する可能性があります。BigQuery は、結合の両側でデータをシャッフルするため、同じ結合キーを持つすべてのデータが同じグループに入ります。このシャッフルは、スロットに過負荷をかける可能性があります。

不均衡な結合に関連したパフォーマンス問題を回避するには:

  • 不平衡なキーを含む行をあらかじめテーブルから除去します。
  • 可能であれば、クエリを 2 つのクエリに分割します。

クロス結合(デカルト積)

おすすめの方法: 入力より出力の方が多い結合を回避します。CROSS JOIN が必要な場合は、データを事前集計します。

クロス結合は、1 つ目のテーブルの各行が 2 つ目のテーブルのすべての行に結合されているクエリです(両側に一意ではないキーが存在します)。最悪の場合の出力は、左側のテーブルの行数に右側のテーブルの行数を掛けたものになります。極端な場合、クエリが終了しないことがあります。

クエリジョブが完了すると、クエリプランの説明に出力行数と入力行数の比較が表示されます。デカルト積を確認するには、JOIN 句の両側の行数を結合キーでグループ分けして出力するようにクエリを変更します。

入力より出力の方が多い結合に関連したパフォーマンス問題を回避するには:

  • GROUP BY 句を使用してデータを事前集計します。
  • ウィンドウ関数を使用します。多くの場合、ウィンドウ関数は、クロス結合を使用するより効率的です。詳細については、分析関数をご覧ください。

単一行を更新または挿入する DML ステートメント

ベスト プラクティス: ポイント固有の DML ステートメント(一度に 1 行ずつの更新または挿入)の使用は避けます。更新と挿入をバッチ処理します。

ポイント固有の DML ステートメントを使用すると、BigQuery をオンライン トランザクション処理(OLTP)システムのように扱おうとしていることになります。BigQuery は、ポイント検索ではなく、テーブル スキャンを使用したオンライン分析処理(OLAP)に重点が置かれています。OLTP に似た動作(単一行の更新または挿入)が必要な場合は、Google Cloud SQL などの OLTP ユースケースをサポートするように設計されたデータベースを検討してください。

BigQuery DML ステートメントの目的は一括更新です。BigQuery 内の UPDATE および DELETE DML ステートメントは、単一行を変更するのではなく、データを定期的に書き換えるためのものです。INSERT DML ステートメントは、控え目に使用するように意図されています。挿入では、読み込みジョブと同じ変更割り当てが消費されます。ユースケースで単一行の挿入が頻繁に行われる場合は、代わりにデータのストリーミングを検討してください。

UPDATE ステートメントをバッチ処理すると非常に長いクエリ内に多数のタプルが発生する場合は、256K というクエリの長さ制限に近づく可能性があります。クエリの長さ制限を回避するには、一連の直接タプル置換ではなく、論理的な基準に基づいて更新を処理できるかどうかを検討してください。

たとえば、置換レコードのセットを別のテーブルに読み込んでから、更新されていない列が一致した場合に元のテーブルのすべての値を更新する DML ステートメントを記述することができます。たとえば、元のデータがテーブル t 内に存在し、更新がテーブル u 内でステージングされている場合、クエリは次のようになります。

UPDATE dataset.t t
SET my_column = u.my_column
FROM dataset.u u
WHERE t.my_key = u.my_key
このページは役立ちましたか?評価をお願いいたします。

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

ご不明な点がありましたら、Google のサポートページをご覧ください。