テーブル スキーマを手動で変更する

このドキュメントでは、既存の BigQuery テーブルのスキーマ定義を手動で変更する方法について説明します。BigQuery では、多くのスキーマの変更がネイティブにサポートされていないため、手動の回避策が必要です。このようなサポートされていないスキーマの変更には、次のものがあります。

• 列の名前を変更する。
• 列のデータ型を変更する。
• 列のモードを変更する（REQUIRED 列を NULLABLE に緩和することを除く）。
• 列を削除する。

BigQuery でサポートされているスキーマの変更については、テーブル スキーマの変更をご覧ください。

注意: このページで説明するオプションには、テーブルの上書き、または新しいテーブルへのデータの読み込みが必要になります。取り込み時間パーティション分割テーブルの場合、これらのオペレーションによって元のパーティショニング情報が破棄されます。このオペレーション以降は、すべてのテーブルデータが単一のパーティションに配置されます。

列の名前を変更する

Google Cloud Console、bq コマンドライン ツール、API では列の名前を変更できません。名前が変更された列を使用してテーブル スキーマを更新しようとすると、次のようなエラーが返されます。BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table

手動で列の名前を変更するには、次の 2 つの方法があります。

• SQL クエリの使用 - わかりやすさと使いやすさを重視し、費用をあまり重視しない場合は、このオプションを選択します。
• テーブルの再作成 - 費用を重視し、わかりやすさと使いやすさをあまり重視しない場合は、このオプションを選択します。

オプション 1: クエリを使用する

SQL クエリを使用して列の名前を変更するには、テーブルのすべての列を選択し、名前の変更が必要な列に別の名前を付けます。クエリ結果を使用して、既存のテーブルを上書きするか、新しい抽出先テーブルを作成できます。列にエイリアスを設定する場合は、BigQuery の列名のルールに従う必要があります。

利点

• クエリを使用して新しい宛先テーブルにデータを書き込む場合は、元のデータが保持されます。
• クエリジョブを使用して元のテーブルを上書きする場合は、発生するストレージ費用は 2 つではなく 1 つのテーブル分ですが、元のデータは失われます。

欠点

• クエリを使用して列の名前を変更する場合は、テーブル全体をスキャンする必要があります。テーブルが非常に大きい場合、クエリの料金がかなりの額になる可能性があります。
• クエリ結果を新しい宛先テーブルに書き込む場合は、古いテーブルと新しいテーブルの両方にストレージ費用が発生します（古いテーブルを削除しない場合）。
• 名前の変更が必要だった列は null 許容になりました。

列エイリアスの例

次の例は、名前の変更が必要な 2 つの列を除き、mytable のすべてのデータを選択する標準 SQL クエリを示しています。エイリアスを使用して、2 つの列の新しい名前を生成します。column_one の名前を newcolumn_one に変更し、column_two の名前を newcolumn_two に変更します。クエリ結果を使用して、既存のテーブルを上書きします。

コンソール

1. Google Cloud Console で、[クエリエディタ] を選択します。

2. [クエリエディタ] で次のクエリを入力して、名前の変更が必要な 2 つの列を除き、mydataset.mytable のすべてのデータが選択されるようにします。mydataset.mytable はデフォルト プロジェクトにあります。クエリでは、エイリアスを使用して column_one の名前を newcolumn_one に変更し、column_two の名前を newcolumn_two に変更します。

   SELECT
    * EXCEPT(column_one, column_two),
    column_one AS newcolumn_one, column_two AS newcolumn_two
   FROM
    mydataset.mytable
   

3. [展開] をクリックして、[クエリの設定] を選択します。

4. [送信先] で次の操作を行います。

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

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

   3. [テーブル ID] に「mytable」と入力します。

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

5. [保存] をクリックして設定を更新してから、[クエリエディタ] で [実行] をクリックします。クエリジョブが完了すると、mytable の列に新しい名前が表示されます。

bq

次の bq query コマンドを入力して、名前の変更が必要な 2 つの列を除き、mydataset.mytable のすべてのデータが選択されるようにします。mydataset.mytable はデフォルト プロジェクトにあります。クエリでは、エイリアスを使用して column_one の名前を newcolumn_one に変更し、column_two の名前を newcolumn_two に変更します。

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

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

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_one,
    column_two),
  column_one AS newcolumn_one,
  column_two AS newcolumn_two
FROM
  mydataset.mytable'

API

column_one の名前を newcolumn_one に変更し、column_two の名前を newcolumn_two に変更するには、jobs.insert メソッドを呼び出して query ジョブを構成します。（省略可）jobReference セクションにある location プロパティでロケーションを指定します。

クエリジョブで使用される SQL クエリは、SELECT * EXCEPT(column_one, column_two), column_one AS newcolumn_one, column_two AS newcolumn_two FROM mydataset.mytable のようになります。このクエリでは、名前の変更が必要な 2 つの列を除き、mytable のすべてのデータが選択されます。エイリアスを使用して、2 つの列の新しい名前を生成します。

mytable をクエリ結果で上書きするには、configuration.query.destinationTable プロパティに mydataset.mytable を含め、configuration.query.writeDisposition プロパティに WRITE_TRUNCATE を指定します。新しい宛先テーブルを指定するには、configuration.query.destinationTable プロパティにテーブル名を入力します。

オプション 2: 新しいテーブルにデータをエクスポートして読み込む

テーブルのデータを Cloud Storage にエクスポートし、適切な列名が組み込まれたスキーマ定義を使用して新しいテーブルにデータを読み込むことによっても、列の名前を変更できます。読み込みジョブは、既存のテーブルの上書きにも使用できます。

利点

• エクスポート ジョブまたは読み込みジョブには料金がかかりません。現在、BigQuery の読み込みジョブとエクスポート ジョブは無料です。
• 読み込みジョブを使用して元のテーブルを上書きする場合、発生するストレージ費用は 2 つではなく 1 つのテーブル分ですが、元のデータは失われます。

欠点

• データを新しいテーブルに読み込む場合は、元のテーブルと新しいテーブルにストレージ費用が発生します（古いテーブルを削除しない場合）。
• エクスポートされたデータを Cloud Storage に保存するための費用が発生します。

列のデータ型を変更する

Google Cloud Console、bq コマンドライン ツール、API では列のデータ型を変更できません。列の新しいデータ型を指定するスキーマを適用してテーブルを更新しようとすると、次のようなエラーが返されます。BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

手動で列のデータ型を変更するには、次の 2 つの方法があります。

• SQL クエリの使用 - わかりやすさと使いやすさを重視し、費用をあまり重視しない場合は、このオプションを選択します。
• テーブルの再作成 - 費用を重視し、わかりやすさと使いやすさをあまり重視しない場合は、このオプションを選択します。

オプション 1: クエリを使用する

SQL クエリを使用してテーブルのすべてのデータを選択し、関連する列を別のデータ型としてキャストします。クエリ結果を使用して、テーブルを上書きするか、新しい宛先テーブルを作成できます。

利点

• クエリを使用して新しい宛先テーブルにデータを書き込む場合は、元のデータが保持されます。
• クエリジョブを使用して元のテーブルを上書きする場合は、発生するストレージ費用は 2 つではなく 1 つのテーブル分ですが、元のデータは失われます。

欠点

• クエリを使用して列のデータ型を変更する場合は、テーブル全体をスキャンする必要があります。テーブルが非常に大きい場合、クエリの料金がかなりの額になる可能性があります。
• クエリ結果を新しい宛先テーブルに書き込む場合は、古いテーブルと新しいテーブルの両方にストレージ費用が発生します（古いテーブルを削除しない場合）。

CAST の例

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

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

コンソール

1. Google Cloud Console で、[クエリの新規作成] をクリックします。

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

   SELECT
    column_two, column_three, CAST(column_one AS STRING) AS column_one
   FROM
    mydataset.mytable
   

3. [展開] をクリックして、[クエリの設定] を選択します。

4. [送信先] セクションで、[クエリ結果の宛先テーブルを設定する] をオンにします。

5. 次のフィールドで:

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

   2. [データセット名] で [mydataset] を選択します。

   3. [テーブル名] フィールドに「mytable」と入力します。

   4. [OK] をクリックします。

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

7. （省略可）[処理を行うロケーション] で [自動選択] をクリックし、データのロケーションを選択します。

8. [保存] をクリックして設定を更新してから、[クエリエディタ] で [実行] をクリックします。クエリジョブが完了すると、column_one のデータ型は STRING になります。

bq

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

--destination_table フラグを使用してクエリ結果を mydataset.mytable に書き込み、--replace フラグを使用して mytable を上書きします。標準 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.mytable の column_two と column_three のすべてのデータを選択し、column_one を DATE から 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 を指定します。

オプション 2: 新しいテーブルにデータをエクスポートして読み込む

テーブルのデータを Cloud Storage にエクスポートし、列の適切なデータ型を指定するスキーマ定義を使用して新しいテーブルにデータを読み込むことによっても、列のデータ型を変更できます。読み込みジョブは、既存のテーブルの上書きにも使用できます。

利点

• エクスポート ジョブまたは読み込みジョブには料金がかかりません。現在、BigQuery の読み込みジョブとエクスポート ジョブは無料です。
• 読み込みジョブを使用して元のテーブルを上書きする場合、発生するストレージ費用は 2 つではなく 1 つのテーブル分ですが、元のデータは失われます。

欠点

• データを新しいテーブルに読み込む場合は、元のテーブルと新しいテーブルにストレージ費用が発生します（古いテーブルを削除しない場合）。
• エクスポートされたデータを Cloud Storage に保存するための費用が発生します。

列モードを変更する

現在、列のモードに対してサポートされている唯一の変更は、REQUIRED から NULLABLE に変更することです。列のモードを REQUIRED から NULLABLE に変更することは、列緩和とも呼ばれます。REQUIRED 列を NULLABLE に緩和する方法については、列のモードの緩和をご覧ください。

サポートされていない変更を列モードに適用しようとすると、次のようなエラーが返されます。この例では、列モードを NULLABLE から REPEATED に変更しようとしました。BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from NULLABLE to REPEATED.

データをエクスポートして新しいテーブルに読み込む

テーブルのデータを Cloud Storage にエクスポートし、列に適切なモードを指定するスキーマ定義を使用して新しいテーブルにデータを読み込むことによって、列のモードを手動で変更できます。読み込みジョブは、既存のテーブルの上書きにも使用できます。

利点

• エクスポート ジョブまたは読み込みジョブには料金がかかりません。現在、BigQuery の読み込みジョブとエクスポート ジョブは無料です。
• 読み込みジョブを使用して元のテーブルを上書きする場合、発生するストレージ費用は 2 つではなく 1 つのテーブル分ですが、元のデータは失われます。

欠点

• データを新しいテーブルに読み込む場合は、元のテーブルと新しいテーブルにストレージ費用が発生します（古いテーブルを削除しない場合）。
• エクスポートされたデータを Cloud Storage に保存するための費用が発生します。

テーブル スキーマから列を削除する

Google Cloud Console、bq コマンドライン ツール、API では既存のテーブル スキーマから列を削除できません。列を削除するスキーマを適用してテーブルを更新しようとすると、次のようなエラーが返されます。BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

手動で列を削除するには、次の 2 つの方法があります。

• SQL クエリの使用 - わかりやすさと使いやすさを重視し、費用をあまり重視しない場合は、このオプションを選択します。
• テーブルの再作成 - 費用を重視し、わかりやすさと使いやすさをあまり重視しない場合は、このオプションを選択します。

オプション 1: クエリを使用する

列の削除に使用できる SQL ステートメントは 2 つあります。

• SELECT * EXCEPT
• ALTER TABLE DROP COLUMN

次のセクションでは、削除する列を除外する SELECT * EXCEPT クエリを使用して、そのクエリ結果を使用してテーブルの上書きまたは新しい宛先テーブルの作成を行う方法について説明します。

ALTER TABLE DROP COLUMN ステートメントを使用する場合は、データ定義言語ページの例をご覧ください。

利点

• クエリを使用して新しい宛先テーブルにデータを書き込む場合は、元のデータが保持されます。
• クエリジョブを使用して元のテーブルを上書きする場合は、発生するストレージ費用は 2 つではなく 1 つのテーブル分ですが、元のデータは失われます。

欠点

• クエリを使用して列を削除する場合は、削除する列以外のすべての列のデータをスキャンする必要があります。テーブルが非常に大きい場合、クエリの料金がかなりの額になる可能性があります。
• クエリ結果を新しい宛先テーブルに書き込む場合は、古いテーブルと新しいテーブルの両方にストレージ費用が発生します（古いテーブルを削除しない場合）。

SELECT * EXCEPT の例

次の例は、mydataset.mytable から column_two 以外のすべてのデータを選択する標準 SQL クエリを示しています。クエリ結果を使用して、既存のテーブルを上書きします。

コンソール

1. Google Cloud Console で、[クエリの新規作成] をクリックします。

2. クエリエディタで次のクエリを入力して、mydataset.mytable から column_two 以外のすべてのデータが選択されるようにします。mydataset.mytable はデフォルト プロジェクトにあります。

   SELECT
    * EXCEPT(column_two)
   FROM
    mydataset.mytable
   

3. [展開] をクリックして、[クエリの設定] を選択します。

4. [送信先] セクションで、[クエリ結果の宛先テーブルを設定する] をオンにします。

5. 次のフィールドで:

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

   2. [データセット名] で [mydataset] を選択します。

   3. [テーブル名] フィールドに「mytable」と入力します。

   4. [OK] をクリックします。

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

7. （省略可）[処理を行うロケーション] で [自動選択] をクリックし、データのロケーションを選択します。

8. [保存] をクリックして設定を更新してから、[クエリエディタ] で [実行] をクリックします。クエリジョブが完了すると、テーブルには column_two を除くすべての列が含まれています。

bq

次の bq query コマンドを入力して、mydataset.mytable から column_two 以外のすべてのデータが選択されるようにします。mydataset.mytable はデフォルト プロジェクトにあります。--destination_table フラグを使用してクエリ結果を mydataset.mytable に書き込み、--replace フラグを使用して mytable を上書きします。標準 SQL 構文を使用するには、use_legacy_sql=false フラグを指定します。

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

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_two)
FROM
  mydataset.mytable'

API

mydataset.mytable から column_two 以外のすべてのデータを選択するには、jobs.insert メソッドを呼び出して query ジョブを構成します。（省略可）jobReference セクションにある location プロパティでロケーションを指定します。

クエリジョブで使用される SQL クエリは、SELECT * EXCEPT(column_two) FROM mydataset.mytable のようになります。

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

オプション 2: 新しいテーブルにデータをエクスポートして読み込む

テーブルのデータを Cloud Storage にエクスポートして、削除する列に対応するデータを削除し、削除した列を含まないスキーマ定義を使用して新しいテーブルにデータを読み込むことによっても、列を削除できます。読み込みジョブは、既存のテーブルの上書きにも使用できます。

利点

• エクスポート ジョブまたは読み込みジョブには料金がかかりません。現在、BigQuery の読み込みジョブとエクスポート ジョブは無料です。
• 読み込みジョブを使用して元のテーブルを上書きする場合、発生するストレージ費用は 2 つではなく 1 つのテーブル分ですが、元のデータは失われます。

欠点

• データを新しいテーブルに読み込む場合は、元のテーブルと新しいテーブルにストレージ費用が発生します（古いテーブルを削除しない場合）。
• エクスポートされたデータを Cloud Storage に保存するための費用が発生します。

テーブルのセキュリティ

BigQuery でテーブルへのアクセスを制御するには、テーブルのアクセス制御の概要をご覧ください。