使用多陳述式查詢

本文說明如何在 BigQuery 中使用多陳述式查詢，例如如何編寫多陳述式查詢、在多陳述式查詢中使用暫存資料表、在多陳述式查詢中參照變數，以及對多陳述式查詢進行偵錯。

多陳述式查詢是一組 SQL 陳述式，可在單一要求中執行。透過多陳述式查詢，您可以在共用狀態的序列中執行多個陳述式。多陳述式查詢可能會產生副作用，例如新增或修改資料表資料。

多陳述式查詢通常用於預存程序，並支援程序式語言陳述式，可讓您執行定義變數和實作控制流程等作業。

編寫、執行及儲存多個陳述式的查詢

多陳述式查詢包含一或多個以分號分隔的 SQL 陳述式。任何有效的 SQL 陳述式都可以用於多陳述式查詢。多陳述式查詢也可以包含程序語言陳述式，讓您使用變數或透過 SQL 陳述式實作控管流程。

編寫多個陳述式的查詢

您可以在 BigQuery 中編寫多陳述式查詢。下列多查詢語句會宣告變數，並在 IF 語句中使用該變數：

DECLARE day INT64;
SET day = (SELECT EXTRACT(DAYOFWEEK from CURRENT_DATE));
if day = 1 or day = 7 THEN
  SELECT 'Weekend';
ELSE
  SELECT 'Weekday';
END IF

BigQuery 會將任何含有多個陳述式的請求解讀為多陳述式查詢，除非該陳述式完全由 CREATE TEMP FUNCTION 陳述式組成，且後面接著單一 SELECT 陳述式。舉例來說，以下查詢不會視為多個陳述式的查詢：

CREATE TEMP FUNCTION Add(x INT64, y INT64) AS (x + y);
SELECT Add(3, 4);

執行多陳述式查詢

您可以按照任何其他查詢的方式執行多陳述式查詢，例如在 Google Cloud 主控台中或使用 bq 指令列工具。

模擬測試多陳述式查詢

如要預估多語句查詢讀取的位元組數，請考慮執行模擬測試。對於只包含 SELECT 陳述式的查詢，模擬測試多陳述式查詢的結果最準確。

模擬執行作業會針對下列查詢和陳述式類型進行特殊處理：

• CALL 陳述式：模擬執行會驗證所呼叫的程序是否存在，以及是否具有與提供的引數相符的簽章。系統不會驗證所呼叫程序的內容，以及 CALL 陳述式後面的所有陳述式。
• DDL 陳述式：模擬執行會驗證第一個 DDL 陳述式，然後停止。系統會略過所有後續陳述式。系統不支援 CREATE TEMP TABLE 陳述式的模擬測試。
• DML 陳述式：模擬執行會驗證 DML 陳述式，然後繼續驗證後續陳述式。在這種情況下，位元組估算值會以原始資料表大小為依據，且不會考量 DML 陳述式的結果。
• EXECUTE IMMEDIATE 陳述式：模擬測試會驗證查詢運算式，但不會評估動態查詢本身。系統會略過 EXECUTE IMMEDIATE 陳述式後面的所有陳述式。
• 在分區篩選器中使用變數的查詢：模擬執行會驗證初始查詢和後續陳述式。不過，模擬測試無法計算分區篩選器中變數的執行階段值。這會影響讀取位元組的預估值。
• 在 FOR SYSTEM TIME AS OF 子句的時間戳記運算式中使用變數的查詢：模擬執行會使用資料表的目前內容，並忽略 FOR SYSTEM TIME AS OF 子句。如果目前的資料表與先前的資料表迭代版本之間存在大小差異，這會影響讀取位元組的預估值。
• FOR、IF 和 WHILE 控制陳述式：模擬測試會立即停止。系統不會驗證條件運算式、控制陳述式的主體，以及後續的所有陳述式。

模擬測試會盡力執行，但基礎程序可能會有所變動。模擬測試須遵守下列規定：

• 成功完成模擬測試的查詢可能無法順利執行。舉例來說，查詢可能會在執行階段失敗，原因是模擬執行無法偵測到。
• 成功執行的查詢可能無法順利完成模擬測試。舉例來說，查詢可能會因執行時發生的錯誤而無法進行模擬測試。
• 今天成功執行的模擬測試，不保證日後一定能執行。舉例來說，如果您變更模擬執行作業的實作方式，可能會偵測到先前未偵測到的查詢錯誤。

儲存多個陳述式的查詢

如要儲存多個陳述式的查詢，請參閱使用已儲存的查詢。

在多陳述式查詢中使用變數

多陳述式查詢可包含使用者建立的變數和系統變數。

• 您可以宣告使用者建立的變數、為這些變數指派值，並在整個查詢中參照這些變數。

• 您可以在查詢中參照系統變數，並為其中部分變數指派值，但與使用者定義的變數不同，您不需要宣告這些變數。系統變數已內建於 BigQuery。

宣告使用者建立的變數

您必須在多陳述式查詢的開頭，或 BEGIN 區塊的開頭，宣告使用者建立的變數。在多個陳述式查詢開頭宣告的變數，會納入整個查詢的範圍。在 BEGIN 區塊中宣告的變數具有該區塊的範圍。在對應的 END 陳述式之後，這些變數就會超出範圍。變數的大小上限為 1 MB，而多語句查詢中使用的所有變數的總和上限為 10 MB。

您可以使用 DECLARE 程序陳述式宣告變數，如下所示：

DECLARE x INT64;

BEGIN
DECLARE y INT64;
-- Here you can reference x and y
END;

-- Here you can reference x, but not y

設定使用者建立的變數

宣告使用者建立的變數後，您可以使用 SET 程序陳述式為其指派值，如下所示：

DECLARE x INT64 DEFAULT 0;
SET x = 10;

設定系統變數

您不需要建立系統變數，但可以覆寫部分系統變數的預設值，如下所示：

SET @@dataset_project_id = 'MyProject';

您也可以在多個陳述式查詢中設定並隱含使用系統變數。舉例來說，每次建立新資料表時，您都必須在下列查詢中加入專案：

BEGIN
  CREATE TABLE MyProject.MyDataset.MyTempTableA (id STRING);
  CREATE TABLE MyProject.MyDataset.MyTempTableB (id STRING);
END;

如果您不想將專案多次新增至資料表路徑，可以將資料集專案 ID MyProject 指派給多個陳述式查詢中的 @@dataset_project_id 系統變數。這項指派會將 MyProject 設為其餘查詢的預設專案。

SET @@dataset_project_id = 'MyProject';

BEGIN
  CREATE TABLE MyDataset.MyTempTableA (id STRING);
  CREATE TABLE MyDataset.MyTempTableB (id STRING);
END;

同樣地，您也可以設定 @@dataset_id 系統變數，為查詢指派預設資料集。例如：

SET @@dataset_project_id = 'MyProject';
SET @@dataset_id = 'MyDataset';

BEGIN
  CREATE TABLE MyTempTableA (id STRING);
  CREATE TABLE MyTempTableB (id STRING);
END;

您也可以在多個查詢陳述式的各個部分中，明確參照 @@dataset_id 等系統變數。詳情請參閱系統變數範例。

參照使用者建立的變數

宣告並設定使用者建立的變數後，您可以在多個陳述式查詢中參照該變數。如果變數和欄的名稱相同，則系統會優先採用欄的名稱。

這會傳回 column x + column x：

DECLARE x INT64 DEFAULT 0;
SET x = 10;

WITH Numbers AS (SELECT 50 AS x)
SELECT (x+x) AS result FROM Numbers;

+--------+
| result |
+--------+
| 100    |
+--------+

這會傳回 column y + variable x：

DECLARE x INT64 DEFAULT 0;
SET x = 10;

WITH Numbers AS (SELECT 50 AS y)
SELECT (y+x) AS result FROM Numbers;

+--------+
| result |
+--------+
| 60     |
+--------+

在多個陳述式查詢中使用暫存資料表

您可以使用暫時性資料表，將中繼結果儲存至資料表。臨時資料表由 BigQuery 管理，因此您不需要在資料集中儲存或維護這些資料表。系統會根據臨時資料表的儲存空間計費。

您可以在多個陳述式查詢中建立及參照臨時資料表。使用完臨時資料表後，您可以手動刪除該資料表，以盡量降低儲存空間費用，也可以等待 BigQuery 在 24 小時後刪除該資料表。

建立臨時資料表

您可以使用 CREATE TABLE 陳述式，為多陳述式查詢建立臨時資料表。下列範例會建立臨時資料表來儲存查詢結果，並在子查詢中使用該臨時資料表：

-- Find the top 100 names from the year 2017.
CREATE TEMP TABLE top_names(name STRING)
AS
 SELECT name
 FROM `bigquery-public-data`.usa_names.usa_1910_current
 WHERE year = 2017
 ORDER BY number DESC LIMIT 100
;
-- Which names appear as words in Shakespeare's plays?
SELECT
 name AS shakespeare_name
FROM top_names
WHERE name IN (
 SELECT word
 FROM `bigquery-public-data`.samples.shakespeare
);

除了使用 TEMP 或 TEMPORARY 以外，語法與 CREATE TABLE 語法完全相同。

建立臨時資料表時，請勿在資料表名稱中使用專案或資料集限定詞。系統會在特殊資料集中自動建立資料表。

參照臨時資料表

您可以在目前的多語句查詢期間，依名稱參照臨時資料表。這包括多個陳述式查詢中程序建立的臨時資料表。您無法共用臨時資料表。臨時資料表會位於隱藏的 _script% 資料集中，並隨機產生名稱。列出資料集一文說明如何列出隱藏的資料集。

刪除臨時資料表

您可以使用 DROP TABLE 陳述式，在多個陳述式查詢完成前明確刪除臨時資料表：

CREATE TEMP TABLE table1(x INT64);
SELECT * FROM table1;  -- Succeeds
DROP TABLE table1;
SELECT * FROM table1;  -- Results in an error

多個陳述式的查詢完成後，這個臨時表最多會存在 24 小時。

查看暫存資料表資料

建立臨時資料表後，您可以查看資料表的結構和其中的任何資料。如要查看資料表結構和資料，請按照下列步驟操作：

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

   前往「Explorer」

2. 按一下「查詢記錄」。

3. 選擇建立暫存資料表的查詢。

4. 在「Destination table」列中，按一下「Temporary table」。

使用 _SESSION 限定臨時資料表

當臨時資料表與預設資料集一起使用時，非限定的資料表名稱指的是臨時資料表 (如果存在的話)，或是預設資料集中的資料表。例外情況是 CREATE TABLE 陳述式，在這種陳述式中，只有在 TEMP 或 TEMPORARY 關鍵字存在時，系統才會將目標資料表視為臨時資料表。

例如，請考量以下多陳述式查詢：

-- Create table t1 in the default dataset
CREATE TABLE t1 (x INT64);

-- Create temporary table t1.
CREATE TEMP TABLE t1 (x INT64);

-- This statement selects from the temporary table.
SELECT * FROM t1;

-- Drop the temporary table
DROP TABLE t1;

-- Now that the temporary table is dropped, this statement selects from the
-- table in the default dataset.
SELECT * FROM t1;

您可以使用 _SESSION 來限定資料表名稱，藉此明確指出您所指的臨時資料表：

-- Create a temp table
CREATE TEMP TABLE t1 (x INT64);

-- Create a temp table using the `_SESSION` qualifier
CREATE TEMP TABLE _SESSION.t2 (x INT64);

-- Select from a temporary table using the `_SESSION` qualifier
SELECT * FROM _SESSION.t1;

如果使用 _SESSION 限定詞的查詢其臨時資料表不存在，多個陳述句查詢會擲回錯誤，指出該資料表不存在。舉例來說，如果沒有名為 t3 的臨時資料表，即使預設資料集中有名為 t3 的資料表存在，多個陳述句查詢也會擲回錯誤。

您不能使用 _SESSION 來建立非臨時的資料表：

CREATE TABLE _SESSION.t4 (x INT64);  -- Fails

收集多語句查詢工作的相關資訊

多陳述式查詢工作包含已執行的多陳述式查詢相關資訊。您可以透過工作資料執行一些常見工作，例如傳回透過多個陳述式查詢執行的最後一個陳述式，或傳回透過多個陳述式查詢執行的所有陳述式。

傳回上次執行的陳述式

jobs.getQueryResults 方法會傳回最後一個陳述式在多陳述式查詢中執行的查詢結果。如果未執行任何陳述式，系統就不會傳回任何結果。

傳回所有執行的陳述式

如要取得多陳述式查詢中的所有陳述式結果，請列舉子項工作，並對每個子項工作呼叫 jobs.getQueryResults。

列舉子項工作

多陳述式查詢會使用 jobs.insert 在 BigQuery 中執行，這與任何其他查詢相同，且多陳述式查詢會指定為查詢文字。執行多陳述式查詢時，系統會為多陳述式查詢中的每個陳述式建立額外的工作 (稱為子項工作)。您可以呼叫 jobs.list，將多個陳述式查詢工作 ID 做為 parentJobId 參數傳入，藉此列舉多個陳述式查詢的子項工作。

對多陳述式查詢進行偵錯

以下是處理多陳述式查詢的幾個提示：

• 使用 ASSERT 陳述式，斷言布林值條件為 true。

• 使用 BEGIN...EXCEPTION...END 擷取錯誤，並顯示錯誤訊息和堆疊追蹤。

• 使用 SELECT FORMAT("....") 顯示中間結果。

• 在 Google Cloud 主控台中執行多陳述式查詢時，您可以查看多陳述式查詢中每個陳述式的輸出內容。執行多語句查詢時，bq 指令列工具的 bq query 指令也會顯示每個步驟的結果。

• 在 Google Cloud 控制台中，您可以選取查詢編輯器中的個別陳述式並執行。

權限

系統會在執行時檢查資料表、模型或其他資源的存取權。如果未執行陳述式或評估運算式，BigQuery 不會檢查執行多陳述式查詢的使用者是否有權存取查詢所參照的任何資源。

在多陳述式查詢中，系統會分別驗證每個運算式或陳述式的權限。例如：

SELECT * FROM dataset_with_access.table1;
SELECT * FROM dataset_without_access.table2;

如果執行查詢的使用者有權存取 table1，但沒有權限存取 table2，第一個查詢會成功，第二個查詢則會失敗。多陳述式查詢工作本身也會失敗。

安全性限制

在多陳述式查詢中，您可以使用動態 SQL 在執行階段建構 SQL 陳述式。這麼做雖然方便，但也可能帶來新的濫用機會。舉例來說，執行下列查詢可能會造成 SQL 注入安全威脅，因為資料表參數可能會篩選不當、允許存取及在非預期的資料表上執行。

-- Risky query vulnerable to SQL injection attack.
EXECUTE IMMEDIATE CONCAT('SELECT * FROM SensitiveTable WHERE id = ', @id);

為避免在資料表中揭露或洩漏機密資料，或執行 DROP TABLE 等指令來刪除資料表中的資料，BigQuery 的動態程序陳述式支援多項安全措施，以減少 SQL 注入攻擊的風險，包括：

• EXECUTE IMMEDIATE 陳述式不允許其查詢 (使用查詢參數和變數擴充) 嵌入多個 SQL 陳述式。
• 下列指令無法動態執行：BEGIN/END、CALL、CASE、IF、LOOP、WHILE 和 EXECUTE IMMEDIATE。

設定欄位限制

以下工作設定查詢欄位無法針對多個陳述式的查詢設定：

• clustering
• create_disposition
• destination_table
• destination_encryption_configuration
• range_partitioning
• schema_update_options
• time_partitioning
• user_defined_function_resources
• write_disposition

定價

多陳述式查詢的定價包含查詢費用 (使用以量計價計費模式時)，以及暫存資料表的儲存空間。使用預留項目時，查詢用量會計入預留項目費用。

以量計價查詢大小計算

如果您採用以量計價的模式，BigQuery 會根據執行多個陳述式查詢時處理的位元組數，收取多個陳述式查詢的費用。

如要估算多語句查詢可能處理的位元組數，您可以執行模擬測試。

以下價格適用於這些多陳述式查詢：

• DECLARE：DEFAULT 運算式中參照的任何資料表所掃描的總位元組數。沒有資料表參照的 DECLARE 陳述式不會產生費用。

• SET：運算式中參照的任何資料表所掃描的總位元組數。沒有資料表參照的 SET 陳述式不會產生費用。

• IF：條件運算式中參照的任何資料表所掃描的總位元組數。沒有資料表參照的 IF 條件運算式不會產生費用。IF 區塊內任何未執行的陳述式也不會產生費用。

• WHILE：條件運算式中參照的任何資料表所掃描的總位元組數。條件運算式中沒有資料表參照的 WHILE 陳述式不會產生費用。WHILE 區塊內任何未執行的陳述式也不會產生費用。

• CONTINUE 或 ITERATE：沒有相關費用。

• BREAK 或 LEAVE：沒有相關費用。

• BEGIN 或 END：沒有相關費用。

如果多陳述式查詢失敗，陳述式啟動後直到失敗的期間仍會計費。失敗的陳述式則不會產生任何費用。

舉例來說，以下程式碼範例會在每個陳述式前方加上註解，說明各個陳述式會產生的費用 (如有)：

-- No cost, since no tables are referenced.
DECLARE x DATE DEFAULT CURRENT_DATE();
-- Incurs the cost of scanning string_col from dataset.table.
DECLARE y STRING DEFAULT (SELECT MAX(string_col) FROM dataset.table);
-- Incurs the cost of copying the data from dataset.big_table.  Once the
-- table is created, you are not charged for storage while the rest of the
-- multi-statement query runs.
CREATE TEMP TABLE t AS SELECT * FROM dataset.big_table;
-- Incurs the cost of scanning column1 from temporary table t.
SELECT column1 FROM t;
-- No cost, since y = 'foo' doesn't reference a table.
IF y = 'foo' THEN
  -- Incurs the cost of scanning all columns from dataset.other_table, if
  -- y was equal to 'foo', or otherwise no cost since it is not executed.
  SELECT * FROM dataset.other_table;
ELSE
  -- Incurs the cost of scanning all columns from dataset.different_table, if
  -- y was not equal to 'foo', or otherwise no cost since it is not executed.
  UPDATE dataset.different_table
  SET col = 10
  WHERE true;
END IF;
-- Incurs the cost of scanning date_col from dataset.table for each
-- iteration of the loop.
WHILE x < (SELECT MIN(date_col) FROM dataset.table) DO
  -- No cost, since the expression does not reference any tables.
  SET x = DATE_ADD(x, INTERVAL 1 DAY);
  -- No cost, since the expression does not reference any tables.
  IF true THEN
    -- LEAVE has no associated cost.
    LEAVE;
  END IF;
  -- Never executed, since the IF branch is always taken, so does not incur
  -- a cost.
  SELECT * FROM dataset.big_table;
END WHILE;

詳情請參閱「查詢大小計算方式」。

儲存空間定價

系統會針對透過多語句查詢建立的臨時資料表收費。您可以使用 TABLE_STORAGE 或 TABLE_STORAGE_USAGE_TIMELINE 檢視畫面，查看這些臨時資料表使用的儲存空間。臨時資料表會位於隱藏的 _script% 資料集中，並隨機產生名稱。

配額

如需多語句查詢配額的相關資訊，請參閱「配額與限制」。

查看多語句查詢的數量

您可以使用 INFORMATION_SCHEMA.JOBS_BY_PROJECT 檢視畫面查看有效的多語句查詢數量。以下範例會使用 INFORMATION_SCHEMA.JOBS_BY_PROJECT 檢視區塊，顯示前一天的多語句查詢數量：

SELECT
  COUNT(*)
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP()
AND job_type = "QUERY"
AND state = 'RUNNING'
AND statement_type = 'SCRIPT'

如要進一步瞭解如何針對多陳述式查詢查詢 INFORMATION_SCHEMA.JOBS，請參閱「多陳述式查詢工作」。