ネストされた列と繰り返し列の指定

BigQuery は非正規化データに最適です。スタースキーマやスノーフレークスキーマなどのリレーショナルスキーマを保存するのではなく、データを非正規化して、ネストされた列と繰り返し列を利用します。ネストされた列と繰り返し列は、リレーショナル（正規化）スキーマを維持することによるパフォーマンスへの影響なしで、リレーションシップを維持できます。

UI または JSON スキーマファイルで、ネストされたデータ、またはネストされた繰り返しデータを指定できます。ネストされた列、またはネストされた列と繰り返し列を指定するには、RECORD（STRUCT）データ型を使用します。

ネストされた列と繰り返し列の指定

BigQuery では、JSON ファイル、Avro ファイル、Firestore エクスポートファイル、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

Cloud Console でネストされた繰り返し addresses 列を指定する手順は次のとおりです。

Cloud Console で BigQuery ウェブ UI を開きます。
Cloud 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 に移動します。

BigQuery ウェブ UI に移動
ナビゲーション内のデータセット名の横にある下矢印アイコンをクリックし、[Create new table] をクリックします。
[Create table] ページで、次の操作を行います。
- [Source Data] で、[Create from source] をクリックします。
- [Destination Table] でデータセットを選択し、[Destination table name] フィールドにテーブル名を入力します。
- [Schema] で、次の操作を行います。
  - [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）を追加します。
- また、[Edit as Text] をクリックして、スキーマを JSON 配列として指定する方法もあります。

BigQuery ウェブ UI でスキーマを調べると、addresses フィールドは次のようになります。

[Schema] タブのネストされたフィールド

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 のリファレンスドキュメントをご覧ください。

GitHub で表示フィードバック

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// createTableComplexSchema demonstrates creating a BigQuery table and specifying a complex schema that includes
// an array of Struct types.
func createTableComplexSchema(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}

	sampleSchema := bigquery.Schema{
		{Name: "id", Type: bigquery.StringFieldType},
		{Name: "first_name", Type: bigquery.StringFieldType},
		{Name: "last_name", Type: bigquery.StringFieldType},
		{Name: "dob", Type: bigquery.DateFieldType},
		{Name: "addresses",
			Type:     bigquery.RecordFieldType,
			Repeated: true,
			Schema: bigquery.Schema{
				{Name: "status", Type: bigquery.StringFieldType},
				{Name: "address", Type: bigquery.StringFieldType},
				{Name: "city", Type: bigquery.StringFieldType},
				{Name: "state", Type: bigquery.StringFieldType},
				{Name: "zip", Type: bigquery.StringFieldType},
				{Name: "numberOfYears", Type: bigquery.StringFieldType},
			}},
	}

	metaData := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metaData); err != nil {
		return err
	}
	fmt.Fprintf(w, "created table %s\n", tableRef.FullyQualifiedName())
	return nil
}

Node.js

このサンプルを試す前に、BigQuery クイックスタート: クライアントライブラリの使用の Node.js の設定手順を実施してください。詳細については、BigQuery Node.js API のリファレンスドキュメントをご覧ください。

GitHub で表示フィードバック

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function nestedRepeatedSchema() {
  // Creates a new table named "my_table" in "my_dataset"
  // with nested and repeated columns in schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  // const schema = [
  //   {name: 'Name', type: 'STRING', mode: 'REQUIRED'},
  //   {
  //     name: 'Addresses',
  //     type: 'RECORD',
  //     mode: 'REPEATED',
  //     fields: [
  //       {name: 'Address', type: 'STRING'},
  //       {name: 'City', type: 'STRING'},
  //       {name: 'State', type: 'STRING'},
  //       {name: 'Zip', type: 'STRING'},
  //     ],
  //   },
  // ];

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);
}

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアントライブラリの使用の Python の設定手順を実施してください。詳細については、BigQuery Python API のリファレンスドキュメントをご覧ください。

GitHub で表示フィードバック

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')

schema = [
    bigquery.SchemaField("id", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("first_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("last_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("dob", "DATE", mode="NULLABLE"),
    bigquery.SchemaField(
        "addresses",
        "RECORD",
        mode="REPEATED",
        fields=[
            bigquery.SchemaField("status", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("address", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("city", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("state", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("zip", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("numberOfYears", "STRING", mode="NULLABLE"),
        ],
    ),
]
table_ref = dataset_ref.table("my_table")
table = bigquery.Table(table_ref, schema=schema)
table = client.create_table(table)  # API request

print("Created table {}".format(table.full_table_id))

ネストされた繰り返し列の変更

ネストされた列やネストされた繰り返し列をテーブルのスキーマ定義に追加した後、他の型の列と同じように列を変更できます。BigQuery は、ネストされた新しいフィールドのレコードへの追加や、ネストされたフィールドのモードの緩和など、複数のスキーマ変更をネイティブにサポートしています。詳細については、テーブルスキーマの変更をご覧ください。

また、ネストされた繰り返し列を含むスキーマ定義を手動で変更することもできます。詳細については、テーブルスキーマの手動変更をご覧ください。

このページは役立ちましたか？評価をお願いいたします。

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

最終更新日: 1月 16, 2020

ネストされた列と繰り返し列の指定

ネストされた列と繰り返し列の指定

制限事項

例

サンプル スキーマ

サンプルでネストされた繰り返し列を指定する

Console

従来の UI

CLI

Go

Node.js

Python

ネストされた繰り返し列の変更

フィードバックを送信...

サンプルスキーマ