BigQuery：利用 EXECUTE IMMEDIATE來動態寫 SQL (Dynamic SQL)

為什麼要動態 SQL？我覺得更好的問題是為什麼寫 SQL要被限制？我能不能Query任意資料集的任意表格？

官方文件連結：點我 (2022–04–30檢查發現連結壞了)

以下舉個我自己遇到的情況。

我們用以下的 code可以查到 myDataset 內的所有表格資訊。

SELECT 
  * 
FROM
  `myDataset.INFORMATION_SCHEMA.TABLES`;

但如果我們想查不同的 Dataset呢？ 一般的 Query做不到這件事。

動態 SQL (Dynamic SQL)

動態 SQL的寫法類似 python 格式化字串的方法

• 先把程式碼視為一整串的字串
• 在字串中插入你需要的文字
• 最後再視為程式碼來執行

把上面再進一步擴展

EXECUTE IMMEDIATE FORMAT("""
  SELECT 
    * 
  FROM
    `%s.INFORMATION_SCHEMA.TABLES`
  """,
  myDataset
)

• FORMAT：將字串拼湊
• EXECUTE IMMEDIATE：執行拼湊起來的字串

在這個基礎下，我們用把 SELECT，FROM等保留字抽換掉也都是可以的。

儲存動態 SQL的結果

以下這段 Code創造了一個procedure，用來找出表格創建的時間並且儲存成變數

CREATE OR REPLACE PROCEDURE `tmp.get_create_time`(
IN dataset_name STRING,
IN table_name STRING,
OUT create_time TIMESTAMP
)
BEGIN
EXECUTE IMMEDIATE FORMAT("""
SELECT
  creation_time
FROM
  `%s.INFORMATION_SCHEMA.TABLES`
WHERE
  table_name = '%s' --如果是變數，要用冒號包起來
""",
dataset_name,
table_name
) INTO create_time;
END;

當我們想要使用時，就可以呼叫來使用。

DECLARE create_time_of_table TIMESTAMP;
CALL tmp.get_create_time("tmp", "Info_UserData", create_time_of_table);
SELECT
  create_time_of_table;