ネストされた繰り返しフィールドを使用する

ベスト プラクティス: ネストされたフィールドと繰り返しフィールドを使用して、データ ストレージを非正規化し、クエリのパフォーマンスを向上させます。

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

最新のシステムでは、正規化データの使用によるストレージ節約はあまり問題とはなりません。ストレージの費用が増加するとしても、データ非正規化によるパフォーマンス向上には価値があります。結合にはデータ調整(通信帯域幅)が必要です。非正規化するとデータがローカライズされて個々のスロットに入れられるため、並行実行が可能です。

データを非正規化すると同時に関係を維持するには、データを完全にフラットにするのではなく、ネスト フィールドと繰り返しフィールドを使用します。リレーショナル データが完全に平坦化されると、ネットワーク通信(シャッフル)がクエリのパフォーマンスを下げる可能性があります。

たとえば、ネスト フィールドや反復フィールドを使用せずにオーダー スキーマを非正規化する際、order_id などのフィールドでデータをグループ化しなければならない場合があります(1 対多の関係がある場合)。シャッフルが必要になると、ネスト フィールドと繰り返しフィールドを使ってデータを非正規化した場合と比較して、データのグループ化はパフォーマンスが低くなります。

場合によっては、データを非正規化し、ネスト フィールドと繰り返しフィールドを使用しても、パフォーマンスは向上しません。以下のような場合は、非正規化を避けてください。

  • 頻繁にディメンションが変化するスタースキーマがある場合。
  • BigQuery が行レベルの変更(ミューテーション)があるオンライン トランザクション処理(OLTP)システムを補完しているが、それに置き換えることはできない場合。

ネスト フィールドと繰り返しフィールドの使用

BigQuery は完全にフラットな非正規化を必要としません。ネストされたフィールドと繰り返しフィールドを使用して、リレーションシップを維持できます。

  • ネストデータ(STRUCT

    • ネストデータを使用すると、外部エンティティをインラインで表すことができます。
    • ネストデータを照会すると、リーフ フィールドを参照する「ドット」構文が使用されます。これは、結合を使用する構文に似ています。
    • ネストデータは、標準 SQL の STRUCTとして表されます。
  • 繰り返しデータ(ARRAY

    • モードを REPEATED に設定して RECORD 型のフィールドを作成すると、1 対多のリレーションシップをインラインで保持できます(リレーションシップのカーディナリティが高くない場合)。
    • 繰り返しデータでは、シャッフルは不要です。
    • 繰り返しデータは、ARRAY として表されます。繰り返しデータをクエリする際は、標準 SQL の ARRAY 関数を使用できます。
  • ネストされた繰り返しデータ(STRUCTARRAY

    • ネストと繰り返しは相互に補完します。
    • たとえば、トランザクション レコードのテーブルに STRUCT 項目の配列を含めることができます。

詳細については、テーブル スキーマでのネストされた列と繰り返し列の指定をご覧ください。

データを非正規化する方法の詳しい例については、非正規化をご覧ください。

販売された各行の行を含む Orders テーブルについて考えてみましょう。

order_id 項目名
001 A1
001 B1
002 A1
002 C1

このテーブルのデータを分析する場合は、次のように GROUP BY 句を使用する必要があります。

SELECT COUNT (Item_Name) from Orders
GROUP BY Order_Id

GROUP BY 句では追加のコンピューティング オーバーヘッドが発生しますが、繰り返しデータをネストすることで回避できます。ネストされた行内に注文明細行があり、行ごとに 1 つの注文を持つテーブルを作成することで、GROUP BY 句を使用しなくても済みます。

Order_Id 項目名
001 A1

B1
002 A1

C1

BigQuery では通常、ネストされたスキーマを STRUCT オブジェクトの ARRAY として指定します。次のクエリに示すように、UNNEST 演算子を使用して、ネストされたデータをフラット化します。

SELECT * from UNNEST(
  [
    STRUCT('001' as Order_Id, ['A1', 'B1'] as Item_Name)
    , STRUCT('002' as Order_Id, ['A1', 'C1'] as Item_Name)
  ]
)

このクエリを実行すると、次のような結果が得られます。

ネストされていないデータを使用したクエリ出力

このデータがネストされていない場合は、注文ごとに販売された商品ごとに 1 つの行が複数存在する可能性があり、その場合、テーブルが大きくなり、GROUP BY オペレーションの負荷が大きくなります。

演習

ネストされたフィールドを使用するクエリと使用しないフィールドのパフォーマンスの違いは、このセクションの手順で確認できます。

  1. bigquery-public-data.stackoverflow.comments 一般公開データセットに基づいてテーブルを作成します。

    CREATE OR REPLACE TABLE `PROJECT.DATASET.stackoverflow`
    AS (
    SELECT
      user_id,
      post_id,
      creation_date
    FROM
      `bigquery-public-data.stackoverflow.comments`
    );
    
  2. stackoverflow テーブルを使用して次のクエリを実行し、各ユーザーの最も古いコメントを確認します。

    SELECT
      user_id,
      ARRAY_AGG(STRUCT(post_id, creation_date AS earliest_comment) ORDER BY creation_date ASC LIMIT 1)[OFFSET(0)].*
    FROM
      `PROJECT.DATASET.stackoverflow`
    GROUP BY user_id
    ORDER BY user_id ASC
    

    このクエリの実行には約 25 秒かかり、1.88 GB のデータが処理されます。

  3. 同じデータを含む 2 番目のテーブルを作成します。このテーブルは、2 つの個別フィールドではなく、STRUCT 型を使用して post_id データと creation_date データを保存するための comments フィールドを作成します。

    CREATE OR REPLACE TABLE `PROJECT.DATASET.stackoverflow_nested`
    AS (
    SELECT
      user_id,
      ARRAY_AGG(STRUCT(post_id, creation_date) ORDER BY creation_date ASC) AS comments
    FROM
      `bigquery-public-data.stackoverflow.comments`
    GROUP BY user_id
    )
    
  4. stackoverflow_nested テーブルを使用して次のクエリを実行し、各ユーザーの最も古いコメントを確認します。

    SELECT
      user_id,
      (SELECT AS STRUCT post_id, creation_date as earliest_comment FROM UNNEST(comments) ORDER BY creation_date ASC LIMIT 1).*
    FROM
      `PROJECT.DATASET.stackoverflow_nested`
    ORDER BY user_id ASC
    

    このクエリの実行には約 10 秒かかり、1.28 GB のデータが処理されます。

  5. 終了したら、stackoverflow テーブルと stackoverflow_nested テーブルを削除します。