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

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"
            }
        ]
    }
]

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

ウェブ 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] タブのネストされたフィールド

コマンドライン

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 のサポートページをご覧ください。