数据库DeepSQL特性实现DB4AI功能,即在数据库内实现AI算法,以更好的支撑大数据的快速分析和计算。这里提供了一整套基于SQL的机器学习、数据挖掘以及统计学的算法,用户可以直接使用SQL进行机器学习工作。Deep SQL能够抽象出端到端从数据到模型的研发过程,配合底层的引擎及自动优化,具备基础SQL知识的技术人员即可完成大部分的机器学习模型训练及预测任务。整个分析和处理都运行在数据库引擎中,用户可以直接分析和处理数据库内的数据,不需要在数据库和其它平台之间进行数据传递,避免在多个环境之间进行不必要地数据移动。
-
概述
-
环境部署
-
使用指导
-
最佳实践
-
常见问题处理
一、概述
DeepSQL是对openGauss DB4AI能力的增强,让对MADLib比较熟悉的数据分析师或开发者可以轻松迁移到openGauss上进行工作。DeepSQL将常用的机器学习算法封装为SQL语句,支持60多个常用算法。其中包括回归算法(例如线性回归,逻辑回归,随机森林等)、分类算法(比如KNN等)、聚类算法(比如K-means)等。除了基础的机器学习算法之外,还包括图相关的算法,比如最短路径,图形直径等等算法;此外还支持数据处理(比如PCA),稀疏向量,统计学常用算法(比如协方差,Pearson系数计算等),训练集测试集分割方法,交叉验证方法等。
表 1 支持的机器学习算法 - 回归类算法
表 2 支持的机器学习算法 - 其他监督学习
表 3 支持的机器学习算法 - 数据处理类算法
表 4 支持的机器学习算法 - 图类
表 5 支持的机器学习算法 - 时间序列
表 6 支持的机器学习算法 - 采样
分层随机抽样,又称类型随机抽样,它是先将总体各单位按一定标准分成各种类型(或层);然后根据各类型单位数与总体单位数的比例,确定从各类型中抽取样本单位的数量;最后,按照随机原则从各类型中抽取样本。 |
||
一些分类算法仅在每个类中的样本数大致相同时才最佳地执行。高度偏斜的数据集在许多领域中是常见的(例如,欺诈检测),因此重新采样以抵消这种不平衡可以产生更好的决策边界。 |
表 7 支持的机器学习算法 - 统计学
描述性统计,求Pearson系数,相关系数,另一个输出协方差。了解数据从统计学上反映的量的特征,以便我们更好地认识这些将要被挖掘的数据。 |
||
表 8 支持的机器学习算法 - 其他算法
二、环境部署
DeepSQL环境包括编译数据库和安装算法库两个部分。
前提条件
- 环境中安装python2.7.12以上版本Python。
- 数据库需要开启对PL/Python存储过程的支持。
- 安装算法库需要拥有管理员权限的用户。
操作步骤
-
检查部署Python环境。
安装前,请查看系统安装的python版本,当前DeepSQL需要python2.7.12以上版本的环境。
- 如果当前系统python2版本高于2.7.12,可以直接安装python-devel包。
- 如果版本过低,或者无法安装python-devel包,可以下载最新python2源码,手动配置编译python2,并配置环境变量。
算法库中,部分算法调用了python包,如numpy,pandas等。用户可以安装以下python库:
pip install numpy pip install pandas pip install scipy
须知:
-
如果自行编译python,需要在configure脚本执行时加入–enable-shared参数。
-
如果系统中的python2使用的是UCS4编码,自行编译python2时,还需要加入–enable-unicode=ucs4参数。
-
可以在系统中自带的python2下执行:“import sys;print sys.maxunicode”并查看结果,如果结果是65535,说明系统默认的是ucs2;如果结果是1114111,说明用的ucs4编码。
-
如果系统中内置的python2使用的ucs4,说明系统中的gdb,gstack等也会依赖ucs4。因此自行编译的python2在configure时,需要添加–enable-unicode=ucs4,否则后续使用gdb,gstack时,会遇到报错。
-
编译部署数据库。
数据库需要开启对PL/Python存储过程的支持。默认编译数据库,不包含此模块。因此需要编译数据库时,在configure阶段,加入--with-python参数;
其他编译保持步骤不变;
编译完成后,需要重新gs_initdb;
默认PL/Python存储过程模块不被加载,请执行“CREATE EXTENSION plpythonu”来加载模块。
-
算法库编译和安装。
算法库使用开源的MADlib机器学习框架。源码包和相应patch可以从第三方库的代码仓库里获取。安装命令如下:
tar -zxf apache-madlib-1.17.0-src.tar.gz cp madlib.patch apache-madlib-1.17.0-src cd apache-madlib-1.17.0-src/ patch -p1
编译命令如下:
./configure -DCMAKE_INSTALL_PREFIX={YOUR_MADLIB_INSTALL_FOLDER} -DPOSTGRESQL_EXECUTABLE=$GAUSSHOME/bin/ -DPOSTGRESQL_9_2_EXECUTABLE=$GAUSSHOME/bin/ -DPOSTGRESQL_9_2_CLIENT_INCLUDE_DIR=$GAUSSHOME/bin/ -DPOSTGRESQL_9_2_SERVER_INCLUDE_DIR=$GAUSSHOME/bin/ # 以上均为configure命令。 make make install
其中, {YOUR_MADLIB_INSTALL_FOLDER}需要改为用户的实际安装路径。
须知: 编译MADlib时,会联网下载依赖软件。无法联网时,需要手动下载依赖包“PyXB-1.2.6.tar.gz”,“eigen-branches-3.2.tar.gz”和“boost_1_61_0.tar.gz”放在本地。使用的configure命令如下:
复制代码
./configure -DCMAKE_INSTALL_PREFIX={YOUR_MADLIB_INSTALL_FOLDER} # your install folder -DPYXB_TAR_SOURCE={YOUR_DEPENDENCY_FOLDER}/PyXB-1.2.6.tar.gz # change to your local folder -DEIGEN_TAR_SOURCE={YOUR_DEPENDENCY_FOLDER}/eigen-branches-3.2.tar.gz # change to your local folder -DBOOST_TAR_SOURCE={YOUR_DEPENDENCY_FOLDER}/boost_1_61_0.tar.gz # change to your local folder -DPOSTGRESQL_EXECUTABLE=$GAUSSHOME/bin/ -DPOSTGRESQL_9_2_EXECUTABLE=$GAUSSHOME/bin/ -DPOSTGRESQL_9_2_CLIENT_INCLUDE_DIR=$GAUSSHOME/bin/ -DPOSTGRESQL_9_2_SERVER_INCLUDE_DIR=$GAUSSHOME/bin/
-
将算法库安装到数据库中。
a.进入{YOUR_MADLIB_INSTALL_FOLDER}路径。
b.进入bin文件夹。
c.执行如下命令。
./madpack -s -p opengauss -c @127.0.0.1:/ install
命令中参数说明如下:
- -s:schema的名称。
- -p:数据库平台,使用opengauss即可。
- -c:连接数据库的参数。包括用户名、‘@’、IP地址、端口号和目标数据库名称。
install为安装的命令,除此之外,还有reinstall(重新安装),uninstall(卸载)等命令可用。
说明:
- 目标数据库必须存在。
- IP请使用127.0.0.1,不要使用localhost。
- 涉及到大量PL/Python存储过程的安装、卸载等操作,需要数据库管理员权限用户来进行,普通用户没有权限创建和修改PL/Python存储过程,只能调用。
- 数据库兼容性,推荐兼容性为B。不同的数据库兼容性下,对空值,NULL等处理有较大差异。建议使用B兼容性。例如,CREATE DATABASE dbcompatibility='B'。
三、使用指导
PL/Python存储过程
当前PL/Python存储过程优先支持python2;默认版本也是python2。
PL/Python中的函数通过标准的CREATE FUNCTION声明:
CREATE FUNCTION funcname (argument-list)
RETURNS return-type
AS $$
# PL/Python function body
$$ LANGUAGE plpythonu;
函数体是一个简单的Python脚本,当函数被调用的时候,它的参数作为列表args的元素传递;命名参数也会被当做普通的变量传递到Python脚本中。命名参数的使用通常更易读。 结果将使用return或yield(结果集语句的情况) 照常从Python代码中返回。如果没有提供返回值,Python返回缺省的None。 PL/Python将Python中的None认为SQL空值。
例如,返回两个整数中较大者的函数定义如下。
CREATE FUNCTION pymax(a integer, b integer) RETURNS integer AS $$
if a > b:
return a
return b
$$ LANGUAGE plpythonu;
注意:
PL/Python函数中,后缀为plpythonu。‘u’说明是untrusted类型的存储过程。
Trusted:这个语言不能访问越权的数据。例如,数据库服务器的文件、数据库内部(包括直接访问共享内存)。
Untrusted:这个语言没有任何限制,允许访问任何数据(包括文件,网络,共享LIB库等,危害性较大),但是功能更加强大。
PL/Python属于untrusted类型的存储过程语言,当前仅允许管理员权限的用户创建和修改,普通用户仅支持使用。
定义PL/Python存储过程时,注意不要定义执行诸如import os;os.system(“rm -rf /”) 等危险语句。管理员权限的用户需要小心创建此类PL/Python存储过程。
数据库Null, None和空串处理
如果向函数传递了一个SQL null值,参数值在Python中将会显示为None。在数据库中,不同的兼容性下,空串的行为会被当做NULL处理。
同一个函数,在不同的兼容性下表现不同。
CREATE FUNCTION quote(t text, how text) RETURNS text AS $$
if how == "literal":
return plpy.quote_literal(t)
elif how == "nullable":
return plpy.quote_nullable(t)
elif how == "ident":
return plpy.quote_ident(t)
else:
raise plpy.Error("unrecognized quote type %s" % how)
$$ LANGUAGE plpythonu;
示例1:
SELECT quote(t, 'literal') FROM (VALUES ('abc'),('a''bc'),('''abc'''),(''),(''''),('xyzv')) AS v(t);
数据库不同兼容性下的结果为:
-
兼容性为A时,返回结果如下:
ERROR: TypeError: argument 1 must be string, not None CONTEXT: Traceback (most recent call last): PL/Python function "quote", line 3, in
return plpy.quote_literal(t) referenced column: quote -
兼容性为B时,返回结果如下:
quote ----------- 'abc' 'a''bc' '''abc''' '' '''' 'xyzv' (6 rows)
示例2:
SELECT quote(t, 'nullable') FROM (VALUES ('abc'),('a''bc'),('''abc'''),(''),(''''),(NULL)) AS v(t);
数据库不同兼容性下的结果为:
-
兼容性为A时,返回结果如下:
quote ----------- 'abc' 'a''bc' '''abc''' NULL '''' NULL (6 rows)
-
兼容性为B时,返回结果如下:
quote ----------- 'abc' 'a''bc' '''abc''' '' '''' NULL (6 rows)
可以看到,在兼容性“A”中,空串被当为NULL了。
触发器
当前PL/Python存储过程中,不支持触发器功能。
匿名代码块
PL/Python也支持DO声明的匿名代码块:
DO $$
# PL/Python code
$$ LANGUAGE plpythonu;
一个匿名代码块不接受参数,并且丢弃它可能返回的值。
共享数据
每个函数都在Python解释器里获得自己的执行环境。
全局字典SD在函数调用之间用于存储数据。这些变量是私有静态数据。每一个函数都有自己的SD数据空间,函数A的全局数据和函数参数是函数B不可用的。
全局字典GD是公共数据,在一个gsql会话中,所有python函数都可访问和改变,使用时需要小心。
当gsql断开或退出,共享数据就被释放。
注意:
运行DeepSQL或者PL/Python存储过程时,需要关闭线程池相关参数。否则PL/Python存储过程中的Sharing Data(“GD”、“SD”)等功能会失效。
在数据库中,当线程池功能关闭,每一个连入的gsql,数据库内会起一个新的线程去处理。在gsql中,如果调用到PL/Python存储过程,会在本线程中完成python解析器模块的初始化,其中包括初始化“GD”,“SD”等共享空间。
在线程池功能开启的状态下,一个gsql执行时,由当前空闲线程执行,每次执行可能分配到不同的线程上。导致共享数据紊乱。
数据库访问
PL/Python语言模块自动import一个叫plpy的Python模块。
plpy模块提供几个函数执行数据库命令:比如plpy.execute,plpy.prepare等。
plpy模块也提供了函数plpy.debug(msg)、 plpy.log(msg)、plpy.info(msg)、 plpy.notice(msg)、plpy.warning(msg)、 plpy.error(msg)和plpy.fatal(msg)。 plpy.error和 plpy.fatal实际上抛出了一个Python异常,会导致当前事务或者子事务退出。
另一个实用函数集是plpy.quote_literal(string)、 plpy.quote_nullable(string)和 plpy.quote_ident(string)。
关于审计
PL/Python存储过程支持审计功能。具体设置可以参考审计。
关于并发执行
当前PL/Python存储过程对并发执行不友好,建议串行执行。
说明: 由于openGauss是多线程架构,C-python中,由于GIL锁(Global Interpreter Lock)的限制,多线程在Python中只能交替执行,无法做到真正的并发。
库内算法
具体库内算法介绍和使用,可参考MADlib官方网站(MADlib文档)。
须知:
- 当前仅支持机器学习算法,不支持深度学习(deep learning)模块。
- 当前数据库不支持xml,所以pmml模块和相关功能不支持。
- 数据库不支持jsonb模块,json格式的模型导出功能也不支持。
其他算法支持
除了MADlib提供的算法外,openGauss又额外提供了以下三个算法。
表 1 额外增加的模块列表
梯度提升树 | |
使用时,需要安装依赖的python库:
-
如果使用prophet算法:
pip install pystan pip install holidays==0.9.8 pip install fbprophet==0.3.post2
-
如果使用xgboost算法:
pip install xgboost pip install scikit-learn
-
gbdt不需要额外安装其他库。
详细操作请参考最佳实践。
四、最佳实践
本章节介绍部分算法的使用,主要包含分类、回归、聚类、gbdt算法、xgboost算法和prohpet算法。
首先需要创建一个数据库,并安装算法。
create database test1 dbcompatibility='B';
./madpack -s madlib -p opengauss -c opg@127.0.0.1:7651/test1 install
分类算法
以svm分类房价为例子:
-
DROP TABLE IF EXISTS houses; CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT, size INT, lot INT); INSERT INTO houses VALUES (1 , 590 , 2 , 1 , 50000 , 770 , 22100), (2 , 1050 , 3 , 2 , 85000 , 1410 , 12000), (3 , 20 , 3 , 1 , 22500 , 1060 , 3500), (4 , 870 , 2 , 2 , 90000 , 1300 , 17500), (5 , 1320 , 3 , 2 , 133000 , 1500 , 30000), (6 , 1350 , 2 , 1 , 90500 , 820 , 25700), (7 , 2790 , 3 , 2.5 , 260000 , 2130 , 25000), (8 , 680 , 2 , 1 , 142500 , 1170 , 22000), (9 , 1840 , 3 , 2 , 160000 , 1500 , 19000), (10 , 3680 , 4 , 2 , 240000 , 2790 , 20000), (11 , 1660 , 3 , 1 , 87000 , 1030 , 17500), (12 , 1620 , 3 , 2 , 118600 , 1250 , 20000), (13 , 3100 , 3 , 2 , 140000 , 1760 , 38000), (14 , 2070 , 2 , 3 , 148000 , 1550 , 14000), (15 , 650 , 3 , 1.5 , 65000 , 1450 , 12000);
-
模型训练。
训练前配置相应schema和兼容性参数:
SET search_path="$user",public,madlib; SET behavior_compat_options = 'bind_procedure_searchpath';
使用默认的参数进行训练,分类的条件为‘price
DROP TABLE IF EXISTS houses_svm, houses_svm_summary; SELECT madlib.svm_classification('public.houses','public.houses_svm','price
-
查看模型。
x on SELECT * FROM houses_svm; x off
结果如下:
-[ RECORD 1 ]------+----------------------------------------------------------------- coef | {.113989576847,-.00226133300602,-.0676303607996,.00179440841072} loss | .614496714256667 norm_of_gradient | 108.171180769224 num_iterations | 100 num_rows_processed | 15 num_rows_skipped | 0 dep_var_mapping | {f,t}
-
进行预测。
DROP TABLE IF EXISTS houses_pred; SELECT madlib.svm_predict('public.houses_svm','public.houses','id','public.houses_pred');
-
查看预测结果
SELECT *, price
结果如下:
id | tax | bedroom | bath | price | size | lot | prediction | decision_function | actual ----+------+---------+------+--------+------+-------+------------+-------------------+-------- 1 | 590 | 2 | 1 | 50000 | 770 | 22100 | t | .09386721875 | t 2 | 1050 | 3 | 2 | 85000 | 1410 | 12000 | t | .134445058042 | t 3 | 20 | 3 | 1 | 22500 | 1060 | 3500 | t | 1.9032054712902 | t 4 | 870 | 2 | 2 | 90000 | 1300 | 17500 | t | .3441000739464 | t 5 | 1320 | 3 | 2 | 133000 | 1500 | 30000 | f | -.3146180966186 | f 6 | 1350 | 2 | 1 | 90500 | 820 | 25700 | f | -1.5350254452892 | t 7 | 2790 | 3 | 2.5 | 260000 | 2130 | 25000 | f | -2.5421154971142 | f 8 | 680 | 2 | 1 | 142500 | 1170 | 22000 | t | .6081106124962 | f 9 | 1840 | 3 | 2 | 160000 | 1500 | 19000 | f | -1.490511259749 | f 10 | 3680 | 4 | 2 | 240000 | 2790 | 20000 | f | -3.336577140997 | f 11 | 1660 | 3 | 1 | 87000 | 1030 | 17500 | f | -1.8592129109042 | t 12 | 1620 | 3 | 2 | 118600 | 1250 | 20000 | f | -1.4416201011046 | f 13 | 3100 | 3 | 2 | 140000 | 1760 | 38000 | f | -3.873244660547 | f 14 | 2070 | 2 | 3 | 148000 | 1550 | 14000 | f | -1.9885277913972 | f 15 | 650 | 3 | 1.5 | 65000 | 1450 | 12000 | t | 1.1445697772786 | t (15 rows)
-
查看误分率
SELECT COUNT(*) FROM houses_pred JOIN houses USING (id) WHERE houses_pred.prediction != (houses.price
结果如下:
count ------- 3 (1 row)
-
-
使用svm其他核进行训练。
DROP TABLE IF EXISTS houses_svm_gaussian, houses_svm_gaussian_summary, houses_svm_gaussian_random; SELECT madlib.svm_classification( 'public.houses','public.houses_svm_gaussian','price
进行预测,并查看训练结果。
DROP TABLE IF EXISTS houses_pred_gaussian; SELECT madlib.svm_predict('public.houses_svm_gaussian','public.houses','id', 'public.houses_pred_gaussian'); SELECT COUNT(*) FROM houses_pred_gaussian JOIN houses USING (id) WHERE houses_pred_gaussian.prediction != (houses.price
结果如下。
count -------+ 0 (1 row)
-
其他参数
除了指定不同的核方法外,还可以指定迭代次数,初始参数,比如init_stepsize, max_iter, class_weight等。
须知:
复制代码
SET search_path="$user",public,madlib; SET behavior_compat_options = 'bind_procedure_searchpath';
执行算法前,需要设置search_path中schema,另外需要bind_procedure_searchpath,否则出现表找不到的情况。因为机器学习所有的方法安装在一个schema中,用户的表安装在用户的schema中,在这个例子中,算法安装在madlib中,用户表放置在public中。如果不设置,可能会出现执行算法时,找不到表的情况。执行算法时,建议把输入表的schema也加进去。
回归算法
我们以线性回归预测波士顿房价为例:
-
数据集准备。
同svm的数据集,请参见1。
-
训练模型。
SET search_path="$user",public,madlib; SET behavior_compat_options = 'bind_procedure_searchpath'; DROP TABLE IF EXISTS houses_linregr, houses_linregr_summary; SELECT madlib.linregr_train( 'public.houses', 'public.houses_linregr', 'price', 'ARRAY[1, tax, bath, size]');
-
查看模型内容。
x ON SELECT * FROM houses_linregr; x OFF
返回结果如下。
-[ RECORD 1 ]------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- coef | {-12849.4168959872,28.9613922651775,10181.6290712649,50.516894915353} r2 | .768577580597462 std_err | {33453.0344331377,15.8992104963991,19437.7710925915,32.9280231740856} t_stats | {-.384103179688204,1.82156166004197,.523806408809163,1.53416118083608} p_values | {.708223134615411,.0958005827189556,.610804093526516,.153235085548177} condition_no | 9002.50457069858 num_rows_processed | 15 num_missing_rows_skipped | 0 variance_covariance | { {1119105512.7847,217782.067878005,-283344228.394538,-616679.693190829},{217782.067878005,252.784894408806,-46373.1796964038,-369.864520095145},{-283344228.394538,-46373.1796964038,377826945.047986,-209088.217319699},{-616679.693190829,-369.864520095145,-209088.217319699,1084.25471015312}}
-
预测,并对比结果。
SELECT houses.*, madlib.linregr_predict( m.coef, ARRAY[1,tax,bath,size]) as predict, price - madlib.linregr_predict( m.coef, ARRAY[1,tax,bath,size]) as residual FROM public.houses, public.houses_linregr AS m ORDER BY id;
返回结果如下。
id | tax | bedroom | bath | price | size | lot | predict | residual ----+------+---------+------+--------+------+-------+------------------+------------------- 1 | 590 | 2 | 1 | 50000 | 770 | 22100 | 53317.4426965543 | -3317.44269655428 2 | 1050 | 3 | 2 | 85000 | 1410 | 12000 | 109152.124955627 | -24152.1249556268 3 | 20 | 3 | 1 | 22500 | 1060 | 3500 | 51459.3486308555 | -28959.3486308555 4 | 870 | 2 | 2 | 90000 | 1300 | 17500 | 98382.215907206 | -8382.21590720599 5 | 1320 | 3 | 2 | 133000 | 1500 | 30000 | 121518.221409606 | 11481.7785903935 6 | 1350 | 2 | 1 | 90500 | 820 | 25700 | 77853.9455638568 | 12646.0544361432 7 | 2790 | 3 | 2.5 | 260000 | 2130 | 25000 | 201007.926371722 | 58992.0736282778 8 | 680 | 2 | 1 | 142500 | 1170 | 22000 | 76130.7259665615 | 66369.2740334385 9 | 1840 | 3 | 2 | 160000 | 1500 | 19000 | 136578.145387499 | 23421.8546125013 10 | 3680 | 4 | 2 | 240000 | 2790 | 20000 | 255033.901596231 | -15033.9015962306 11 | 1660 | 3 | 1 | 87000 | 1030 | 17500 | 97440.5250982859 | -10440.5250982859 12 | 1620 | 3 | 2 | 118600 | 1250 | 20000 | 117577.415360321 | 1022.58463967856 13 | 3100 | 3 | 2 | 140000 | 1760 | 38000 | 186203.892319614 | -46203.8923196141 14 | 2070 | 2 | 3 | 148000 | 1550 | 14000 | 155946.739425522 | -7946.73942552213 15 | 650 | 3 | 1.5 | 65000 | 1450 | 12000 | 94497.4293105374 | -29497.4293105374 (15 rows)
聚类算法
以kmeans为例:
-
准备数据。
DROP TABLE IF EXISTS km_sample; CREATE TABLE km_sample(pid int, points double precision[]); INSERT INTO km_sample VALUES (1, '{14.23, 1.71, 2.43, 15.6, 127, 2.8, 3.0600, 0.2800, 2.29, 5.64, 1.04, 3.92, 1065}'), (2, '{13.2, 1.78, 2.14, 11.2, 1, 2.65, 2.76, 0.26, 1.28, 4.38, 1.05, 3.49, 1050}'), (3, '{13.16, 2.36, 2.67, 18.6, 101, 2.8, 3.24, 0.3, 2.81, 5.6799, 1.03, 3.17, 1185}'), (4, '{14.37, 1.95, 2.5, 16.8, 113, 3.85, 3.49, 0.24, 2.18, 7.8, 0.86, 3.45, 1480}'), (5, '{13.24, 2.59, 2.87, 21, 118, 2.8, 2.69, 0.39, 1.82, 4.32, 1.04, 2.93, 735}'), (6, '{14.2, 1.76, 2.45, 15.2, 112, 3.27, 3.39, 0.34, 1.97, 6.75, 1.05, 2.85, 1450}'), (7, '{14.39, 1.87, 2.45, 14.6, 96, 2.5, 2.52, 0.3, 1.98, 5.25, 1.02, 3.58, 1290}'), (8, '{14.06, 2.15, 2.61, 17.6, 121, 2.6, 2.51, 0.31, 1.25, 5.05, 1.06, 3.58, 1295}'), (9, '{14.83, 1.64, 2.17, 14, 97, 2.8, 2.98, 0.29, 1.98, 5.2, 1.08, 2.85, 1045}'), (10, '{13.86, 1.35, 2.27, 16, 98, 2.98, 3.15, 0.22, 1.8500, 7.2199, 1.01, 3.55, 1045}');
-
运行kmeans算法。
使用kmeans++进行计算,距离函数使用欧几里得距离。
SET search_path="$user",public,madlib; SET behavior_compat_options = 'bind_procedure_searchpath'; DROP TABLE IF EXISTS km_result; CREATE TABLE km_result AS SELECT * FROM madlib.kmeanspp( 'public.km_sample', -- Table of source data 'points', -- Column containing point co-ordinates 2, -- Number of centroids to calculate 'madlib.squared_dist_norm2', -- Distance function 'madlib.avg', -- Aggregate function 20, -- Number of iterations 0.001 -- Fraction of centroids reassigned to keep iterating );
kmeans执行完后,不会自动创建表保存内容,所以需要用户自行创建table。
x on select * from km_result; x off
返回结果如下。
-[ RECORD 1 ]----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- centroids | { {14.0333333333333,1.84111111111111,2.41,15.5111111111111,96.2222222222222,2.91666666666667,3.01111111111111,.282222222222222,1.95444444444444,5.88553333333333,1.02222222222222,3.38222222222222,1211.66666666667},{13.24,2.59,2.87,21,118,2.8,2.69,.39,1.82,4.32,1.04,2.93,735}} cluster_variance | {257041.999707571,0} objective_fn | 257041.999707571 frac_reassigned | 0 num_iterations | 2
-
应用聚类结果。
执行以下函数,计算每个节点的临近节点和相应距离。
DROP TABLE IF EXISTS km_points_silh; SELECT * FROM madlib.simple_silhouette_points('public.km_sample', -- Input points table 'public.km_points_silh', -- Output table 'pid', -- Point ID column in input table 'points', -- Points column in input table 'public.km_result', -- Centroids table 'centroids', -- Column in centroids table containing centroids