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

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

ネストされたデータと繰り返しデータは Cloud Console または 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] ページを開きます。

[BigQuery] ページに移動
[エクスプローラ] パネルでプロジェクトを展開し、データセットを選択します。

注: デフォルトでは、プレビューリリースの Cloud Console が表示されます。[PREVIEW 機能を非表示] をクリックして一般提供の Cloud Console に移動した場合は、ナビゲーションパネルの [リソース] セクションでプロジェクトを展開し、データセットを選択します。
詳細ビューで [テーブルを作成] をクリックします。
[テーブルの作成] ページで次の操作を行います。
- [ソース] で [空のテーブル] を選択します。
- [送信先] でデータセットを選択し、[テーブル名] フィールドにテーブル名を入力します。
- [スキーマ] でフィールドを追加します。
  - [名前] フィールドに「addresses」と入力します。
  - [Type] で [RECORD] を選択します。
  - [モード] で [REPEATED] を選択します。
  - addresses の右側にあるプラスアイコンをクリックして、ネストされたフィールドを追加します。
    - [名前] フィールドに「status」と入力します。このフィールドには addresses. が事前に入力され、これがネストされたフィールドであることを示します。
    - [型] で [STRING] を選択します。
    - [モード] の値は [NULLABLE] のままにします。
    - 上記の手順を繰り返して、address（NULLABLE STRING）、city（NULLABLE STRING）、state（NULLABLE STRING）、zip（NULLABLE STRING）、numberOfYears（NULLABLE STRING）を追加します。
- また、[テキストとして編集] をクリックして、スキーマを JSON 配列として指定する方法もあります。

bq

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)
	}
	defer client.Close()

	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
}

Java

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

GitHub で表示フィードバック

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Field.Mode;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

public class NestedRepeatedSchema {

  public static void runNestedRepeatedSchema() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createTableWithNestedRepeatedSchema(datasetName, tableName);
  }

  public static void createTableWithNestedRepeatedSchema(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      Schema schema =
          Schema.of(
              Field.of("id", StandardSQLTypeName.STRING),
              Field.of("first_name", StandardSQLTypeName.STRING),
              Field.of("last_name", StandardSQLTypeName.STRING),
              Field.of("dob", StandardSQLTypeName.DATE),
              // create the nested and repeated field
              Field.newBuilder(
                      "addresses",
                      StandardSQLTypeName.STRUCT,
                      Field.of("status", StandardSQLTypeName.STRING),
                      Field.of("address", StandardSQLTypeName.STRING),
                      Field.of("city", StandardSQLTypeName.STRING),
                      Field.of("state", StandardSQLTypeName.STRING),
                      Field.of("zip", StandardSQLTypeName.STRING),
                      Field.of("numberOfYears", StandardSQLTypeName.STRING))
                  .setMode(Mode.REPEATED)
                  .build());

      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Table with nested and repeated schema created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

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()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, '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 は、ネストされた新しいフィールドのレコードへの追加や、ネストされたフィールドのモードの緩和など、複数のスキーマ変更をネイティブにサポートしています。詳細については、テーブルスキーマの変更をご覧ください。

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

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