テーブル スキーマの手動変更

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

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

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

列の名前の変更

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

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

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

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

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

長所

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

短所

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

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

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

長所

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

短所

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

列エイリアスの例

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

Console

  1. Console の BigQuery ウェブ UI で、[クエリエディタ] を選択します。

  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. [送信先] セクションで、[クエリ結果の宛先テーブルを設定する] をオンにします。

    宛先テーブルを設定する

  5. フィールドで次の操作を行います。

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

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

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

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

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

    テーブルを上書きする

  7. (省略可)[処理を行うロケーション] でデータのロケーションを選択します。

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

従来の UI

  1. 従来の BigQuery ウェブ UI で、[Compose Query] をクリックします。

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

    #standardSQL
    SELECT
     * EXCEPT(column_one, column_two),
     column_one AS newcolumn_one, column_two AS newcolumn_two
    FROM
     mydataset.mytable
    
  3. [Show Options] をクリックします。

  4. [Destination Table] セクションで、[Select Table] をクリックします。

  5. [Select Destination Table] ダイアログで次の操作を行います。

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

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

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

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

  6. [Destination Table] セクションの [Write Preference] で、[Overwrite table] を選択します。これにより、クエリ結果を使用して mytable が上書きされます。

  7. (省略可)[Processing Location] で [Unspecified] をクリックし、データのロケーションを選択します。

  8. [Run query] をクリックします。クエリジョブが完了すると、mytable の列に新しい名前が表示されます。

CLI

次の 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 プロパティにテーブル名を入力します。

列のデータ型の変更

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

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

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

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

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

長所

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

短所

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

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

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

長所

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

短所

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

CAST の例

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

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

Console

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

  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. [送信先] セクションで、[クエリ結果の宛先テーブルを設定する] をオンにします。

  5. フィールドで次の操作を行います。

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

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

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

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

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

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

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

従来の UI

  1. 従来の BigQuery ウェブ UI で、[Compose Query] をクリックします。

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

    #standardSQL
    SELECT
     column_two, column_three, CAST(column_one AS STRING) AS column_one
    FROM
     mydataset.mytable
    
  3. [Show Options] をクリックします。

  4. [Destination Table] セクションで、[Select Table] をクリックします。

  5. [Select Destination Table] ダイアログで次の操作を行います。

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

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

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

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

  6. [Destination Table] セクションの [Write Preference] で、[Overwrite table] を選択します。これにより、クエリ結果を使用して mytable が上書きされます。

  7. (省略可)[Processing Location] で [Unspecified] をクリックし、データのロケーションを選択します。

  8. [Run query] をクリックします。クエリジョブが完了すると、column_one のデータ型は STRING になります。

CLI

次の bq query コマンドを入力して、mydataset.mytablecolumn_twocolumn_three のすべてのデータを選択し、column_oneDATE から 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.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 に変更することは、列緩和とも呼ばれます。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 に保存するための費用が発生します。

テーブル スキーマからの列の削除

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

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

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

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

削除する列を除外する SELECT * EXCEPT クエリを使用し、クエリ結果を使用してテーブルを上書きするか、新しい宛先テーブルを作成します。

長所

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

短所

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

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

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

長所

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

短所

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

SELECT * EXCEPT の例

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

Console

  1. GCP 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 を除くすべての列が含まれます。

従来の UI

  1. 従来の BigQuery ウェブ UI で、[Compose Query] をクリックします。

  2. [New Query] ボックスに次のクエリを入力して、mydataset.mytable から column_two 以外のすべてのデータを選択します。mydataset.mytable はデフォルト プロジェクト内にあります。

    #standardSQL
    SELECT
     * EXCEPT(column_two)
    FROM
     mydataset.mytable
    
  3. [Show Options] をクリックします。

  4. [Destination Table] セクションで、[Select Table] をクリックします。

  5. [Select Destination Table] ダイアログで次の操作を行います。

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

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

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

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

  6. [Destination Table] セクションの [Write Preference] で、[Overwrite table] を選択します。これにより、クエリ結果を使用して mytable が上書きされます。

  7. (省略可)[Processing Location] で [Unspecified] をクリックし、データのロケーションを選択します。

  8. [Run query] をクリックします。クエリジョブが完了すると、テーブルに column_two を除くすべての列が含まれます。

CLI

次の 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 を指定します。

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

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

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