修改資料表結構定義

本文件說明如何修改現有 BigQuery 資料表的結構定義。BigQuery 原生支援以下結構定義修改作業:

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

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

所有其他的結構定義修改作業均不受支援,且需手動變通解決,這些作業包括:

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

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

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

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

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

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

手動新增空白資料欄

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

  • 使用 Cloud Console 或 BigQuery 傳統網頁版 UI
  • 使用指令列工具的 bq update 指令
  • 呼叫 tables.patch API 方法
  • 使用用戶端程式庫

新增資料欄至現有的資料表結構定義時,資料欄必須為 NULLABLEREPEATED。您無法將 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 資料欄。

新增資料欄至資料表的結構定義後,您就可以利用以下方法將資料載入到新資料欄中:

如要將空白資料欄新增至資料表的結構定義,請進行以下操作:

主控台

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

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

編輯資料表結構定義

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

    • 針對「Name」(名稱),請輸入資料欄名稱。
    • 針對「Type」(類型),請選擇資料類型
    • 針對「Mode」(模式),請選擇 NULLABLEREPEATED
  2. 完成新增資料欄後,請按一下 [Save] (儲存)

傳統版 UI

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

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

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

    • 針對「Name」(名稱),請輸入資料欄名稱。
    • 針對「Type」(類型),請選擇資料類型
    • 針對「Mode」(模式),請選擇 NULLABLEREPEATED

      更新資料表結構定義

  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 檔案為新資料欄指定說明、NULLABLEREPEATED 模式以及 RECORD 類型。例如,如果使用上一個步驟中的結構定義,新的 JSON 陣列會如下所示。此範例中會新增名為 column4NULLABLE 資料欄,且 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 的結構定義。您本機機器上的 JSON 結構定義檔路徑為 /tmp/myschema.json

    bq update mydataset.mytable /tmp/myschema.json
    

API

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

Go

// 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
}

Node.js

在嘗試此範例之前,請先按照 BigQuery 快速入門導覽課程:使用用戶端程式庫中的 Node.js 設定操作說明進行操作。詳情請參閱 BigQuery Node.js API 參考資料說明文件


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

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

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

主控台

Cloud Console 目前不支援新增巢狀欄位至現有的 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.table

    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 是巢狀的重複資料欄。巢狀資料欄為 nested1nested2fields 陣列列出了以巢狀形式嵌套在 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 方法。

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

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

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

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

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

  • 使用指令列工具的 bq load 指令
  • 呼叫 API 的 jobs.insert 方法並設定 load 工作
  • 使用用戶端程式庫

Cloud Console 或 BigQuery 傳統網頁版 UI 目前不支援在附加作業期間,將資料欄新增至現有資料表。

在載入工作中使用附加作業新增資料欄時,已更新的結構定義:

  • 可供自動偵測 (適用於 CSV 和 JSON 檔案)
  • 可在 JSON 結構定義檔中指定 (適用於 CSV 和 JSON 檔)
  • 可以是擷取自 Avro、ORC、Parquet 和 Datastore 匯出檔的自述式來源資料

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

在附加作業期間新增資料欄時,現有資料列中新資料欄的值將會設定為 NULL

如要在載入工作期間,在將資料附加到資料表時一併新增資料欄,請進行以下操作:

主控台

使用 Cloud Console 載入資料時,無法新增資料欄至現有資料表。

傳統版 UI

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

CLI

請使用 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 檔來設定位置的預設值。
  • formatNEWLINE_DELIMITED_JSONCSVAVROPARQUETORCDATASTORE_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 中指定。mydatasetmyotherproject 中,而不在您的預設專案中。

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

// 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
}

Node.js

在嘗試此範例之前,請先按照 BigQuery 快速入門導覽課程:使用用戶端程式庫中的 Node.js 設定操作說明進行操作。詳情請參閱 BigQuery Node.js API 參考資料說明文件

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

# 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 方法並設定 query 工作
  • 使用用戶端程式庫

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

在查詢工作中使用附加作業新增資料欄時,系統會使用查詢結果的結構定義來更新目的地資料表的結構定義。請注意,您無法在一個位置查詢資料表後,將結果寫入不同位置的資料表。

如要在查詢工作期間,於將資料附加至資料表時一併新增資料欄,請進行以下操作:

主控台

使用 Cloud Console 附加查詢結果時,無法新增資料欄至現有資料表。

傳統版 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=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'query'

其中:

  • location 是您的位置的名稱,--location 是選用旗標。舉例來說,如果您在東京地區使用 BigQuery,就可將該旗標的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔來設定位置的預設值。請注意,您無法將查詢結果附加至不同位置的資料表。
  • project_id 是您的專案 ID。
  • 「dataset」是含有您要附加之資料表的資料集名稱。
  • table 是您要附加的資料表名稱。
  • query 是採用標準 SQL 語法的查詢。

範例:

輸入下列指令,以查詢預設專案中的 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 屬性指定標準 SQL 查詢。

Go

// 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
}

Node.js

在嘗試此範例之前,請先按照 BigQuery 快速入門導覽課程:使用用戶端程式庫中的 Node.js 設定操作說明進行操作。詳情請參閱 BigQuery Node.js API 參考資料說明文件

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

# 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

  • 使用 Cloud Console 或 BigQuery 傳統網頁版 UI
  • 使用指令列工具的 bq update 指令
  • 呼叫 tables.patch API 方法
  • 使用用戶端程式庫

如要手動將資料欄的模式從 REQUIRED 變更為 NULLABLE,請進行以下操作:

主控台

您目前無法使用 Cloud Console 來放寬資料欄模式。

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

      bq update mydataset.mytable /tmp/myschema.json
    

API

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

Go

// 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
}

Node.js

在嘗試此範例之前,請先按照 BigQuery 快速入門導覽課程:使用用戶端程式庫中的 Node.js 設定操作說明進行操作。詳情請參閱 BigQuery Node.js API 參考資料說明文件

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

使用 mode 屬性設為 'NULLABLE'SchemaField 物件清單來覆寫 Table.schema 屬性。

# 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。覆寫現有的資料表時,系統會使用所載入資料的結構定義來覆寫現有資料表的結構定義。如要瞭解如何使用載入工作覆寫資料表,請參閱:

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

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

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

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

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

在載入工作中使用附加作業放寬資料欄的模式時,您可以:

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

如要在載入工作期間將資料附加到資料表時,一併將資料欄從 REQUIRED 放寬為 NULLABLE,請進行以下操作:

主控台

您目前無法使用 Cloud Console 來放寬資料欄模式。

傳統版 UI

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

CLI

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

如果您要附加的資料是 CSV 格式,或是以換行符號分隔的 JSON 格式,請在本機 JSON 結構定義檔中指定放寬的資料欄,或透過 --autodetect 旗標使用結構定義偵測功能,在來源資料中找出放寬的資料欄。如要瞭解如何使用 JSON 結構定義檔放寬資料欄模式,請參閱手動將 REQUIRED 資料欄變更為 NULLABLE 一節。

系統可以從 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 檔來設定位置的預設值。
  • formatNEWLINE_DELIMITED_JSONCSVPARQUETORCAVRODATASTORE_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。該指令使用結構定義自動偵測功能來找出來源資料中放寬的資料欄。mydatasetmyotherproject 中,而不在您的預設專案中。

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

// 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
}

Node.js

在嘗試此範例之前,請先按照 BigQuery 快速入門導覽課程:使用用戶端程式庫中的 Node.js 設定操作說明進行操作。詳情請參閱 BigQuery Node.js API 參考資料說明文件

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

# 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 方法並設定查詢工作
  • 使用用戶端程式庫

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

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

如要在查詢工作期間,於將資料附加到目的地資料表時,一併放寬所有資料欄,請進行以下操作:

主控台

您目前無法使用 Cloud Console 來放寬資料欄模式。

傳統版 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=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'query'

其中:

  • location 是您的位置的名稱,--location 是選用旗標。舉例來說,如果您在東京地區使用 BigQuery,就可將該旗標的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔來設定位置的預設值。
  • project_id 是您的專案 ID。
  • 「dataset」是含有您要附加之資料表的資料集名稱。
  • table 是您要附加的資料表名稱。
  • query 是採用標準 SQL 語法的查詢。

範例:

輸入下列指令,查詢預設專案中的 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 屬性指定標準 SQL 查詢。

Go

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

# 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))