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

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

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

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

BigQuery では、JSON ファイル、Avro ファイル、Firestore エクスポート ファイル、Datastore エクスポート ファイルなど、オブジェクトベースのスキーマをサポートするソース形式からネストされた繰り返しデータを読み込むことができます。

たとえば、図書館の書籍の追跡に使用されるリレーショナル データベースでは、すべての著者情報が別の表に保管されている可能性があります。author_id などのキーは、書籍を著者にリンクするために使用されます。

BigQuery では、別の著者テーブルを作成せずに書籍と著者の関係を維持できます。代わりに、著者の列を作成し、著者の名、姓、生年月日などのフィールドを入れ子にします。書籍に複数の著者がいる場合は、ネストされた著者列を繰り返すことができます。

ネストされた繰り返しデータを含む列を作成するには、列のデータ型を RECORD に設定します。RECORDSTRUCT として格納され、標準 SQL で STRUCT としてアクセスできます。STRUCT は順序付きフィールドのコンテナであり、各フィールドは型(必須)と名前(省略可)を持ちます。列をネストにするには、子フィールドを RECORD(親)に追加します。列を繰り返すには、モードを REPEATED に変更します。

制限事項

ネストされた繰り返しスキーマには、次の制限事項があります。

ネストされた繰り返しデータを読み込む場合、スキーマにネストレベルが 15 を超える STRUCTRECORD 型)を含めることはできません。
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 列を指定する手順は次のとおりです。

  1. Cloud Console で [BigQuery] ページを開きます。

    [BigQuery] ページに移動

  2. ナビゲーション パネルの [リソース] セクションでプロジェクトを展開し、データセットを選択します。[テーブルを作成] をクリックします。

    テーブルの作成

  3. [テーブルの作成] ページで、次の操作を行います。

    • [ソース] で [空のテーブル] を選択します。
    • [送信先] でデータセットを選択し、[テーブル名] フィールドにテーブル名を入力します。
    • [スキーマ] でフィールドを追加します。

      • [名前] フィールドに「addresses」と入力します。
      • [Type] で [RECORD] を選択します。
      • [モード] で [REPEATED] を選択します。

        アドレス スキーマ

      • addresses の右側にあるプラスアイコンをクリックして、ネストされたフィールドを追加します。

        ネストボタン

        • [名前] フィールドに「status」と入力します。このフィールドには addresses. が事前に入力され、これがネストされたフィールドであることを示します。
        • [] で [STRING] を選択します。
        • [モード] の値は [NULLABLE] のままにします。

          ステータス スキーマ

        • 上記の手順を繰り返して、addressNULLABLE STRING)、cityNULLABLE STRING)、stateNULLABLE STRING)、zipNULLABLE STRING)、numberOfYearsNULLABLE STRING)を追加します。

    • また、[テキストとして編集] をクリックして、スキーマを JSON 配列として指定する方法もあります。

従来の UI

従来の BigQuery ウェブ UI でネストされた繰り返し addresses 列を指定する手順は次のとおりです。

  1. BigQuery ウェブ UI に移動します。

    BigQuery ウェブ UI に移動

  2. ナビゲーション内のデータセット名の横にある下矢印アイコン 下矢印アイコン をクリックし、[Create new table] をクリックします。

  3. [Create table] ページで、次の操作を行います。

    • [Source Data] で、[Create from source] をクリックします。
    • [Destination Table] でデータセットを選択し、[Destination table name] フィールドにテーブル名を入力します。
    • [Schema] で、次の操作を行います。

      • [Name] フィールドに「addresses」と入力します。
      • [Type] で [RECORD] を選択します。
      • [Mode] で [REPEATED] を選択します。
      • RECORD の右側にあるプラスアイコン プラスアイコン をクリックして、ネストされたフィールドを追加します。

        • [Name] フィールドに「status」と入力します。このフィールドには addresses. が事前に入力され、これがネストされたフィールドであることを示します。
        • [] で [STRING] を選択します。
        • [Mode] の値は [NULLABLE] のままにします。
        • 上記の手順を繰り返して、addressNULLABLE STRING)、cityNULLABLE STRING)、stateNULLABLE STRING)、zipNULLABLE STRING)、numberOfYearsNULLABLE STRING)を追加します。

        UI のネストされたフィールド

    • また、[Edit as Text] をクリックして、スキーマを JSON 配列として指定する方法もあります。

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

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

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

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

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

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

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

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