コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

テーブル スキーマの変更

このドキュメントでは、既存の BigQuery テーブルのスキーマ定義を変更する方法について説明します。

このドキュメントで説明するスキーマの変更は、SQL データ定義言語(DDL)ステートメントを使用して行えます。これらのステートメントには課金されません。

このページで説明されているすべての方法でテーブル スキーマを変更できます。テーブル データを Cloud Storage にエクスポートし、変更されたスキーマ定義を使用して新しいテーブルにデータを読み込みます。BigQuery の読み込みジョブとエクスポート ジョブは無料ですが、エクスポートしたデータを Cloud Storage に保存する場合は料金が発生します。以下のセクションでは、さまざまなタイプのスキーマ変更を行うその他の方法について説明します。

列の追加

次のいずれかの方法で、既存のテーブルのスキーマ定義に列を追加できます。

  • 新しい空の列を追加します。
  • 読み込みジョブまたはクエリジョブでテーブルを上書きします。
  • 読み込みジョブまたはクエリジョブを使用してテーブルにデータを追加します。

追加する列は、BigQuery の列名の規則に準拠している必要があります。スキーマ コンポーネントの作成の詳細については、スキーマの指定をご覧ください。

空の列を追加する

既存のテーブル スキーマに新しい列を追加する場合、その列は NULLABLE または REPEATED である必要があります。REQUIRED 列を既存のテーブル スキーマに追加することはできません。API または bq コマンドライン ツールで既存のテーブル スキーマに REQUIRED 列を追加すると、エラーが発生します。REQUIRED 列を追加できるのは、データの読み込み時にテーブルを作成する場合、またはスキーマ定義を持つ空のテーブルを作成する場合だけです。

テーブルのスキーマ定義に空の列を追加するには:

コンソール

  1. Google Cloud Console で [BigQuery] ページに移動します。

    BigQuery に移動

  2. [エクスプローラ] パネルで、プロジェクトとデータセットを開いて、テーブルを選択します。

  3. 詳細パネルで [スキーマ] タブをクリックします。

  4. [スキーマを編集] をクリックします。このボタンを表示するには、スクロールが必要な場合があります。

  5. [現在のスキーマ] ページの [新しいフィールド] で、[フィールドを追加] をクリックします。

    • [名前] に列名を入力します。
    • [タイプ] で、データ型を選択します。
    • [モード] で、NULLABLE または REPEATED を選択します。
  6. 列の追加が完了したら、[保存] をクリックします。

SQL

ALTER TABLE ADD COLUMN DDL ステートメントを使用します。

  1. Google Cloud コンソールで [BigQuery] ページに移動します。

    BigQuery に移動

  2. クエリエディタで次のステートメントを入力します。

    ALTER TABLE mydataset.mytable
    ADD COLUMN new_column STRING;
    

  3. [ 実行] をクリックします。

クエリの実行方法については、インタラクティブ クエリの実行をご覧ください。

bq

bq update コマンドを発行し、JSON スキーマ ファイルを指定します。更新するテーブルがデフォルト以外のプロジェクトにある場合は、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。

bq update project_id:dataset.table schema

ここで

  • project_id はプロジェクト ID です。
  • dataset は、更新しているテーブルを含むデータセットの名前です。
  • table は更新するテーブルの名前です。
  • schema は、ローカルマシン上の JSON スキーマ ファイルのパスです。

bq コマンドライン ツールを使用してスキーマを指定する場合、RECORDSTRUCT)型および列の説明を含めることはできず、列のモードも指定できません。すべてのモードはデフォルトの NULLABLE になります。

インライン スキーマ定義を使用して列を追加しようとする場合は、新しい列を含めてスキーマ定義全体を指定する必要があります。インライン スキーマ定義を使用して列モードを指定できないため、更新を行うと、既存の REPEATED 列が NULLABLE に変更され、次のエラーが発生します。BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from REPEATED to NULLABLE.

bq コマンドライン ツールを使用して既存のテーブルに列を追加する場合に推奨される方法は、JSON スキーマ ファイルを指定することです。

JSON スキーマ ファイルを使用して、テーブルのスキーマに列を追加するには:

  1. まず、--schema フラグを指定した bq show コマンドを発行して、既存のテーブル スキーマをファイルに書き込みます。更新するテーブルがデフォルト以外のプロジェクトにある場合は、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    ここで

    • project_id はプロジェクト ID です。
    • dataset は、更新しているテーブルを含むデータセットの名前です。
    • table は更新するテーブルの名前です。
    • schema_file は、ローカルマシンに書き込まれるスキーマ定義ファイルです。

    たとえば、mydataset.mytable のスキーマ定義をファイルに書き込むには、次のコマンドを入力します。mydataset.mytable はデフォルト プロジェクトにあります。

       bq show \
       --schema \
       --format=prettyjson \
       mydataset.mytable > /tmp/myschema.json
    
  2. スキーマ ファイルをテキスト エディタで開きます。スキーマは次のようになっています。

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. スキーマ定義の末尾に新しい列を追加します。新しい列を配列内の別の場所に追加しようとすると、BigQuery error in update operation: Precondition Failed というエラーが返されます。

    JSON ファイルを使用して、新しい列の説明、NULLABLE または REPEATED モード、RECORD 型を指定できます。たとえば、前の手順のスキーマ定義を使用すると、新しい JSON 配列は次のようになります。この例では、新しい NULLABLE 列が column4 という名前で追加されています。column4 に説明が含まれています。

      [
        {
          "mode": "REQUIRED",
          "name": "column1",
          "type": "STRING"
        },
        {
          "mode": "REQUIRED",
          "name": "column2",
          "type": "FLOAT"
        },
        {
          "mode": "REPEATED",
          "name": "column3",
          "type": "STRING"
        },
        {
          "description": "my new column",
          "mode": "NULLABLE",
          "name": "column4",
          "type": "STRING"
        }
      ]
      

    JSON スキーマ ファイルの操作の詳細については、JSON スキーマ ファイルの指定をご覧ください。

  4. スキーマ ファイルを更新したら、次のコマンドを発行してテーブルのスキーマを更新します。更新するテーブルがデフォルト以外のプロジェクトにある場合は、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。

    bq update project_id:dataset.table schema
    

    ここで

    • project_id はプロジェクト ID です。
    • dataset は、更新しているテーブルを含むデータセットの名前です。
    • table は更新するテーブルの名前です。
    • schema は、ローカルマシン上の JSON スキーマ ファイルのパスです。

    たとえば、次のコマンドを入力すると、デフォルト プロジェクトにある mydataset.mytable のスキーマ定義が更新されます。ローカルマシン上にあるスキーマ ファイルへのパスは /tmp/myschema.json です。

    bq update mydataset.mytable /tmp/myschema.json
    

API

tables.patch メソッドを呼び出して、schema プロパティを使用して空の列をスキーマ定義に追加します。tables.update メソッドはテーブル リソース全体を置き換えるため、tables.patch メソッドの方が適切です。

Go

import (
	"context"
	"fmt"

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

// updateTableAddColumn demonstrates modifying the schema of a table to append an additional column.
func updateTableAddColumn(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	newSchema := append(meta.Schema,
		&bigquery.FieldSchema{Name: "phone", Type: bigquery.StringFieldType},
	)
	update := bigquery.TableMetadataToUpdate{
		Schema: newSchema,
	}
	if _, err := tableRef.Update(ctx, update, meta.ETag); err != nil {
		return err
	}
	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.FieldList;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.Table;
import java.util.ArrayList;
import java.util.List;

public class AddEmptyColumn {

  public static void runAddEmptyColumn() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableId = "MY_TABLE_NAME";
    String newColumnName = "NEW_COLUMN_NAME";
    addEmptyColumn(newColumnName, datasetName, tableId);
  }

  public static void addEmptyColumn(String newColumnName, String datasetName, String tableId) {
    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();

      Table table = bigquery.getTable(datasetName, tableId);
      Schema schema = table.getDefinition().getSchema();
      FieldList fields = schema.getFields();

      // Create the new field/column
      Field newField = Field.of(newColumnName, LegacySQLTypeName.STRING);

      // Create a new schema adding the current fields, plus the new one
      List<Field> fieldList = new ArrayList<Field>();
      fields.forEach(fieldList::add);
      fieldList.add(newField);
      Schema newSchema = Schema.of(fieldList);

      // Update the table with the new schema
      Table updatedTable =
          table.toBuilder().setDefinition(StandardTableDefinition.of(newSchema)).build();
      updatedTable.update();
      System.out.println("Empty column successfully added to table");
    } catch (BigQueryException e) {
      System.out.println("Empty column was not added. \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 addEmptyColumn() {
  // Adds an empty column to the schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';
  const column = {name: 'size', type: 'STRING'};

  // Retrieve current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Update table schema
  const schema = metadata.schema;
  const new_schema = schema;
  new_schema.fields.push(column);
  metadata.schema = new_schema;

  const [result] = await table.setMetadata(metadata);
  console.log(result.schema.fields);
}

Python

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

新しい SchemaField オブジェクトを Table.schema のコピーに付加し、Table.schema プロパティの値を更新後のスキーマで置き換えます。
from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table
#                  to add an empty column.
# table_id = "your-project.your_dataset.your_table_name"

table = client.get_table(table_id)  # Make an API request.

original_schema = table.schema
new_schema = original_schema[:]  # Creates a copy of the schema.
new_schema.append(bigquery.SchemaField("phone", "STRING"))

table.schema = new_schema
table = client.update_table(table, ["schema"])  # Make an API request.

if len(table.schema) == len(original_schema) + 1 == len(new_schema):
    print("A new column has been added.")
else:
    print("The column has not been added.")

ネストされた列を RECORD 列に追加する

新しい列をテーブルのスキーマに追加するだけでなく、ネストされた列を RECORD 列に追加することもできます。ネストされた列を追加する方法は、新しい列を追加する場合と非常によく似ています。

コンソール

ネストされた新しいフィールドを既存の RECORD 列に追加することは、Google Cloud コンソールではサポートされていません。

SQL

SQL DDL ステートメントを使用して新しくネストされたフィールドを既存の RECORD 列に追加できません。

bq

bq update コマンドを発行して、ネストされたフィールドを既存の RECORD 列のスキーマ定義に追加する JSON スキーマ ファイルを指定します。更新するテーブルがデフォルト以外のプロジェクトにある場合は、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。

bq update project_id:dataset.table schema

ここで

  • project_id はプロジェクト ID です。
  • dataset は、更新しているテーブルを含むデータセットの名前です。
  • table は更新するテーブルの名前です。
  • schema は、ローカルマシン上の JSON スキーマ ファイルのパスです。

bq コマンドライン ツールを使用してスキーマを指定する場合、RECORDSTRUCT)型および列の説明を含めることはできず、列のモードも指定できません。すべてのモードはデフォルトの NULLABLE になります。そのため、ネストされた新しい列を RECORD に追加する場合は、JSON スキーマ ファイルを指定する必要があります。

JSON スキーマ ファイルを使用して、ネストされた列を RECORD に追加するには:

  1. まず、--schema フラグを指定した bq show コマンドを発行して、既存のテーブル スキーマをファイルに書き込みます。更新するテーブルがデフォルト以外のプロジェクトにある場合は、project_id:dataset.table の形式でプロジェクト ID をデータセット名に追加します。

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    ここで

    • project_id はプロジェクト ID です。
    • dataset は、更新しているテーブルを含むデータセットの名前です。
    • table は更新するテーブルの名前です。
    • schema_file は、ローカルマシンに書き込まれるスキーマ定義ファイルです。

    たとえば、mydataset.mytable のスキーマ定義をファイルに書き込むには、次のコマンドを入力します。mydataset.mytable はデフォルト プロジェクトにあります。

    bq show \
    --schema \
    --format=prettyjson \
    mydataset.mytable > /tmp/myschema.json
    
  2. スキーマ ファイルをテキスト エディタで開きます。スキーマは次のようになっています。この例では、column3 はネストされた繰り返し列です。ネストされた列は nested1nested2 です。fields 配列には、column3 内にネストされたフィールドが入ります。

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "fields": [
          {
            "mode": "NULLABLE",
            "name": "nested1",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "nested2",
            "type": "STRING"
          }
        ],
        "mode": "REPEATED",
        "name": "column3",
        "type": "RECORD"
      }
    ]
    
  3. 新しくネストされた列を fields 配列の末尾に追加します。この例では、nested3 が新しくネストされた列です。

      [
        {
          "mode": "REQUIRED",
          "name": "column1",
          "type": "STRING"
        },
        {
          "mode": "REQUIRED",
          "name": "column2",
          "type": "FLOAT"
        },
        {
          "fields": [
            {
              "mode": "NULLABLE",
              "name": "nested1",
              "type": "STRING"
            },
            {
              "mode": "NULLABLE",
              "name": "nested2",
              "type": "STRING"
            },
            {
              "mode": "NULLABLE",
              "name": "nested3",
              "type": "STRING"
            }
          ],
          "mode": "REPEATED",
          "name": "column3",
          "type": "RECORD"
        }
      ]
      

    JSON スキーマ ファイルの操作の詳細については、JSON スキーマ ファイルの指定をご覧ください。

  4. スキーマ ファイルを更新したら、次のコマンドを発行してテーブルのスキーマを更新します。更新するテーブルがデフォルト以外のプロジェクトにある場合は、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。

    bq update project_id:dataset.table schema
    

    ここで

    • project_id はプロジェクト ID です。
    • dataset は、更新しているテーブルを含むデータセットの名前です。
    • table は更新するテーブルの名前です。
    • schema は、ローカルマシン上の JSON スキーマ ファイルのパスです。

    たとえば、次のコマンドを入力すると、デフォルト プロジェクトにある mydataset.mytable のスキーマ定義が更新されます。ローカルマシン上にあるスキーマ ファイルへのパスは /tmp/myschema.json です。

    bq update mydataset.mytable /tmp/myschema.json
    

API

tables.patch メソッドを呼び出して、schema プロパティを使用してネストされた列をスキーマ定義に追加します。tables.update メソッドはテーブル リソース全体を置き換えるため、tables.patch メソッドの方が適切です。

データを上書きまたは追記するときの列の追加

既存のテーブルにデータを読み込んで上書きする際、そのテーブルに新しい列を追加できます。上書きする既存のテーブルのスキーマは、読み込んでいるデータのスキーマを使用して上書きされます。読み込みジョブを使用してテーブルを上書きする方法については、データの形式のドキュメントをご覧ください。

読み込み追記ジョブで列を追加する

読み込みジョブでデータをテーブルに追記するときに、テーブルに列を追加できます。新しいスキーマは次のいずれかによって決定されます。

  • 自動検出(CSV ファイルおよび JSON ファイルの場合)
  • JSON スキーマ ファイルで指定されたスキーマ(CSV ファイルおよび JSON ファイルの場合)
  • Avro、ORC、Parquet および Datastore のエクスポート ファイルの自己記述型ソースデータ

JSON ファイルでスキーマを指定する場合は、その中で新しい列を定義する必要があります。新しい列の定義がない場合は、データを追記しようとするとエラーが返されます。

追記オペレーション中に新しい列を追加する場合、既存の行の新しい列の値は NULL に設定されます。

読み込みジョブ中にテーブルにデータを追記するときに新しい列を追加するには、次のいずれかのオプションを使用します。

bq

bq load コマンドを使用してデータを読み込み、--noreplace フラグを指定して、データを既存のテーブルに追記していることを示します。

付加するデータが CSV 形式または改行区切りの JSON 形式である場合は、--autodetect フラグを指定してスキーマの自動検出を使用するか、JSON スキーマ ファイルでスキーマを指定します。追加された列は、Avro または Datastore エクスポート ファイルから自動的に推定されます。

--schema_update_option フラグを ALLOW_FIELD_ADDITION に設定して、追記しているデータに新しい列が含まれていることを示します。

追記するテーブルがデフォルト以外のプロジェクトのデータセットにある場合は、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。

(省略可)--location フラグを指定して、その値をロケーションに設定します。

load コマンドを次のように入力します。

bq --location=location load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

ここで

  • location はロケーションの名前です。--location フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値を asia-northeast1 に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。
  • format は、NEWLINE_DELIMITED_JSONCSVAVROPARQUETORC または DATASTORE_BACKUP です。
  • project_id はプロジェクト ID です。
  • dataset は、テーブルを含むデータセットの名前です。
  • table は、追記するテーブルの名前です。
  • path_to_source は、完全修飾の Cloud Storage URI、URI のカンマ区切りのリスト、またはローカルマシン上のデータファイルのパスです。
  • schema は、ローカル JSON スキーマ ファイルのパスです。--autodetect を指定していない場合、スキーマ ファイルは CSV ファイルおよび JSON ファイルに対してのみ必要です。Avro と Datastore のスキーマはソースデータから推定されます。

例:

次のコマンドを入力して、ローカル Avro データファイル /tmp/mydata.avromydataset.mytable に、読み込みジョブを使用して追記します。スキーマは Avro データから自動的に推定できるため、--autodetect フラグを使用する必要はありません。mydataset はデフォルト プロジェクトにあります。

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=AVRO \
mydataset.mytable \
/tmp/mydata.avro

次のコマンドを入力して、Cloud Storage の改行区切りの JSON データファイルを mydataset.mytableに、読み込みジョブを使用して追記します。--autodetect フラグは、新しい列を検出するために使用します。mydataset はデフォルト プロジェクトにあります。

bq load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json

次のコマンドを入力して、Cloud Storage の改行区切りの JSON データファイルを mydataset.mytableに、読み込みジョブを使用して追記します。新しい列を含むスキーマは、ローカルの JSON スキーマ ファイル /tmp/myschema.json で定義されています。mydataset はデフォルト プロジェクトではなく myotherproject にあります。

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
myotherproject:mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

API

jobs.insert メソッドを呼び出します。load ジョブを構成し、次のプロパティを設定します。

  • sourceUris プロパティを使用して、Cloud Storage 内のデータを参照します。
  • sourceFormat プロパティを設定して、データ形式を指定します。
  • schema プロパティでスキーマを指定します。
  • schemaUpdateOptions プロパティを使用して、スキーマ更新オプションを指定します。
  • writeDisposition プロパティを使用して、宛先テーブルの書き込み処理を WRITE_APPEND に設定します。

Go

import (
	"context"
	"fmt"
	"os"

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

// createTableAndWidenLoad demonstrates augmenting a table's schema to add a new column via a load job.
func createTableAndWidenLoad(projectID, datasetID, tableID, filename string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	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: "full_name", Type: bigquery.StringFieldType},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, import data from a local file, but specify field additions are allowed.
	// Because the data has a second column (age), the schema is amended as part of
	// the load.
	f, err := os.Open(filename)
	if err != nil {
		return err
	}
	source := bigquery.NewReaderSource(f)
	source.AutoDetect = true   // Allow BigQuery to determine schema.
	source.SkipLeadingRows = 1 // CSV has a single header line.

	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)
	loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	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.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.TableId;
import com.google.common.collect.ImmutableList;
import java.util.UUID;

public class AddColumnLoadAppend {

  public static void runAddColumnLoadAppend() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    addColumnLoadAppend(datasetName, tableName, sourceUri);
  }

  public static void addColumnLoadAppend(String datasetName, String tableName, String sourceUri)
      throws Exception {
    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);

      // Add a new column to a BigQuery table while appending rows via a load job.
      // 'REQUIRED' fields cannot  be added to an existing schema, so the additional column must be
      // 'NULLABLE'.
      Schema newSchema =
          Schema.of(
              Field.newBuilder("name", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.REQUIRED)
                  .build(),
              // Adding below additional column during the load job
              Field.newBuilder("post_abbr", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build());

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setSchema(newSchema)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_ADDITION))
              .build();

      // Create a job ID so that we can safely retry.
      JobId jobId = JobId.of(UUID.randomUUID().toString());
      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).setJobId(jobId).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Column successfully added during load append job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

Node.js

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

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate client
const bigquery = new BigQuery();

async function addColumnLoadAppend() {
  // Adds a new column to a BigQuery table while appending rows via a load job.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const fileName = '/path/to/file.csv';
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // In this example, the existing table contains only the 'Name', 'Age',
  // & 'Weight' columns. 'REQUIRED' fields cannot  be added to an existing
  // schema, so the additional column must be 'NULLABLE'.
  const schema = 'Name:STRING, Age:INTEGER, Weight:FLOAT, IsMagic:BOOLEAN';

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // Set load job options
  const options = {
    schema: schema,
    schemaUpdateOptions: ['ALLOW_FIELD_ADDITION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
  };

  // Load data from a local file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(fileName, options);

  console.log(`Job ${job.id} completed.`);
  console.log(`New Schema:`);
  console.log(job.configuration.load.schema.fields);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

Python

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

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, 'my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the length of the schema
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
print("Table {} contains {} columns.".format(table_id, len(table.schema)))

# Configures the load job to append the data to the destination table,
# allowing field addition
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION
]
# In this example, the existing table contains only the 'full_name' column.
# 'REQUIRED' fields cannot be added to an existing schema, so the
# additional column must be 'NULLABLE'.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated length of the schema
table = client.get_table(table)
print("Table {} now contains {} columns.".format(table_id, len(table.schema)))

クエリ追記ジョブで列を追加する

クエリ結果をテーブルに追記するときに列をテーブルに追加できます。

クエリジョブで追記オペレーションを使用して列を追加すると、クエリ結果のスキーマが使用されて宛先テーブルのスキーマが更新されます。ある場所のテーブルにクエリを実行して、別の場所のテーブルに結果を書き込むことはできません。

クエリジョブ中にテーブルにデータを追記するときに新しい列を追加するには、次のいずれかのオプションを選択します。

bq

bq query コマンドを使用してデータに対するクエリを実行し、--destination_table フラグを指定してどのテーブルを追記しているかを示します。

クエリ結果を既存の宛先テーブルに追記していることを指定するには、--append_table フラグを指定します。

--schema_update_option フラグを ALLOW_FIELD_ADDITION に設定して、追記しているクエリ結果に新しい列が含まれていることを示します。

Google 標準 SQL 構文をクエリで使用するには、use_legacy_sql=false フラグを指定します。

追記するテーブルがデフォルト以外のプロジェクトのデータセットにある場合は、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。クエリを実行するテーブルと宛先テーブルは同じ場所にある必要があります。

(省略可)--location フラグを指定して、その値をロケーションに設定します。

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'query'

ここで

  • location はロケーションの名前です。--location フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値を asia-northeast1 に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。クエリ結果は、別のロケーションのテーブルに追記できません。
  • project_id はプロジェクト ID です。
  • dataset は、追記するテーブルが含まれているデータセットの名前です。
  • table は、追記するテーブルの名前です。
  • query は、Google 標準 SQL 構文で記述したクエリです。

例:

次のコマンドを入力して、デフォルト プロジェクトで mydataset.mytable のクエリを実行したり、クエリ結果をmydataset.mytable2 に (デフォルト プロジェクトでも同様)追記したりできます。

bq query \
--destination_table mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

次のコマンドを入力して、デフォルト プロジェクトで mydataset.mytable のクエリを実行できます。また、クエリ結果を myotherprojectmydataset.mytable2 に追記することもできます。

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

jobs.insert メソッドを呼び出します。query ジョブを構成し、次のプロパティを設定します。

  • destinationTable プロパティを使用して宛先テーブルを指定します。
  • writeDisposition プロパティを使用して、宛先テーブルの書き込み処理を WRITE_APPEND に設定します。
  • schemaUpdateOptions プロパティを使用して、スキーマ更新オプションを指定します。
  • query プロパティを使用して、Google 標準 SQL クエリを指定します。

Go

import (
	"context"
	"fmt"

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

// createTableAndWidenQuery demonstrates how the schema of a table can be modified to add columns by appending
// query results that include the new columns.
func createTableAndWidenQuery(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	// First, we create a sample table.
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	original := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, original); err != nil {
		return err
	}
	// Our table has two columns.  We'll introduce a new favorite_color column via
	// a subsequent query that appends to the table.
	q := client.Query("SELECT \"Timmy\" as full_name, 85 as age, \"Blue\" as favorite_color")
	q.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
	q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
	q.WriteDisposition = bigquery.WriteAppend
	q.Location = "US"
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	_, err = job.Wait(ctx)
	if err != nil {
		return err
	}
	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.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableList;

public class RelaxTableQuery {

  public static void runRelaxTableQuery() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    relaxTableQuery(projectId, datasetName, tableName);
  }

  // To relax all columns in a destination table when you append data to it during a query job
  public static void relaxTableQuery(String projectId, String datasetName, String tableName)
      throws Exception {
    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);

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query = "SELECT word FROM " + sourceTable + " WHERE word like '%is%'";

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // Use standard SQL syntax for queries.
              // See: https://cloud.google.com/bigquery/sql-reference/
              .setUseLegacySql(false)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setDestinationTable(tableId)
              .build();

      Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).build());

      queryJob = queryJob.waitFor();

      // Check for errors
      if (queryJob == null) {
        throw new Exception("Job no longer exists");
      } else if (queryJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(queryJob.getStatus().getError().toString());
      }

      // Get the results.
      TableResult results = queryJob.getQueryResults();

      // Print all pages of the results.
      results
          .iterateAll()
          .forEach(
              rows -> {
                rows.forEach(row -> System.out.println("row: " + row.toString()));
              });

      System.out.println("Successfully relaxed all columns in destination table during query job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Columns not relaxed during query job \n" + e.toString());
    }
  }
}

Node.js

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

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate client
const bigquery = new BigQuery();

async function addColumnQueryAppend() {
  // Adds a new column to a BigQuery table while appending rows via a query job.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // In this example, the existing table contains only the 'name' column.
  // 'REQUIRED' fields cannot  be added to an existing schema,
  // so the additional column must be 'NULLABLE'.
  const query = `SELECT name, year
    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
    WHERE state = 'TX'
    LIMIT 10`;

  // Set load job options
  const options = {
    query: query,
    schemaUpdateOptions: ['ALLOW_FIELD_ADDITION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
  };

  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();
  console.log(`Job ${job.id} completed.`);

  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

Python

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

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the destination table.
# table_id = "your-project.your_dataset.your_table_name"

# Retrieves the destination table and checks the length of the schema.
table = client.get_table(table_id)  # Make an API request.
print("Table {} contains {} columns".format(table_id, len(table.schema)))

# Configures the query to append the results to a destination table,
# allowing field addition.
job_config = bigquery.QueryJobConfig(
    destination=table_id,
    schema_update_options=[bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION],
    write_disposition=bigquery.WriteDisposition.WRITE_APPEND,
)

# Start the query, passing in the extra configuration.
query_job = client.query(
    # In this example, the existing table contains only the 'full_name' and
    # 'age' columns, while the results of this query will contain an
    # additional 'favorite_color' column.
    'SELECT "Timmy" as full_name, 85 as age, "Blue" as favorite_color;',
    job_config=job_config,
)  # Make an API request.
query_job.result()  # Wait for the job to complete.

# Checks the updated length of the schema.
table = client.get_table(table_id)  # Make an API request.
print("Table {} now contains {} columns".format(table_id, len(table.schema)))

列の名前を変更する

テーブルの列の名前を変更するには、ALTER TABLE RENAME COLUMN DDL ステートメントを使用します。次の例では、mytable の列 old_name の名前を new_name に変更しています。

ALTER TABLE mydataset.mytable
  RENAME COLUMN old_name TO new_name;

列のデータ型を変更する

コンソール、bq コマンドライン ツール、BigQuery API では列のデータ型を変更できません。列の新しいデータ型を指定するスキーマを適用してテーブルを更新しようとすると、エラーが返されます。

列のデータ型を強制変換する

列のデータ型を強制変換型に変更するには、ALTER COLUMN SET DATA TYPE DDL ステートメントを使用します。次の例では、INT64 型の列を持つテーブルを作成し、NUMERIC 型に更新します。

CREATE TABLE mydataset.mytable(c1 INT64);

ALTER TABLE mydataset.mytable
ALTER COLUMN c1 SET DATA TYPE NUMERIC;

列のデータ型をキャストする

列のデータ型をキャスト可能型に変更するには、SQL クエリを使用してテーブルデータを選択し、関連するキャストを行い、テーブルを上書きします。非常に大きなテーブルの場合、テーブルを完全にスキャンする必要があるため、キャストと上書きはおすすめしません。

次の例は、mydataset.mytablecolumn_twocolumn_three のすべてのデータを選択し、column_oneDATE から STRING にキャストする SQL クエリを示しています。クエリ結果を使用して、既存のテーブルを上書きします。上書きされたテーブルには、column_oneSTRING データ型として保存されます。

CAST を使用したときに、BigQuery がキャストできない場合はクエリが失敗します。Google 標準 SQL でのキャストルールの詳細については、キャスティングをご覧ください。

コンソール

  1. Google Cloud コンソールで [BigQuery] ページに移動します。

    BigQuery に移動

  2. [クエリエディタ] で次のクエリを入力して、mydataset.mytablecolumn_twocolumn_three のすべてのデータが選択され、column_oneDATE から STRING にキャストされるようにします。クエリでは、エイリアスを使用して column_one を同じ名前でキャストします。mydataset.mytable はデフォルト プロジェクトにあります。

    SELECT
     column_two,
     column_three,
     CAST(column_one AS STRING) AS column_one
    FROM
     mydataset.mytable;
    
  3. [展開] をクリックして、[クエリの設定] を選択します。

  4. [送信先] セクションで、次の操作を行います。

    1. [クエリ結果の宛先テーブルを設定する] を選択して、

    2. [プロジェクト名] の値は、デフォルトのプロジェクトのままにします。これは mydataset.mytable を含むプロジェクトです。

    3. [Dataset] で [mydataset] を選択します。

    4. [Table ID] フィールドに「mytable」と入力します。

    5. [宛先テーブルの書き込み設定] で、[テーブルを上書きする] を選択します。このオプションにより、クエリ結果を使用して mytable が上書きされます。

  5. 必要に応じて、データの [ロケーション] を選択します。

  6. 設定を更新するには、[保存] をクリックします。

  7. [ 実行] をクリックします。

    クエリジョブが完了すると、column_one のデータ型は STRING になります。

bq

次の bq query コマンドを入力して、mydataset.mytablecolumn_twocolumn_three のすべてのデータが選択され、column_oneDATE から STRING にキャストされるようにします。クエリでは、エイリアスを使用して column_one を同じ名前でキャストします。mydataset.mytable はデフォルト プロジェクトにあります。

--destination_table フラグを使用してクエリ結果を mydataset.mytable に書き込み、--replace フラグを使用して mytable を上書きします。Google 標準 SQL 構文を使用するには、use_legacy_sql=false フラグを指定します。

必要に応じて、--location フラグを指定して、その値をロケーションに設定します。

bq query \
    --destination_table mydataset.mytable \
    --replace \
    --use_legacy_sql=false \
'SELECT
  column_two,
  column_three,
  CAST(column_one AS STRING) AS column_one
FROM
  mydataset.mytable'

API

mydataset.mytablecolumn_twocolumn_three のすべてのデータを選択し、column_oneDATE から STRING にキャストするには、jobs.insert メソッドを呼び出して query ジョブを構成します。必要に応じて、jobReference セクションにある location プロパティでロケーションを指定します。

クエリジョブで使用される SQL クエリは、SELECT column_two, column_three, CAST(column_one AS STRING) AS column_one FROM mydataset.mytable のようになります。クエリでは、エイリアスを使用して column_one を同じ名前でキャストします。

mytable をクエリ結果で上書きするには、configuration.query.destinationTable プロパティに mydataset.mytable を含め、configuration.query.writeDisposition プロパティに WRITE_TRUNCATE を指定します。

列のモードを変更する

列のモードに対してサポートされている唯一の変更は、REQUIRED から NULLABLE に変更することです。列のモードを REQUIRED から NULLABLE に変更することは、列緩和とも呼ばれます。また、データを読み込むと、既存のテーブルを上書きする場合や、既存のテーブルにデータを追加する場合は、列を緩和できます。

既存のテーブルに列 NULLABLE を作成する

列のモードを REQUIRED から NULLABLE に変更するには、次のいずれかのオプションを選択します。

コンソール

  1. Google Cloud コンソールで [BigQuery] ページに移動します。

    BigQuery に移動

  2. [エクスプローラ] パネルで、プロジェクトとデータセットを開いて、テーブルを選択します。

  3. 詳細パネルで [スキーマ] タブをクリックします。

  4. [スキーマを編集] をクリックします。このボタンを表示するには、スクロールが必要な場合があります。

  5. [現在のスキーマ] ページで、変更するフィールドを特定します。

  6. そのフィールドの [モード] プルダウン リストで、NULLABLE を選択します。

  7. 設定を更新するには、[保存] をクリックします。

SQL

ALTER COLUMN DROP NOT NULL DDL ステートメントを使用します。次の例では、列 mycolumn のモードを REQUIRED から NULLABLE に変更しています。

  1. Google Cloud コンソールで [BigQuery] ページに移動します。

    BigQuery に移動

  2. クエリエディタで次のステートメントを入力します。

    ALTER TABLE mydataset.mytable
    ALTER COLUMN mycolumn
    DROP NOT NULL;
    

  3. [ 実行] をクリックします。

クエリの実行方法については、インタラクティブ クエリの実行をご覧ください。

bq

  1. まず、--schema フラグを指定した bq show コマンドを発行して、既存のテーブル スキーマをファイルに書き込みます。更新するテーブルがデフォルト以外のプロジェクトにある場合は、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    ここで

    • project_id はプロジェクト ID です。
    • dataset は、更新しているテーブルを含むデータセットの名前です。
    • table は更新するテーブルの名前です。
    • schema_file は、ローカルマシンに書き込まれるスキーマ定義ファイルです。

    たとえば、mydataset.mytable のスキーマ定義をファイルに書き込むには、次のコマンドを入力します。mydataset.mytable はデフォルト プロジェクトにあります。

      bq show \
      --schema \
      --format=prettyjson \
      mydataset.mytable > /tmp/myschema.json
    
  2. スキーマ ファイルをテキスト エディタで開きます。スキーマは次のようになっています。

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. 既存の列のモードを REQUIRED から NULLABLE に変更します。この例では、column1 のモードを緩和しています。

    [
      {
        "mode": "NULLABLE",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    

    JSON スキーマ ファイルの操作の詳細については、JSON スキーマ ファイルの指定をご覧ください。

  4. スキーマ ファイルを更新したら、次のコマンドを発行してテーブルのスキーマを更新します。更新するテーブルがデフォルト以外のプロジェクトにある場合は、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。

    bq update project_id:dataset.table schema
    

    ここで

    • project_id はプロジェクト ID です。
    • dataset は、更新しているテーブルを含むデータセットの名前です。
    • table は更新するテーブルの名前です。
    • schema は、ローカルマシン上の JSON スキーマ ファイルのパスです。

    たとえば、次のコマンドを入力すると、デフォルト プロジェクトにある mydataset.mytable のスキーマ定義が更新されます。ローカルマシン上にあるスキーマ ファイルへのパスは /tmp/myschema.json です。

      bq update mydataset.mytable /tmp/myschema.json
    

API

tables.patch を呼び出し、schema プロパティを使用してスキーマ定義の中の REQUIRED 列を NULLABLE に変更します。tables.update メソッドはテーブル リソース全体を置き換えるため、tables.patch メソッドの方が適切です。

Go

import (
	"context"
	"fmt"

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

// relaxTableAPI demonstrates modifying the schema of a table to remove the requirement that columns allow
// no NULL values.
func relaxTableAPI(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()

	// Setup: We first create a table with a schema that's restricts NULL values.
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	original := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	if err := client.Dataset(datasetID).Table(tableID).Create(ctx, original); err != nil {
		return err
	}

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	// Iterate through the schema to set all Required fields to false (nullable).
	var relaxed bigquery.Schema
	for _, v := range meta.Schema {
		v.Required = false
		relaxed = append(relaxed, v)
	}
	newMeta := bigquery.TableMetadataToUpdate{
		Schema: relaxed,
	}
	if _, err := tableRef.Update(ctx, newMeta, meta.ETag); err != nil {
		return err
	}
	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.LegacySQLTypeName;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.Table;

public class RelaxColumnMode {

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

  public static void relaxColumnMode(String datasetName, String tableId) {
    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();

      Table table = bigquery.getTable(datasetName, tableId);

      // Create new relaxed schema based on the existing table schema
      Schema relaxedSchema =
          Schema.of(
              // The only supported modification you can make to a column's mode is changing it from
              // REQUIRED to NULLABLE
              // Changing a column's mode from REQUIRED to NULLABLE is also called column relaxation
              // INFO: LegacySQLTypeName will be updated to StandardSQLTypeName in release 1.103.0
              Field.newBuilder("word", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("word_count", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("corpus", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("corpus_date", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build());

      // Update the table with the new schema
      Table updatedTable =
          table.toBuilder().setDefinition(StandardTableDefinition.of(relaxedSchema)).build();
      updatedTable.update();
      System.out.println("Table schema successfully relaxed.");
    } catch (BigQueryException e) {
      System.out.println("Table schema not relaxed \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 relaxColumn() {
  /**
   * Changes columns from required to nullable.
   * Assumes existing table with the following schema:
   * [{name: 'Name', type: 'STRING', mode: 'REQUIRED'},
   * {name: 'Age', type: 'INTEGER'},
   * {name: 'Weight', type: 'FLOAT'},
   * {name: 'IsMagic', type: 'BOOLEAN'}];
   */

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  const newSchema = [
    {name: 'Name', type: 'STRING', mode: 'NULLABLE'},
    {name: 'Age', type: 'INTEGER'},
    {name: 'Weight', type: 'FLOAT'},
    {name: 'IsMagic', type: 'BOOLEAN'},
  ];

  // Retrieve current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Update schema
  metadata.schema = newSchema;
  const [apiResponse] = await table.setMetadata(metadata);

  console.log(apiResponse.schema.fields);
}

Python

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

Table.schema プロパティを、mode プロパティを 'NULLABLE' に設定した SchemaField オブジェクトのリストで上書きします。

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
# table_id = 'my_table'

original_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]

dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_ref = dataset_ref.table(table_id)
table = bigquery.Table(table_ref, schema=original_schema)
table = client.create_table(table)
assert all(field.mode == "REQUIRED" for field in table.schema)

# SchemaField properties cannot be edited after initialization.
# To make changes, construct new SchemaField objects.
relaxed_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
table.schema = relaxed_schema
table = client.update_table(table, ["schema"])

assert all(field.mode == "NULLABLE" for field in table.schema)

読み込みジョブで列 NULLABLE を作成する

既存のテーブルにデータを読み込んで上書きする際、そのテーブルのスキーマの REQUIRED 列を NULLABLE に緩和できます。上書きする既存のテーブルのスキーマは、読み込んでいるデータのスキーマを使用して上書きされます。読み込みジョブを使用してテーブルを上書きする方法については、ファイル形式についてのドキュメントを選択してください。

追記ジョブで列 NULLABLE を作成する

読み込みジョブでデータをテーブルに追記する際に列のモードを緩和できます。ファイルの種類に応じて、次のいずれかを選択します。

  • CSV ファイルと JSON ファイルからデータを追記するときは、JSON スキーマ ファイルを指定して個々の列のモードを緩和します。
  • Avro、ORC または Parquet ファイルからデータを追加するときは、スキーマで列を NULL に緩和し、緩和された列がスキーマ推定によって検出されるようにします。

読み込みジョブでデータをテーブルに追記するときに列を REQUIRED から NULLABLE に緩和するには、次のいずれかのオプションを選択します。

コンソール

Google Cloud Console で列のモードを緩和することはできません。

bq

bq load コマンドを使用してデータを読み込み、--noreplace フラグを指定して、データを既存のテーブルに追記していることを示します。

追記するデータが CSV 形式または改行区切りの JSON 形式である場合は、ローカル JSON スキーマ ファイルで緩和した列を指定します。あるいは、緩和した列がソースデータ内で検出されるように、--autodetect フラグでスキーマの検出を使用します。JSON スキーマ ファイルを使用した列モードの緩和の詳細については、REQUIRED 列から NULLABLE への手動での変更 をご覧ください。

Avro、ORC、Parquet ファイルから読み込む場合、緩和した列は自動的に推定されます。列緩和は、Datastore エクスポートの追記には適用されません。Datastore エクスポート ファイルの読み込みによって作成されたテーブル内の列は常に NULLABLE です。

--schema_update_option フラグを ALLOW_FIELD_RELAXATION に設定して、追記しているデータに緩和した列が含まれていることを示します。

追記するテーブルがデフォルト以外のプロジェクトのデータセットにある場合は、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。

(省略可)--location フラグを指定して、その値をロケーションに設定します。

load コマンドを次のように入力します。

bq --location=location load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

ここで

  • location はロケーションの名前です。--location フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値を asia-northeast1 に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。
  • format は、NEWLINE_DELIMITED_JSONCSVPARQUETORC または AVRO です。DATASTORE_BACKUP ファイルの場合、列緩和は必要ありません。Datastore エクスポート ファイルから作成されたテーブルの列は常に NULLABLE です。
  • project_id はプロジェクト ID です。
  • dataset は、テーブルを含むデータセットの名前です。
  • table は、追記するテーブルの名前です。
  • path_to_source は、完全修飾の Cloud Storage URI、URI のカンマ区切りのリスト、またはローカルマシン上のデータファイルのパスです。
  • schema は、ローカル JSON スキーマ ファイルのパスです。このオプションは、CSV ファイルと JSON ファイルに対してのみ使用します。Avro ファイルから読み込まれたデータでは、緩和した列は自動的に推定されます。

例:

次のコマンドを入力して、ローカル Avro データファイル /tmp/mydata.avromydataset.mytable に、読み込みジョブを使用して追記します。緩和された列は Avro データから自動的に推定できるため、スキーマ ファイルを指定する必要はありません。mydataset はデフォルト プロジェクトにあります。

bq load \
    --noreplace \
    --schema_update_option=ALLOW_FIELD_RELAXATION \
    --source_format=AVRO \
    mydataset.mytable \
    /tmp/mydata.avro

次のコマンドを入力して、Cloud Storage の改行区切りの JSON ファイルのデータを mydataset.mytable に、読み込みジョブを使用して追記します。緩和された列を含むスキーマは、ローカル JSON スキーマ ファイル /tmp/myschema.json 内にあります。mydataset はデフォルト プロジェクトにあります。

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

次のコマンドを入力して、ローカルマシン上の CSV ファイルのデータを mydataset.mytable に、読み込みジョブを使用して追記します。このコマンドは、スキーマの自動検出機能を使用して、ソースデータ内の緩和した列を検出します。mydataset はデフォルト プロジェクトではなく myotherproject にあります。

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=CSV \
--autodetect \
myotherproject:mydataset.mytable \
mydata.csv

API

jobs.insert メソッドを呼び出します。load ジョブを構成し、次のプロパティを設定します。

  • sourceUris プロパティを使用して、Cloud Storage 内のデータを参照します。
  • sourceFormat プロパティを設定して、データ形式を指定します。
  • schema プロパティでスキーマを指定します。
  • schemaUpdateOptions プロパティを使用して、スキーマ更新オプションを指定します。
  • writeDisposition プロパティを使用して、宛先テーブルの書き込み処理を WRITE_APPEND に設定します。

Go

import (
	"context"
	"fmt"
	"os"

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

// relaxTableImport demonstrates amending the schema of a table to relax columns from
// not allowing NULL values to allowing them.
func relaxTableImport(projectID, datasetID, tableID, filename string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	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: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, import data from a local file, but specify relaxation of required
	// fields as a side effect while the data is appended.
	f, err := os.Open(filename)
	if err != nil {
		return err
	}
	source := bigquery.NewReaderSource(f)
	source.AutoDetect = true   // Allow BigQuery to determine schema.
	source.SkipLeadingRows = 1 // CSV has a single header line.

	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)
	loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	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.CsvOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.Table;
import com.google.cloud.bigquery.TableId;
import com.google.common.collect.ImmutableList;

// Sample to append relax column in a table.
public class RelaxColumnLoadAppend {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    relaxColumnLoadAppend(datasetName, tableName, sourceUri);
  }

  public static void relaxColumnLoadAppend(String datasetName, String tableName, String sourceUri) {
    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();

      // Retrieve destination table reference
      Table table = bigquery.getTable(TableId.of(datasetName, tableName));

      // column as a 'REQUIRED' field.
      Field name =
          Field.newBuilder("name", StandardSQLTypeName.STRING).setMode(Field.Mode.REQUIRED).build();
      Field postAbbr =
          Field.newBuilder("post_abbr", StandardSQLTypeName.STRING)
              .setMode(Field.Mode.REQUIRED)
              .build();
      Schema schema = Schema.of(name, postAbbr);

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      // Set job options
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(table.getTableId(), sourceUri)
              .setSchema(schema)
              .setFormatOptions(csvOptions)
              .setSchemaUpdateOptions(
                  ImmutableList.of(JobInfo.SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(JobInfo.WriteDisposition.WRITE_APPEND)
              .build();

      // Create a load job and wait for it to complete.
      Job job = bigquery.create(JobInfo.of(loadConfig));
      job = job.waitFor();
      // Check the job's status for errors
      if (job.isDone() && job.getStatus().getError() == null) {
        System.out.println("Relax column append successfully loaded in a table");
      } else {
        System.out.println(
            "BigQuery was unable to load into the table due to an error:"
                + job.getStatus().getError());
      }
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

Node.js

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

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate client
const bigquery = new BigQuery();

async function relaxColumnLoadAppend() {
  // Changes required column to nullable in load append job.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const fileName = '/path/to/file.csv';
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // In this example, the existing table contains the 'Name'
  // column as a 'REQUIRED' field.
  const schema = 'Age:INTEGER, Weight:FLOAT, IsMagic:BOOLEAN';

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // Set load job options
  const options = {
    schema: schema,
    schemaUpdateOptions: ['ALLOW_FIELD_RELAXATION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
  };

  // Load data from a local file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(fileName, options);

  console.log(`Job ${job.id} completed.`);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

Python

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

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, 'my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the number of required fields
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
# In this example, the existing table has 3 required fields.
print("{} fields in the schema are required.".format(original_required_fields))

# Configures the load job to append the data to a destination table,
# allowing field relaxation
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION
]
# In this example, the existing table contains three required fields
# ('full_name', 'age', and 'favorite_color'), while the data to load
# contains only the first two fields.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated number of required fields
table = client.get_table(table)
current_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
print("{} fields in the schema are now required.".format(current_required_fields))

追記ジョブですべての列を NULLABLE にする

クエリ結果をテーブルに追記する際にテーブル内のすべての列を緩和できます。--schema_update_option フラグを ALLOW_FIELD_RELAXATION に設定すると、宛先テーブル内のすべての必須フィールドを緩和できます。クエリ追記を使用して宛先テーブル内の個々の列を緩和することはできません。読み込み追記ジョブで個々の列を緩和するには、追記ジョブで列 NULLABLE を作成するをご覧ください。

クエリ結果を宛先テーブルに追記する際にすべての列を緩和するには、次のいずれかのオプションを選択します。

コンソール

Google Cloud Console で列のモードを緩和することはできません。

bq

bq query コマンドを使用してデータに対するクエリを実行し、--destination_table フラグを指定してどのテーブルを追記しているかを示します。

クエリ結果を既存の宛先テーブルに追記していることを指定するには、--append_table フラグを指定します。

--schema_update_option フラグを ALLOW_FIELD_RELAXATION に設定して、追記しているテーブル内のすべての REQUIRED 列を NULLABLE に変更するように指示します。

Google 標準 SQL 構文をクエリで使用するには、use_legacy_sql=false フラグを指定します。

追記するテーブルがデフォルト以外のプロジェクトのデータセットにある場合は、project_id:dataset の形式でプロジェクト ID をデータセット名に追加します。

(省略可)--location フラグを指定して、その値をロケーションに設定します。

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'query'

ここで

  • location はロケーションの名前です。--location フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値を asia-northeast1 に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。
  • project_id はプロジェクト ID です。
  • dataset は、追記するテーブルが含まれているデータセットの名前です。
  • table は、追記するテーブルの名前です。
  • query は、Google 標準 SQL 構文で記述したクエリです。

例:

次のコマンドを入力して、デフォルト プロジェクトで mydataset.mytable のクエリを実行でき、クエリ結果を mydataset.mytable2 に (デフォルト プロジェクトでも同様)追記できます。このコマンドは、宛先テーブル内のすべての REQUIRED 列を NULLABLE に変更します。

bq query \
    --destination_table mydataset.mytable2 \
    --append_table \
    --schema_update_option=ALLOW_FIELD_RELAXATION \
    --use_legacy_sql=false \
    'SELECT
       column1,column2
     FROM
       mydataset.mytable'

次のコマンドを入力して、デフォルト プロジェクトで mydataset.mytable のクエリを実行でき、クエリ結果を myotherprojectmydataset.mytable2 に追記できます。このコマンドは、宛先テーブル内のすべての REQUIRED 列を NULLABLE に変更します。

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

jobs.insert メソッドを呼び出します。query ジョブを構成し、次のプロパティを設定します。

  • destinationTable プロパティを使用して宛先テーブルを指定します。
  • writeDisposition プロパティを使用して、宛先テーブルの書き込み処理を WRITE_APPEND に設定します。
  • schemaUpdateOptions プロパティを使用して、スキーマ更新オプションを指定します。
  • query プロパティを使用して、Google 標準 SQL クエリを指定します。

Go

import (
	"context"
	"fmt"

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

// relaxTableQuery demonstrates relaxing the schema of a table by appending query results to
// enable the table to allow NULL values.
func relaxTableQuery(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	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: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, append a query result that includes nulls, but allow the job to relax
	// all required columns.
	q := client.Query("SELECT \"Beyonce\" as full_name")
	q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
	q.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
	q.WriteDisposition = bigquery.WriteAppend
	q.Location = "US"
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	_, err = job.Wait(ctx)
	if err != nil {
		return err
	}
	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.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableList;

public class RelaxTableQuery {

  public static void runRelaxTableQuery() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    relaxTableQuery(projectId, datasetName, tableName);
  }

  // To relax all columns in a destination table when you append data to it during a query job
  public static void relaxTableQuery(String projectId, String datasetName, String tableName)
      throws Exception {
    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);

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query = "SELECT word FROM " + sourceTable + " WHERE word like '%is%'";

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // Use standard SQL syntax for queries.
              // See: https://cloud.google.com/bigquery/sql-reference/
              .setUseLegacySql(false)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setDestinationTable(tableId)
              .build();

      Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).build());

      queryJob = queryJob.waitFor();

      // Check for errors
      if (queryJob == null) {
        throw new Exception("Job no longer exists");
      } else if (queryJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(queryJob.getStatus().getError().toString());
      }

      // Get the results.
      TableResult results = queryJob.getQueryResults();

      // Print all pages of the results.
      results
          .iterateAll()
          .forEach(
              rows -> {
                rows.forEach(row -> System.out.println("row: " + row.toString()));
              });

      System.out.println("Successfully relaxed all columns in destination table during query job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Columns not relaxed during query job \n" + e.toString());
    }
  }
}

Python

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