Användardefinierade funktioner och lagrade procedurer

Automatisering av datapipelines i Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Vad är en UDF?

nanobanana: half: Två abstrakta hänglåsikoner sida vid sida, ett öppet och ett stängt, platt minimalistisk design, DataCamp marinblå och gröna färger, vit bakgrund, ingen text

Användardefinierade funktioner

Anpassade operationer bortom inbyggda SQL-funktioner
Skrivna i SQL, Python, Java, Scala eller JavaScript
Ingen DDL eller DML — returnerar endast värden
Standard eller SECURE (döljer logiken för anroparen)
Returnerar ett skalärt värde per rad, eller en tabell

SQL-UDF:er

Rekommenderad startpunkt

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

UDF:er i Python och andra programmeringsspråk

När SQL inte räcker till

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:er i pipeline

mermaid: UDF-transformationslager i pipeline

UDF:er befinner sig i transformationslagret – mellan rålagring och rapportering

Anropas i SELECT; returnerar ett värde per rad
Centraliserar affärslogik som används i flera frågor
Lägg till SECURE för att dölja definitionen för andra användare

Vad är en lagrad procedur?

Deklarera variabler, iterera över resultat, hantera fel
Kör Data Manipulation Language (DML) över flera tabeller
Anropas fristående med CALL

mermaid: datacamp-purple: orchestringsflöde för lagrad procedur

Lagrad procedur: 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:er vs lagrade procedurer

	UDF	Lagrad procedur
Anropas i	`SELECT`	`CALL`
Returnerar	Ett värde per rad	Enskilt värde
Kan köra DML	Nej	Ja
Språk	SQL, Python, Java, JS	SQL (Snowflake Scripting), Python, Java, JS, Scala
Användning i pipeline	Transformera data	Orkestrera logik

Låt oss öva!

Automatisering av datapipelines i Snowflake