MySQLのUDFを作ってみよう（cmake＋Aggregate Function編)

こんにちは。ちょびえです。MySQLカジュアルAdventCalendarの21日目ということでして、今日はカジュアルにUDFを作ってみようという記事でお茶を濁したいと思います。

統計系のSQLをMySQLでどうやってかきますか？

MySQLで統計系の結果を出そうとしている時によく出くわすのがPercentile面倒くさい問題だと思います。SQLでもがんばればできるんですが、それを特定の機能のメインの機能にするとちょっとつらいかなー、と。

そんな時にUDFをさっくり書けるようになっておくと（運用上の問題は別途でてきますが）簡単なクエリで解決できるようになります。なんて素敵なんでしょう。

それじゃあ書いてみよう

gccで直接指定してもいいんですが、色々な環境でビルドできるようにしておいたほうが楽なので今日はcmakeを使って書いていきます。

今回は単純なpercentile機能を実装したいと思うのでこんな感じのCmakeLists.txtを書きます

PROJECT(percentile)
CMAKE_MINIMUM_REQUIRED(VERSION 2.6)
set(CMAKE_MODULE_PATH ${CMAKE_SOURCE_DIR}/cmake ${CMAKE_MODULE_PATH})
set(CMAKE_COLOR_MAKEFILE ON)

ADD_DEFINITIONS("-DHAVE_DLOPEN")
INCLUDE_DIRECTORIES("/usr/include/mysql")

ADD_LIBRARY(percentile SHARED percentile.c)
SET_TARGET_PROPERTIES(percentile PROPERTIES OUTPUT_NAME "percentile")

find_package(MYSQL REQUIRED)
if (MYSQL_FOUND)
    include_directories(${MYSQL_INCLUDE_DIR})
    link_directories(${MYSQL_LIBRARY})
endif(MYSQL_FOUND)

if (MYSQL_PLUGIN_DIR)
    INSTALL(TARGETS percentile DESTINATION ${MYSQL_PLUGIN_DIR})
endif(MYSQL_PLUGIN_DIR)

他のUDF書きたいのであればpercentileの部分を別の名前にしてもらえばOKです。

percentile.c - Aggregate Functionの実装 -

では続いて、C言語でUDFを書いていきます。

http://dev.mysql.com/doc/refman/5.6/en/adding-udf.html

詳しい説明は上記ページに書いてありますが、大雑把に集約関数の実行部分を訳すと
* 集約関数を実行する際にモジュール名_init()を呼び出し、メモリ確保などを行います.
* グループの最初の行が出てきた時にモジュール名_clear()がグループ毎に呼び出されます.
* 同じグループに属する行が来たらモジュール名_add()が呼び出されます.
* 集約結果を取得する際にモジュール名()が呼び出されます
* モジュール名_deinit()で不要になったメモリの解放等を行います

と、いうことなので最低限この4つの関数を実装すれば集約関数がつかえるようです。

今回実装するpercentileは対象データをどんどん積んでいき、最後にソートを行ってほしいpercentileの位置を指定するという効率の悪い、しかしながらとても単純な実装にしました。

#include <stdlib.h>
#include <string.h>
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <stdint.h>

#define BUFFERSIZE 8192

my_bool percentail_init(UDF_INIT* initid, UDF_ARGS* args, char* message);
void percentail_deinit(UDF_INIT* initid);
void percentile_clear(UDF_INIT *initid, char *is_null, char *is_error);
void percentile_add(UDF_INIT *initid, UDF_ARGS* args, char *is_null, char *error);
double percentile(UDF_INIT* initid, UDF_ARGS *args, char *is_null, char *error);

typedef struct percentile_data
{
    uint64_t count;
    uint64_t abscount;
    uint64_t pages;
    double *values; 
} percentile_data;

my_bool percentile_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
    percentile_data *data;
    double score;

    if (args->arg_count != 2) {
        strcpy(message, "wrong number of arguments; percentile() requires two arguments");
        return 1;
    }
    args->arg_type[1] = REAL_RESULT;

    data = malloc(sizeof(percentile_data));
    data->count = 0;
    data->abscount = 0;
    data->pages = 1;
    data->values = NULL;

    initid->decimals = 2;
    initid->maybe_null = 1;
    initid->max_length = 64;
    initid->ptr = (char*)data;
    return 0;
}

void percentile_deinit(UDF_INIT *initid)
{
    percentile_data *data = (percentile_data*)initid->ptr;

    if (data->values != NULL) {
        free(data->values);
        data->values = NULL;
    }
    free(initid->ptr);
    initid->ptr = NULL;
}

void percentile_clear(UDF_INIT *initid, char *is_null, char *is_error)
{
    percentile_data *data = (percentile_data*)initid->ptr;
    data->count = 0;
    data->abscount = 0;
    data->pages = 1;
    *is_null = 0;
    *is_error = 0;

    if (data->values != NULL) {
        free(data->values);
        data->values = NULL;
    }

    data->values = (double*)malloc(BUFFERSIZE*sizeof(double));
}

void percentile_reset(UDF_INIT* initid, UDF_ARGS* args, char *is_null, char *is_error)
{
    percentile_clear(initid, is_null, is_error);
    percentile_add(initid, args, is_null, is_error);
}

void percentile_add(UDF_INIT* initid, UDF_ARGS *args, char *is_null, char * is_error)
{
    if (args->args[0] != NULL) {
        percentile_data *data = (percentile_data*)initid->ptr;
        if (data->count >= BUFFERSIZE) {
            data->pages++;
            data->count=0;
            data->values = (double *)realloc(data->values, BUFFERSIZE * data->pages * sizeof(double));
        }
        data->values[data->abscount++] = *((double*)args->args[0]);
        data->count++;
    }
}

int compare_doubles(const void *a, const void *b)
{
    const double *da = (const double *)a;
    const double *db = (const double *)b;

    return (*da > *db) - (*da < *db);
}


double percentile(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *is_error)
{
    percentile_data *data = (percentile_data*)initid->ptr;
    int index;
    double score = 0.5;

    if (data->abscount == 0 || *is_error != 0) {
        strcpy(is_error, "values seems null");
        *is_null = 1;
        return 0.0;
    }

    *is_null = 0;
    if (data->abscount == 1) {
        return data->values[0];
    }

        score = (double)*((double*)args->args[1]);
        if (score < 0.0 || score > 1.0) {
        *is_null = 1;
                return 0.0;
        }

    qsort(data->values, data->abscount, sizeof(double), compare_doubles);
    index = (int)(score * data->abscount);
    if (index > data->abscount-1) {
        index = data->abscount-1;
    } else if (index < 0) {
        index = 0;
    }
    return data->values[index];
}

はじめてのAggregate Function、ということでこれくらいで。

実際にproduction環境などで真面目に利用する場合はもうちょいエラーハンドリングを考慮したり、メモリの実行効率等も検討しないと使い物になりませんがある程度小規模なデータセットならこれぐらいで十分動くと思います。MySQLは一つの関数で複数の結果を出力するようなタイプのものは書けないそうなので少々残念ですが、統計系のUDFなどを事前に書いておくと色々と捗るかもしれません。
（今となってはPrestoから利用したほうが楽かもしれませんが）

ひとまず、これでselect percentile(column, 0.5) from table group by 〜というクエリで指定した位置の値（この場合medianっぽいの、ですね）が出るようになりそうです。

build

mkdir build && cd build && cmake .. && make
chobie% mkdir build && cd build && cmake .. && make 
-- The C compiler identification is AppleClang 5.1.0.5030038
-- The CXX compiler identification is AppleClang 5.1.0.5030038
-- Check for working C compiler: /usr/bin/cc
-- Check for working C compiler: /usr/bin/cc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler: /usr/bin/c++
-- Check for working CXX compiler: /usr/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Using mysql-config: /usr/local/bin/mysql_config
-- Found MySQL library: /usr/local/lib/libmysqlclient_r.dylib
-- Found MySQL headers: /usr/local/Cellar/mysql/5.6.20_1/include/mysql
-- Configuring done
CMake Warning (dev):
  Policy CMP0042 is not set: MACOSX_RPATH is enabled by default.  Run "cmake
  --help-policy CMP0042" for policy details.  Use the cmake_policy command to
  set the policy and suppress this warning.

  MACOSX_RPATH is not specified for the following targets:

   percentile

This warning is for project developers.  Use -Wno-dev to suppress it.

-- Generating done
-- Build files have been written to: /Users/chobie/src/percentail_udf/build
Scanning dependencies of target percentile
[100%] Building C object CMakeFiles/percentile.dir/percentile.c.o
Linking C shared library libpercentile.dylib
[100%] Built target percentile

ということでlibpercentile.dylibが出来上がったのでmysqlに読み込ませます。

cp libpercentile.dylib `/usr/local/Cellar/mysql/5.6.20_1/bin/mysql_config --plugindir`
chobie% mysql.server start          
Starting MySQL
. SUCCESS! 
chobie% mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.20-log Homebrew

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create aggregate function percentile returns real soname 'libpercentile.dylib';
Query OK, 0 rows affected (0.00 sec)

ロードが出来たので実際にクエリを投げてみましょう。

mysql> select name, value from uhi;
+------+-------+
| name | value |
+------+-------+
| uni  |     1 |
| moe  |     2 |
| uni  |     3 |
| moe  |     4 |
| uni  |     5 |
| moe  |     6 |
| uni  |     7 |
| moe  |     8 |
| uni  |     9 |
| moe  |    10 |
| uhi  |   0.5 |
| uhi  |   0.5 |
+------+-------+
12 rows in set (0.00 sec)

mysql> select name, percentile(value, 0.5) from uhi group by name;
+------+------------------------+
| name | percentile(value, 0.5) |
+------+------------------------+
| moe  |                   6.00 |
| uhi  |                   0.50 |
| uni  |                   5.00 |
+------+------------------------+
3 rows in set (0.00 sec)

なんとなくそれっぽい値が出ましたね。

カジュアルにUDFを書けばMySQLの既存の集約関数だけでは困難な計算も簡単に書けるようになるのは魅力的ですね。cmakeで管理していれば色々な環境でUDFをビルドするのも簡単ですのでお勧めです。