Category Archive: Vertica

Предыстория В проекте возникла необходимость отобразить товары, которые поставляются или могут поставляться в определенные регионы Казахстана. У каждого поставщика есть перечень товаров с указанием регионов поставки. Есть таблица товаров, назовем ее product. Регионов в Казахстане всего 17, поэтому я решил хранить данные о регионах поставки в одном числовом поле c помощью битов. Таблица product выглядит следующим образом:

create table product (
	code varchar(20),
	name varchar(50),
	regions bigint,
	company_code varchar(20),
	company_name varchar(50)
);

code - код продукта; name - наименование продукта; regions - регионы поставки; company_code - код компании; company_name - наименование компании; Теперь о битовом хранение регионов. Каждому региону соответствует свой бит:

Число	Битовое представление числа	Наименование региона
1	000000000000000001	По всей территории РК
2	000000000000000010	г. Астана
4	000000000000000100	г. Алматы
8	000000000000001000	Акмолинская область
16	000000000000010000	Актюбинская область
32	000000000000100000	Алматинская область
64	000000000001000000	Атырауская область
128	000000000010000000	Западно-Казахстанская область
256	000000000100000000	Жамбылская область
512	000000001000000000	Карагандинская область
1024	000000010000000000	Костанайская область
2048	000000100000000000	Кызылординская область
4096	000001000000000000	Мангистауская область
8192	000010000000000000	Туркестанская область
16384	000100000000000000	Павлодарская область
32768	001000000000000000	Северо-Казахстанская область
65536	010000000000000000	Восточно-Казахстанская область
131072	100000000000000000	г. Шымкент

Например, если нужно указать, что товар поставляется в три региона: г. Астана, г. Алматы и Карагандинскую область, то нужно применить бинарную операцию побитового "ИЛИ" к битам регионов, в итоге регионы закодируются в число 518:

ИЛИ	000000000000000010	2
	000000000000000100	4
	000000001000000000	512
Результат	000000001000000110	518

Заполним таблицу тестовыми данными:

INSERT INTO product VALUES ('001', 'Трансформатор тока', 6, 'abc', 'Energy Corp.');
INSERT INTO product VALUES ('001', 'Трансформатор тока', 2, 'xyz', 'Power Inc.');
INSERT INTO product VALUES ('002', 'Усилитель низкой частоты', 1, 'abc', 'Energy Corp.');
INSERT INTO product VALUES ('003', 'Набор резисторов', 20, 'abc', 'Energy Corp.');
INSERT INTO product VALUES ('003', 'Набор резисторов', 160, 'volt', 'Volt Group');
INSERT INTO product VALUES ('003', 'Набор резисторов', 36, 'xyz', 'Power Inc.');

Данные готовы, но проблема в том, что в Vertica нет подходящей агрегационной функции, позволяющей делать побитовое "ИЛИ" чисел. В Vertica есть функция bit_or, но по ряду причин она не подходит:

1. Функция bit_or работает с бинарными данными
2. Напрямую преобразовать число в бинарный вид нет возможности, только через hex
3. Функция bit_or производит побитовые операции слева направо, поэтому если битов не хватает правая часть дополняется нулями, что дает не тот результат, который мы ожидаем.

Таким образом, мы пришли к необходимости создания своей функции. Vertica позволяется писать свои функции на разных языках: C++, Java и Python. Но именного агрегационные функции User Defined Aggregate Function (UDAF) можно почему-то писать только на С++. В каталоге, где вы установили Vertica, есть SDK с примерами. Например, в CentOS 7 это директория: /opt/vertica/sdk/examples У вас должна быть установлена среда разработки g++. Чтобы установить среду разработки g++, запустите:

yum install gcc gcc-c++ make

---

Если у вас Ubuntu, библиотеки могу собираться, но не работать выдавая ошибки вида:

Failure in UDx RPC call InvokeGetLibraryManifest()

Насколько я понял, это из-за версий gcc и g++, которые в Ubuntu по умолчанию 5.4.0, а в Centos 7 они по умолчанию 4.8.5. Поэтому, если у вас возникли подобные проблемы, можно доставить нужные версии gcc и g++:

sudo apt-get install gcc-4.8
sudo apt-get install g++-4.8

Создайте файл OrSum.cpp со следующим содержимым:

#include "Vertica.h"
#include 
#include 
#include 

using namespace Vertica;

class OrSum : public AggregateFunction
{

public:

    virtual void initAggregate(ServerInterface &srvInterface,
                               IntermediateAggs &aggs)
    {
        vint &sum = aggs.getIntRef(0);
        sum = 0;
    }

    void aggregate(ServerInterface &srvInterface,
                   BlockReader &arg_reader,
                   IntermediateAggs &aggs)
    {
        vint &sum = aggs.getIntRef(0);
        do {
            const vint &val = arg_reader.getIntRef(0);
            if (val != Vertica::vint_null) {
                sum |= val;
            }
        } while (arg_reader.next());
    }

    virtual void combine(ServerInterface &srvInterface,
                         IntermediateAggs &aggs,
                         MultipleIntermediateAggs &aggs_other)
    {
        vint &sum = aggs.getIntRef(0);

        do {
            const vint &otherSum = aggs_other.getIntRef(0);
            if (otherSum != Vertica::vint_null) {
                sum |= otherSum;
            }
        } while (aggs_other.next());
    }

    virtual void terminate(ServerInterface &srvInterface,
                           BlockWriter &res_writer,
                           IntermediateAggs &aggs)
    {
        res_writer.setInt(aggs.getIntRef(0));
    }

    InlineAggregate()
};

class OrSumFactory : public AggregateFunctionFactory
{
    virtual void getPrototype(ServerInterface &srvInterface,
                              ColumnTypes &argTypes,
                              ColumnTypes &returnType)
    {
        argTypes.addInt();
        returnType.addInt();
    }

    virtual void getReturnType(ServerInterface &srvInterface,
                               const SizedColumnTypes &input_types,
                               SizedColumnTypes &output_types)
    {
        output_types.addInt();
    }

    virtual void getIntermediateTypes(ServerInterface &srvInterface,
                                      const SizedColumnTypes &input_types,
                                      SizedColumnTypes &intermediateTypeMetaData)
    {
        intermediateTypeMetaData.addInt();
    }

    virtual AggregateFunction *createAggregateFunction(ServerInterface &srvInterface)
    { return vt_createFuncObject(srvInterface.allocator); }
};

RegisterFactory(OrSumFactory);

Сборка библиотеки В разработке... Создание функции

CREATE OR REPLACE LIBRARY KESH_LIB AS '/opt/vertica/sdk/examples/build/KeshFunctions.so' LANGUAGE 'C++';
CREATE OR REPLACE AGGREGATE FUNCTION KESH_OR_SUM AS LANGUAGE 'C++' NAME 'KeshOrSumFactory' LIBRARY EPROC_LIB;

Использование функции

SELECT code, kesh_or_sum(regions) FROM product GROUP BY code

Удаление функции

DROP AGGREGATE FUNCTION KESH_OR_SUM(bigint);
DROP LIBRARY KESH_LIB;