BigQuery は非正規化データに最適です。スタースキーマやスノーフレーク スキーマなどのリレーショナル スキーマを保存するのではなく、データを非正規化して、ネストされた列と繰り返し列を利用します。ネストされた列と繰り返し列は、リレーショナル(正規化)スキーマを維持することによるパフォーマンスへの影響なしで、リレーションシップを維持できます。
UI または JSON スキーマ ファイルで、ネストされたデータ、またはネストされたデータと繰り返しデータを指定できます。ネストされた列、またはネストされた列と繰り返し列を指定するには、RECORD
(STRUCT
)データ型を使用します。
ネストされた繰り返し列の指定
BigQuery では、JSON ファイル、Avro ファイル、Cloud Firestore エクスポート ファイル、Cloud Datastore エクスポート ファイルなど、オブジェクト ベースのスキーマをサポートするソース形式のネストされたデータおよび繰り返しデータを読み込むことができます。
たとえば、図書館の書籍を追跡するために使用されるリレーショナル データベースでは、すべての著者情報が別のテーブルに保管されている可能性があります。author_id
などのキーは、書籍を著者にリンクするために使用されます。
BigQuery では、別の著者テーブルを作成せずに書籍と著者の関係を維持できます。代わりに、作成者の列を作成し、作成者の名、姓、生年月日などのフィールドを入れ子にします。書籍に複数の著者がいる場合は、ネストされた著者列を繰り返すことができます。
ネストされた繰り返しデータを含む列を作成するには、列のデータ型を RECORD
に設定します。RECORD
は STRUCT
として格納され、標準 SQL で STRUCT
としてアクセスできます。STRUCT
は順序付きフィールドのコンテナであり、各フィールドは型(必須)と名前(省略可)を持ちます。列をネストにするには、子フィールドを RECORD
(親)に追加します。列を繰り返すには、モードを REPEATED
に変更します。
制限事項
ネストされたスキーマと繰り返しスキーマには、次の制限事項があります。
- ネストされた繰り返しデータを読み込む場合、スキーマにネストレベルが 15 を超える
STRUCT
(RECORD
型)を含めることはできません。 - BigQuery では
STRUCT
(すなわちRECORD
)型の列がサポートされています。STRUCT
は、複数の子列を持つオブジェクトの記述に使用できる複合型です。STRUCT
列では、1 つ以上の子列をSTRUCT
型(ネストまたは埋め込みSTRUCT
と呼ばれます)として定義することもできます。STRUCTS
をネストする場合、BigQuery ではネストの深さが 15 レベルに制限されます。ネストの深さの制限は、STRUCT
がスカラーか、配列ベースであるかに依存しません。
例
サンプル スキーマ
次の例は、ネストされたデータと繰り返しデータの例を示しています。この表には人に関する情報が含まれています。これは、次のフィールドで構成されています。
id
first_name
last_name
dob
(生年月日)addresses
(ネストされた繰り返しフィールド)addresses.status
(現在または以前)addresses.address
addresses.city
addresses.state
addresses.zip
addresses.numberOfYears
(居住年数)
JSON データファイルは次のようになります。addresses 列には値の配列が含まれています([ ]
によって示される)。配列内の複数のアドレスは繰り返しデータです。各アドレス内の複数のフィールドは、ネストされたデータです。
{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]} {"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}
このテーブルのスキーマは次のようになります。
[ { "name": "id", "type": "STRING", "mode": "NULLABLE" }, { "name": "first_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "last_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "dob", "type": "DATE", "mode": "NULLABLE" }, { "name": "addresses", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "status", "type": "STRING", "mode": "NULLABLE" }, { "name": "address", "type": "STRING", "mode": "NULLABLE" }, { "name": "city", "type": "STRING", "mode": "NULLABLE" }, { "name": "state", "type": "STRING", "mode": "NULLABLE" }, { "name": "zip", "type": "STRING", "mode": "NULLABLE" }, { "name": "numberOfYears", "type": "STRING", "mode": "NULLABLE" } ] } ]
サンプルでネストされた繰り返し列を指定する
Console
GCP Console でネストされた繰り返し addresses
列を指定する手順は次のとおりです。
GCP Console で BigQuery ウェブ UI を開きます。
GCP Console に移動するナビゲーション パネルの [リソース] セクションでプロジェクトを展開し、データセットを選択します。[テーブルを作成] をクリックします。
[テーブルの作成] ページで、次の操作を行います。
- [ソース] で [空のテーブル] を選択します。
- [送信先] でデータセットを選択し、[テーブル名] フィールドにテーブル名を入力します。
[スキーマ] でフィールドを追加します。
- [名前] フィールドに「
addresses
」と入力します。 - [型] で [
RECORD
] を選択します。 [モード] で [
REPEATED
] を選択します。addresses
の右側にあるプラスアイコンをクリックして、ネストされたフィールドを追加します。- [名前] フィールドに「
status
」と入力します。このフィールドにはaddresses.
が最初から入力されていて、これがネストされたフィールドであることを示します。 - [型] で [
STRING
] を選択します。 [モード] の値は [
NULLABLE
] のままにします。上記の手順を繰り返して、
address
(NULLABLE
STRING
)、city
(NULLABLE
STRING
)、state
(NULLABLE
STRING
)、zip
(NULLABLE
STRING
)、numberOfYears
(NULLABLE
STRING
)を追加します。
- [名前] フィールドに「
- [名前] フィールドに「
また、[テキストとして編集] をクリックして、スキーマを JSON 配列として指定する方法もあります。
従来の UI
従来の BigQuery ウェブ UI でネストされた繰り返し addresses
列を指定する手順は次のとおりです。
BigQuery ウェブ UI に移動します。
ナビゲーション内のデータセット名の横にある下矢印アイコン
をクリックし、[Create new table] をクリックします。
[Create table] ページで、次の操作を行います。
- [Source Data] で、[Create from source] をクリックします。
- [Destination Table] でデータセットを選択し、[Destination table name] フィールドにテーブル名を入力します。
スキーマの場合:
- [Name] フィールドに「
addresses
」と入力します。 - [Type] で [
RECORD
] を選択します。 - [Mode] で [
REPEATED
] を選択します。 RECORD
の右側にあるプラスアイコンをクリックして、ネストされたフィールドを追加します。
- [Name] フィールドに「
status
」と入力します。このフィールドにはaddresses.
が最初から入力されていて、これがネストされたフィールドであることを示します。 - [Type] で [
STRING
] を選択します。 - [Mode] の値は [
NULLABLE
] のままにします。 - 上記の手順を繰り返して、
address
(NULLABLE
STRING
)、city
(NULLABLE
STRING
)、state
(NULLABLE
STRING
)、zip
(NULLABLE
STRING
)、numberOfYears
(NULLABLE
STRING
)を追加します。
- [Name] フィールドに「
- [Name] フィールドに「
また、[Edit as Text] をクリックして、スキーマを JSON 配列として指定する方法もあります。
BigQuery ウェブ UI でスキーマを調べると、addresses
フィールドは次のようになります。
CLI
JSON スキーマ ファイルでネストされた addresses
列と繰り返し列を指定するには、テキスト エディタを使用して次のように入力します。
[ { "name": "id", "type": "STRING", "mode": "NULLABLE" }, { "name": "first_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "last_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "dob", "type": "DATE", "mode": "NULLABLE" }, { "name": "addresses", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "status", "type": "STRING", "mode": "NULLABLE" }, { "name": "address", "type": "STRING", "mode": "NULLABLE" }, { "name": "city", "type": "STRING", "mode": "NULLABLE" }, { "name": "state", "type": "STRING", "mode": "NULLABLE" }, { "name": "zip", "type": "STRING", "mode": "NULLABLE" }, { "name": "numberOfYears", "type": "STRING", "mode": "NULLABLE" } ] } ]
JSON スキーマ ファイルを作成したら、コマンドラインでそのスキーマ ファイルを指定できます。
Go
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用にある Go 向けの手順に沿って設定を行ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。
Python
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用にある Python の設定手順に沿って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
ネストされた列と繰り返し列の変更
ネストされた列やネストされた繰り返し列をテーブルのスキーマ定義に追加した後、他の型の列と同じように列を変更できます。BigQuery は、ネストされた新しいフィールドのレコードへの追加や、ネストされたフィールドのモードの緩和など、複数のスキーマ変更をネイティブでサポートしています。詳細については、テーブル スキーマの変更をご覧ください。
また、ネストされた列と繰り返した列を含むスキーマ定義を手動で変更することもできます。詳細については、テーブル スキーマの手動変更をご覧ください。