使用者自訂函式與儲存過程

在 Snowflake 進行資料管線自動化

Emily Melhuish

Technical Curriculum Developer, Snowflake

什麼是 UDF？

nanobanana: half: 兩個抽象鎖頭圖示並排，一開一關，扁平極簡風格，DataCamp 海軍藍與綠色，白色背景，無文字

使用者自訂函式（UDF）

超出內建 SQL 函式的自訂運算
以 SQL、Python、Java、Scala 或 JavaScript 撰寫
不含 DDL 或 DML——只回傳值
標準或 SECURE（對呼叫者隱藏邏輯）
可回傳每列一個標量值，或回傳一個資料表

SQL UDFs

建議的起手式

CREATE FUNCTION delivery_tier(days INT)
RETURNS VARCHAR AS $$
  CASE WHEN days <= 2 THEN 'Express'
       WHEN days <= 5 THEN 'Standard'
       ELSE 'Delayed' END $$;

Python 與其他語言的 UDF

當 SQL 力有未逮時

CREATE FUNCTION parse_weight(label STRING)
RETURNS FLOAT LANGUAGE PYTHON
RUNTIME_VERSION = '3.9' HANDLER = 'parse'
AS $$ def parse(s): return float(s.split('kg')[0]) $$;

管線中的 UDF

mermaid: UDF 轉換層管線

UDF 位於轉換層——介於原始儲存與報表之間

於 SELECT 中呼叫；每列回傳一個值
將跨多個查詢的商業邏輯集中化
加上 SECURE 可對其他使用者隱藏定義

什麼是儲存過程？

宣告變數、迴圈處理結果、處理錯誤
可跨多個資料表執行資料操作語言（DML）
以 CALL 獨立呼叫

mermaid: datacamp-purple: 儲存過程協調流程

儲存過程：Snowflake Scripting

CREATE OR REPLACE PROCEDURE archive_old_shipments(cutoff INT)
RETURNS STRING LANGUAGE SQL AS $$
DECLARE                                    -- 1. 宣告變數
    rows_moved INT DEFAULT 0;
BEGIN                                      -- 2. 開始程序
    INSERT INTO shipments_archive          -- 3. 執行 SQL 指令
        SELECT * FROM shipments WHERE delivery_days > :cutoff;
    rows_moved := SQLROWCOUNT;
    DELETE FROM shipments WHERE delivery_days > :cutoff;
    RETURN 'Archived ' || rows_moved       -- 4. 回傳結果
        || ' shipments.';
END;                                       -- 5. 結束程序
$$;

UDF 與儲存過程比較

	UDF	儲存過程
呼叫位置	`SELECT`	`CALL`
回傳	每列一個值	單一值
可執行 DML	否	是
語言	SQL、Python、Java、JS	SQL（Snowflake Scripting）、Python、Java、JS、Scala
管線中用途	轉換資料	協調邏輯

一起來練習吧！

在 Snowflake 進行資料管線自動化