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

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

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

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

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

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

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

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

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

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

  • ネストデータ（STRUCT）

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

  • 繰り返しデータ（ARRAY）

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

  • ネストされた繰り返しデータ（STRUCT の ARRAY）

    • ネストと繰り返しは相互に補完します。
    • たとえば、トランザクション レコードのテーブルに 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 テーブルを削除します。