Geekly articles weekly

Vertica的机器学习

注解


在本文中，我想分享我在Vertica上的数据仓库中的机器学习经验。

坦白说，我不是专家分析师，他将能够详细描述所有研究方法和数据预测算法。 但是，作为Vertica的专家并拥有ML的基本经验，我将尝试谈论使用内置服务器功能和R语言在Vertica中进行预测分析的方法。

机器学习库Vertica


从版本7开始，Vertica已使用机器学习库进行了扩展，您可以使用该库：

  • 准备用于机器学习的数据示例
  • 在准备好的数据上训练机器学习模型；
  • 对保存的机器学习模型上的存储数据进行预测分析。

该库随所有版本的Vertica安装一起提供，包括免费的Community。 它的使用以从SQL调用函数的形式进行构造，在文档中对此进行了详细说明，并提供了示例数据的用法示例。

在Vertica中使用ML的示例


作为有关ML工作原理的简单示例，我采用了mtcars演示数据，该数据是Vertica的ML数据示例的一部分。 该数据包括两个表：

  • 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字段之间的关系，揭示了依赖关系公式，并将依赖关系的模拟结果写入模型“ logistic_reg_mtcars”中的Vertica数据库中。 使用此保存的模型，您现在可以分析汽车上的数据并预测自动变速箱的可用性。

可以查看有关模型的信息：

 =>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的实际值与预测预测中获得的值进行比较：

 =>SELECT * FROM mtcars_predict_results;



在这种情况下，对100％的预测与所提出模型中的实际盒子类型一致。 在准备用于训练的新数据时，您将需要删除并重新保存模型。

Vertica中的ML功能


Vertica ML库支持以下类型的预测分析：

  • 预测：
    • 线性回归
    • 随机森林回归
    • 支持向量机（SVM）用于回归
  • 分类：
    • 逻辑回归
    • 朴素的贝叶斯
    • 分类随机森林
    • 支持向量机（SVM）用于分类
  • 聚类：
    • k均值

为了准备训练数据，需要提供以下功能：

  • 资料平衡
  • 排放清洁
  • 编码分类（文本）列值
  • 替换丢失的数据
  • 数据标准化
  • 主成分分析
  • 资料取样
  • 奇异值分解

考虑到Vertica中的ML功能，我们可以说内置库使我们能够解决相当多的问题，但没有积压来研究数据的模式和依存关系。 有准备用于机器学习的数据的功能，但是没有以图表的形式可视化数据的分布，只有具有分析数据专家知识的分析专家才能“准备”此类数据并在其上训练学习模型。

带Vertica的R Studio


对于更彻底和交互式的预测数据分析，R语言非常适合，它具有用于处理R Studio数据的可视环境。 将R与Vertica一起使用的明显优势将是：

  • 环境的交互性，可以保存状态以在下一次运行后进行进一步分析；
  • 以表格和图表的形式直观地查看数据；
  • R语言处理数据集的能力；
  • 与Vertica ML中提供的算法相似的各种预测分析算法。

使用R处理大数据的缺点是对RAM的要求，处理大数据阵列的速度以及导入和导出Vertica数据的需求。 这些缺点被嵌入书面R函数以直接在Vertica中的群集上执行的能力所弥补，这将在下面进行描述。

R的简要介绍


我们将使用R在Vertica数据上重现对自动包装盒的预测。为了不吓走不熟悉这种语言的程序员，我将进行一门年轻的R战斗机的短期课程。

因此，R语言与具有对象，类和函数的过程语言相同。
一个对象可以是数据集（向量，列表，数据集...），值（文本，数字，日期，时间...）或函数。 对于值，支持数字，字符串，布尔值和日期时间类型。 对于数据集，数组编号从1开始，而不是0。

传统上，使用赋值运算符“ <-”代替R中的“ =”。 尽管不禁止在另一侧使用“->”，甚至是通常的“ =”。 调用函数以指定命名参数时，使用运算符“ =”本身。

代替“。” “ $”用于访问数据集的字段。 点不是关键字，而是用于对象名称中以提高其可读性。 因此，“ my.data $ field”将从数据集“ my.data”中解密为字段“ field”的记录数组。

您可以使用单引号或双引号将文本框起来。

最重要的是： R致力于处理数据集。 即使代码说“ a <-1”，也要确保R内部本身认为“ a”是1个元素的数组。 语言设计使您可以像处理普通变量一样使用数据集：加和减，连接和断开连接，按度量过滤。 创建列出其元素的数组的最简单方法是调用函数“ c（用逗号分隔的数组元素）”。 名称“ c”显然是Collection的缩写，但我不确定。

将数据从DBMS加载到R中


要通过ODBC for R使用RDBMS，必须安装RODBC软件包。 可以将其安装在R Studio的“程序包”选项卡上，或使用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' )

分析结果作为预测概率的百分比返回到预测字段。 通过与Vertica的类比简化为0或1，考虑到预测为正数且概率大于50％：

 mtcars.result$predict <- ifelse(mtcars.result$predict > 0.5, 1, 0)

我们计算预测预测字段与am中的实际值不匹配的记录总数：

 nrow(mtcars[mtcars.result$am != mtcars.result$predict, ])

R返回零。 因此，与Vertica的ML一样，该预测也适用于所有汽车模型。

请注意：来自mtcars的记录由过滤器（方括号中的第一个参数）返回所有列（方括号中的逗号后省略第二个参数）返回。

在R中本地保存和加载数据


退出R后，工作室会保存所有对象的状态，以便在重新启动后继续工作。 如果由于某种原因需要保存然后恢复单个对象的状态，R中提供了此特殊功能：

 #      save(mtcars.model, file = 'mtcars.model') #      load('mtcars.model')

将数据从R保存到Vertica


如果使用R Studio为训练ML Vertica模型准备数据，或者直接在其中进行分析（然后在Vertica数据库中使用），则可以将R数据集写入Vertica表。

由于R的ODBC库是为OLTP RDBMS设计的，因此它无法正确生成Vertica的表创建查询。 因此，要成功记录数据，您将需要使用SQL在Vertica中手动创建必要的表，其字段和类型的集合与可记录的数据集R一致。

此外，录制过程本身看起来很简单（不要忘记打开然后关闭连接）：

 sqlSave(con, mtcars.result, tablename = 'public.mtcars_result', append = TRUE, rownames = FALSE, colnames = FALSE)

将Vertica与R结合使用


R Studio中的数据交互工作非常适合研究和准备数据的模式。 但是它完全不适合在自动模式下分析数据流和大型阵列。 Vertica的混合R预测分析方案的一种选择是准备用于R的学习数据并确定构建模型的依存关系。 然后，使用Vertica内置的ML函数，考虑到已识别的变量依存关系，对R上准备的数据的预测模型进行训练。

如果直接在Vertica中使用R语言的所有功能，则有一个更灵活的选择。 为此，Vertica以插件库的形式开发了R发行版，使您可以在SQL查询中使用直接用R语言编写的转换函数。文档详细描述了Vertica的R支持的安装以及操作所需的其他R软件包（如果有）。

在Vertica中保存Model R


要使用R Studio先前在Vertica下运行的R函数中准备的分析模型，您需要将其保存在Vertica服务器上。 使用文件在集群的每个服务器上本地保存既不方便也不可靠，可以将新服务器添加到集群，并且在更改模型时，您将需要记住再次重写所有文件。

最方便的方法是将R模型序列化为文本并将Vertica函数另存为UDF，这会将此文本作为常量返回（不要忘记打开然后关闭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中使用传递的模型中的指定名称生成函数。

Vertica的R函数


为了将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将其自己的字段名称传输到该功能的数据集，因此将显式字段名称设置为该数据集。 基于获得的数据，使用机器模型的名称和零预测来构造结果集。 接下来，仅使用从获得的数据集中进行分析所需的字段来构建预测。 结果集的预测字段设置为布尔值（用于更改而不是数字值），结果从函数返回。

现在剩下要描述此功能在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文档中看到类型兼容性表。

您可以在上传到R studio的数据上测试Vertica书面功能的运行情况：

 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”中，并将此文件从“ / home / dbadmin”中的Vertica群集上传到其中一台服务器。

要点：在R Studio中，您需要设置选项以Posix（LF）模式将行的转换保存在文件中。 这可以在全局选项的“代码”部分的“保存”选项卡中完成。 如果您在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;

Vertica中的Call R函数


我们调用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操作都在内存中执行，并且如果缺少OS内存来满足Vertica和R一起工作的需求，则存在交换风险。

如果在PARTITION BY中为OVER指定的数据分区上调用了该函数，则Vertica会并行化集群服务器上每个分区的执行。 因此，如果除了机器型号之外，数据集中还存在制造商，则可以在PARTITION BY中指定它，并并行化每个制造商的分析。

其他Vertica机器学习机会


除R外，Vertica可以使用C，Java和Python开发其自己的转换函数。 每种语言都有其自己的细微差别，并且具有编写和连接到Vertica的功能。 连同其自己的ML，所有这些都为Vertica提供了进行预测数据分析的良好储备。

谢谢和链接


我要衷心感谢来自彼尔姆的朋友和同事Vlad Malofeev，他向我介绍了R，并帮助我在我们的一个联合项目中弄清楚了这一点。

最初，在一个使用过去一年的数据对未来的困难条件进行预测的项目中，开发人员尝试使用SQL和Java。 考虑到这些来源的质量，这造成了很大的困难，并大大减缓了该项目的开发。 弗拉德（Vlad）与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 in action”。 以R语言对数据进行分析和可视化 。 ” 它以一种简单易用的人类语言编写，适合以前从未接触过机器学习的初学者。

在这里，您可以看到有关将R库连接到Vertica的信息。

对于已经开始在Python中学习和使用ML的人来说，值得关注IDE Rodeo，它是R Studio的类似产品，因为没有交互式质量分析是不可能的。 我认为，本文在R下以类似方式描述的所有内容都可以用Python开发，包括将模型保存在UDF函数中以及为Vertica开发分析函数。 如果您进行检查，请不要忘记在评论中退订结果，我将不胜感激。

感谢您的宝贵时间，我希望我能够证明R和Vertica共生的简单性和令人难以置信的功能。

Source: https://habr.com/ru/post/zh-CN436306/

More articles:


All Articles