Snowflake JavaScript UDF — модульное тестирование и развертывание с помощью DBT.

Snowflake предлагает возможность использовать JavaScript (JS) для написания определяемых пользователем функций (UDF). В этой статье я хочу показать, как автономно тестировать такие пользовательские функции и как можно использовать DBT для их создания в Snowflake.

Это не учебник по Snowflake или DBT. Я предполагаю базовые знания как о JavaScript, так и о нем. Для простоты я использую минимальный пример, исключая использование какой-либо среды модульного тестирования JS или более сложной структуры проекта.
Кроме того, интеграция такого модульного тестирования и развертывания DBT в более крупный конвейер выходит за рамки этой статьи. .

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

Мы будем использовать пример UDF из официальной документации Snowflake.

Табличные пользовательские функции JavaScript (UDTF) — документация Snowflake
В этом разделе объясняется, как писать UDTF (определяемые пользователем табличные функции) в JavaScript. В этой теме: Этот документ…docs.snowflake.com

CREATE OR REPLACE FUNCTION "CHAR_SUM"(INS STRING)
    RETURNS TABLE (NUM FLOAT)
    LANGUAGE JAVASCRIPT
    AS '{
    processRow: function (row, rowWriter, context) {
      this.ccount = this.ccount + 1;
      this.csum = this.csum + row.INS.length;
      rowWriter.writeRow({NUM: row.INS.length});
    },
    finalize: function (rowWriter, context) {
     rowWriter.writeRow({NUM: this.csum});
    },
    initialize: function(argumentInfo, context) {
     this.ccount = 0;
     this.csum = 0;
    }}';

Эта определяемая пользователем функция подсчитывает количество символов в заданных строках и записывает как количество символов в строке, так и общую сводку в качестве результата.

CREATE TABLE parts (p FLOAT, s STRING);

INSERT INTO parts VALUES (1, 'michael'), (1, 'kelly'), (1, 'brian');
INSERT INTO parts VALUES (2, 'clara'), (2, 'maggie'), (2, 'reagan');
select * from parts, table(char_sum(s));
+--------+---------+-----+
| P      | S       | NUM |
+--------+---------+-----+
| 1      | michael | 7   |
| 1      | kelly   | 5   |
| 1      | brian   | 5   |
| 2      | clara   | 5   |
| 2      | maggie  | 6   |
| 2      | reagan  | 6   |
| [NULL] | [NULL]  | 34  |
+--------+---------+-----+

Проект DBT

Мы используем проект DBT udf_testing с тремя основными папками: макросы, модели и udf_js_tests, а также dbt_project.yml и профили. yml файлы.

Мы помещаем наш код UDF в macros/udfs. Чтобы иметь возможность протестировать код JS отдельно, мы разделили оператор UDF на два макроса: char_sum_js.sql для кода JavaScript и create_char_sum_udf.sqlдля кода JavaScript. Оператор SQL для создания пользовательской функции.

Кроме того, мы определяем одну модель, которую можно использовать в качестве тестовых данных для UDF. Файл модели parts.sql находится в папке models/char_sum.

Папка udf_js_tests позже будет использоваться для размещения нашего модульного теста.

udf_testing/
  macros/
    udfs/
      char_sum_js.sql
      create_char_sum_udf.sql
  models/
    char_sum/
      parts.sql
  udf_js_tests/
  dbt_project.yml
  profiles.yml

макросы/udfs/char_sum_js.sql

{% macro char_sum_js() %}
{
  processRow: function (row, rowWriter, context) {
    this.ccount = this.ccount + 1;
    this.csum = this.csum + row.INS.length;
    rowWriter.writeRow({NUM: row.INS.length});
  },
  
  finalize: function (rowWriter, context) {
   rowWriter.writeRow({NUM: this.csum});
  },
  
  initialize: function(argumentInfo, context) {
    this.ccount = 0;
    this.csum = 0;
  }
}
{% endmacro %}

макросы/udfs/create_char_sum_udf.sql

{% macro create_char_sum_udf() %}
CREATE OR REPLACE FUNCTION "CHAR_SUM"(INS STRING)
  RETURNS TABLE (NUM FLOAT)
  LANGUAGE JAVASCRIPT
  AS '{{ char_sum_js() }}';
{% endmacro %}

models/char_sum/parts.sql

SELECT
  $1 as p,
  $2 as s
FROM VALUES
  (1, 'michael'), 
  (1, 'kelly'), 
  (1, 'brian'), 
  (2, 'clara'), 
  (2, 'maggie'), 
  (2, 'reagan')

profiles.yml

udf_testing:
  outputs:
    char_sum:
      account: ""
      database: ""
      password: ""
      schema: "public"
      threads: 1
      type: snowflake
      user: ""

dbt_project.yml

name: 'udf_testing'
version: '1.0.0'
config-version: 2
profile: 'udf_testing'
macro-paths: ["macros"]
model-paths: ["models"]
models:
  udf_testing:
    char_sum:
      +post-hook:
        - "{{ create_char_sum_udf() }}"

Запустите проект DBT

Учитывая, что необходимая недостающая информация в profiles.yml была предоставлена, теперь мы можем запустить проект.

dbt run — profiles-dir . — target char_sum

Обратите внимание, что DBT не печатает выполнение макроса в случае успешного запуска DBT.

Теперь мы можем протестировать нашу пользовательскую функцию в Snowflake, выполнив ее на тестовых данных в контексте базы данных, указанной в profiles.yml.

select * from parts, table(char_sum(s));

Модульный тест UDF

Для модульного теста мы будем использовать nodejs и ванильный JavaScript. Nodejs позволяет нам выполнять JavaScript из командной строки. Установка проста, соответствующие инструкции можно найти здесь: https://nodejs.dev/learn/how-to-install-nodejs.

Мы создаем файл udf_js_tests/test_char_sum.js, который будет содержать наши тесты и некоторые вспомогательные функции.

Сначала добавим небольшую вспомогательную функцию для проверки результатов наших тестов. В версии производственного кода эта функциональность, конечно же, будет обеспечиваться средой модульного тестирования.

function assert(test_name, is_true) {
  if (is_true == false) {
    console.log('  Failed test: ' + test_name);
  } else {
    console.log('  Ok test: ' + test_name);
  }
}

Далее нам нужен способ загрузки кода JS из macros/udfs/char_sum_js.sql. Следующая функция сделает это, а также преобразует код в объект среды выполнения, который можно вызывать в наших тестах.

const fs = require('fs');
function load_udf_to_test(file_name) {
  const udf_string = fs.
                      readFileSync(file_name, 'utf8').
                      replace(/{%(.)*%}/g,'')
                      .trim();
  const udf_object = eval('(function self(){return ' + 
                      udf_string + 
                      ';})()');
  return udf_object;
};

Макросы DBT начинаются с {% macro .. %} и заканчиваются {% endmacro %}. Эту часть кода, а также символы новой строки в начале и конце блока JS необходимо удалить. Это достигается с помощью регулярного выражения replace и операции trim.
Чтобы преобразовать JS-код (строку) в полезный объект, используется операция eval. Функция-обертка нужна для получения доступа к JS-объекту. Более подробное объяснение последнего можно найти здесь: https://stackoverflow.com/questions/7399024/how-can-i-use-js-eval-to-return-a-value

Теперь мы можем загрузить нашу UDF, передав путь к файлу sql в функцию.

const char_sum_udf = load_udf_to_test('../macros/udfs/char_sum_js.sql');

Теперь мы можем добавить наш первый тест. Мы гарантируем, что функция initialize работает правильно.

console.log('Test initialize function');
char_sum_udf.initialize({}, {});
assert('Init csum', char_sum_udf.csum == 0);
assert('Init ccount', char_sum_udf.ccount == 0);

Затем мы можем протестировать функцию processRow. Однако эта функция ожидает аргументы: средство записи строки, а также строку. Теперь мы можем просто сымитировать автора и предоставить тестовую строку.

const row_writer_result = [];
const mock_row_writer = {
   writeRow: function(o) {
    row_writer_result.push(o);
   }
};
const test_row = {
  INS: 'michael'
};
char_sum_udf.processRow(test_row, mock_row_writer, {});
assert('Process row result exists', row_writer_result.length == 1);
assert('Process row result correct', row_writer_result[0].NUM == 7);
assert('Process row csum correct', my_udf.csum == 7);
assert('Process row ccount correct', my_udf.ccount == 1);

Модульный тест функции finalise следует тому же подходу. Теперь мы можем запустить наш тестовый файл и проверить результаты.

node test_char_sum.js

Заключение

Возможность поддерживать пользовательские функции через DBT и проводить их автономное модульное тестирование, как и другие артефакты кода, помогает обеспечить высокое качество и следовать передовым инженерным практикам. Пользовательские функции — это просто код в репозитории, который можно изменять, просматривать, тестировать и развертывать в контролируемых конвейерах.
Возможность изолированного тестирования UDF также может помочь в исследовании и выборке базовой производительности при различных нагрузках без участия реального экземпляра Snowflake. В целом это полезно, особенно в локальных средах разработки и тестирования.
Несмотря на то, что эта статья упростила процедуры тестирования и структуру проекта, общий подход не сильно изменился бы в условиях производственного уровня. Могут быть введены надлежащие среды модульного тестирования, а тест JS может быть вызван, например. конвейеры gitlab в качестве обязательного шага перед применением DBT или тестом DBT на фактическом экземпляре Snowflake.
Обертывание кода JS в макрос добавляет некоторый шум в файл и имеет недостатки, например. подсветка кода в редакторе. Однако на данный момент, похоже, нет другого способа создавать файлы, которые принимает DBT.
Snowflake также поддерживает пользовательские функции Java. Их можно использовать как встроенные, как JS, или путем загрузки и ссылки на JAR-файлы. В обоих случаях также возможны процедуры тестирования в сочетании с контролируемым созданием DBT.
Хранимые процедуры JS можно тестировать так же, как и пользовательские функции JS. Потребуется имитация объекта snowflake и связанных с ним функций.