ネストされ繰り返されているフィールドを使用する
BigQuery は、さまざまなデータ モデリング方式で使用でき、通常は多くのデータモデルの手法で高いパフォーマンスを実現します。パフォーマンスのためにデータモデルをさらに調整するには、データの非正規化を検討します。つまり、データの列を 1 つのテーブルに追加して、テーブル結合を減らすか削除します。
ベスト プラクティス: ネストされた繰り返しフィールドを使用して、データ ストレージを非正規化し、クエリのパフォーマンスを改善します。
非正規化は、以前に正規化されたリレーショナル データセットの読み取りパフォーマンスを向上させるための一般的な手法です。BigQuery でデータを非正規化するために推奨されている方法は、ネストされたフィールドと繰り返しフィールドを使用することです。関係が階層的で、多くの場合、親子関係のように一緒に照会されるため、これが最適な方法です。
最新のシステムでは、正規化データの使用によるストレージ節約はあまり問題とはなりません。ストレージの費用が増加するとしても、データ非正規化によるパフォーマンス向上には価値があります。結合にはデータ調整(通信帯域幅)が必要です。非正規化するとデータがローカライズされて個々のスロットに入れられるため、並行実行が可能です。
データを非正規化すると同時に関係を維持するには、データを完全にフラットにするのではなく、ネスト フィールドと繰り返しフィールドを使用します。リレーショナル データが完全に平坦化されると、ネットワーク通信(シャッフル)がクエリのパフォーマンスを下げる可能性があります。
たとえば、ネスト フィールドや反復フィールドを使用せずにオーダー スキーマを非正規化する際、order_id
などのフィールドでデータをグループ化しなければならない場合があります(1 対多の関係がある場合)。シャッフルが必要になると、ネスト フィールドと繰り返しフィールドを使ってデータを非正規化した場合と比較して、データのグループ化はパフォーマンスが低くなります。
場合によっては、データを非正規化し、ネスト フィールドと繰り返しフィールドを使用しても、パフォーマンスは改善されません。たとえば、一般にスタースキーマは分析用に最適化されたスキーマであるため、さらに非正規化を試みても、パフォーマンスが大きく変わるとは限りません。
ネスト フィールドと繰り返しフィールドの使用
BigQuery は完全にフラットな非正規化を必要としません。ネストされたフィールドと繰り返しフィールドを使用して、リレーションシップを維持できます。
ネストデータ(
STRUCT
)- ネストデータを使用すると、外部エンティティをインラインで表すことができます。
- ネストデータを照会するときには、リーフ フィールドを参照する「ドット」構文を使用します。これは、結合を使用する構文に似ています。
- ネストデータは、GoogleSQL の
STRUCT
型として表されます。
繰り返しデータ(
ARRAY
)- モードを
REPEATED
に設定してRECORD
型のフィールドを作成すると、1 対多のリレーションシップをインラインで保持できます(リレーションシップのカーディナリティが高くない場合)。 - 繰り返しデータでは、シャッフルは不要です。
- 繰り返しデータは、
ARRAY
として表されます。繰り返しデータをクエリする際は、GoogleSQL のARRAY
関数を使用できます。
- モードを
ネストされた繰り返しデータ(
STRUCT
のARRAY
)- ネストと繰り返しは相互に補完します。
- たとえば、トランザクション レコードのテーブルに
STRUCT
項目の配列を含めることができます。
詳細については、テーブル スキーマでネストされた列と繰り返し列を指定するをご覧ください。
データの非正規化の詳細については、非正規化をご覧ください。
例
販売されたアイテムごとに 1 行が含まれる Orders
テーブルを検討します。
Order_Id | Item_Name |
---|---|
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 | Item_Name |
---|---|
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
テーブルを削除します。