データ ウェアハウスの BigQuery への移行: パフォーマンスの最適化

このドキュメントは、オンプレミス データ ウェアハウスから Google Cloud 上の BigQuery への移行に役立つシリーズの一部です。BigQuery を使用したデータ ウェアハウスでクエリ パフォーマンスを最適化するための重要な手法について説明します。このドキュメントは、以前のデータ ウェアハウスからスキーマとデータを BigQuery に移行し、古いジョブのクエリを BigQuery 標準 SQL に変換していることを前提にしています。

この記事を読みながら、移行作業で最適化が必要な理由を考えてみてください。このドキュメントの目標は、利用可能なすべての最適化手法を説明することではありません。最適化手法の詳細については、BigQuery のベスト プラクティスに関する公式ドキュメントをご覧ください。このドキュメントは、これらの手法を検討する必要があるかどうか、また、必要な場合、いつ検討するべきかについて解説することを目的としています。

移行についてのシリーズは、次のパートで構成されています。

パフォーマンスに関する一般的な考慮事項

BigQuery は、小規模とペタバイト規模のデータセットのデータを効率的に処理します。BigQuery を使用すると、新しく移行したデータ ウェアハウスでもデータ分析ジョブをそのまま実行できます。特定の状況下でクエリのパフォーマンスが期待と一致しない場合は、ここで説明する主要な原則を検討して、さまざまな要因がどのようにパフォーマンスに影響するかを理解してください。

最適化のトレードオフ

他のシステムと同様に、パフォーマンスの最適化にもトレードオフが伴います。たとえば、高度な SQL 構文を使用すると、複雑さが増し、SQL の専門家ではないとクエリを理解できなくなる可能性もあります。また、重要ではないワークロードのマイクロ最適化に時間を費やすと、アプリケーションの新機能の構築や、より効果的な最適化を行うためのリソースが不足することになります。したがって、費用対効果を最大にするため、データ分析パイプラインで最も重要なワークロードの最適化に集中することをおすすめします。

特定のクエリに問題があるかどうかを評価するには、Cloud Monitoring を使用して、BigQuery ジョブによるリソースの消費状況をモニタリングします。低速またはリソース集約型のクエリを特定したら、そのクエリの最適化を集中的に行います。

容量と同時実行性

BigQuery は、SQL クエリの実行に必要な計算能力をスロットという単位に分解します。BigQuery は、クエリのサイズと複雑さに応じて、クエリに必要なスロット数を自動的に計算します。

BigQuery では、お客様の履歴、使用量、費用に基づいて、クエリを実行するスロットの割り当てを自動的に管理します。ほとんどのユーザーにとって、プロジェクトごとにデフォルトで割り当てられるスロットの容量は十分です。それ以上のスロットにアクセスしても、クエリごとのパフォーマンスが改善される保証はありません。ただし、スロットプールが大きければ、大規模で複雑なクエリのパフォーマンスが向上するとともに、同時実行数の多いワークロードのパフォーマンスも向上します。クエリのパフォーマンスをさらに向上させるには、データモデルとクエリの最適化を行うだけでなく、必要に応じて予約スロットの追加購入も検討する必要があります。

クエリに関して、BigQuery はオンデマンド定額の 2 種類の料金モデルを提供します。オンデマンドの料金は、実行した各クエリで処理されたデータの量に基づいて計算されます。毎月の分析費用を一定にしたい場合は、定額料金が適しています。定額料金プランに加入する場合は、クエリ処理に使用する BigQuery スロットの割り当て数を購入します。処理データの費用はすべて月定額料金に含まれます。クエリが定額容量を超えた場合、定額リソースが使用可能になるまでクエリはキューに入れられます。

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

BigQuery ウェブ UI を使用すると、クエリプランとタイムラインを視覚的に確認できます。jobs.get API メソッドを使用すると、クエリプランとタイムライン情報を取得できます。また、オープンソース ツールの BigQuery Visualiser で、BigQuery ジョブの実行ステージのフローを視覚的に表示することもできます。

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

クエリ実行プラン。

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

タイムライン統計。

クエリの計算コストは、クエリが 1 秒あたりに消費するスロット数の合計で判断します。1 秒間で消費されるスロット数が少ないほど、同じプロジェクトで同時に実行されている他のクエリがより多くのリソースを使用できることを意味します。

クエリプランとタイムラインの統計情報は、BigQuery でのクエリの実行状態や、特定のステージでのリソースの使用状況の把握に役立ちます。たとえば、JOIN ステージで入力行よりも出力行が多い場合は、クエリの前にフィルタリングが必要かもしれません。ただし、サービスの管理特性のため、この情報をそのまま利用できるとは限りません。クエリの実行とパフォーマンスを改善する場合は、クエリの最適化方法セクションと BigQuery のベスト プラクティス ドキュメントに記載されている手法に従うことをおすすめします。

外部データソース

外部データソース(フェデレーション データソースとも呼ばれます)は、データが BigQuery に格納されていない場合でも直接クエリできるデータソースです。データの読み込みまたはストリーミングの代わりに、外部データソースを参照するテーブルを作成します。BigQuery でサポートされる外部ソースには、Cloud BigtableCloud StorageGoogle Drive などがあります。

外部データソースに対するクエリのパフォーマンスは、ネイティブな BigQuery テーブルのデータに対するクエリよりも遅くなる可能性があります。クエリ速度を優先する場合は、外部データソースを設定するのではなく、データを BigQuery に読み込むことをおすすめします。外部データソースを含むクエリのパフォーマンスは、外部ストレージのタイプに依存します。たとえば、Cloud Storage に格納されたデータのクエリは、Google ドライブに格納されたデータのクエリよりも高速です。一般的に、外部データソースのクエリ パフォーマンスは、その外部ストレージからデータを直接読み取る処理と同等になります。

外部データソースに対してクエリを実行する場合、結果はキャッシュに保存されません。同じクエリを複数回発行する場合でも、外部テーブルに対するクエリごとに課金されます。頻繁には変更されない外部テーブルに対してクエリを繰り返し発行する必要がある場合は、クエリ結果を永続的なテーブルに書き込み、永続的なテーブルに対してクエリを実行することを検討してください。

次のような場合は、外部データソースの使用を検討します。

  • データの読み込み時に抽出 / 変換 / 読み込み(ETL)オペレーションを実行する
  • 頻繁に変更されるデータにクエリを実行する
  • Cloud Bigtable からのデータの反復取り込みなどの定期的な読み込みを処理する

外部データソースを使用する機会、制限、ベスト プラクティスについては、BigQuery の公式ドキュメントをご覧ください。

クエリの最適化方法

前のセクションを確認した後で、特定のクエリのパフォーマンスが期待と一致しないことがわかった場合は、このセクションで説明する方法でクエリを最適化することを検討してください。

パーティショニング

テーブルのパーティショニングでは、テーブルをパーティションというセグメントに分割します。大きいテーブルを小さいパーティションに分割することでクエリのパフォーマンスを向上させることができ、クエリで読み取られるバイト数を減らすことによってコストを管理できます。テーブルのスキーマで許可されている場合は、10 GB を超えるテーブルをパーティションに分割することをおすすめします。

BigQuery には次の 2 種類のテーブル分割があります。

BigQuery では次のようにしてパーティション分割テーブルを作成できます。

  • CREATE TABLE ステートメントを使用し、PARTITION BY 句に partition_expression を指定して作成する。
  • BigQuery コマンドライン ツールの bq mk コマンドを使用して手動で作成する。
  • プログラムで tables.insert API メソッドを呼び出して作成する
  • クエリの結果から作成する
  • データの読み込み時に作成する

たとえば、次のスキーマを使用して、以前のデータ ウェアハウスから BigQuery に orders テーブルを移行したとします。

orders
列名
salesOrderKey STRING
orderDate TIMESTAMP
customerKey STRING
totalSale FLOAT
currencyKey INTEGER

次のように、orderDate 列の日付で orders テーブルをパーティションに分割できます。

CREATE TABLE `your-project.sales.orders_partitioned` (
  salesOrderKey STRING,
  orderDate TIMESTAMP,
  customerKey STRING,
  totalSale FLOAT64,
  currencyKey INT64
)
PARTITION BY DATE(orderDate)
AS
SELECT * FROM `your-project.sales.orders`

以下のクエリでは、WHERE 句により、BigQuery が単一パーティションからデータをスキャンするため、パーティション分割テーブルのほうがクエリの処理時間が短くなります。

SELECT
 DATE_TRUNC(DATE(orderDate), MONTH) AS firstOfMonth,
 currencyKey,
 COUNT(*) AS numberOfOrders,
 SUM(totalSale) AS totalSales
FROM `your-project.sales.orders_partitioned`
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-01-01'
GROUP BY DATE_TRUNC(DATE(orderDate), MONTH), currencyKey

スキーマの改善でパーティショニングの検討が必要な条件については、スキーマとデータ転送の概要をご覧ください。

クラスタリング

クラスタリングは、フィルタ句を使用するクエリやデータを集計するクエリなど、特定のタイプのクエリのパフォーマンスを向上させることができます。クエリジョブまたは読み込みジョブでクラスタ化されたテーブルにデータが書き込まれると、BigQuery はクラスタリング列の値を使用してデータを並べ替えます。これらの値は、BigQuery ストレージ内の複数のブロックにデータを整理するために使用されます。クラスタリング列に基づいてデータをフィルタリングする句を含むクエリを送信すると、BigQuery は、並べ替えられたブロックを使用して不要なデータのスキャンを回避し、クエリを高速かつ低価格で実行します。

BigQuery では現在、パーティション分割テーブルのクラスタリングのみがサポートされています。テーブルのクラスタリングは、取り込み時間で分割されたテーブルと、DATE 列または TIMESTAMP 列で分割されたテーブルの両方でサポートされています。

次の状況では、パーティション分割テーブルでクラスタリングを使用できます。

  • データがすでに日付列またはタイムスタンプ列によって分割されている。
  • 通常、クエリで特定の列に対するフィルタまたは集計を使用している。

クラスタリングとパーティショニングを併用すると、データを日付列またはタイムスタンプ列で分割し、異なる列のセットにクラスタ化できます。この場合、各パーティション内のデータは、クラスタリング列の値に基づいてクラスタ化されます。パーティショニングにより、クエリの正確な費用を見積もることもできます(スキャンされるパーティションに基づいて計算できます)。

前のセクションで示した例をさらに改善するため、元の orders テーブルにパーティション化とクラスタ化を行い、orders_clustered という名前のテーブルを作成します。

CREATE TABLE
 `your-project.sales.orders_clustered` (
   salesOrderKey STRING,
   orderDate TIMESTAMP,
   customerKey STRING,
   totalSale FLOAT64,
   currencyKey INT64 )
PARTITION BY DATE(orderDate)
CLUSTER BY customerKey
AS
SELECT * FROM `your-project.sales.orders`

次のクエリは、パーティショニングと WHERE フィルタの組み合わせにより、元の orders テーブルよりも orders_clustered テーブルのほうが高速に実行されます。また、クラスタリングを行い、customerKey IN [...]GROUP BY customerKey 句を組み合わせたことで、orders_partitioned テーブルよりも高速に実行されます。

SELECT
  customerKey,
  DATE_TRUNC(DATE(orderDate), MONTH) AS firstOfMonth,
  currencyKey,
  COUNT(*) AS numberOfOrders,
  SUM(totalSale) AS totalSales
FROM `your-project.sales.orders_clustered`
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-02-01'
  AND customerKey IN (
    '1292803200000-402',
    '1298764800000-414',
    '1267401600000-212',
    '1267488000000-209')
GROUP BY customerKey, DATE_TRUNC(DATE(orderDate), MONTH), currencyKey

スキーマの改善でクラスタリングの検討が必要な条件については、スキーマとデータ転送の概要をご覧ください。

非正規化

非正規化は、以前に正規化されたリレーショナル データセットの読み取りパフォーマンスを向上させるための一般的な手法です。BigQuery でデータを非正規化するために推奨されている方法は、ネストされたフィールドと繰り返しフィールドを使用することです。関係が階層的で、多くの場合、親子関係のように一緒に照会されるため、これが最適な方法です。

ネストされたフィールドと繰り返しフィールドを使用すると、データが個々のスロットに局所化されるため、並行して実行できます。このアプローチでは、非正規化されたリレーショナル データを完全にフラット化せずに関係を維持します。これにより、シャッフル フェーズでのネットワーク通信が減少し、パフォーマンスがさらに向上します。

データの非正規化により、必要なストレージ量が増加します。ただし、BigQuery の低コスト ストレージにより、この増加はほとんどのユースケースで問題になりません。非正規化では、コンピューティング リソースとストレージ リソースのバランスを取ることで、読み取り率の高いアプリケーションの全体的なコストを削減します。このコスト削減は、全体的な速度の向上と、コンピューティングとネットワークの使用率の低下による結果です。

ネストデータは、BigQuery 標準 SQL の STRUCTとして表されます。繰り返しデータは ARRAYとして表され、ARRAY 関数を使用できます。ネストと繰り返しは互いに補完することもできます(STRUCTARRAY)。詳細については、BigQuery の非正規化のベスト プラクティスをご覧ください。

たとえば、次のスキーマを持つ orders_clusteredorder_lines_clustered の 2 つのテーブルについて考えてみましょう。

orders_clustered
列名
salesOrderKey STRING
orderDate TIMESTAMP
customerKey STRING
totalSale FLOAT
currencyKey INTEGER
order_lines_clustered
列名
salesOrderKey STRING
salesOrderLineKey STRING
productKey STRING
quantity INTEGER
unitPrice FLOAT
orderDate TIMESTAMP
totalSale FLOAT

他の 2 つのテーブルのデータを繰り返しネストされたフィールドに事前に結合する非正規化テーブル(orders_nested)を作成します。

CREATE TABLE `your-project.sales.orders_denormalized`
PARTITION BY DATE(orderDate)
CLUSTER BY customerKey
AS
SELECT
  o.salesOrderKey,
  o.orderDate,
  o.currencyKey,
  o.customerKey,
  o.totalSale,
  ARRAY_AGG(
    STRUCT(
      ol.salesOrderLineKey,
      ol.productKey,
      ol.totalSale
  )) AS lineItem
FROM `your-project.sales.orders_clustered` AS o
  JOIN `your-project.sales.order_lines_clustered` AS ol
    ON ol.orderDate = o.orderDate
    AND ol.salesOrderKey = o.salesOrderKey
GROUP BY 1,2,3,4,5

次の 2 つの SELECT クエリについて考えてみましょう。

SELECT
  o.salesOrderKey,
  o.orderDate,
  o.currencyKey,
  ol.salesOrderLineKey,
  ol.productKey,
  ol.totalSale
FROM `your-project.sales.orders_clustered` AS o
  JOIN `your-project.sales.order_lines_clustered` AS ol
    ON ol.orderDate = o.orderDate
    AND ol.salesOrderKey = o.salesOrderKey
WHERE DATE_TRUNC(DATE(o.orderDate), MONTH) = '2015-02-01'
  AND o.customerKey = '1201392000000-325'

SELECT
 salesOrderKey,
 orderDate,
 currencyKey,
 lineItem
FROM `your-project.sales.orders_denormalized`
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-02-01'
 AND customerKey = '1201392000000-325'

2 番目のクエリは、事前に結合されたデータを含む非正規化テーブルに実行されるため、最初のクエリよりもはるかに高速に実行されます。

2 番目のクエリは、ARRAY(STRUCT()) データ型の lineItem 値を返します。

2 番目のクエリの結果。

UNNEST 演算子を使用して、配列をフラット化します。

SELECT
 o.salesOrderKey,
 o.orderDate,
 o.currencyKey,
 ol.*
FROM `your-project.sales.orders_denormalized` AS o
  JOIN UNNEST(lineItem) AS ol
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-02-01'
 AND customerKey = '1201392000000-325'

次のスクリーンショットのように、クエリの結果がフラット化された形式で返されます。

クエリ結果はフラットな形式で表示されます。

スキーマの改善で非正規化の検討が必要な条件については、スキーマとデータ転送のガイドをご覧ください。

近似関数

近似集計は、カーディナリティや変位値などの集計関数出力の見積もりです。BigQuery は、HyperLogLog++アルゴリズムを使用して、大規模なデータセット内の個別の値を推測する近似集計関数をサポートしています。

近似集計関数は、対応する正確な集計関数よりもメモリを必要としませんが、統計的な不確実性をもたらします。近似集計関数は、線形メモリの使用が実用的でない大規模なデータ ストリームや、すでに概算されているデータに対して使用できます。

たとえば、次の 2 つのクエリについて考えてみましょう。

SELECT
  COUNT(DISTINCT salesOrderKey)
FROM `your-project.sales.orders`

SELECT
 APPROX_COUNT_DISTINCT(salesOrderKey)
FROM `your-project.sales.orders`

最初のクエリは正確なカウント値を返します。2 番目のクエリは、正確な値から無視できる程度の偏差でカウント値を返します。ただし、最初のクエリよりもはるかに高速に実行されます。

アンチパターン

さらにパフォーマンスを向上させる方法については、アンチパターンに関する BigQuery のドキュメントをご覧ください。アンチパターンを使用すると、入力よりも多くの出力を生成する結合の作成、ポイント固有の DML ステートメントの使用、フィルタリングされていないスキューデータのクエリなど、一般的な間違いを防ぐことができます。

その他のベスト プラクティス

このドキュメントでは、移行されたデータ ウェアハウスのクエリ パフォーマンスを向上させるためによく利用されている手法について説明しました。さらなる改善を行い、より多くのベスト プラクティスを学ぶには、クエリ パフォーマンスの最適化にある関する他のドキュメントもご覧ください。

次のステップ

Google Cloud のその他の機能を試す。チュートリアルをご覧ください。