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

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

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

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

列の名前の変更

列の名前の変更は、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: データをエクスポートして新しいテーブルに読み込む

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

長所

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

短所

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

列エイリアスの例

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

ウェブ 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] をクリックし、データのロケーションを選択します。データが US または EU マルチリージョン ロケーションに存在する場合、処理ロケーションを未指定のままにしておくことができます。データが US または EU に存在する場合、処理ロケーションは自動的に検出されます。

  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 --location=[LOCATION] 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 メソッドを呼び出してクエリジョブを構成します。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 プロパティにテーブル名を入力します。

列のデータ型の変更

列のデータ型の変更は、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: データをエクスポートして新しいテーブルに読み込む

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

長所

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

短所

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

CAST の例

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

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

ウェブ 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] をクリックし、データのロケーションを選択します。データが US または EU マルチリージョン ロケーションに存在する場合、処理ロケーションを未指定のままにしておくことができます。データが US または EU に存在する場合、処理ロケーションは自動的に検出されます。

  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 --location=[LOCATION] 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 メソッドを呼び出してクエリジョブを構成します。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

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

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

長所

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

短所

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

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

既存のテーブル スキーマからの列の削除は、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: データをエクスポートして新しいテーブルに読み込む

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

長所

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

短所

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

SELECT * EXCEPT の例

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

ウェブ 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] をクリックし、データのロケーションを選択します。データが US または EU マルチリージョン ロケーションに存在する場合、処理ロケーションを未指定のままにしておくことができます。データが US または EU に存在する場合、処理ロケーションは自動的に検出されます。

  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 --location=[LOCATION] query --destination_table mydataset.mytable --replace --use_legacy_sql=false 'SELECT * EXCEPT(column_two) FROM mydataset.mytable'

API

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

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

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

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

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

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