ネストされた繰り返しフィールドを使用する
ベスト プラクティス: ネストされたフィールドと繰り返しフィールドを使用して、データ ストレージを非正規化し、クエリのパフォーマンスを向上させます。
非正規化は、以前に正規化されたリレーショナル データセットの読み取りパフォーマンスを向上させるための一般的な手法です。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
オペレーションの負荷が大きくなります。
演習
ネストされたフィールドを使用するクエリと使用しないフィールドのパフォーマンスの違いは、このセクションの手順で確認できます。
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` );
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 のデータが処理されます。
同じデータを含む 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 )
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 のデータが処理されます。
終了したら、
stackoverflow
テーブルとstackoverflow_nested
テーブルを削除します。