Пользовательские функции и хранимые процедуры

Автоматизация конвейеров данных в 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 $$;

UDF на Python и других языках программирования

Когда 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 vs хранимые процедуры

	UDF	Хранимая процедура
Вызывается в	`SELECT`	`CALL`
Возвращает	Одно значение на строку	Одно значение
Может выполнять DML	Нет	Да
Языки	SQL, Python, Java, JS	SQL (Snowflake Scripting), Python, Java, JS, Scala
Роль в пайплайне	Преобразование данных	Оркестрация логики

Вперёд, к практике!

Автоматизация конвейеров данных в Snowflake