修改資料表結構定義

本文件說明如何修改現有 BigQuery 資料表的結構定義。BigQuery 可以直接支援以下的結構定義修改作業：

• 將資料欄新增至結構定義
• 將資料欄的模式從 REQUIRED 放寬為 NULLABLE

您可以在不定義初始結構的情況下建立資料表，稍後再將結構定義加入資料表中。

修改其他類型的結構定義並不在支援的範圍內，您必須自行採取變通方法。這些修改作業包括：

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

如要進一步瞭解這類不受支援而必須另尋解決方法的結構定義變更，請參閱手動變更資料表結構定義一文。

將資料欄新增至資料表的結構定義

您可以透過以下方式將資料欄新增至現有資料表的結構定義：

• 手動 (建立空白資料欄)
• 使用載入或查詢工作覆寫資料表時
• 使用載入或查詢工作將資料附加到資料表時

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

手動新增空白資料欄

您可以透過以下方式，將空白資料欄新增至現有的資料表：

• 使用 GCP 主控台或傳統 BigQuery 網頁版 UI
• 使用指令列工具的 bq update 指令
• 呼叫 tables.patch API 方法

新增至現有資料表結構定義的資料欄必須為 NULLABLE 或 REPEATED。您無法將 REQUIRED 資料欄新增至現有的資料表結構定義。如果您嘗試在 CLI 或 API 中將 REQUIRED 資料欄新增至現有的資料表結構定義，系統將會傳回以下錯誤訊息：BigQuery error in update operation: Provided Schema does not match Table [PROJECT_ID]:[DATASET].[TABLE]. Cannot add required columns to an existing schema。只有在載入資料時建立資料表，或者使用結構定義建立空白資料表時才能新增 REQUIRED 資料欄。

將資料欄新增至資料表的結構定義後，您就可以使用以下方法將資料載入新的資料欄：

• DML 陳述式，可針對每一列執行批次更新
• 載入工作，可覆寫資料表
• 查詢結果，可覆寫資料表

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

主控台

1. 在「Resources」(資源) 窗格中，選取資料表。

2. 在「Query editor」(查詢編輯器) 下方，捲動至「Schema」(結構定義) 區段並按一下 [Edit schema](編輯結構定義)。

1. 捲動至開啟的面板底部，按一下 [Add field] (新增欄位)。

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

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

傳統版 UI

1. 在導覽窗格中，選取資料表。

2. 在「Table Details」(資料表詳細資料) 頁面上，按一下 [Add New Fields] (新增欄位)。

3. 在「New Fields」(新增欄位) 區段中：

   • 在「Name」(名稱) 部分，輸入資料欄名稱。
   • 在「Type」(類型) 部分，選擇資料類型。

   • 在「Mode」(模式) 部分，選擇 NULLABLE 或 REPEATED。

     

4. 資料欄新增完畢後，請按一下 [Add to Table] (新增至資料表)。

CLI

發出 bq update 指令並提供 JSON 結構定義檔案。如果您要更新的資料表位於預設專案以外的專案中，請使用下列格式將專案 ID 新增至資料集名稱：[PROJECT_ID]:[DATASET]。

  bq update [PROJECT_ID]:[DATASET].[TABLE] [SCHEMA]

其中：

• [PROJECT_ID] 是您的專案 ID。
• [DATASET] 是包含您要更新之資料表的資料集名稱。
• [TABLE] 是您要更新之資料表的名稱。
• [SCHEMA] 是您本機上的 JSON 結構定義檔案路徑。

在指令列中指定結構定義時，不能包含 RECORD (STRUCT) 類型和資料欄說明，也不能指定資料欄的模式。所有模式均預設為 NULLABLE。

如果您嘗試使用內嵌結構定義新增資料欄，則必須提供包括新資料欄在內的整個結構定義。由於您無法使用內嵌結構定義指定資料欄模式，因此更新作業會嘗試將任何現有的 REQUIRED 資料欄變更為 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。

使用 CLI 將資料欄新增至現有資料表的建議方法是提供 JSON 結構定義檔案。

如何使用 JSON 結構定義檔案將空白資料欄新增至資料表的結構定義：

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. 將資料欄新增至結構定義的結尾處。如果您嘗試在陣列的其他地方新增資料欄，系統將會傳回以下錯誤訊息：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] 是您本機上的 JSON 結構定義檔案路徑。

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

   bq update mydataset.mytable /tmp/myschema.json
   

API

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

Go 應用程式

前往 GitHub 查看

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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
}

Python

在試行此示例之前，請至 BigQuery 快速入門導覽課程：使用用戶端程式庫，按照 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

將新的 SchemaField 物件附加至 Table.schema 的副本，然後用已更新的結構定義取代 Table.schema 屬性的值。

前往 GitHub 查看

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
# table_id = 'my_table'

table_ref = client.dataset(dataset_id).table(table_id)
table = client.get_table(table_ref)  # 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"])  # API request

assert len(table.schema) == len(original_schema) + 1 == len(new_schema)

將巢狀資料欄新增至 RECORD

除了新增新的資料欄至資料表的結構之外，您亦可以將巢狀資料欄新增至 RECORD，這個過程與新增資料欄的操作相當類似。

主控台

主控台 BigQuery 網頁版 UI 目前不支援於現有的 RECORD 資料欄新增巢狀欄位。

傳統版 UI

傳統 BigQuery 網頁版 UI 目前不支援於現有的 RECORD 資料欄新增巢狀欄位。

CLI

發出 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]。

   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. 在文字編輯器中開啟結構定義檔。結構定義應如下所示。在此範例中，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 的結構定義。您本機上的結構定義檔案路徑為 /tmp/myschema.json。

   bq update mydataset.mytable /tmp/myschema.json
   

API

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

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

將資料載入現有資料表並選擇覆寫現有資料表時，您可以一併將資料欄新增至現有資料表。覆寫現有的資料表時，所載入資料的結構定義會覆寫現有資料表的結構定義。如需使用載入工作覆寫資料表的相關資訊，請參閱：

• 將 Avro 資料附加到資料表或使用 Avro 資料覆寫資料表
• 使用 CSV 資料附加到資料表或覆寫資料表
• 將 JSON 資料附加到資料表或使用 JSON 資料覆寫資料表
• 將本機檔案附加到資料表或使用本機檔案覆寫資料表

您也可以使用載入或查詢工作，在將資料附加到現有的資料表時一併新增資料欄。

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

您可以藉由以下操作，在載入工作期間將資料附加至資料表時一併新增資料欄：

• 使用指令列工具的 bq load 指令
• 呼叫 API 的 jobs.insert 方法並設定載入工作

GCP 主控台或傳統 BigQuery 網頁版 UI 目前不支援在附加作業期間，將資料欄新增至現有的資料表。

在載入工作期間使用附加作業新增資料欄時，已更新的結構定義可以：

• 自動偵測 (適用於 CSV 和 JSON 檔案)
• 在 JSON 結構定義檔案中指定 (適用於 CSV 和 JSON 檔案)
• 從 Avro 和 Cloud Datastore 匯出檔案的資料自動推斷

若您在 JSON 檔案中指定結構，則必須在其中定義新的資料欄。如果缺少新的資料欄定義，則當您嘗試附加資料時系統將會傳回以下錯誤訊息：Error while reading data, error message: parsing error in row starting at position [INT]: No such field: [FIELD].

在附加作業期間新增資料欄時，系統將會針對現有的列將新資料欄中的值設定為 NULL。

如何在載入工作期間，於資料附加到資料表時一併新增資料欄：

主控台

使用主控台 BigQuery 網頁版 UI 載入資料時，無法將資料欄新增至現有的資料表。

傳統版 UI

使用傳統 BigQuery 網頁版 UI 載入資料時，無法將資料欄新增至現有的資料表。

CLI

使用 bq load 指令載入資料並指定 --noreplace 標記以指示您要將資料附加到現有的資料表。

如果您要附加的資料是 CSV 格式，或是以換行符號分隔的 JSON 格式，請指定 --autodetect 標記以使用結構定義自動偵測，或在 JSON 結構定義檔案中提供結構定義。系統可以從 Avro 或 Cloud 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] 是您的位置名稱。如果您的資料位於 US 或 EU 多地區位置，則 --location 是選用標記。例如，如果您是在東京地區使用 BigQuery，請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
• [FORMAT] 是 NEWLINE_DELIMITED_JSON、CSV、AVRO 或 DATASTORE_BACKUP。
• [PROJECT_ID] 是您的專案 ID。
• [DATASET] 是包含該資料表之資料集的名稱。
• [TABLE] 是您要附加之資料表的名稱。
• [PATH_TO_SOURCE] 是完整的 Cloud Storage URI、以逗號分隔的 URI 清單，或您本機電腦上的資料檔案路徑。
• [SCHEMA] 是本機 JSON 結構定義檔案的路徑。不指定 --autodetect 時，只有 CSV 和 JSON 檔案需要結構定義檔案。系統會從來源資料推斷 Avro 和 Cloud Datastore 結構定義。

範例：

輸入下列指令，使用載入工作將本機 Avro 資料檔案 /tmp/mydata.avro 附加到 mydataset.mytable。由於新增的資料欄可以從 Avro 資料自動推斷，因此您不需要使用 --autodetect 標記。mydataset 位於預設專案中，而且是在 US 多個區域地點中建立的。

bq --location=US load --noreplace --schema_update_option=ALLOW_FIELD_ADDITION --source_format=AVRO mydataset.mytable /tmp/mydata.avro

輸入下列指令，使用載入工作將 Cloud Storage 中以換行符號分隔的 JSON 資料檔案附加到 mydataset.mytable 中。--autodetect 標記會用於偵測新的資料欄。mydataset 位於預設專案中，而且是在 US 多個區域地點中建立的。

bq --location=US 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 而非預設專案中。mydataset 是在 US 多個區域地點中建立的。

bq --location=US load --noreplace --schema_update_option=ALLOW_FIELD_ADDITION --source_format=NEWLINE_DELIMITED_JSON myotherproject:mydataset.mytable gs://mybucket/mydata.json /tmp/myschema.json

輸入下列指令，使用載入工作將 Cloud Storage 中以換行符號分隔的 JSON 資料檔案附加到 mydataset.mytable 中。--autodetect 標記會用於偵測新的資料欄。mydataset 位於預設專案中，而且是在 asia-northeast1 區域中建立的。

bq --location=asia-northeast1 load --noreplace --autodetect --schema_update_option=ALLOW_FIELD_ADDITION --source_format=NEWLINE_DELIMITED_JSON mydataset.mytable gs://mybucket/mydata.json

API

呼叫 jobs.insert 方法。在工作資源的 jobReference 區段的 location 屬性中指定您的區域。

設定載入工作並設定以下屬性：

• 使用 configuration.load.sourceUris 屬性，以參考您在 Cloud Storage 中的資料。
• 設定 configuration.load.sourceFormat 屬性，以指定資料格式。
• 在 schema 屬性中指定結構定義。
• 使用 schemaUpdateOptions 屬性，以指定結構定義更新選項。
• 使用 configuration.load.writeDisposition 屬性，將目的地資料表寫入配置設定為 WRITE_APPEND。

Go 應用程式

前往 GitHub 查看

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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
}

Python

在試行此示例之前，請至 BigQuery 快速入門導覽課程：使用用戶端程式庫，按照 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

前往 GitHub 查看 意見回饋

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('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 query 指令
• 呼叫 API 的 jobs.insert 方法並設定查詢工作

GCP 主控台或傳統 BigQuery 網頁版 UI 目前不支援在附加作業期間新增資料欄。

在查詢工作中使用附加作業新增資料欄時，系統會使用查詢結果的結構定義來更新目的地資料表的結構定義。

進行查詢工作期間，如何在將資料附加到資料表時一併新增資料欄：

主控台

使用主控台 BigQuery 網頁版 UI 附加查詢結果時，無法將資料欄新增至現有的資料表。

傳統版 UI

使用傳統 BigQuery 網頁版 UI 附加查詢結果時，無法將資料欄新增至現有的資料表。

CLI

使用 bq query 指令查詢您的資料並指定 --destination_table 標記以指示您要附加的資料表。

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

將 --schema_update_option 標記設定為 ALLOW_FIELD_ADDITION 以指示您要附加的查詢結果包含新的資料欄。

指定 use_legacy_sql=false 標記以在查詢時使用標準 SQL 語法。

如果您要附加的資料表位於預設專案以外的專案的資料集中，請使用下列格式將專案 ID 新增至資料集名稱：[PROJECT_ID]:[DATASET]。

提供 --location 標記，並將值設為您的位置。

bq --location=[LOCATION] query --destination_table [PROJECT_ID]:[DATASET].[TABLE] --append_table --schema_update_option  --use_legacy_sql=false '[QUERY]'

其中：

• [LOCATION] 是您的位置名稱。如果您的資料位於 US 或 EU 多地區位置，則 --location 是選用標記。例如，如果您是在東京地區使用 BigQuery，請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
• [PROJECT_ID] 是您的專案 ID。
• [DATASET] 是包含您要附加之資料表的資料集名稱。
• [TABLE] 是您要附加之資料表的名稱。
• [QUERY] 是使用標準 SQL 語法的查詢。

範例：

輸入下列指令，查詢預設專案中的 mydataset.mytable，並將查詢結果附加到 mydataset.mytable2 (也在預設專案中)。mydataset 是在 US 多個區域地點中建立的。

bq --location=US 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。mydataset 是在 US 多個區域地點中建立的。

bq --location=US query --destination_table myotherproject:mydataset.mytable2 --append_table --schema_update_option=ALLOW_FIELD_ADDITION --use_legacy_sql=false 'SELECT column1,column2 FROM mydataset.mytable'

輸入下列指令，查詢預設專案中的 mydataset.mytable，並將查詢結果附加到 mydataset.mytable2 (也在預設專案中)。mydataset 是在 asia-northeast1 區域中建立的。

bq --location=asia-northeast1 query --destination_table mydataset.mytable2 --append_table --schema_update_option=ALLOW_FIELD_ADDITION --use_legacy_sql=false 'SELECT column1,column2 FROM mydataset.mytable'

API

呼叫 jobs.insert 方法。在工作資源的 jobReference 區段的 location 屬性中指定您的區域。

設定查詢工作並設定以下屬性：

• 使用 configuration.query.destinationTable 屬性，以指定目的地資料表。
• 使用 configuration.query.writeDisposition 屬性，將目的地資料表寫入配置設定為 WRITE_APPEND。
• 使用 configuration.query.schemaUpdateOptions 屬性，以指定結構定義更新選項。
• 使用 configuration.query.query 屬性，以指定標準 SQL 查詢。

Go 應用程式

前往 GitHub 查看

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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
}

Python

在試行此示例之前，請至 BigQuery 快速入門導覽課程：使用用戶端程式庫，按照 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

前往 GitHub 查看 意見回饋

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')

# 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 query to append the results to a destination table,
# allowing field addition
job_config = bigquery.QueryJobConfig()
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION
]
job_config.destination = table_ref
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND

query_job = client.query(
    # 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;',
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location="US",
    job_config=job_config,
)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print("Query job {} complete.".format(query_job.job_id))

# Checks the updated length of the schema
table = client.get_table(table)
print("Table {} now contains {} columns.".format(table_id, len(table.schema)))

放寬資料欄的模式

目前，您唯一可以對資料欄模式進行的修改是將其從 REQUIRED 變更為 NULLABLE。將資料欄的模式從 REQUIRED 變更為 NULLABLE 也稱為資料欄放寬。您可以透過以下方式放寬 REQUIRED 資料欄：

• 手動
• 使用載入或查詢工作覆寫資料表時
• 使用查詢工作將資料附加到資料表時

注意：將 REQUIRED 資料欄變更為 NULLABLE 後即無法復原。

手動將 REQUIRED 資料欄變更為 NULLABLE

如何手動將資料欄的模式從 REQUIRED 變更為 NULLABLE：

• 使用 GCP 主控台或傳統 BigQuery 網頁版 UI
• 使用指令列工具的 bq update 指令
• 呼叫 tables.patch API 方法。

如何手動將資料欄的模式從 REQUIRED 變更為 NULLABLE：

主控台

您目前無法使用主控台 BigQuery 網頁版 UI 放寬資料欄的模式。

傳統版 UI

1. 展開您的資料集，然後選取資料表。

2. 在「Table Details」(資料表詳細資料) 頁面上，按一下 [Schema] (結構定義) 分頁標籤。

3. 按一下所需資料欄右側的向下箭號，然後選擇以下任一項：

   • Make NULLABLE (設為 NULLABLE) — 放寬個別資料欄的模式

   • All REQUIRED to NULLABLE (將所有 REQUIRED 變為 NULLABLE) — 將結構定義中的所有 REQUIRED 資料欄變更為 NULLABLE

     

4. 在「Confirm Mode Change」(確認模式變更) 對話方塊中，按一下 [OK] (確定)，將模式變更為 NULLABLE。請注意，這項變更無法復原。

CLI

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 的結構定義。您本機上的結構定義檔案路徑為 /tmp/myschema.json。

   bq update mydataset.mytable /tmp/myschema.json
   

API

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

Go 應用程式

前往 GitHub 查看

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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
}

Python

在試行此示例之前，請至 BigQuery 快速入門導覽課程：使用用戶端程式庫，按照 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

使用 mode (模式) 屬性設為 'NULLABLE' 的 SchemaField 物件清單覆寫 Table.schema 屬性

前往 GitHub 查看

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
# table_id = 'my_table'

original_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
table_ref = client.dataset(dataset_id).table(table_id)
table = bigquery.Table(table_ref, schema=original_schema)
table = client.create_table(table)
assert all(field.mode == "REQUIRED" for field in table.schema)

# SchemaField properties cannot be edited after initialization.
# To make changes, construct new SchemaField objects.
relaxed_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
table.schema = relaxed_schema
table = client.update_table(table, ["schema"])

assert all(field.mode == "NULLABLE" for field in table.schema)

在載入或查詢工作期間將 REQUIRED 變更為 NULLABLE

在將資料載入現有資料表的結構定義並選擇覆寫現有的資料表時，可以在現有資料表的結構定義中將 REQUIRED 資料欄放寬為 NULLABLE。覆寫現有的資料表時，所載入資料的結構定義會覆寫現有資料表的結構定義。如需使用載入工作覆寫資料表的相關資訊，請參閱：

• 將 Avro 資料附加到資料表或使用 Avro 資料覆寫資料表
• 使用 CSV 資料附加到資料表或覆寫資料表
• 將 JSON 資料附加到資料表或使用 JSON 資料覆寫資料表
• 將本機檔案附加到資料表或使用本機檔案覆寫資料表

當您使用查詢工作將資料附加到現有資料表的結構定義時，也可以將其中的 REQUIRED 資料欄放寬為 NULLABLE。

在載入附加工作期間將 REQUIRED 變更為 NULLABLE

您可以藉由以下操作，在載入工作期間將資料附加至資料表時放寬資料欄的模式：

• 使用指令列工具的 bq load 指令
• 呼叫 API 的 jobs.insert 方法並設定載入工作

GCP 主控台或傳統 BigQuery 網頁版 UI 目前不支援在附加作業期間變更資料欄的模式。

在載入工作期間使用附加作業放寬資料欄的模式時，您可以：

• 指定 JSON 結構定義檔案以放寬個別資料欄 (從 CSV 和 JSON 檔案附加資料時)
• 在 Avro 結構定義中將資料欄放寬為 null，並允許系統推斷結構定義以偵測放寬的資料欄

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

進行載入工作期間將資料附加到資料表時，一併將資料欄從 REQUIRED 放寬為 NULLABLE：

主控台

您目前無法使用主控台 BigQuery 網頁版 UI 放寬資料欄的模式。

傳統版 UI

使用傳統 BigQuery 網頁版 UI 在載入工作期間將資料附加到資料表時，無法放寬現有資料欄的模式。

CLI

使用 bq load 指令載入資料並指定 --noreplace 標記以指示您要將資料附加到現有的資料表。

若您要附加的資料是採用 CSV 格式，或以換行符號分隔的 JSON 格式，請在本機 JSON 結構定義檔中指定放寬的資料欄，或透過 --autodetect 標記使用結構定義偵測以在來源資料中探索放寬的資料欄。如要瞭解使用 JSON 結構定義檔放寬資料欄模式的相關資訊，請參閱手動將 REQUIRED 資料欄變更為 NULLABLE。

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

將 --schema_update_option 標記設定為 ALLOW_FIELD_RELAXATION 以指示您要附加的資料包含放寬的資料欄。

如果您要附加的資料表位於預設專案以外的專案的資料集中，請使用下列格式將專案 ID 新增至資料集名稱：[PROJECT_ID]:[DATASET]。

提供 --location 標記，並將值設為您的位置。

輸入 load 指令，如下所示：

bq --location=[LOCATION] load --noreplace --schema_update_option --source_format=[FORMAT] [PROJECT_ID]:[DATASET].[TABLE] [PATH_TO_SOURCE] [SCHEMA]

其中：

• [LOCATION] 是您的位置名稱。如果您的資料位於 US 或 EU 多地區位置，則 --location 是選用標記。例如，如果您是在東京地區使用 BigQuery，請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
• [FORMAT] 是 NEWLINE_DELIMITED_JSON、CSV 或 AVRO。DATASTORE_BACKUP 檔案不需要資料欄放寬。從 Cloud 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 位於預設專案中，而且是在 US 多個區域地點中建立的。

bq --location=US 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 位於預設專案中，而且是在 US 多個區域地點中建立的。

bq --location=US 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 而非預設專案中。mydataset 是在 US 多個區域地點中建立的。

bq --location=US load --noreplace --schema_update_option=ALLOW_FIELD_RELAXATION --source_format=CSV --autodetect myotherproject:mydataset.mytable mydata.csv

輸入下列指令，使用載入工作將資料從 Cloud Storage 中以換行符號分隔的 JSON 檔案附加到 mydataset.mytable 中。包含放寬的資料欄的結構定義位於本機 JSON 結構定義檔案 (/tmp/myschema.json) 中。mydataset 位於預設專案中，而且是在 asia-northeast1 區域中建立的。

bq --location=asia-northeast1 load --noreplace --schema_update_option=ALLOW_FIELD_RELAXATION --source_format=NEWLINE_DELIMITED_JSON mydataset.mytable gs://mybucket/mydata.json /tmp/myschema.json

API

呼叫 jobs.insert 方法。在工作資源的 jobReference 區段的 location 屬性中指定您的區域。設定載入工作並設定以下屬性：

• 使用 configuration.load.sourceUris 屬性，以參考您在 Cloud Storage 中的資料。
• 設定 configuration.load.sourceFormat 屬性，以指定資料格式。
• 在 configuration.load.schema 屬性中指定結構定義。
• 使用 configuration.load.schemaUpdateOptions 屬性，以指定結構定義更新選項。
• 使用 configuration.load.writeDisposition 屬性，將目的地資料表寫入配置設定為 WRITE_APPEND。

Go 應用程式

前往 GitHub 查看

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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
}

Python

在試行此示例之前，請至 BigQuery 快速入門導覽課程：使用用戶端程式庫，按照 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

前往 GitHub 查看 意見回饋

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('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))

在查詢附加工作期間將 REQUIRED 變更為 NULLABLE

您可以藉由以下操作，在將查詢結果附加至資料表時一併放寬所有資料欄：

• 使用指令列工具的 bq query 指令
• 呼叫 API 的 jobs.insert 方法並設定查詢工作

GCP 主控台或傳統 BigQuery 網頁版 UI 目前不支援在附加作業期間放寬資料欄。

在查詢工作期間使用附加作業放寬資料欄時，您可以將 --schema_update_option 標記設定為 ALLOW_FIELD_RELAXATION 來放寬目的地資料表中的所有必要欄位。您無法使用查詢附加工作放寬目的地資料表中的個別資料欄。

如何在查詢工作期間，將資料附加到目的地資料表時一併放寬所有資料欄：

主控台

您目前無法使用主控台 BigQuery 網頁版 UI 放寬資料欄的模式。

傳統版 UI

使用 BigQuery 網頁版 UI 附加查詢結果時，無法放寬目的地資料表中的資料欄。

CLI

使用 bq query 指令查詢您的資料並指定 --destination_table 標記以指示您要附加的資料表。

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

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

指定 use_legacy_sql=false 標記以在查詢時使用標準 SQL 語法。

如果您要附加的資料表位於預設專案以外的專案的資料集中，請使用下列格式將專案 ID 新增至資料集名稱：[PROJECT_ID]:[DATASET]。

提供 --location 標記，並將值設為您的位置。

bq --location=[LOCATION] query --destination_table [PROJECT_ID]:[DATASET].[TABLE] --append_table --schema_update_option --use_legacy_sql=false '[QUERY]'

其中：

• [LOCATION] 是您的位置名稱。如果您的資料位於 US 或 EU 多地區位置，則 --location 是選用標記。例如，如果您是在東京地區使用 BigQuery，請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
• [PROJECT_ID] 是您的專案 ID。
• [DATASET] 是包含您要附加之資料表的資料集名稱。
• [TABLE] 是您要附加之資料表的名稱。
• [QUERY] 是使用標準 SQL 語法的查詢。

範例：

輸入下列指令，查詢預設專案中的 mydataset.mytable，並將查詢結果附加到 mydataset.mytable2 (也在預設專案中)。該指令會將目的地資料表中的所有 REQUIRED 資料欄變更為 NULLABLE。mydataset 是在 US 多個區域地點中建立的。

bq --location=US 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。mydataset 是在 US 多個區域地點中建立的。

bq --location=US query --destination_table myotherproject:mydataset.mytable2 --append_table --schema_update_option=ALLOW_FIELD_RELAXATION --use_legacy_sql=false 'SELECT column1,column2 FROM mydataset.mytable'

輸入下列指令，查詢預設專案中的 mydataset.mytable，並將查詢結果附加到 mydataset.mytable2 (也在預設專案中)。該指令會將目的地資料表中的所有 REQUIRED 資料欄變更為 NULLABLE。mydataset 是在 asia-northeast1 多個區域地點中建立的。

bq --location=asia-northeast1 query --destination_table mydataset.mytable2 --append_table --schema_update_option=ALLOW_FIELD_RELAXATION --use_legacy_sql=false 'SELECT column1,column2 FROM mydataset.mytable'

API

呼叫 jobs.insert 方法。在工作資源的 jobReference 區段的 location 屬性中指定您的區域。

設定查詢工作並設定以下屬性：

• 使用 configuration.query.destinationTable 屬性，以指定目的地資料表。
• 使用 configuration.query.writeDisposition 屬性，將目的地資料表寫入配置設定為 WRITE_APPEND。
• 使用 configuration.query.schemaUpdateOptions 屬性，以指定結構定義更新選項。
• 使用 configuration.query.query 屬性，以指定標準 SQL 查詢。

Go 應用程式

前往 GitHub 查看

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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
}

Python

在試行此示例之前，請至 BigQuery 快速入門導覽課程：使用用戶端程式庫，按照 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

前往 GitHub 查看 意見回饋

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')

# 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 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()
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION
]
job_config.destination = table_ref
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND

query_job = client.query(
    # 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;',
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location="US",
    job_config=job_config,
)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print("Query job {} complete.".format(query_job.job_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))