пятница, 18 января 2019 г.

Machine Learning для Vertica


Аннотация

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

Machine Learning библиотека Vertica

Начиная с 7 версии Vertica дополнили библиотекой Machine Learning, с помощью которой можно:
  • подготавливать примеры данных для машинного обучения;
  • тренировать модели машинного обучения на подготовленных данных;
  • проводить предиктивный анализ данных хранилища на сохраненных моделях машинного обучения.
Библиотека идет сразу в комплекте с инсталляцией Vertica для всех версий, в том числе бесплатной Community. Работа с ней оформлена в виде вызова функций из-под SQL, которые подробно описаны в документации с примерами использования на подготовленных демонстрационных данных.

Пример работы с ML в Vertica

В качестве простого примера работы ML я взял демонстрационные данные по автомобилям mtcars, входящие в состав примера данных ML для Vertica. В эти данные входит две таблицы:
  • mtcars_train – подготовленные для тренировки модели машинного обучения данные
  • mtcars – данные для анализа
Посмотрим на данные для тренировки:
=>SELECT * FROM mtcars_train;

В наборе данных по моделям автомобилей расписаны их характеристики. Попробуем натренировать машинное обучение так, чтобы по характеристикам автомобилей можно было прогнозировать, какой тип коробки передач задействован в автомобиле – ручная коробка или коробка автомат. Для этого нам понадобится построить модель логистической регрессии на подготовленных данных, найдя зависимость типа коробки поля "am" и полями веса автомобиля "wt", количества цилиндров "cyl" и количества скоростей в коробке "gear":
=>SELECT LOGISTIC_REG('logistic_reg_mtcars', 'mtcars_train', 'am', 'cyl, wt, gear');
Finished in 19 iterations
Вызванная функция проанализировала зависимость между am и полями cyl, wt, gear, выявила формулу зависимости и результат моделирования зависимости записала в базу данных Vertica в модель "logistic_reg_mtcars". С помощью этой сохраненной модели теперь можно анализировать данные по автомобилям и прогнозировать наличие коробки автомат.
Информацию по модели можно посмотреть:
=>SELECT GET_MODEL_SUMMARY(USING PARAMETERS model_name='logistic_reg_mtcars');

Используем теперь модель на данных по автомобилям, сохранив результат в новую таблицу:
=>CREATE TABLE mtcars_predict_results AS (
SELECT car_model, am,
PREDICT_LOGISTIC_REG(cyl, wt, gear
USING PARAMETERS model_name='logistic_reg_mtcars') AS prediction
FROM mtcars
);
И сравнив реальные значения am с полученными в прогнозе prediction:
=>SELECT * FROM mtcars_predict_results;




































В данном случае прогноз на 100% совпал с реальным типом коробки у представленных моделей. В случае подготовки новых данных для обучения потребуется удалить и заново сохранить модель.

Функциональность ML в Vertica

Библиотека ML в Vertica поддерживает следующие виды предиктивного анализа:
  • Прогнозирование:
    • Linear Regression
    • Random Forest for Regression
    • SVM (Support Vector Machine) for Regression
  • Классификация:
    • Logistic Regression
    • Naive Bayes
    • Random Forest for Classification
    • SVM (Support Vector Machine) for Classification
  • Кластеризация:
    • k-means
Для подготовки данных к обучению представлен следующий функционал:
  • Балансировка данных
  • Очистка выбросов
  • Кодировка категориальных (текстовых) значений столбцов
  • Замена пропущенных данных
  • Нормализация данных
  • Principal Component Analysis
  • Сэмплирование данных
  • Singular Value Decomposition
Рассматривая функционал ML в Vertica можно сказать, что встроенная библиотека позволяет решать достаточно широкий круг задач, но не имеет задела на исследование закономерностей и зависимостей в данных. Присутствуют функции подготовки данных для машинного обучения, однако без визуализации распределения данных в виде графиков "готовить" такие данные и тренировать по ним модели обучения смогут разве что гуру анализа, обладающие экспертными знаниями по анализируемым данным.

R Studio с Vertica

Для более тщательного и интерактивного предиктивного анализа данных идеально подходит язык R, который имеет визуальную среду работы с данными R Studio. Ощутимыми плюсами использования R с Vertica будут являться:
  • интерактивность среды с возможностью сохранения состояния для дальнейшего анализа после следующего запуска;
  • визуальный просмотр данных в виде таблиц и графиков;
  • мощность языка R для работы с наборами данных;
  • многообразие алгоритмов предиктивного анализа, аналогичных представленных в Vertica ML.
В качестве минусов работы R с большими данными можно назвать требования к оперативной памяти, скорость работы с большими массивами данных и необходимость импорта и экспорта данных Vertica. Эти недостатки покрываются возможностью встраивания написанных функций R для непосредственного выполнения на кластере в Vertica, о чем будет рассказано ниже.

Небольшое введение в R

Воспроизведем прогноз по коробкам автомат на данных Vertica с помощью R. Для того, чтобы не отпугнуть программистов, незнакомых с этим языком, я проведу краткий курс молодого бойца R.
Итак, язык R — это такой же процедурный язык, имеющий объекты, классы и функции. Объект может быть набором данных (вектор, список, датасет…), значением (текст, число, дата, время…) или функцией. Для значений поддерживаются числовые, строковые, булевые и дата время типы. Для наборов данных нумерация массивов начинается с 1, а не 0.
Классически вместо "=" в R используется оператор присваивания "<->" и даже привычный "=". Сам же оператор "=" используется при вызове функций для указания именованных параметров.
Вместо "." для доступа к полям наборов данных используется "$". Точка не является ключевым словом и используется в именах объектов для повышения их читабельности. Таким образом, "my.data$field" будет расшифровываться как массив записей поля "field" из набора данных "my.data".
Для обрамления текстов можно использовать как одинарные, так и двойные кавычки.
Самое главное: R заточен на работу с множествами данных. Даже если в коде написано "a<-1 1="" a="" c="" collection="" p="" r="">

Загрузка данных из СУБД в R

Для работы с РСУБД через ODBC для R требуется установить пакет RODBC. Его можно установить в R Studio на вкладке packages или с помощью команды R:
install.packages('RODBC')
library('RODBC')

Теперь мы можем работать с Vertica. Делаем ODBC алиас к серверу и получаем данные тестового и полного набора данных по автомобилю:
# Создаем подключение к Vertica
con <- br="" dsn="VerticaDSN" odbcconnect="">
# получаем данные таблицы mtcars_train
mtcars.train <- br="" con="" from="" public.mtcars_train="" sqlquery="">
# получаем данные таблицы mtcars
mtcars.data <- br="" con="" from="" public.mtcars="" sqlquery="">
# закрываем соединение
odbcClose(con)
При загрузке данных из источников R для полей текстовых типов и даты-времени автоматически устанавливается их принадлежность к факторам. Поле "am" имеет числовой тип и воспринимается R как числовой показатель, а не фактор, что не позволит провести логистическую регрессию. Поэтому преобразуем это поле в числовой фактор:
mtcars.data$am = factor(mtcars.data$am)
mtcars.train$am = factor(mtcars.train$am)


В R Studio удобно интерактивно смотреть данные, строить графики предиктивного анализа и писать код на R с подсказками:

Построение модели в R

Построим модель логистической регрессии над подготовленным набором данных по тем же измерениям, что и в Vertica:
mtcars.model <- cyl="" data="mtcars.train)</p" family="binomial()," formula="am" gear="" glm="" wt="">
Пояснение: в языке R формула предиктивного анализа указывается как:
<поле результата анализа>~<влияющие на анализ поля>

Анализ данных по модели в R

Инициализируем результирующий набор данных, взяв из mtcars все записи по нужным полям:
mtcars.result <- br="" car_model="mtcars.data$car_model," data.frame=""> am = mtcars.data$am, predict = 0)
Теперь по построенной модели можно выполнить анализ на самих данных:
mtcars.result$predict <- br="" mtcars.model="" predict.glm=""> newdata = subset(mtcars.data, select = c('cyl', 'wt', 'gear')),
type = 'response' )
Результат анализа возвращается в поле predict как процент вероятности прогноза. Упростим по аналогии с Vertica до значений 0 или 1, считая прогноз положительным при вероятности более 50%:
mtcars.result$predict <- ifelse="" mtcars.result="" predict=""> 0.5, 1, 0)

Посчитаем общее количество записей, у которых прогнозируемое поле predict не совпало с реальным значением в am:
nrow(mtcars[mtcars.result$am != mtcars.result$predict, ])
R вернул ноль. Таким образом, прогноз сошелся на все модели автомобилей, как и в ML у Vertica.

Обратите внимание: записи из mtcars были возвращены по фильтру (первый параметр в квадратных скобках) со всеми колонками (второй пропущенный после запятой параметр в квадратных скобках).

Локальное сохранение и загрузка данных в R

При выходе из R, студия предлагает сохранить состояние всех объектов, чтобы продолжить работу после повторного запуска. Если по каким-то причинам потребуется сохранить и затем восстановить состояние отдельных объектов, для этого в R предусмотрены специальные функции:
# Сохранить объект модели в файл
save(mtcars.model, file = 'mtcars.model')

# Восстановить объект модели из файла load('mtcars.model')

Сохранение данных из R в Vertica

В случае, если R Studio использовалась для подготовки данных для тренировки моделей ML Vertica или же прямо в ней был произведен анализ, который требуется далее использовать в базе данных Vertica, наборы данных R можно записать в таблицу Vertica.

Так как библиотека ODBC для R рассчитана на OLTP РСУБД, она не умеет корректно генерировать запросы создания таблиц для Vertica. Поэтому для успешной записи данных потребуется вручную создать нужную таблицу в Vertica с помощью SQL, набор полей и типов которой совпадает с записываемым набором данных R.

Далее сам процесс записи выглядит просто (не забываем открыть и потом закрыть соединение con):
sqlSave(con, mtcars.result, tablename = 'public.mtcars_result',
append = TRUE, rownames = FALSE, colnames = FALSE)

Работа Vertica с R

Интерактивная работа с данными в R Studio хорошо подходит для режима исследования и подготовки данных. Но совершенно не годится для анализа потоков данных и больших массивов в автоматическом режиме. Один из вариантов гибридной схемы предиктивного анализа R с Vertica — это подготовка данных для обучения на R и выявление зависимостей для построения моделей. Далее с помощью встроенных в Vertica функций ML тренируются модели прогноза на подготовленных на R данных с учетом выявленных зависимостей переменных.
Есть и более гибкий вариант, когда вся мощь языка R используется прямо из-под Vertica. Для этого под Vertica разработан R дистрибутив в виде подключаемой библиотеки, который позволяет использовать в SQL запросах функции трансформации, написанные прямо на языке R. В документации подробно описана установка поддержки R для Vertica и требуемых для работы дополнительных пакетов R, если таковые требуются.

Сохранение модели R в Vertica

Чтобы использовать ранее подготовленную в R Studio модель анализа в функциях R, работающих из-под Vertica, требуется их сохранить на серверах Vertica. Сохранять на каждом сервере кластера локально файлом не удобно и не надежно, в кластер могут добавляться новые сервера, да и при изменении модели потребуется не забыть переписать заново все файлы.
Самым удобным способом видится сериализовать модель R в текст и сохранить как UDF функцию Vertica, которая будет возвращать этот текст в виде константы (не забываем открыть и потом закрыть соединение con):
# Сериализуем модель в текст
mtcars.model.text <- br="" rawtochar=""> serialize(mtcars.model, connection = NULL, ascii = TRUE))

# Собираем текст функции для выполнения в Vertica
# (в тексте модели одинарные кавычки дублируются)
mtcars.func <- br="" paste0=""> "CREATE OR REPLACE FUNCTION public.MtCarsAnalizeModel()
RETURN varchar(65000)
AS
BEGIN
RETURN '", gsub("'", "''", mtcars.model.text), "';
END;
GRANT EXECUTE ON FUNCTION public.MtCarsAnalizeModel() TO public;"
)

# Создаем функцию на Vertica
sqlQuery(con, mtcars.func)
Предложенный способ позволяет обойти ограничение Vertica на передаваемые параметры в функции трансформации, где требуется передача только константы или выражения из констант. В Vertica UDF SQL компилируются не как функции, а как вычисляемые выражения, то есть при передаче параметра, вместо вызова функции будет передан ее текст (в данном случае константа), который был сохранен в коде выше.

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

Функции R для работы в Vertica

Для того, чтобы подключить R функции к Vertica, надо написать функции анализа данных и регистрации в Vertica.

Сама функция работы с данными из-под Vertica должна иметь два параметра: получаемый набор данных (как data.frame) и параметры работы (как list):
MtCarsAnalize <- br="" data="" function="" parameters=""> if ( is.null(parameters[['model']]) ) {
stop("NULL value for model! Model cannot be NULL.")
} else {
model <- br="" chartoraw="" model="" parameters="" unserialize=""> }

names(data) <- br="" c="" car_model="" cyl="" gear="" wt="">
result <- car_model="data$car_model," data.frame="" predict="0)<br">
result$predict <- br="" model="" predict.glm=""> newdata = subset(data, select = c('cyl', 'wt', 'gear')),
type = 'response' )

result$predict <- ifelse="" predict="" result=""> 0.5, TRUE, FALSE)

return(result) }
В теле функции проверяется, что передан параметр модели, текст которого переводится в бинарный вид и десериализуется в объект модели анализа. Так как Vertica передает в набор данных для функции собственные имена полей запроса, то набору данных устанавливаются явные имена полей. На базе полученных данных строится результирующий набор с именем модели машины и нулевым predict. Далее строится прогноз с использованием только нужных для анализа полей из полученного набора данных. Полю predict результирующего набора выставляются булевые значения (для разнообразия вместо числовых) и результат возвращается из функции.

Теперь остается описать регистрацию этой функции в Vertica:
MtCarsAnalizeFactory <- br="" function=""> list(name = MtCarsAnalize,
udxtype = c("transform"),
intype = c("varchar", "int", "float", "int"),
outtype = c("varchar", "boolean"),
outnames = c("car_model", "predict"),
parametertypecallback=MtCarsAnalizeParameters)
}
MtCarsAnalizeParameters <- br="" function=""> parameters <- br="" datatype="c(" list="" varchar=""> length = 65000,
scale = c("NA"),
name = c("model"))
return(parameters)
}
Функция MtCarsAnalizeFactory описывает имя используемой для работы функции, поля для входящего и исходящего набора данных, а вторая функция описывает передаваемый параметр "model". В качестве типов полей указываются типы данных Vertica. При передаче и возврате данных Vertica автоматически преобразует значения в нужные типы данных для языка R.

Таблицу совместимости типов можно посмотреть в документации Vertica.
Можно протестировать работу написанной функции для Vertica на загруженных в R студию данных:
test.data = subset(mtcars.data, select = c('car_model', 'cyl', 'wt', 'gear'))
test.params = list(model = mtcars.model.text)
test.result = MtCarsAnalize(test.data, test.params)

Подключение библиотеки функций к Vertica

Сохраняем все вышеописанные функции в один файл "mtcars_func.r" и загружаем этот файл на один из серверов из кластера Vertica в "/home/dbadmin".

Важный момент: в R Studio требуется установить параметр сохранения перевода строк в файлах в режим Posix (LF). Это можно сделать в глобальных опциях, разделе Code, вкладке Saving. Если Вы работаете на Windows, по умолчанию файл будет сохранен с переводом каретки и не сможет быть загружен в Vertica.

Подключаемся к серверу из кластера Vertica, на который сохранили файл и загружаем библиотеку:
CREATE LIBRARY MtCarsLibs AS '/home/dbadmin/mtcars_func.r' LANGUAGE 'R';
Теперь из этой библиотеки можно зарегистрировать R функцию:
CREATE TRANSFORM FUNCTION public.MtCarsAnalize
AS LANGUAGE 'R' NAME 'MtCarsAnalizeFactory'
LIBRARY MtCarsLibs;

GRANT EXECUTE ON TRANSFORM FUNCTION
public.MtCarsAnalize(varchar, int, float, int)
TO public;

Вызов R функций в Vertica

Вызываем функцию R, передавая ей текст модели, который ранее был сохранен как UDF функция:
SELECT MtCarsAnalize(car_model, cyl, wt, gear USING PARAMETERS model = public.MtCarsAnalizeModel()) OVER() FROM public.mtcars;


































Можно проверить, что так же, как и в предыдущих случаях, дается совпадающий на 100% с реальным положением дел прогноз:
SELECT c.*, p.predict, p.predict = c.am::int AS valid
FROM public.mtcars c
INNER JOIN (
SELECT MtCarsAnalize(car_model, cyl, wt, gear
USING PARAMETERS model = public.MtCarsAnalizeModel()) OVER()
FROM public.mtcars
) p ON c.car_model = p.car_model
Обратите внимание: функции трансформации в Vertica возвращают собственный набор данных из определяемых внутри функций полей и записей, однако они могут быть использованы в запросах, если обернуты в подзапрос.

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

Если функция вызывается на партиции данных, указанных в PARTITION BY для OVER, то Vertica распараллеливает выполнения каждой партиции по серверам кластера. Таким образом, если бы в наборе данных помимо модели машины еще присутствовал производитель, можно было бы указать его в PARTITION BY и распараллелить выполнение анализа на каждого производителя.

Прочие возможности Vertica в области машинного обучения

Помимо R для Vertica можно разрабатывать собственные функции трансформации на языках C, Java и Python. Для каждого из языков есть свои нюансы и особенности написания и подключения к Vertica. Вкупе с собственным ML все это дает в Vertica хороший задел для предиктивного анализа данных.

Благодарности и ссылки

Хочу от всей души поблагодарить моего друга и коллегу Влада Малофеева из Перми, который познакомил меня с R и помог с ним разобраться на одном из наших совместных проектов.

Изначально в проекте, где строился прогноз по сложным условиям на будущее с использованием данных прошедшего года, разработчики пытались использовать SQL и Java. Это вызывало большие сложности с учетом качества данных источников и здорово тормозило разработку проекта. В проект пришел Влад с R, мы с ним подключили R под Vertica, он погонял данные на студии и все сразу красиво закрутилось и завертелось. Буквально за недели разгреблось все, что тянулось месяцами, избавив проект от сложного кода.

Приведенный пример данных с автомобилями можно скачать с GIT репозитория:
git clone https://github.com/vertica/Machine-Learning-Examples
и загрузить в Vertica:
/opt/vertica/bin/vsql -d -f load_ml_data.sql
Если Вы хотите углубиться в ML и научиться работать с R, рекомендую к изучению книгу на русском "R в действии. Анализ и визуализация данных на языке R". Написано простым доступным человеческим языком и подойдет для начинающих, кто ранее не сталкивался с машинным обучением.

Здесь можно посмотреть сведения о подключении R библиотеки к Vertica.

Для тех, кто уже начал изучать и использовать ML на Python, стоит обратить внимание на IDE Rodeo, это аналог R Studio, ведь без интерактива качественный анализ невозможен. Думаю, все описанное в этой статье под R аналогичном образом может быть разработано на Python, включая сохранение модели в UDF функции и разработку функций анализа под Vertica. Если будете проверять, не забудьте отписаться о результатах в комментариях, буду признателен за информацию.

Благодарю за уделенное время и надеюсь, что смог продемонстрировать простоту и невероятные возможности симбиоза R и Vertica.

Источник: Статья на habr.com

Комментариев нет:

Отправить комментарий