指定預設資料欄值

本頁說明如何為 BigQuery 資料表中的資料欄設定預設值。如果資料表中沒有含有預設值的資料欄，當您新增資料列時，系統會改為將預設值寫入該資料欄。

預設值運算式

資料欄的預設值運算式必須為常值或下列任一函式：

您可以使用這些函式 (例如 [CURRENT_DATE(), DATE '2020-01-01']) 編寫 STRUCT 或 ARRAY 預設值。

在工作處理期間，函式會在資料寫入資料表之前評估。預設值的類型必須與所套用的資料欄類型相符，或強制為該類型。如未設定預設值，則預設值為 NULL。

設定預設值

建立新資料表時，您可以設定資料欄的預設值。您可以使用 CREATE TABLE DDL 陳述式，並在資料欄名稱和類型後方加入 DEFAULT 關鍵字和預設值運算式。以下範例會建立名為 simple_table 的資料表，其中包含兩個 STRING 資料欄，分別是 a 和 b。資料欄 b 的預設值為 'hello'。

CREATE TABLE mydataset.simple_table (
  a STRING,
  b STRING DEFAULT 'hello');

當您在省略資料欄 b 的 simple_table 中插入資料時，系統會改用預設值 'hello'，例如：

INSERT mydataset.simple_table (a) VALUES ('val1'), ('val2');

資料表 simple_table 包含下列值：

+------+-------+
| a    | b     |
+------+-------+
| val1 | hello |
| val2 | hello |
+------+-------+

如果資料欄的類型為 STRUCT，則必須為整個 STRUCT 欄位設定預設值。您無法為部分欄位設定預設值。陣列的預設值不得為 NULL，也不可包含任何 NULL 元素。以下範例會建立名為 complex_table 的資料表，並為資料欄 struct_col 設定預設值，該資料欄包含巢狀欄位，包括 ARRAY 類型：

CREATE TABLE mydataset.complex_table (
  struct_col STRUCT<x STRUCT<x1 TIMESTAMP, x2 NUMERIC>, y ARRAY<DATE>>
    DEFAULT ((CURRENT_TIMESTAMP(), NULL),
             [DATE '2022-01-01', CURRENT_DATE()])
);

您無法設定違反資料欄限制的預設值，例如不符合參數化類型的預設值，或是在資料欄模式為 REQUIRED 時的 NULL 預設值。

變更預設值

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

主控台

  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」。

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

設定資料欄的預設值只會影響日後插入資料表的資料。不會變更任何現有表格資料。以下範例會將資料欄 a 的預設值設為 SESSION_USER()；

ALTER TABLE mydataset.simple_table ALTER COLUMN a SET DEFAULT SESSION_USER();

如果您在 simple_table 中插入一列，而該列省略了 a 欄，系統會改用目前的工作階段使用者。

INSERT mydataset.simple_table (b) VALUES ('goodbye');

資料表 simple_table 包含下列值：

+------------------+---------+
| a                | b       |
+------------------+---------+
| val1             | hello   |
| val2             | hello   |
| user@example.com | goodbye |
+------------------+---------+

移除預設值

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

主控台

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

    前往 BigQuery

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

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

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

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

  6. 輸入 NULL 做為預設值。

  7. 按一下 [儲存]。

SQL

使用 ALTER COLUMN DROP DEFAULT DDL 陳述式。

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

    前往 BigQuery

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

    ALTER TABLE mydataset.mytable ALTER COLUMN column_name DROP DEFAULT;

    您也可以使用 ALTER COLUMN SET DEFAULT DDL 陳述式，將資料欄的值變更為 NULL，藉此移除資料欄中的預設值。

  3. 按一下 「Run」。

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

使用含有預設值的 DML 陳述式

您可以使用 INSERT DML 陳述式，在資料表中新增含有預設值的資料列。如果未指定資料欄的值，或是使用關鍵字 DEFAULT 取代值運算式，系統會使用預設值。以下範例會建立資料表，並插入資料列，其中每個值都是預設值：

CREATE TABLE mydataset.mytable (
  x TIME DEFAULT CURRENT_TIME(),
  y INT64 DEFAULT 5,
  z BOOL);

INSERT mydataset.mytable (x, y, z) VALUES (DEFAULT, DEFAULT, DEFAULT);

資料表 mytable 如下所示：

+-----------------+---+------+
| x               | y | z    |
+-----------------+---+------+
| 22:13:24.799555 | 5 | null |
+-----------------+---+------+

欄 z 沒有預設值，因此會使用 NULL 做為預設值。如果預設值是函式 (例如 CURRENT_TIME())，系統會在寫入值時評估該函式。再次以資料欄 x 的預設值呼叫 INSERT，會導致 TIME 的值有所不同。在下列範例中，只有資料欄 z 明確設定了值，而省略的資料欄則使用預設值：

INSERT mydataset.mytable (z) VALUES (TRUE);

資料表 mytable 如下所示：

+-----------------+---+------+
| x               | y | z    |
+-----------------+---+------+
| 22:13:24.799555 | 5 | null |
| 22:18:29.890547 | 5 | true |
+-----------------+---+------+

您可以使用 MERGE DML 陳述式，更新含有預設值的資料表。以下範例會建立兩個資料表，並使用 MERGE 陳述式更新其中一個資料表：

CREATE TABLE mydataset.target_table (
  a STRING,
  b STRING DEFAULT 'default_b',
  c STRING DEFAULT SESSION_USER())
AS (
  SELECT
    'val1' AS a, 'hi' AS b, '123@google.com' AS c
  UNION ALL
  SELECT
    'val2' AS a, 'goodbye' AS b, SESSION_USER() AS c
);

CREATE TABLE mydataset.source_table (
  a STRING DEFAULT 'default_val',
  b STRING DEFAULT 'Happy day!')
AS (
  SELECT
    'val1' AS a, 'Good evening！' AS b
  UNION ALL
  SELECT
    'val3' AS a, 'Good morning!' AS b
);

MERGE mydataset.target_table T
USING mydataset.source_table S
ON T.a = S.a
WHEN NOT MATCHED THEN
  INSERT(a, b) VALUES (a, DEFAULT);

結果如下：

+------+-----------+--------------------+
| a    | b         | c                  |
+------+-----------+--------------------+
| val1 | hi        | 123@google.com     |
| val2 | goodbye   | default@google.com |
| val3 | default_b | default@google.com |
+------+-----------+--------------------+

您可以使用 UPDATE DML 陳述式，更新含有預設值的資料表。以下範例會更新資料表 source_table，讓 b 資料欄的每個資料列都等於預設值：

UPDATE mydataset.source_table
SET b =  DEFAULT
WHERE TRUE;

結果如下：

+------+------------+
| a    | b          |
+------+------------+
| val1 | Happy day! |
| val3 | Happy day! |
+------+------------+

附加表格

您可以搭配使用 bq query 指令與 --append_table 旗標，將查詢結果附加至含有預設值的目標資料表。如果查詢省略含有預設值的資料欄，系統會指派預設值。以下範例會附加資料，該資料僅指定資料欄 z 的值：

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT FALSE AS z UNION ALL SELECT FALSE AS Z'

資料表 mytable 會為資料欄 x 和 y 使用預設值：

+-----------------+---+-------+
|        x        | y |   z   |
+-----------------+---+-------+
| 22:13:24.799555 | 5 |  NULL |
| 22:18:29.890547 | 5 |  true |
| 23:05:18.841683 | 5 | false |
| 23:05:18.841683 | 5 | false |
+-----------------+---+-------+

載入資料

您可以使用 bq load 指令或 LOAD DATA 陳述式，將資料載入含有預設值的資料表。如果載入的資料列數少於目的地資料表，系統會套用預設值。系統不會將載入資料中的 NULL 值轉換為預設值。

AVRO、Parquet 或 ORC 等二進位制格式具有已編碼的檔案結構定義。如果檔案結構定義省略部分資料欄，系統會套用預設值。

文字格式 (例如 JSON 和 CSV) 沒有編碼的檔案結構定義。如要使用 bq 指令列工具指定結構定義，您可以使用 --autodetect 旗標或提供 JSON 結構定義。如要使用 LOAD DATA 陳述式指定其結構定義，您必須提供資料欄清單。以下範例只會從 CSV 檔案載入 a 欄：

LOAD DATA INTO mydataset.insert_table (a)
FROM FILES(
  uris = ['gs://test-bucket/sample.csv'],
  format = 'CSV');

Write API

只有在寫入串流結構定義缺少目的地資料表結構定義中的欄位時，Storage Write API 才會填入預設值。在這種情況下，系統會在每次寫入時，為缺少的欄位填入預設值。如果寫入串流結構定義中存在欄位，但資料本身缺少該欄位，則系統會使用 NULL 填入缺少的欄位。舉例來說，假設您要將資料寫入具有下列結構定義的 BigQuery 資料表：

[
  {
    "name": "a",
    "mode": "NULLABLE",
    "type": "STRING",
  },
  {
    "name": "b",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_b'"
  },
  {
    "name": "c",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_c'"
  }
]

下列寫入串流結構定義缺少目的地資料表中的欄位 c：

[
  {
    "name": "a",
    "type": "STRING",
  },
  {
    "name": "b",
    "type": "STRING",
  }
]

假設您將下列值串流至資料表：

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}

結果如下：

+-------+-------+-----------+
| a     | b     | c         |
+-------+-------+-----------+
| val_a | val_b | default_c |
| val_a | NULL  | default_c |
+-------+-------+-----------+

寫入串流結構定義包含 b 欄位，因此即使未為欄位指定值，也不會使用預設值 default_b。由於寫入串流結構定義不含 c 欄位，因此 c 欄中的每個資料列都會填入目的地資料表的預設值 default_c。

下列寫入串流結構定義會與您要寫入的資料表結構定義相符：

[
  {
    "name": "a",
    "type": "STRING",
  },
  {
    "name": "b",
    "type": "STRING",
  }
  {
    "name": "c",
    "type": "STRING",
  }
]

假設您將下列值串流至資料表：

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}

寫入串流結構定義中沒有缺少任何目的地資料表所含的欄位，因此無論欄位是否在串流資料中填入，都不會套用任何資料欄的預設值：

+-------+-------+------+
| a     | b     | c    |
+-------+-------+------+
| val_a | val_b | NULL |
| val_a | NULL  | NULL |
+-------+-------+------+

您可以在 AppendRowsRequest 訊息的 default_missing_value_interpretation 中指定連線層級的預設值設定。如果值設為 DEFAULT_VALUE，即使資料欄在使用者結構定義中顯示，缺少的值也會採用預設值。

您也可以在 AppendRowsRequest 訊息的 missing_value_interpretations 對應項目中指定要求層級的預設值。每個鍵都是資料欄的名稱，其值則表示如何解讀遺漏值。

舉例來說，映射 {'col1': NULL_VALUE, 'col2': DEFAULT_VALUE} 表示 col1 中的所有遺漏值都會解讀為 NULL，而 col2 中的所有遺漏值都會解讀為資料表結構定義中 col2 的預設值。

如果某個欄位不在這個對應項目中，且缺少值，系統會將缺少的值解讀為 NULL。

鍵只能是頂層欄名稱。鍵不得為結構體子欄位，例如 col1.subfield1。

使用 insertAll API 方法

當資料寫入資料表時，tabledata.insertAll API 方法會在資料表的資料列層級填入預設值。如果資料列缺少含有預設值的欄，系統會將預設值套用至這些欄。

舉例來說，假設您有下列資料表結構定義：

[
  {
    "name": "a",
    "mode": "NULLABLE",
    "type": "STRING",
  },
  {
    "name": "b",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_b'"
  },
  {
    "name": "c",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_c'"
  }
]

假設您將下列值串流至資料表：

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}
{}

結果如下：

+-------+------------+-----------+
| a     | b          | c         |
+-------+------------+-----------+
| val_a | val_b      | default_c |
| val_a | default_b  | default_c |
| NULL  | default_b  | default_c |
+-------+------------+-----------+

第一個插入的資料列不包含欄位 c 的值，因此預設值 default_c 會寫入欄位 c。插入的第二個資料列不含 b 或 c 欄位的值，因此會將這些欄位的預設值寫入 b 和 c 欄。插入的第三列不含任何值。由於未設定其他預設值，因此寫入 a 資料欄的值為 NULL。預設值 default_b 和 default_c 會寫入 b 和 c 欄。

查看預設值

如要查看資料欄的預設值，請查詢 INFORMATION_SCHEMA.COLUMNS 檢視畫面。column_default 欄欄位包含資料欄的預設值。如未設定預設值，則為 NULL。以下範例顯示資料表 mytable 的資料欄名稱和預設值：

SELECT
  column_name,
  column_default
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

結果大致如下：

+-------------+----------------+
| column_name | column_default |
+-------------+----------------+
| x           | CURRENT_TIME() |
| y           | 5              |
| z           | NULL           |
+-------------+----------------+

限制

  • 您可以使用舊版 SQL 讀取含有預設值的資料表，但無法使用舊版 SQL 寫入含有預設值的資料表。
  • 您無法在現有資料表中新增含有預設值的新資料欄。不過，您可以新增不含預設值的資料欄，然後使用 ALTER COLUMN SET DEFAULT DDL 陳述式變更其預設值。
  • 您無法將來源資料表複製並附加至目的地資料表，因為目的地資料表的資料欄數多於來源資料表，且額外資料欄含有預設值。您可以改為執行 INSERT destination_table SELECT * FROM source_table 來複製資料。

後續步驟

  • 如要進一步瞭解如何將資料載入 BigQuery，請參閱「載入資料簡介」。