MySQL предоставляет широкий набор встроенных функций, которые покрывают значительную часть ежедневных задач. В ситуациях, когда необходимо реализовать что-то специфичное для вашего проекта — можно создать Хранимую Функцию (Stored Function). Однако, при всей своей гибкости, не все задачи можно легко написать на SQL. В таких ситуациях на помощь приходят User Defined Functions — компилируемые в нативный код функций загружаемых из shared library.
Создать свою UDF, в целом, не сложно надо реализовать несколько методов си-API.
Для самой простой UDF достаточно реализовать всего лишь одну функцию:
extern "C" double xxx(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) { return 0.0; }Для функций, возвращающих INTEGER или STRING сигнатура будет чуть отличаться.
Входящие аргументы можно достать из полей структуры UDF_ARGS — просто по индексу args->args[0] (количество аргументов хранится в args->arg_count, а их типы в массиве args->arg_type). Возвращаемое значение можно вернуть в MySQL — путем возвращения значения из функции 🙂
Чтобы вызвать нашу функцию из SQL — ее надо собрать в shared library, и подложить в каталог для плагинов (полный путь можно получить выполнив select @@plugin_dir;). После чего функцию нужно загрузить CREATE FUNCTION xxx RETURNS REAL SONAME "xxx.dylib"; После успешной загрузки, нашу функцию можно будет вызывать из консоли MySQL: SELECT xxx();
Ура! У нас есть работающая функция!
Жизненный цикл UDF
MySQL вызывает UDF-функцию на каждой строке. Если хочется сохранять какое-то состояние между вызовами функции — состояние надо где-то хранить. Создавать изменяемые глобальные переменные для UDF-функций не рекомендуется, так как UDF должны быть потоко-безопасными. MYSQL предоставляет возможность хранить разделяемое состояние за указателем UDF_INIT->ptr, а для большего удобства MySQL вызовет для нас функцию xxx_init() перед выполнением SQL statement-а и xxx_deinit()— по окончании. В этих методах мы сможем выделять и освобождать ресурсы.
extern "C" bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message); extern "C" void xxx_deinit(UDF_INIT *intd);Помимо аллокации ресурсов, в xxx_init() мы можем валидировать входные параметры: если мы не хотим работать с типами, с которыми функция вызвана — надо вернрнуть true — MySQL прервет выполнение SQL Statement-а и показажет message в качестве текста ошибки.
Для Агрегатных UDF-функций жизненный цикл чуть более сложный:
-
Вызывается
xxx_init() -
Выполняется запрос, строки разбиваются на группы согласно
GROUP BY. -
Для сброса накопленной статистики на первой строке группы вызывается
xxx_clear() -
Для каждой строки в группе вызывается
xxx_add() -
В конце вызывается
xxx()для получения значения агрегатной функции -
Шаги 3-5 повторяются
-
Вызывается
xxx_deinit()
Let’s code!
Попробуем реализовать агрегатную функцию рассчета квантилей. Воспользуемся алгоритмом и структурой данных t-digest ( https://github.com/tdunning/t-digest/ ) а точнее — его реализацию на языке Си( https://github.com/RedisBloom/t-digest-c ). Этот алгоритм позволяет для потока входящих значений с высокой точностью оценить квантили, при этом использую небольшое количество памяти. (Для наших целей этот алгоритм даже избыточен — т.к. мы высчитываем только один квантиль, а t-digest позволяет делать любое число запросов к накопленной статистике).
Начнем с инициализации:
#include "tdigest.h" #include "include/mysql.h" typedef struct { td_histogram_t *tdigest; } Data; extern "C" bool mysql_tdigest_init( UDF_INIT *initid, UDF_ARGS *args, char *message) { if(args->arg_count != 2) { {1} strlcpy(message, "2 args expected", MYSQL_ERRMSG_SIZE); return true; } // force arguments to double {2} args->arg_type[0]=REAL_RESULT; args->arg_type[1]=REAL_RESULT; {3} Data data =(Data)malloc(sizeof(Data)); data->tdigest = td_new(100); // 100 is recommended by author initid->ptr =(char*)data; return false; // This function should return 1 if something goes wrong. }-
messages— указатель на буфер, куда можно вывести текст ошибки длиной доMYSQL_ERRMSG_SIZEбайт. Рекомендуется ограничиваться 80-ю символвами для лучшего UX. -
args->arg_typeсодержит массив изargs->arg_countэлементов, указывающих тип аргументов. Можно самостоятельно валидировать эти значения (если наша функция поддерживает различные типы входящих аргументов) или можно указать желаемые типы данных — MySQL сам проверит типы и по возможности приведет к нужному типу. -
Создаем структурку, в которой будем хранить разделяемое состояние
В конце работы, надо освободить все занятые ресурсы. Напишем deinit-функцию:
extern "C" void mysql_tdigest_deinit(UDF_INIT *initid) { Data *data = (Data*) (initid->ptr); td_free(data->tdigest); free(data); initid->ptr = NULL; }Жизненный цикл UDF-функции отлично ложится на API библиотеки t-digest-c, реализация этих методов тоже тривиальна:
extern "C" void mysql_tdigest_clear( UDF_INIT *initid, unsigned char *is_null, unsigned char *error) { Data *data =(Data*) (initid->ptr); td_reset(data->tdigest); } extern "C" void mysql_tdigest_add( UDF_INIT *initid, UDF_ARGS *args, unsigned char *is_null, unsigned char *error) { Data data =(Data) (initid->ptr); double value = ((double) (args->args[0])); td_add(data->tdigest, value, 1); } extern "C" double mysql_tdigest( UDF_INIT *initid, UDF_ARGS *args, unsigned char *is_null, unsigned char *error) { Data data =(Data) (initid->ptr); double quantile = *((double*) (args->args[1])); return td_quantile(data->tdigest, quantile); } Собираем:
clang -dynamiclib -lm -lc -Lt-digest-c/build/src -ltdigest_static -I percona-server/bld/include src/main.cc -o mysql_tdigest.dylib
Подкладываем нашу библиотеку к MySQL:
cp mysql_tdigest.dylib /usr/local/opt/mysql/lib/plugin/
Создаем функцию:
CREATE AGGREGATE FUNCTION mysql_tdigest RETURNS REAL SONAME "mysql_tdigest.dylib";На примере тестовой базы доступной в интернете посчитаем квантили:
mysql> SELECT avg(amount_charged), mysql_tdigest(amount_charged, 0.5), mysql_tdigest(amount_charged, 0.9) FROM orders GROUP BY user_id LIMIT 5; +---------------------+------------------------------------+------------------------------------+ | avg(amount_charged) | mysql_tdigest(amount_charged, 0.5) | mysql_tdigest(amount_charged, 0.9) | +---------------------+------------------------------------+------------------------------------+ | 1661.3750 | 1247.1 | 3809.0 | | 1079.3158 | 908.0 | 2740.0 | | 1331.5581 | 1280.5 | 1991.0 | | 2987.0000 | 2796.5 | 6235.0 | | 1150.0909 | 1289.0 | 1630.0 | +---------------------+------------------------------------+------------------------------------+ Удалить функцию:
DROP FUNCTION mysql_tdigest;Вроде, не сложно =)
Прочие возможности
На самом деле никто не заставляет писать UDF на чистом Си — главное, поддерживать C calling convention. Например, можно использовать C++ wrappers ( https://jira.percona.com/browse/PS-7348 ), а если не бояться unsafe и raw-pointers — то функции на Rust FFI тоже отлично работают:
#[no_mangle] pub unsafe extern "C" fn my_summ( initid: *mut UDF_INIT, args: *mut UDF_ARGS, is_null: *mut c_uchar, error: *mut c_uchar, ) -> f64 { 0.0 }Ограничения
-
Не получится заставить работать в Managed Database — ни один Cloud Provider не разрешит загружать пользовательскую shared library
-
для работы со Statment Based Replication наши UDF должны быть на 100% детерминированными и установленными на всех репликах. Из хороших новостей — SBR используется не часто, а UDF не требуют дополнительной сопроводительной работы — после первоначальной настройки, при последующих рестартах UDF функции будут автоматически загружены из библиотеки.
-
Ограниченый набор типов входящих аргументов и возвращаемых значений
-
segfault в UDF так же уронит и весь MySQL
Выводы
API довольно краток и создать UDF не сложно. UDF может стать палочкой-выручалочкой, когда других выходов расширить стандартный набор функций в MySQL не осталось. А может стать проклятием при заезде в облако. Можно надеяться, что в MySQL, как и в PostgreSQL, завезут поддержку интерпретируемых языков — Python или ECMAScript.
Ссылки
ссылка на оригинал статьи https://habr.com/ru/post/672138/
Добавить комментарий