User-Defined Functions en Stored Procedures

Automatisering van datapijplijnen in Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Wat is een UDF?

nanobanana: half: Twee abstracte hangslotpictogrammen naast elkaar, één open en één gesloten, plat minimalistisch ontwerp, DataCamp marineblauw en groen, witte achtergrond, geen tekst

User-Defined Functions

Aangepaste bewerkingen buiten ingebouwde SQL-functies
Geschreven in SQL, Python, Java, Scala of JavaScript
Geen DDL of DML — retourneert alleen waarden
Standaard of SECURE (verbergt logica voor aanroepers)
Retourneert een scalair per rij of een tabel

SQL-UDF's

Aanbevolen startpunt

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

Python- en andere programmeertaal-UDF's

Wanneer SQL tekortschiet

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's in de pipeline

mermaid: UDF-transformatielaag-pipeline

UDF's zitten in de transformatielaag: tussen ruwe opslag en rapportage

Aangeroepen binnen SELECT; geven één waarde per rij terug
Centraliseer bedrijfslogica voor meerdere queries
Voeg SECURE toe om de definitie voor anderen te verbergen

Wat is een Stored Procedure?

Variabelen declareren, over resultaten loopen, fouten afhandelen
DML uitvoeren over meerdere tabellen
Los aanroepen met CALL

mermaid: datacamp-paars: orchestratiestroom van stored procedure

Stored Procedure: 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's vs Stored Procedures

	UDF	Stored Procedure
Aangeroepen in	`SELECT`	`CALL`
Retourneert	Eén waarde per rij	Enkele waarde
Kan DML uitvoeren	Nee	Ja
Talen	SQL, Python, Java, JS	SQL (Snowflake Scripting), Python, Java, JS, Scala
Gebruik in pipeline	Data transformeren	Logica orkestreren

Laten we oefenen!

Automatisering van datapijplijnen in Snowflake