手動變更資料表結構定義

本文件說明如何手動變更現有 BigQuery 資料表的結構定義。許多結構定義變更不在 BigQuery 的原生支援範圍內，需要使用手動解決方法。這些不受支援的結構定義修改包括：

  • 變更資料欄的名稱
  • 變更資料欄的資料類型
  • 變更資料欄模式 (除了將 REQUIRED 資料欄放寬為 NULLABLE 以外)
  • 刪除資料欄

如要瞭解 BigQuery 支援的結構定義變更，請參閱修改資料表結構定義。

變更資料欄的名稱

Cloud Console、BigQuery 傳統網頁版 UI、指令列工具或 API 均不支援重新命名資料欄。如果您嘗試以更名後的資料欄更新資料表結構定義，系統會傳回以下錯誤訊息：BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table。

您可以透過以下兩個方式手動變更資料欄名稱：

  • 使用 SQL 查詢 - 如果您更在意簡便和易用程度，而成本不是主要考量的話，請選擇這種方法。
  • 重新建立資料表 - 如果您更在意成本，而簡便和易用程度不是主要考量的話，請選擇這種方法。

方法 1：使用查詢

如要使用 SQL 查詢變更資料欄的名稱，請選取資料表中的所有資料欄，並為需要重新命名的資料欄指定別名。您可以使用查詢結果覆寫現有的資料表，也可以建立新的目的地資料表。當您為資料欄指定別名時，新名稱必須遵循 BigQuery 的資料欄名稱規則。

優點

  • 使用查詢將資料寫入新的目標資料表可保留原始資料。
  • 如果使用查詢工作覆寫原始資料表，只會產生一個資料表 (而不是兩個) 的儲存空間費用，但您將失去原始資料。

缺點

  • 您必須掃描整個資料表，才能使用查詢重新命名資料欄 - 如果資料表非常龐大，查詢費用可能會相當可觀。
  • 如果將查詢結果寫入新的目標資料表，會同時產生新舊資料表兩者的儲存空間費用 (除非刪除舊資料表)。

方法 2：匯出資料並載入至新的資料表

您也可以透過下列方式變更資料欄名稱：將資料表中的資料匯出至 Cloud Storage，再將結構定義中包含正確資料欄名稱的資料載入至新的資料表。您還可以使用載入工作覆寫現有的資料表。

優點

  • 您不需為匯出工作或載入工作付費。目前可免費執行 BigQuery 載入與匯出工作。
  • 如果使用載入工作覆寫原始資料表，只會產生一個資料表 (而不是兩個) 的儲存空間費用，但您將失去原始資料。

缺點

  • 如果將資料載入至新的資料表，會產生原始資料表和新資料表兩者的儲存空間費用 (除非刪除舊資料表)。
  • 您必須付費才能將匯出的資料儲存在 Cloud Storage 中。

資料欄別名範例

以下範例使用標準 SQL 查詢選取 mytable 中的所有資料 (2 個需要重新命名的資料欄除外)，並使用別名產生這 2 個資料欄的新名稱。column_one 已重新命名為 newcolumn_one，column_two 已重新命名為 newcolumn_two。系統會使用查詢結果覆寫現有的資料表。

主控台

  1. 在主控台 BigQuery 網頁版 UI 中，選取 [Query editor] (查詢編輯器)。

  2. 在「Query editor」(查詢編輯器) 中輸入以下查詢，藉此選取 mydataset.mytable 中的所有資料 (2 個需要重新命名的資料欄除外)。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. 按一下 [More] (更多) 並選取 [Query settings] (查詢設定)。

    查詢設定

  4. 在「Destination」(目的地) 部分中，勾選 [Set a destination table for query results] (為查詢結果設定目標資料表)。

    設定目標資料表

  5. 在以下欄位中：

    1. 保留「Project name」(專案名稱) 欄位中預設專案的設定值 (含有 mydataset.mytable 的專案)。

    2. 在「Dataset name」(資料集名稱) 部分，選擇 mydataset。

    3. 在「Table name」(資料表名稱) 欄位中輸入 mytable。

    4. 按一下 [OK] (確定)。

  6. 在「Destination table write preference」(目標資料表寫入偏好設定) 部分中，針對「Write Preference」(寫入偏好設定) 選擇 [Overwrite table] (覆寫資料表)。這樣一來，您就能使用查詢結果覆寫 mytable。

    覆寫資料表

  7. (選用) 在「Processing Location」(處理位置) 中選擇所需資料的位置。

  8. 按一下 [Save] (儲存) 以更新設定，然後在「Query editor」(查詢編輯器) 中按一下 [Run] (執行)。查詢工作完成時，mytable 中的資料欄就會套用新名稱。

傳統版 UI

  1. 在 BigQuery 傳統網頁版 UI 中，按一下 [Compose Query] (撰寫查詢)。

  2. 在「New Query」(新查詢) 方塊中輸入以下查詢，藉此選取 mydataset.mytable 中的所有資料 (2 個需要重新命名的資料欄除外)。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」(資料表 ID) 欄位中輸入 mytable。

    4. 按一下 [OK] (確定)。

  6. 在「Destination Table」(目標資料表) 部分中，針對「Write Preference」(寫入偏好設定) 選擇 [Overwrite table] (覆寫資料表)；這樣一來，您就能使用查詢結果覆寫 mytable。

  7. (選用) 在「Processing Location」(處理位置) 中，按一下 [Unspecified] (未指定)，然後選擇您資料的位置。

  8. 按一下 [Run query] (執行查詢)。查詢工作完成時，mytable 中的資料欄就會套用新名稱。

CLI

輸入下列 bq query 指令，選取 mydataset.mytable 中的所有資料 (2 個需要重新命名的資料欄除外)。mydataset.mytable 在您的預設專案中。這項查詢會使用別名將 column_one 的名稱變更為newcolumn_one，將 column_two 的名稱變更為 newcolumn_two。

使用 --destination_table 旗標將查詢結果寫入 mydataset.mytable，並指定 --replace 旗標以覆寫 mytable。指定 use_legacy_sql=false 旗標以使用標準 SQL 語法。

(選用) 提供 --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。這項查詢會選取 mytable 中的所有資料 (2 個需要重新命名的資料欄位除外)，並使用別名產生這 2 個資料欄的新名稱。

如要使用查詢結果覆寫 mytable，請將 mydataset.mytable 加入 configuration.query.destinationTable 屬性，並在 configuration.query.writeDisposition 屬性中指定 WRITE_TRUNCATE。如要指定新的目標資料表，請在 configuration.query.destinationTable 屬性中輸入資料表名稱。

變更資料欄的資料類型

Cloud Console、BigQuery 傳統網頁版 UI、指令列工具或 API 均不支援變更資料欄的資料類型。如果您嘗試套用結構定義，藉此為資料欄指定新資料類型來更新資料表，系統會傳回以下錯誤訊息：BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

您可以透過以下兩個方式手動變更資料欄資料類型：

  • 使用 SQL 查詢 - 如果您更在意簡便和易用程度，而成本不是主要考量的話，請選擇這種方法。
  • 重新建立資料表 - 如果您更在意成本，而簡便和易用程度不是主要考量的話，請選擇這種方法。

方法 1：使用查詢

使用 SQL 查詢選取資料表中的所有資料，並將相關資料欄轉換成其他資料類型。您可以使用查詢結果覆寫資料表，也可以建立新的目標資料表。

優點

  • 使用查詢將資料寫入新的目標資料表可保留原始資料。
  • 如果使用查詢工作覆寫原始資料表，只會產生一個資料表 (而不是兩個) 的儲存空間費用，但您將失去原始資料。

缺點

  • 您必須掃描整個資料表，才能使用查詢變更資料欄的資料類型 - 如果資料表非常龐大，查詢費用可能會相當可觀。
  • 如果將查詢結果寫入新的目標資料表，會同時產生新舊資料表兩者的儲存空間費用 (除非刪除舊資料表)。

方法 2：匯出資料並載入至新的資料表

您也可以透過下列方式變更資料欄的資料類型：將資料表中的資料匯出至 Cloud Storage，再將結構定義中已指定資料欄正確資料類型的資料載入至新的資料表。您還可以使用載入工作覆寫現有的資料表。

優點

  • 您不需為匯出工作或載入工作付費。目前可免費執行 BigQuery 載入與匯出工作。
  • 如果使用載入工作覆寫原始資料表，只會產生一個資料表 (而不是兩個) 的儲存空間費用，但您將失去原始資料。

缺點

  • 如果將資料載入至新的資料表，會產生原始資料表和新資料表兩者的儲存空間費用 (除非刪除舊資料表)。
  • 您必須付費才能將匯出的資料儲存在 Cloud Storage 中。

CAST 範例

以下範例使用標準 SQL 查詢選取 mydataset.mytable 中 column_two 和 column_three 的所有資料、將 column_one 由 DATE 轉換為 STRING，並使用查詢結果覆寫現有的資料表。經覆寫的資料表會以 STRING 資料類型儲存 column_one。

使用 CAST 時，如果 BigQuery 無法執行類型轉換，查詢可能會失敗。如要進一步瞭解標準 SQL 的類型轉換規則，請參閱函式與運算子參考文件中的類型轉換說明。

主控台

  1. 按一下 Cloud Console 中的 [Compose new query] (撰寫新查詢)。

  2. 在「Query editor」(查詢編輯器) 中輸入以下查詢，藉此選取 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. 按一下 [More] (更多) 並選取 [Query settings] (查詢設定)。

  4. 在「Destination」(目的地) 部分中，勾選 [Set a destination table for query results] (為查詢結果設定目標資料表)。

  5. 在以下欄位中：

    1. 保留「Project name」(專案名稱) 欄位中預設專案的設定值 (含有 mydataset.mytable 的專案)。

    2. 在「Dataset name」(資料集名稱) 部分，選擇 mydataset。

    3. 在「Table name」(資料表名稱) 欄位中輸入 mytable。

    4. 按一下 [OK] (確定)。

  6. 在「Destination table write preference」(目標資料表寫入偏好設定) 部分中，針對「Write Preference」(寫入偏好設定) 選擇 [Overwrite table] (覆寫資料表)。這樣一來，您就能使用查詢結果覆寫 mytable。

  7. (選用) 在「Processing location」(處理位置)，按一下 [Auto-select] (自動選取)，然後選擇資料的位置。

  8. 按一下 [Save] (儲存) 以更新設定，然後在「Query editor」(查詢編輯器) 中按一下 [Run] (執行)。查詢工作完成時，column_one 的資料類型就會是 STRING。

傳統版 UI

  1. 在 BigQuery 傳統網頁版 UI 中，按一下 [Compose Query] (撰寫查詢)。

  2. 在「New Query」(新查詢) 方塊中輸入以下查詢，藉此選取 mydataset.mytable 中 column_two 和 column_three 的所有資料，並將 column_one 由 DATE 轉換為 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」(資料表 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.mytable 中 column_two 和 column_three 的所有資料，並將 column_one 由 DATE 轉換為 STRING。這項查詢會使用別名轉換具備相同名稱的 column_one。mydataset.mytable 在您的預設專案中。

查詢結果會使用 --destination_table 旗標寫入 mydataset.mytable，並使用 --replace 旗標覆寫 mytable。指定 use_legacy_sql=false 旗標以使用標準 SQL 語法。

(選用) 提供 --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，請將 mydataset.mytable 加入 configuration.query.destinationTable 屬性，並在 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 載入與匯出工作。
  • 如果使用載入工作覆寫原始資料表，只會產生一個資料表 (而不是兩個) 的儲存空間費用，但您將失去原始資料。

缺點

  • 如果將資料載入至新的資料表，會產生原始資料表和新資料表兩者的儲存空間費用 (除非刪除舊資料表)。
  • 您必須付費才能將匯出的資料儲存在 Cloud Storage 中。

從資料表結構定義中刪除資料欄

Cloud Console、BigQuery 傳統網頁版 UI、指令列工具或 API 均不支援從現有資料表的結構定義刪除資料欄。如果您嘗試套用結構定義來移除資料欄並更新資料表，系統會傳回以下錯誤訊息：BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

您可以透過以下兩個方法手動刪除資料欄：

  • 使用 SQL 查詢 - 如果您更在意簡便和易用程度，而成本不是主要考量的話，請選擇這種方法。
  • 重新建立資料表 - 如果您更在意成本，而簡便和易用程度不是主要考量的話，請選擇這種方法。

方法 1：使用查詢

使用 SELECT * EXCEPT 查詢排除您要移除的一或多個資料欄，並使用查詢結果覆寫資料表，或是建立新的目標資料表。

優點

  • 使用查詢將資料寫入新的目標資料表可保留原始資料。
  • 如果使用查詢工作覆寫原始資料表，只會產生一個資料表 (而不是兩個) 的儲存空間費用，但您將失去原始資料。

缺點

  • 您必須掃描所有資料欄 (要移除的資料欄除外) 中的資料，才能使用查詢刪除資料欄。如果資料表非常龐大，查詢費用可能會相當可觀。
  • 如果將查詢結果寫入新的目標資料表，會同時產生新舊資料表兩者的儲存空間費用 (除非刪除舊資料表)。

方法 2：匯出資料並載入至新的資料表

您也可以透過下列方式移除資料欄：將資料表中的資料匯出至 Cloud Storage，刪除待移除的一或多個資料欄相應的資料，再將結構定義不含遭移除資料欄的資料載入至新的資料表。您還可以使用載入工作覆寫現有的資料表。

優點

  • 您不需為匯出工作或載入工作付費。目前可免費執行 BigQuery 載入與匯出工作。
  • 如果使用載入工作覆寫原始資料表，只會產生一個資料表 (而不是兩個) 的儲存空間費用，但您將失去原始資料。

缺點

  • 如果將資料載入至新的資料表，會產生原始資料表和新資料表兩者的儲存空間費用 (除非刪除舊資料表)。
  • 您必須付費才能將匯出的資料儲存在 Cloud Storage 中。

SELECT * EXCEPT 範例

以下範例使用標準 SQL 查詢選取 mydataset.mytable 中的所有資料 (column_two 除外)，並使用查詢結果覆寫現有的資料表。

主控台

  1. 按一下 Cloud Console 中的 [Compose new query] (撰寫新查詢)。

  2. 在「Query editor」(查詢編輯器) 中輸入以下查詢，藉此選取 mydataset.mytable 中的所有資料 (column_two 除外)。mydataset.mytable 在您的預設專案中。

    SELECT
 * EXCEPT(column_two)
FROM
 mydataset.mytable

  3. 按一下 [More] (更多) 並選取 [Query settings] (查詢設定)。

  4. 在「Destination」(目的地) 部分中，勾選 [Set a destination table for query results] (為查詢結果設定目標資料表)。

  5. 在以下欄位中：

    1. 保留「Project name」(專案名稱) 欄位中預設專案的設定值 (含有 mydataset.mytable 的專案)。

    2. 在「Dataset name」(資料集名稱) 部分，選擇 mydataset。

    3. 在「Table name」(資料表名稱) 欄位中輸入 mytable。

    4. 按一下 [OK] (確定)。

  6. 在「Destination table write preference」(目標資料表寫入偏好設定) 部分中，針對「Write Preference」(寫入偏好設定) 選擇 [Overwrite table] (覆寫資料表)。這樣一來，您就能使用查詢結果覆寫 mytable。

  7. (選用) 在「Processing location」(處理位置)，按一下 [Auto-select] (自動選取)，然後選擇資料的位置。

  8. 按一下 [Save] (儲存) 以更新設定，然後在「Query editor」(查詢編輯器) 中按一下 [Run] (執行)。查詢工作完成時，資料表中就會包含 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」(資料表 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。指定 use_legacy_sql=false 旗標以使用標準 SQL 語法。

(選用) 提供 --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，請將 mydataset.mytable 加入 configuration.query.destinationTable 屬性，並在 configuration.query.writeDisposition 屬性中指定 WRITE_TRUNCATE。

本頁內容對您是否有任何幫助？請提供意見：

傳送您對下列選項的寶貴意見...

這個網頁
文件意見
BigQuery
產品意見
需要協助嗎？請前往我們的支援網頁。