Пользовательские функции (User-defined functions)

Пользовательские функции (UDF) позволяют определять и регистрировать собственные функции непосредственно в вашем хранилище данных. Подобно макросам, UDF способствуют повторному использованию кода, но в отличие от макросов они являются объектами в хранилище. Это означает, что одну и ту же логику можно использовать не только в dbt, но и в других инструментах — например, в BI-системах, ноутбуках для data science и т.д.

UDF особенно полезны для переиспользования логики в нескольких инструментах, стандартизации сложных бизнес‑расчётов, повышения производительности вычислительно тяжёлых операций (поскольку они компилируются и оптимизируются движком запросов вашего хранилища), а также для контроля версий кастомной логики внутри вашего dbt‑проекта.

dbt создаёт, обновляет и переименовывает UDF в процессе выполнения DAG. UDF создаётся в хранилище до той модели, которая на него ссылается. Подробнее о том, как dbt строит UDF в проекте, см. listing and building UDFs.

Дополнительную информацию о конфигурациях и свойствах UDF см. в разделах Function properties и Function configurations.

Предварительные требования

• Убедитесь, что вы используете Latest Fusion или Latest release track платформы dbt либо dbt Core версии v1.11.

• Используйте один из следующих адаптеров:

  dbt Core

  • BigQuery
  • Snowflake
  • Redshift
  • Postgres
  • Databricks

  dbt Fusion engine

  • BigQuery
  • Snowflake
  • Redshift
  • Databricks

UDF support

При разработке UDF важно учитывать следующие ограничения поддержки:

• Python UDF пока не поддерживаются в Fusion.
• Дополнительные языки (например, Java, JavaScript, Scala) в настоящий момент не поддерживаются.

Полный список поддерживаемых возможностей UDF см. в разделе Limitations ниже.

Определение UDF в dbt

В dbt можно определять UDF на SQL и Python. Python UDF в настоящее время поддерживаются в Snowflake и BigQuery при использовании dbt Core.

Выполните следующие шаги, чтобы определить UDF в dbt:

1. Создайте SQL‑ или Python‑файл в каталоге functions. Например, следующая UDF проверяет, представляет ли строка положительное целое число:

   SQL

   Определение SQL UDF в SQL‑файле.

   functions/is_positive_int.sql

   # syntax for BigQuery, Snowflake, and Databricks
   REGEXP_INSTR(a_string, '^[0-9]+$') 
   
   # syntax for Redshift and Postgres
   SELECT REGEXP_INSTR(a_string, '^[0-9]+$')
   

   Python

   Определение Python UDF в Python‑файле.

   functions/is_positive_int.py

   import re
   
   def main(a_string):
       return 1 if re.search(r'^[0-9]+$', a_string or '') else 0

   Примечание: конфигурации можно указать либо в config‑блоке SQL‑файла, либо в соответствующем YAML‑файле со свойствами на следующем шаге (шаг 2).

2. Укажите имя функции и задайте конфигурации, свойства, тип возвращаемого значения и (опционально) аргументы в соответствующем YAML‑файле со свойствами. Например:

   SQL

   functions/schema.yml

   functions:
     - name: is_positive_int       # required
       description: My UDF that returns 1 if a string represents a naked positive integer (like "10", "+8" is not allowed). # optional
       config:
         schema: udf_schema
         database: udf_db
         volatility: deterministic
       arguments:                  # optional
         - name: a_string          # required if arguments is specified
           data_type: string       # required if arguments is specified
           description: The string that I want to check if it's representing a positive integer (like "10") 
           default_value: "'1'"    # optional, available in Snowflake and Postgres
       returns:                    # required
         data_type: integer        # required 

   Python

   При определении Python UDF требуются следующие конфигурации:

   • runtime_version — версия Python, в которой будет выполняться код. Поддерживаемые значения:
     • Snowflake: 3.10, 3.11, 3.12, 3.13
     • BigQuery: 3.11
   • entry_point — имя Python‑функции, которая будет вызываться.

   Пример:

   functions/schema.yml

     functions:
       - name: is_positive_int # обязательно
         description: Моя UDF, которая возвращает 1, если строка представляет собой «чистое» положительное целое число (например, "10"; "+8" не допускается). # optional
         config:
           runtime_version: "3.11"   # обязательно
           entry_point: main         # обязательно
           schema: udf_schema
           database: udf_db
           volatility: deterministic  
         arguments:                   # опционально
           - name: a_string           # обязательно, если указан arguments
             data_type: string        # обязательно, если указан arguments
             description: Строка, по которой я хочу проверить, что она представляет положительное целое число (например, "10")
             default_value: "'1'"     # опционально; доступно в Snowflake и Postgres
         returns:                     # обязательно
           data_type: integer         # обязательно
   volatility зависит от хранилища данных

   Обратите внимание, что параметр volatility принимается dbt как для SQL‑, так и для Python‑UDF, но его обработка зависит от хранилища. BigQuery игнорирует volatility, и dbt выводит предупреждение. В Snowflake volatility применяется при создании UDF. Подробнее см. в разделе volatility.

3. Выполните одну из следующих команд dbt build, чтобы собрать UDF и создать их в хранилище:

   Сборка всех UDF:

   dbt build --select "resource_type:function"

   Или сборка конкретной UDF:

   dbt build --select is_positive_int

   При выполнении dbt build файл functions/schema.yml и соответствующий SQL‑ или Python‑файл (например, functions/is_positive_int.sql или functions/is_positive_int.py) совместно используются для генерации оператора CREATE FUNCTION.

   Сгенерированный оператор CREATE FUNCTION зависит от используемого адаптера. Например:

   SQL

   Snowflake

   CREATE OR REPLACE FUNCTION udf_db.udf_schema.is_positive_int(a_string STRING DEFAULT '1')
   RETURNS INTEGER
   LANGUAGE SQL
   IMMUTABLE
   AS $$
     REGEXP_INSTR(a_string, '^[0-9]+$')
   $$;

   Redshift

   CREATE OR REPLACE FUNCTION udf_db.udf_schema.is_positive_int(a_string VARCHAR)
   RETURNS INTEGER
   IMMUTABLE
   AS $$
     SELECT REGEXP_INSTR(a_string, '^[0-9]+$')
   $$ LANGUAGE SQL;

   BigQuery

   CREATE OR REPLACE FUNCTION udf_db.udf_schema.is_positive_int(a_string STRING)
   RETURNS INT64
   AS (
     REGEXP_INSTR(a_string, r'^[0-9]+$')
   );
   

   Databricks

   CREATE OR REPLACE FUNCTION udf_db.udf_schema.is_positive_int(a_string STRING)
   RETURNS INT
   DETERMINISTIC
   RETURN REGEXP_INSTR(a_string, '^[0-9]+$');

   Postgres

   CREATE OR REPLACE FUNCTION udf_schema.is_positive_int(a_string text DEFAULT '1')
   RETURNS int
   LANGUAGE sql
   IMMUTABLE
   AS $$
     SELECT regexp_instr(a_string, '^[0-9]+$')
   $$;

   Python

   Snowflake

   CREATE OR REPLACE FUNCTION udf_db.udf_schema.is_positive_int(a_string STRING DEFAULT '1')
     RETURNS INTEGER
     LANGUAGE PYTHON
     RUNTIME_VERSION = '3.11'
     HANDLER = 'main'
   AS $$
   import re
   def main(a_string):
     return 1 if re.search(r'^[0-9]+$', a_string or '') else 0
   $$;

   BigQuery

   CREATE OR REPLACE FUNCTION udf_db.udf_schema.is_positive_int(a_string STRING)
   RETURNS INT64
   LANGUAGE python
   OPTIONS(runtime_version="python-3.11", entry_point="main")
   AS r'''
     import re
     def main(a_string):
       return 1 if re.search(r'^[0-9]+$', a_string or '') else 0
   ''';

4. Используйте UDF в модели с помощью макроса {{ function(...) }}. Например:

   models/my_model.sql

   select
       maybe_positive_int_column,
       {{ function('is_positive_int') }}(maybe_positive_int_column) as is_positive_int
   from {{ ref('a_model_i_like') }}

5. Выполните dbt compile, чтобы увидеть, как UDF подставляется в скомпилированный SQL. В следующем примере {{ function('is_positive_int') }} заменяется на имя UDF udf_db.udf_schema.is_positive_int.

   models/my_model.sql

   select
       maybe_positive_int_column,
       udf_db.udf_schema.is_positive_int(maybe_positive_int_column) as is_positive
   from analytics.dbt_schema.a_model_i_like

   В DAG создаётся узел UDF на основе SQL/Python‑файла и YAML‑описания, и появляется зависимость is_positive_int → my_model.

   DAG для узла UDF

После определения UDF, если вы обновите SQL‑ или Python‑файл с телом функции либо её конфигурации, изменения будут применены к UDF в хранилище при следующем запуске build.

Использование UDF в unit‑тестах

Вы можете использовать unit tests для проверки моделей, которые ссылаются на UDF. Перед запуском unit‑тестов убедитесь, что функция существует в хранилище. Чтобы гарантировать это для unit‑теста, выполните:

dbt build --select "+my_model_to_test" --empty

Следуя примеру из раздела Определение UDF в dbt, ниже приведён пример unit‑теста, который проверяет модель, вызывающую UDF:

tests/test_is_positive_int.yml

unit_tests:
  - name: test_is_positive_int 
    description: "Проверьте, что моя логика is_positive_int учитывает крайние случаи"
    model: my_model
    given:
      - input: ref('a_model_i_like')
        rows:
          - { maybe_positive_int_column: 10 }
          - { maybe_positive_int_column: -4 }
          - { maybe_positive_int_column: +8 }
          - { maybe_positive_int_column: 1.0 }
    expect:
      rows:
        - { maybe_positive_int_column: 10,  is_positive: true }
        - { maybe_positive_int_column: -4,  is_positive: false }
        - { maybe_positive_int_column: +8,  is_positive: true }
        - { maybe_positive_int_column: 1.0, is_positive: true }

Просмотр и сборка UDF

Используйте команду list, чтобы вывести список UDF в проекте:
dbt list --select "resource_type:function" или dbt list --resource-type function.

Используйте команду build, чтобы выбирать UDF при сборке проекта:
dbt build --select "resource_type:function".

Подробнее о выборе UDF см. в примерах в разделе Node selector methods.

Ограничения

• Создание UDF на других языках (например, Java, JavaScript или Scala) пока не поддерживается.
• Python UDF в настоящее время поддерживаются только в Snowflake и BigQuery. Другие хранилища пока не поддерживаются.
• Поддержка Python UDF в Fusion пока недоступна. Актуальные обновления см. в Fusion Diaries.
• В настоящий момент поддерживаются только функции типов scalar и aggregate. Подробнее см. в разделе Supported function types.

Связанные FAQ

Когда следует использовать UDF вместо макроса?

И пользовательские функции (UDF), и макросы позволяют переиспользовать логику в dbt‑проекте, однако работают они принципиально по‑разному. Ниже — рекомендации, когда стоит использовать каждый из подходов.

Используйте UDF, когда:

 Вам нужна логика, доступная за пределами dbt

UDF создаются непосредственно в хранилище данных и могут использоваться BI‑инструментами, ноутбуками для data science, SQL‑клиентами или любыми другими инструментами, которые подключаются к вашему хранилищу. Макросы же работают только внутри dbt.

 Вы хотите стандартизировать нативные функции хранилища

UDF позволяют создавать переиспользуемые функции хранилища для валидации данных, пользовательского форматирования или бизнес‑специфичных вычислений, которые должны быть единообразными во всех инструментах работы с данными. После создания такие функции становятся частью каталога функций вашего хранилища.

 Вы хотите, чтобы dbt управлял жизненным циклом функции

dbt управляет UDF как частью выполнения DAG, гарантируя, что они будут созданы до моделей, которые на них ссылаются. Вы можете хранить определения UDF в системе контроля версий вместе с моделями, тестировать изменения в средах разработки и разворачивать их через CI/CD‑пайплайны.

 Jinja компилируется в момент создания, а не при каждом вызове функции

В конфигурации UDF можно использовать Jinja (циклы, условия, макросы, ref, source, var). dbt разрешает этот Jinja в момент создания UDF, и именно получившееся SQL‑тело сохраняется в хранилище.

Jinja влияет на функцию в момент её создания, тогда как аргументы влияют на неё во время выполнения в хранилище:

• ✅ Разрешено: Jinja, зависящий от состояния проекта или времени сборки — например, var(“can_do_things”), статический ref(‘orders’) или логика, зависящая от окружения. Всё это вычисляется один раз при создании.
• ❌ Запрещено: Jinja, зависящий от аргументов функции, передаваемых во время выполнения. Компилятор их не видит, поэтому динамический ref(ref_name) или условный Jinja на основе значений аргументов работать не будут.

 Вам нужна Python‑логика, выполняемая в вашем хранилище

Python UDF создаёт Python‑функцию непосредственно в хранилище данных, которую затем можно вызывать с помощью SQL.
Это упрощает применение сложных трансформаций, вычислений или логики, которые трудно или громоздко выразить на SQL.

Python UDF поддерживают условия и циклы внутри самой функции (с использованием синтаксиса Python) и выполняются во время выполнения запроса, а не на этапе компиляции, как макросы. В настоящее время Python UDF поддерживаются в Snowflake и BigQuery.

Используйте макросы, когда:

 Вам нужно генерировать SQL на этапе компиляции

Макросы динамически генерируют SQL до отправки его в хранилище (на этапе компиляции). Это критично для:

• построения разного SQL для разных хранилищ;
• генерации повторяющихся SQL‑паттернов (например, создания десятков похожих колонок);
• создания целых определений моделей или DDL‑выражений;
• динамических ссылок на модели в зависимости от структуры проекта.

UDF выполняются во время выполнения запроса в хранилище. Хотя в их определениях можно использовать Jinja, они не генерируют новые SQL‑запросы — это заранее определённые функции, которые просто вызываются из SQL.

Расширение UDF

В настоящее время поддерживаются SQL‑ и Python‑UDF. Поддержка Java и Scala UDF планируется в будущих релизах.

 Вам нужно генерировать DDL или DML‑выражения

В настоящее время поддерживаются SQL‑ и Python‑UDF. Поддержка Java и Scala UDF планируется в будущих релизах.

 Вам нужно адаптировать SQL под разные хранилища

Макросы могут использовать условную логику Jinja для генерации SQL, специфичного для конкретного хранилища (см. cross-database macros), что делает dbt‑проект переносимым между платформами.

UDF — это объекты, специфичные для хранилища. Даже несмотря на то, что в определениях UDF можно использовать Jinja, у каждого хранилища свой синтаксис создания функций, разные поддерживаемые типы данных и диалекты SQL. Для каждого поддерживаемого хранилища вам придётся определять отдельные файлы UDF.

 Вашей логике нужен доступ к контексту dbt

И макросы, и UDF могут использовать Jinja, а значит имеют доступ к переменным контекста dbt, таким как {{ ref() }}, {{ source() }}, переменные окружения и конфигурации проекта. Более того, можно вызывать макрос из UDF (и наоборот), комбинируя динамическую генерацию SQL с выполнением логики во время выполнения.

Однако ключевое различие между ними — это момент, когда выполняется логика:

• Макросы выполняются на этапе компиляции, генерируя SQL до отправки его в хранилище.
• UDF выполняются внутри хранилища во время выполнения запроса.

 Вы хотите избежать создания объектов в хранилище

Макросы ничего не создают в вашем хранилище; они лишь генерируют SQL на этапе компиляции. UDF же создают реальные объекты функций в хранилище, которыми нужно управлять.

Можно ли использовать оба подхода вместе?

Да! Вы можете использовать макрос для вызова UDF или вызывать макрос внутри UDF, сочетая преимущества обоих подходов. Например, следующий пример показывает, как использовать макрос для задания значений аргументов по умолчанию вместе с логикой UDF:

{% macro cents_to_dollars(column_name, scale=2) %}
  {{ function('cents_to_dollars') }}({{ column_name }}, {{scale}})
{% endmacro %}

Связанная документация

• Пользовательские функции (UDF)
• Jinja‑макросы

Нашли ошибку?

Создать GitHub Issue