Аннотация

В данной статье я хочу поделиться собственным опытом работы с машинным обучением в хранилище данных на 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», то будьте уверены, R внутри себя считает, что «a» это массив из 1 элемента. Конструкция языка позволяет работать с наборами данных, как с обычными переменными: складывать и вычитать, соединять и разъединять, фильтровать по измерениям. Самый простой способ создать массив с перечислением его элементов, это вызвать функцию «c(элементы массива через запятую)». Название «c» видимо взято как краткое сокращение Collection, но не буду утверждать точно.

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

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

install.packages('RODBC')
library('RODBC')

Теперь мы можем работать с Vertica. Делаем ODBC алиас к серверу и получаем данные тестового и полного набора данных по автомобилю:

# Создаем подключение к Vertica
con <- odbcConnect(dsn='VerticaDSN')

# получаем данные таблицы mtcars_train
mtcars.train <- sqlQuery(con, "SELECT * FROM public.mtcars_train")

# получаем данные таблицы mtcars</b>
mtcars.data <- sqlQuery(con, "SELECT * FROM public.mtcars")

# закрываем соединение
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 <- glm(formula = am ~ cyl + wt + gear, family = binomial(), data = mtcars.train)

Пояснение: в языке R формула предиктивного анализа указывается как:

<поле результата анализа>~<влияющие на анализ поля>

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

Инициализируем результирующий набор данных, взяв из mtcars все записи по нужным полям:

mtcars.result <- data.frame(car_model = mtcars.data$car_model, 
  am = mtcars.data$am, predict = 0)

Теперь по построенной модели можно выполнить анализ на самих данных:

mtcars.result$predict <- predict.glm(mtcars.model, 
  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 <- rawToChar(
  serialize(mtcars.model, connection = NULL, ascii = TRUE))

# Собираем текст функции для выполнения в Vertica
# (в тексте модели одинарные кавычки дублируются)
mtcars.func <- 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 <- function(data, parameters) {
  if ( is.null(parameters[['model']]) ) {
    stop("NULL value for model! Model cannot be NULL.")
  } else {
    model <- unserialize(charToRaw(parameters[['model']]))
  }
  
  names(data) <- c('car_model', 'cyl', 'wt', 'gear')
  
  result <- data.frame(car_model = data$car_model, predict = 0)
  
  result$predict <- predict.glm(model, 
    newdata = subset(data, select = c('cyl', 'wt', 'gear')), 
    type = 'response' )
	
  result$predict <- ifelse(result$predict > 0.5, TRUE, FALSE)
  
  return(result)
}

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

Теперь остается описать регистрацию этой функции в Vertica:

MtCarsAnalizeFactory <- 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 <- function() {
  parameters <- list(datatype = c("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 <name of your database> -f load_ml_data.sql

Если Вы хотите углубиться в ML и научиться работать с R, рекомендую к изучению книгу на русском «R в действии. Анализ и визуализация данных на языке R». Написано простым доступным человеческим языком и подойдет для начинающих, кто ранее не сталкивался с машинным обучением.

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

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

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