修改資料表結構定義

本文件說明如何修改現有 BigQuery 資料表的結構定義。

您可以使用 SQL 資料定義語言 (DDL) 陳述式，執行本文所述的大部分結構定義修改作業。這些對帳單不會產生費用。

您可以匯出資料表資料至 Cloud Storage，然後載入資料至新的資料表 (並使用修改過的結構定義)，以便修改資料表結構定義。您可以免費執行 BigQuery 載入與匯出工作，但必須支付在 Cloud Storage 中儲存匯出資料的費用。以下各節說明執行各種型別的結構定義修改方式。

注意： 更新結構定義後，變更內容可能不會立即反映在 INFORMATION_SCHEMA.TABLES 和 INFORMATION_SCHEMA.COLUMNS 檢視畫面中。如要查看即時結構定義變更，請呼叫 tables.get 方法。

新增資料欄

您可以使用下列任一選項，將資料欄新增至現有資料表的結構定義：

• 新增空白資料欄。
• 使用載入或查詢工作覆寫資料表。
• 使用載入或查詢工作將資料附加至資料表。

您新增的任何資料欄都必須遵守 BigQuery 的資料欄名稱規則。如要進一步瞭解如何建立結構定義元件，請參閱指定結構定義。

新增空白資料欄

新增資料欄至現有的資料表結構定義時，資料欄必須為 NULLABLE 或 REPEATED。您無法將 REQUIRED 資料欄新增至現有的資料表結構定義。在 API 或 bq 指令列工具中，將 REQUIRED 資料欄新增至現有的資料表結構定義會導致錯誤。不過，您可以建立巢狀 REQUIRED 欄，做為新 RECORD 欄位的一部分。只有在載入資料時建立資料表，或者使用結構定義建立空白資料表時，您才能新增 REQUIRED 資料欄。

如何將空白資料欄新增至資料表的結構定義：

主控台

1. 前往 Google Cloud 控制台的「BigQuery」頁面。

   前往 BigQuery

2. 在「Explorer」面板中展開專案和資料集，然後選取資料表。

3. 在詳細資料面板中，按一下「Schema」分頁標籤。

4. 點選「編輯結構定義」。你可能需要捲動畫面才能看到這個按鈕。

5. 在「Current schema」頁面的「New fields」下方，按一下「Add field」。

   • 在「Name」(名稱) 部分，輸入資料欄名稱。
   • 針對「Type」(類型)，請選擇資料類型。
   • 針對「Mode」(模式)，請選擇 NULLABLE 或 REPEATED。

6. 資料欄新增完畢後，按一下 [Save] (儲存)。

注意： 您無法使用 Google Cloud 控制台將資料欄新增至外部資料表。

SQL

使用 ALTER TABLE ADD COLUMN DDL 陳述式：

1. 前往 Google Cloud 控制台的「BigQuery」頁面。

   前往 BigQuery

2. 在查詢編輯器中輸入以下陳述式：

   ALTER TABLE mydataset.mytable
   ADD COLUMN new_column STRING;

3. 按一下 「Run」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」一文。

注意： 您無法使用 ALTER TABLE ADD COLUMN 陳述式將資料欄新增至外部資料表。

bq

發出 bq update 指令並提供 JSON 結構定義檔。如果您要更新的資料表位於非預設專案中，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET。

bq update PROJECT_ID:DATASET.TABLE SCHEMA

更改下列內容：

• PROJECT_ID：您的專案 ID。
• DATASET：含有您要更新的資料表的資料集名稱。
• TABLE：您要更新的資料表名稱。
• SCHEMA：您本機電腦上的 JSON 結構定義檔路徑。

指定內嵌結構定義時，您無法指定資料欄說明、模式和 RECORD (STRUCT) 類型。所有資料欄模式都會預設為 NULLABLE。因此如要新增巢狀資料欄至 RECORD，就必須提供 JSON 結構定義檔。

如果您嘗試使用內嵌結構定義新增資料欄，則必須提供包括新資料欄在內的整個結構定義。您無法使用內嵌結構定義指定資料欄模式，因此更新作業會將任何現有的 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. 首先，請發出 bq show 指令並加上 --schema 旗標，並將現有的資料表結構定義寫入檔案。如果您要更新的資料表位於非預設專案中，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET。

   bq show \
   --schema \
   --format=prettyjson \
   PROJECT_ID:DATASET.TABLE > SCHEMA

   更改下列內容：

   • PROJECT_ID：您的專案 ID。
   • DATASET：含有您要更新的資料表的資料集名稱。
   • TABLE：您要更新的資料表名稱。
   • SCHEMA：寫入本機的結構定義檔案。

   舉例來說，如要將 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 陣列會如下所示。此範例中會新增名為 column4 的 NULLABLE 資料欄，且 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. 更新結構定義檔後，請發出下列指令來更新資料表的結構定義。如果您要更新的資料表位於預設專案以外的專案中，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET。

   bq update PROJECT_ID:DATASET.TABLE SCHEMA

   更改下列內容：

   • PROJECT_ID：您的專案 ID。
   • DATASET：含有您要更新的資料表的資料集名稱。
   • TABLE：您要更新的資料表名稱。
   • SCHEMA：寫入本機的結構定義檔案。

   例如，輸入以下指令來更新預設專案中 mydataset.mytable 的結構定義。您本機機器上的 JSON 結構定義檔路徑為 /tmp/myschema.json。

   bq update mydataset.mytable /tmp/myschema.json
   

API

呼叫 tables.patch 方法，並使用 schema 屬性將空白資料欄新增至結構定義。由於 tables.update 方法會取代整個資料表資源，因此建議使用 tables.patch 方法。

Go

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

// 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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

將新的 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 資料欄。新增巢狀資料欄的程序與新增資料欄的程序類似。

主控台

Google Cloud 主控台不支援在現有的 RECORD 資料欄中新增巢狀欄位。

SQL

系統不支援使用 SQL DDL 陳述式，在現有的 RECORD 資料欄中新增巢狀欄位。

bq

發出 bq update 指令並提供 JSON 結構定義檔，該檔案會將巢狀欄位新增至現有 RECORD 資料欄的結構定義。如果您要更新的資料表位於非預設專案中，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET。

bq update PROJECT_ID:DATASET.TABLE SCHEMA

更改下列內容：

• PROJECT_ID：您的專案 ID。
• DATASET：含有您要更新的資料表的資料集名稱。
• TABLE：您要更新的資料表名稱。
• SCHEMA：您本機電腦上的 JSON 結構定義檔路徑。

指定內嵌結構定義時，您無法指定資料欄說明、模式和 RECORD (STRUCT) 類型。所有資料欄模式都會預設為 NULLABLE。因此如要新增巢狀資料欄至 RECORD，就必須提供 JSON 結構定義檔。

若要使用 JSON 結構檔案將巢狀欄位新增至 RECORD，請進行以下操作：

1. 首先，請發出 bq show 指令並加上 --schema 旗標，並將現有的資料表結構定義寫入檔案。如果您要更新的資料表位於非預設專案中，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET.TABLE。

   bq show \
   --schema \
   --format=prettyjson \
   PROJECT_ID:DATASET.TABLE > SCHEMA

   更改下列內容：

   • PROJECT_ID：您的專案 ID。
   • DATASET：含有您要更新的資料表的資料集名稱。
   • TABLE：您要更新的資料表名稱。
   • SCHEMA：寫入本機的結構定義檔案。

   舉例來說，如要將 mydataset.mytable 的結構定義寫入檔案，請輸入下列指令。mydataset.mytable 位於您的預設專案中。

   bq show \
   --schema \
   --format=prettyjson \
   mydataset.mytable > /tmp/myschema.json
   

2. 在文字編輯器中開啟結構定義檔。結構定義應如下所示。在此範例中，column3 是巢狀的重複資料欄。巢狀資料欄為 nested1 和 nested2。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. 更新結構定義檔後，請發出下列指令來更新資料表的結構定義。如果您要更新的資料表位於預設專案以外的專案中，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET。

   bq update PROJECT_ID:DATASET.TABLE SCHEMA

   更改下列內容：

   • PROJECT_ID：您的專案 ID。
   • DATASET：含有您要更新的資料表的資料集名稱。
   • TABLE：您要更新的資料表名稱。
   • SCHEMA：您本機電腦上的 JSON 結構定義檔路徑。

   例如，輸入以下指令來更新預設專案中 mydataset.mytable 的結構定義。您本機機器上的 JSON 結構定義檔路徑為 /tmp/myschema.json。

   bq update mydataset.mytable /tmp/myschema.json
   

API

呼叫 tables.patch 方法，並使用 schema 屬性將巢狀資料欄新增至結構定義。由於 tables.update 方法會取代整個資料表資源，因此建議使用 tables.patch 方法。

在覆寫或附加資料時新增資料欄

將資料載入現有資料表並選擇覆寫現有資料表時，可以新增資料欄至現有資料表。覆寫現有的資料表時，系統會使用所載入資料的結構定義來覆寫現有資料表的結構定義。如要瞭解如何使用載入工作覆寫資料表，請參閱資料格式的說明文件：

• Avro
• Parquet
• ORC
• CSV
• JSON

在載入附加工作中新增資料欄

您可以在載入工作中將資料附加至資料表時，一併新增資料欄。新結構定義會根據下列任一項目決定：

• 自動偵測 (適用於 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，以指出要附加的資料中包含新的資料欄。

如果您要附加的資料表位於非預設專案中的資料集裡，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET。

(選用) 提供 --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_JSON、CSV、AVRO、PARQUET、ORC 或 DATASTORE_BACKUP。
• PROJECT_ID：您的專案 ID。
• DATASET：含有資料表的資料集名稱。
• TABLE：您要附加的資料表名稱。
• PATH_TO_SOURCE：完整的 Cloud Storage URI、以逗號分隔的 URI 清單，或您本機機器上的資料檔案路徑。
• SCHEMA：本機 JSON 結構定義檔的路徑。不指定 --autodetect 時，只有 CSV 和 JSON 檔案需要結構定義檔。系統會從來源資料推斷 Avro 和 Datastore 的結構定義。

範例：

輸入下列指令，使用載入工作將本機 Avro 資料檔案 (/tmp/mydata.avro) 附加至 mydataset.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

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

// 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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

# 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，以指出要附加的查詢結果中包含新的資料欄。

指定 use_legacy_sql=false 旗標，以便在查詢中使用 GoogleSQL 語法。

如果您要附加的資料表位於非預設專案中的資料集裡，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET。請注意，您要查詢的資料表和目的地資料表必須位於同一位置。

(選用) 提供 --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：採用 GoogleSQL 語法的查詢。

範例：

輸入下列指令，以查詢預設專案中的 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，並將查詢結果附加至位於 myotherproject 中的 mydataset.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 屬性指定 GoogleSQL 查詢。

Go

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

// 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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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.
client.query_and_wait(
    # 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 and wait for 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;

如要進一步瞭解 ALTER TABLE RENAME COLUMN 陳述式，請參閱 DDL 詳細資料。

變更資料欄的資料類型

Google Cloud 主控台、bq 指令列工具或 BigQuery API 均不支援變更資料欄的資料類型。如果您嘗試套用結構定義，藉此為資料欄指定新資料類型來更新資料表，系統會傳回錯誤訊息。

使用 DDL 陳述式變更資料欄的資料類型

您可以使用 GoogleSQL 對資料欄的資料類型進行特定變更。如需更多資訊和支援的資料類型轉換完整清單，請參閱 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;

以下範例會建立一個含有兩個欄位的巢狀資料欄的資料表，然後將其中一個欄的類型從 INT 更新為 NUMERIC：

CREATE TABLE mydataset.mytable(s1 STRUCT<a INT64, b STRING>);

ALTER TABLE mydataset.mytable ALTER COLUMN s1
SET DATA TYPE STRUCT<a NUMERIC, b STRING>;

修改巢狀欄類型

對於複雜的巢狀結構定義變更 (例如在 STRUCT 陣列中變更欄位)，系統不支援 ALTER TABLE DDL 陳述式。解決方法是使用 CREATE OR REPLACE TABLE 陳述式搭配 SELECT 陳述式，轉換巢狀結構定義變更。

以下範例說明如何轉換 STRUCTS 陣列中的資料欄：

請考慮使用下列結構定義和資料的資料表 samples.test：

CREATE OR REPLACE TABLE
  samples.test(D STRUCT <L ARRAY<STRUCT<R STRING, U STRING, V STRING>>, F STRING>);

INSERT INTO
  samples.test(D)
VALUES
  (STRUCT([STRUCT("r1", "u1", "v1"), STRUCT("r2", "u2", "v2")], "f1"));

傳回的結果看起來類似下列內容：

+----------------------------------------------------------------------------+
|                                     D                                      |
+----------------------------------------------------------------------------+
| {"L":[{"R":"r1","U":"u1","V":"v1"},{"R":"r2","U":"u2","V":"v2"}],"F":"f1"} |
+----------------------------------------------------------------------------+

假設您需要將 STRUCT 的巢狀陣列內的欄位 U 類型變更為 STRUCT<W STRING>。以下 SQL 陳述式說明如何完成這項操作：

CREATE OR REPLACE TABLE
  samples.new_table AS
SELECT
  STRUCT(ARRAY(
    SELECT
      STRUCT(tmp.R,
        STRUCT(tmp.U AS W) AS U,
        tmp.V)
    FROM
      UNNEST(t.D.L) AS tmp) AS L,
    t.D.F) AS D
FROM
  samples.test AS t

這個陳述式會使用目標結構定義建立新資料表 samples.new_table。UNNEST 函式會在 t.D.L 中展開 STRUCT 陣列。運算式 STRUCT(tmp.U AS W) AS U 會使用欄位 W 建構新的 STRUCT，並以原始 U 欄位的值填入資料。產生的資料表 samples.new_table 具有下列結構定義和資料：

+----------------------------------------------------------------------------------------+
|                                           D                                            |
+----------------------------------------------------------------------------------------+
| {"L":[{"R":"r1","U":{"W":"u1"},"V":"v1"},{"R":"r2","U":{"W":"u2"},"V":"v2"}],"F":"f1"} |
+----------------------------------------------------------------------------------------+

轉換資料欄的資料類型

如要將資料欄的資料類型變更為可轉換的類型，請使用 SQL 查詢選取資料表資料、轉換相關資料欄，然後覆寫資料表。對於非常大型的資料表，不建議進行轉換和覆寫，因為這需要完整的資料表掃描。

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

使用 CAST 時，如果 BigQuery 無法執行類型轉換，查詢可能會失敗。如要進一步瞭解 GoogleSQL 中的類型轉換規則，請參閱「類型轉換」一文。

主控台

1. 前往 Google Cloud 控制台的「BigQuery」頁面。

   前往 BigQuery

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」部分中，執行下列操作：

   1. 選取「Set a destination table for query results」(為查詢結果設定目標資料表)。

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

   3. 在「Dataset」(資料集) 部分，選擇 mydataset。

   4. 在「Table ID」欄位中輸入 mytable。

   5. 針對「Destination table write preference」(目標資料表寫入偏好設定)，選取「Overwrite table」(覆寫資料表)。這個選項會使用查詢結果覆寫 mytable。

5. 視需要選擇資料的位置。

6. 如要更新設定，請按一下「儲存」。

7. 按一下 「Run」。

   查詢工作完成時，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。指定 use_legacy_sql=false 旗標以使用 GoogleSQL 語法。

您可以選擇提供 --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 也稱為資料欄放寬。您也可以在載入資料覆寫現有資料表，或將資料附加至現有資料表時，放寬資料欄。您無法將資料欄的模式從 NULLABLE 變更為 REQUIRED。

在現有資料表中建立資料欄 NULLABLE

如要將資料欄的模式從 REQUIRED 變更為 NULLABLE，請選取下列其中一個選項：

主控台

1. 前往「BigQuery」頁面

   前往 BigQuery

2. 在「Explorer」面板中展開專案和資料集，然後選取資料表。

3. 在詳細資料面板中，按一下「Schema」分頁標籤。

4. 點選「編輯結構定義」。你可能需要捲動畫面才能看到這個按鈕。

5. 在「現行結構定義」頁面中，找出要變更的欄位。

6. 在該欄位的「模式」下拉式清單中，選取 NULLABLE。

7. 如要更新設定，請按一下「儲存」。

注意： 您無法使用 Google Cloud 主控台變更外部資料表中的資料欄。

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. 按一下 「Run」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」一文。

bq

1. 首先，請發出 bq show 指令並加上 --schema 旗標，並將現有的資料表結構定義寫入檔案。如果您要更新的資料表位於非預設專案中，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET。

   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. 更新結構定義檔後，請發出下列指令來更新資料表的結構定義。如果您要更新的資料表位於非預設專案中，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET。

   bq update PROJECT_ID:DATASET.TABLE SCHEMA

   更改下列內容：

   • PROJECT_ID：您的專案 ID。
   • DATASET：含有您要更新的資料表的資料集名稱。
   • TABLE：您要更新的資料表名稱。
   • SCHEMA：您本機電腦上的 JSON 結構定義檔路徑。

   例如，輸入以下指令來更新預設專案中 mydataset.mytable 的結構定義。您本機機器上的 JSON 結構定義檔路徑為 /tmp/myschema.json。

     bq update mydataset.mytable /tmp/myschema.json
   

API

呼叫 tables.patch 並使用 schema 屬性，將結構定義中的 REQUIRED 資料欄變更為 NULLABLE。由於 tables.update 方法會取代整個資料表資源，因此建議使用 tables.patch 方法。

Go

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

// 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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

覆寫 Table.schema 屬性，使用 SchemaField 物件清單，並將 mode 屬性設為 'NULLABLE'

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change table_id to full name of the table you want to create.
table_id = "your-project.your_dataset.your_table"

table = client.get_table(table_id)
new_schema = []
for field in table.schema:
    if field.mode != "REQUIRED":
        new_schema.append(field)
    else:
        # SchemaField properties cannot be edited after initialization.
        # To make changes, construct new SchemaField objects.
        new_field = field.to_api_repr()
        new_field["mode"] = "NULLABLE"
        relaxed_field = bigquery.SchemaField.from_api_repr(new_field)
        new_schema.append(relaxed_field)

table.schema = new_schema
table = client.update_table(table, ["schema"])

print(f"Updated {table_id} schema: {table.schema}.")

使用附加載入工作建立資料欄 NULLABLE

在載入工作期間將資料附加至資料表時，您可以放寬資料欄的模式。請根據檔案類型選取下列任一選項：

• 從 CSV 和 JSON 檔案附加資料時，請指定 JSON 結構定義檔，放寬個別資料欄的模式。
• 附加 Avro、ORC 或 Parquet 檔案中的資料時，請在結構定義中將資料欄放寬為 NULL，並讓結構推斷功能偵測放寬的資料欄。

注意：資料欄放寬不適用於 Datastore 匯出檔的附加作業。透過載入 Datastore 匯出檔所建立的資料表中，其資料欄一律為 NULLABLE。

。

如要在載入工作期間將資料附加到資料表時，一併將資料欄從 REQUIRED 放寬為 NULLABLE，請選取下列任一選項：

主控台

您無法使用 Google Cloud 主控台放寬資料欄的模式。

bq

請使用 bq load 指令載入資料並指定 --noreplace 旗標，以指出您要將資料附加至現有的資料表。

如果您要附加的資料是 CSV 格式，或是以換行符號分隔的 JSON 格式，請在本機 JSON 結構定義檔中指定放寬的資料欄，或透過 --autodetect 標記使用結構定義偵測功能，在來源資料中找出放寬的資料欄。

系統可以從 Avro、ORC 和 Parquet 檔案自動推斷放寬的資料欄。不過，資料欄放寬不適用於 Datastore 匯出檔的附加作業。透過載入 Datastore 匯出檔所建立的資料表，其資料欄一律為 NULLABLE。

將 --schema_update_option 旗標設為 ALLOW_FIELD_RELAXATION，以指出您要附加的資料中包含放寬的資料欄。

如果您要附加的資料表位於非預設專案中的資料集裡，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET。

(選用) 提供 --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_JSON、CSV、PARQUET、ORC 或 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.avro) 附加至 mydataset.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

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

// 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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

# 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 主控台放寬資料欄的模式。

bq

使用 bq query 指令查詢資料，並指定 --destination_table 旗標來指出您要附加的資料表。

如要指定將查詢結果附加至現有目的地資料表，請指定 --append_table 旗標。

將 --schema_update_option 旗標設為 ALLOW_FIELD_RELAXATION，以指出您要附加之資料表中的所有 REQUIRED 資料欄，都應變更為 NULLABLE。

指定 use_legacy_sql=false 旗標，以便在查詢中使用 GoogleSQL 語法。

如果您要附加的資料表位於非預設專案中的資料集裡，請依照下列格式將該專案的 ID 加到資料集名稱中：PROJECT_ID:DATASET。

(選用) 提供 --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：採用 GoogleSQL 語法的查詢。

範例：

輸入下列指令，查詢預設專案中的 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，並將查詢結果附加至位於 myotherproject 中的 mydataset.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 屬性指定 GoogleSQL 查詢。

Go

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

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 number of required fields.
table = client.get_table(table_id)  # Make an API request.
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)

# In this example, the existing table has 2 required fields.
print("{} fields in the schema are required.".format(original_required_fields))

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

# Start the query, passing in the extra configuration.
client.query_and_wait(
    # In this example, the existing table contains 'full_name' and 'age' as
    # required columns, but the query results will omit the second column.
    'SELECT "Beyonce" as full_name;',
    job_config=job_config,
)  # Make an API request and wait for job to complete

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

變更資料欄的預設值

如要變更資料欄的預設值，請選取下列其中一個選項：

主控台

1. 前往 Google Cloud 控制台的「BigQuery」頁面。

   前往 BigQuery

2. 在「Explorer」面板中展開專案和資料集，然後選取資料表。

3. 在詳細資料面板中，按一下「Schema」分頁標籤。

4. 點選「編輯結構定義」。你可能需要捲動畫面才能看到這個按鈕。

5. 在「Current schema」頁面中，找出要變更的頂層欄位。

6. 輸入該欄位的預設值。

7. 按一下 [儲存]。

SQL

使用 ALTER COLUMN SET DEFAULT DDL 陳述式。

1. 前往 Google Cloud 控制台的「BigQuery」頁面。

   前往 BigQuery

2. 在查詢編輯器中輸入以下陳述式：

   ALTER TABLE mydataset.mytable
   ALTER COLUMN column_name SET DEFAULT default_expression;

3. 按一下 「Run」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」一文。

變更資料欄說明

如要變更資料欄的說明，請選取下列其中一個選項：

主控台

1. 前往 Google Cloud 控制台的「BigQuery」頁面。

   前往 BigQuery

2. 在「Explorer」面板中展開專案和資料集，然後選取資料表。

3. 在詳細資料面板中，按一下「Schema」分頁標籤。

4. 點選「編輯結構定義」。你可能需要捲動畫面才能看到這個按鈕。

5. 在「Current schema」頁面中，找出要變更的欄位。

6. 輸入該欄位的說明。

7. 按一下 [儲存]。

SQL

使用 ALTER COLUMN SET OPTIONS DDL 陳述式。

1. 前往 Google Cloud 控制台的「BigQuery」頁面。

   前往 BigQuery

2. 在查詢編輯器中輸入以下陳述式：

   ALTER TABLE mydataset.mytable
   ALTER COLUMN column_name
   SET OPTIONS (description = 'This is a column description.');

3. 按一下 「Run」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」一文。

刪除資料欄

您可以使用 ALTER TABLE DROP COLUMN DDL 陳述式，從現有資料表中刪除資料欄。

這項陳述式不會立即釋出與已刪除資料欄相關聯的儲存空間。如要進一步瞭解在儲存空間中刪除資料欄對儲存空間的影響，請參閱 ALTER TABLE DROP COLUMN 陳述式的詳細資料。您可以透過兩種方式立即回收儲存空間：

• 使用 SELECT * EXCEPT 查詢覆寫資料表：

  CREATE OR REPLACE TABLE mydataset.mytable AS (
    SELECT * EXCEPT (column_to_delete) FROM mydataset.mytable
  );
  

• 將資料匯出至 Cloud Storage，刪除不需要的資料欄，然後將資料載入至含有正確結構定義的新資料表。