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

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

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

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

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

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

  1. GCP Console で BigQuery ウェブ UI を開きます。
    GCP Console に移動する

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

    テーブルを作成

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

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

      • [名前] フィールドに「addresses」と入力します。
      • [] で [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] フィールドにテーブル名を入力します。
    • スキーマの場合:

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

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

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

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

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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.Printf("created table %s\n", tableRef.FullyQualifiedName())

Python

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

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

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

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

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

ご不明な点がありましたら、Google のサポートページをご覧ください。