Uživatelsky definované funkce a uložené procedury

Automatizace datových pipeline ve Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Co je UDF?

nanobanana: half: Dvě abstraktní ikony zámků vedle sebe, jedna otevřená a jedna zavřená, plochý minimalistický design, barvy DataCamp námořní modré a zelené, bílé pozadí, bez textu

Uživatelsky definované funkce

Vlastní operace nad rámec vestavěných funkcí SQL
Napsané v SQL, Pythonu, Javě, Scale nebo JavaScriptu
Bez DDL ani DML — vrací pouze hodnoty
Standardní nebo SECURE (skrývá logiku před volajícími)
Vrací skalární hodnotu na řádek nebo tabulku

SQL UDF

Doporučený výchozí bod

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

UDF v Pythonu a dalších programovacích jazycích

Když SQL nestačí

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 v pipeline

mermaid: pipeline transformační vrstvy UDF

UDF se nacházejí v transformační vrstvě – mezi surovým úložištěm a reportingem

Volány uvnitř SELECT; vrací jednu hodnotu na řádek
Centralizují obchodní logiku sdílenou napříč dotazy
Přidejte SECURE pro skrytí definice před ostatními uživateli

Co je uložená procedura?

Deklarace proměnných, iterace přes výsledky, ošetření chyb
Spouštění DML (Data Manipulation Language) přes více tabulek
Volány samostatně pomocí CALL

mermaid: datacamp-purple: orchestrační tok uložené procedury

Uložená procedura: 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. uložené procedury

	UDF	Uložená procedura
Volána v	`SELECT`	`CALL`
Vrací	Jednu hodnotu na řádek	Jednu hodnotu
Může spustit DML	Ne	Ano
Jazyky	SQL, Python, Java, JS	SQL (Snowflake Scripting), Python, Java, JS, Scala
Použití v pipeline	Transformace dat	Orchestrace logiky

Lass uns üben!

Automatizace datových pipeline ve Snowflake