用户自定义函数与存储过程

Snowflake 中的数据管道自动化

Emily Melhuish

Technical Curriculum Developer, Snowflake

什么是 UDF？

nanobanana: half: 两个抽象的挂锁图标并排，一个打开一个关闭，极简扁平风格，DataCamp 藏青与绿色配色，白色背景，无文字

用户自定义函数（UDF）

扩展内置 SQL 函数的自定义操作
可用 SQL、Python、Java、Scala、JavaScript 编写
不执行 DDL/DML——仅返回值
标准或 SECURE（对调用者隐藏逻辑）
返回每行一个标量值，或返回表

SQL UDF

推荐的起点

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. Declare variables
    rows_moved INT DEFAULT 0;
BEGIN                                      -- 2. Begin the procedure
    INSERT INTO shipments_archive          -- 3. Execute SQL commands
        SELECT * FROM shipments WHERE delivery_days > :cutoff;
    rows_moved := SQLROWCOUNT;
    DELETE FROM shipments WHERE delivery_days > :cutoff;
    RETURN 'Archived ' || rows_moved       -- 4. Return the result
        || ' shipments.';
END;                                       -- 5. End the procedure
$$;

UDF 与存储过程对比

	UDF	存储过程
调用于	`SELECT`	`CALL`
返回	每行一个值	单个值
可执行 DML	否	是
语言	SQL、Python、Java、JS	SQL（Snowflake Scripting）、Python、Java、JS、Scala
在管道中的作用	转换数据	编排逻辑

¡Vamos a practicar!

Snowflake 中的数据管道自动化