ユーザー定義関数とストアドプロシージャ

Snowflakeにおけるデータパイプラインの自動化

Emily Melhuish

Technical Curriculum Developer, Snowflake

UDFとは？

nanobanana: half: 南京錠のアイコンが2つ並んでいる。1つは開いた状態、もう1つは閉じた状態。フラットなミニマルデザイン、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 内で呼び出し、行ごとに1値を返却
複数クエリで共通するビジネスロジックを集約
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 vs ストアドプロシージャ

	UDF	ストアドプロシージャ
呼び出し方	`SELECT`	`CALL`
戻り値	行ごとに1値	単一値
DML実行	不可	可能
言語	SQL, Python, Java, JS	SQL (Snowflake Scripting), Python, Java, JS, Scala
パイプラインでの用途	データ変換	ロジックの統合

練習しましょう！

Snowflakeにおけるデータパイプラインの自動化