在PostgreSQL里进行机器学习：KMEANS 聚类尝试

10行解释机器学习

每个阅读报纸，杂志或任何其他普遍感兴趣的媒体的人至少都对机器学习的基本概念有所了解。机器学习不仅仅是一种时尚，它更成为我们日常生活的一部分，并且将来会更多：从互联网上的个性化广告到机器人牙医或自动驾驶汽车，机器学习似乎成为一切事物的某种超能力。

但是，什么是机器学习呢？它主要是一组统计算法，基于现有数据，能够从中获得洞察力。这些算法基本上分为两个系列，监督学习和非监督学习。在监督学习中，目标是执行某种预测，例如，电子邮件是否是垃圾邮件（分类），下周将在超市中销售多少啤酒（回归）等等，相反地，无监督学习侧重于回答我的案例如何分组的问题？这些算法的作用（每个算法都有其特殊性）是尽可能使相似的事物尽可能接近，不同的事物则尽可能地远离。

机器学习的普及化彻底改变了我们开展业务的方式。 无论您是在谈论10或10,000名员工，如果您没有利用您的数据做出决策，那么您肯定会落后于您的竞争对手。

不离开数据库就可以进行机器学习

关系数据库绝对是数据持久性最重要的工具。 虽然还有其他替代方案可能适用于某些目的，但可能没有至少具有最小IT基础架构的公司没有数据库。

因此，如果每个公司都有一个数据库，它包含值得使用的数据。 这意味着每家公司都有机会通过使用机器学习以最小的努力改进其决策过程。 但是，他的缺点是，对于大多数DBMS，您需要在数据库之外执行机器学习过程。 但对PostgreSQL来说并不是这样。

由于PostgreSQL包含对其他语言的多个扩展，您可以在不离开PostgreSQL的情况下训练和使用您的机器学习算法（MadLib也是不错的选择，不过对平台比较挑剔）。

让我们看一下如何使用PLPython直接在PostgreSQL中进行Kmeans聚类，这是最流行的无监督学习算法之一。

PostgreSQL中逐步实现KMeans

1. 加载数据样例

本示例，我们将使用知名的鸢尾花数据，首先我们下载数据（https://archive.ics.uci.edu/ml/machine-learning-databases/iris/iris.data）到本地。

接下创建iris 表：

CREATE TABLE iris(
sepal_length REAL,
sepal_width REAL,
petal_length REAL,
petal_width REAL,
species varchar(20)
);

表创建完毕，我们就可以导入刚下载的数据了，在执行下面命令前，记得删除iris.data最后的空行，

COPY iris FROM '/path/to/iris.data' DELIMITER ',';

数据准备完毕，接下来可以进入到KMeans的核心函数环节了。

2. 安装依赖

1. Python：目前有两个主要的版本，2.7和3.7。PostgreSQL默认支持Python 2，你可以根据需要选择Python 3。

2. PL/Python：对应Python 2和Python 3，也有两个版本的plpython，分别是plpython2u和plpython3u，你可以选择一个，或者同时安装。

3. 安装额外的Python组件：使用你喜欢的Python包管理器（pip，conda等等）来安装scikit-learn和pandas。顺便说下，如果你想使用Python进行机器学习，这两个包都是必须的。

pip install sklearn pandas

4. 安装好之后就可以创建插件了。

   CREATE EXTENSION plpythonu

3. PostgreSQL里的Kmeans

用PL/Python编写的函数可以像任何其他SQL函数一样调用。 由于Python拥有无尽的机器学习库，因此集成非常简单。 此外，除了完全支持Python之外，PL/Python还提供了一组便捷函数来运行任何参数化查询。 因此，执行机器学习算法可能只是几行代码的问题。 让我们来看看吧。

CREATE OR replace FUNCTION kmeans(input_table text, columns text[], clus_num int) RETURNS bytea AS
$$
from pandas import DataFrame
from sklearn.cluster import KMeans
from cPickle import dumps 
all_columns = ",".join(columns)
if all_columns == "":
    all_columns = "*"
rv = plpy.execute('SELECT %s FROM %s;' % (all_columns, plpy.quote_ident(input_table)))
 
frame = []
 
for i in rv:
    frame.append(i)
df = DataFrame(frame).convert_objects(convert_numeric =True)
kmeans = KMeans(n_clusters=clus_num, random_state=0).fit(df._get_numeric_data())
return dumps(kmeans)
 
$$ LANGUAGE plpythonu;

如您所见，脚本非常简单。首先，我们导入我们需要的函数，然后我们从传递的列生成一个字符串，或者用*替换它，如果传递一个空数组，最后我们使用PL/Python的执行函数构建查询。虽然它超出了本文的范围，但我强烈建议阅读有关如何使用PL/Python参数化查询的内容。

一旦构建并执行了查询，我们需要将其转换为将其转换为数据框并将数值变量转换为数字类型（默认情况下，它们可能会被解释为其他内容）。然后，我们调用kmeans，其中传递的输入组数量作为参数传递，作为您想要获取的聚类数量。最后，我们将其转储到cPickle（Python3下是_pickle)中并返回存储在Pickle中的对象。稍后恢复模型是必要的，否则Python将无法直接从来自PostgreSQL的bytearray中恢复kmeans对象。

最后一行指定扩展语言：在这种情况下，我们使用Python 2，因此，扩展名称为plpythonu。如果您想在Python 3中执行它，您应该使用名为plpython3u的扩展语言。

4. 保存模型

创建模型但不用它做任何事情是没有什么意义的。 所以，我们需要存储它。为此，我们先创建一个模型表：

CREATE TABLE models (
id SERIAL PRIMARY KEY,
model BYTEA NOT NULL
);

本例中，我们的表只有一个主键和一个字节数组字段，即序列化的实际模型。 请注意，它与我们定义的kmeans返回的数据类型相同。

一旦我们有了表格，我们就可以轻松地在模型中插入新记录：

INSERT INTO models(model) 
SELECT kmeans('iris', array[]::text[], 3);

本例中，我们将columns参数作为空数组传递，以使用表中的所有数值变量执行聚类。 请考虑这只是一个例子。 在生产案例中，您可能希望添加一些额外的字段，以便更容易识别不同的模型。

5. 显示模型信息

到目前为止，我们能够创建一个模型并存储它，但直接从数据库中获取它并不是很有用。 你可以通过运行来检查它

select * from models;

出于这个原因，我们需要回到Python以显示有关我们模型的有用信息。 这是我们将要使用的函数：

CREATE OR replace FUNCTION get_kmeans_centroids(
    model_table text, model_column text, model_id int) 
    RETURNS real[] AS
$$
from pandas import DataFrame
from cPickle import loads
 
rv = plpy.execute('SELECT %s FROM %s WHERE id = %s;' % 
                  (plpy.quote_ident(model_column), 
                   plpy.quote_ident(model_table), model_id))
model = loads(rv[0][model_column])
ret = map(list, model.cluster_centers_)
return ret
$$ LANGUAGE plpythonu;

让我们从头开始：我们再次传递包含模型的表和包含二进制的列。输出由cPickle（Python 3下是_pickle)的加载函数读取（[here]（https://www.postgresql.org/docs/10/static/plpython-database.html），您可以看到如何将plpython查询的结果加载到Python中）。

加载模型后，我们知道所有kmeans对象都有一个属性“cluster_centers_”，这是存储质心的位置。质心是每组的平均向量，即每组中每个变量的平均值。即，它们存储为numpy数组，但由于plpython无法处理numpy数组，我们需要将它们转换为列表的列表。这就是为什么返回的对象是列出每一行的输出，产生列表的列表的原因，其中每个子列表代表一个组的质心。

这只是如何输出模型的某个特征的示例。您可以创建类似的函数来返回其他特征，甚至是所有特征。

我们来看看它的返回：

select get_kmeans_centroids('models','model',1);

get_kmeans_centroids

[[1.464, 0.244, 5.006, 3.418], [4.39355, 1.43387, 5.90161, 2.74839], [5.74211, 2.07105, 6.85, 3.07368]]

括号括起来的每个元素代表一个组，值是其平均值的向量。

6. 预测

现在我们有了一个模型，让我们用它来做预测！ 在kmeans中，这意味着传递一组值（对应于每个变量）并获取它所属的组号。 该函数与前一个函数非常相似：

CREATE OR replace FUNCTION predict_kmeans(
    model_table text, model_column text
    , model_id int, input_values real[]) 
    RETURNS int[] AS
$$
from cPickle import loads
rv = plpy.execute('SELECT %s FROM %s WHERE id = %s;' % 
                  (plpy.quote_ident(model_column)
                  , plpy.quote_ident(model_table)
                  , model_id))
model = loads(rv[0][model_column])
ret = model.predict(input_values)
return ret
$$ LANGUAGE plpythonu;

与前一个函数相比，我们添加一个输入参数（input_values），传递一个表示案例（每个变量一个值）的输入值，我们希望根据聚类获取组值。

我们返回一个整数数组，而不是返回一个浮点数组，因为我们正在讨论一个组索引。

select predict_kmeans('models','model',1
,array[[0.5,0.5,0.5,0.5]]);
#返回
{1}

请注意，即使只传递一个元素，也需要传递一个数组的数组。 这与Python处理数组的方式有关。

我们还可以将列名传递给函数，例如：

select species
,predict_kmeans('models','model',1
                ,array[[petal_length,petal_width,sepal_length,sepal_width]]) 
from iris;

如您所见，相关组与它们的种类密切相关。

结论

我们在本文中已经看到，您可以在不离开postgres的情况下训练和使用机器学习。 但是，您需要拥有Python的知识来准备一切。 尽管如此，对于那些可能不知道如何用Python或任何其他语言进行操作的人来说，这可以是一个非常好的解决方案，可以在PostgreSQL中创建一个完整的机器学习工具包。

本文翻译自MACHINE LEARNING IN POSTGRESQL PART 1: KMEANS CLUSTERING,  https://www.cybertec-postgresql.com/en/machine-learning-in-postgresql-part-1-kmeans-clustering/

作者，Hernan Resnizky

P.S.

Pivot的Madlib（仅仅支持Linux和MacOS环境）插件也可以让PostgreSQL具有机器学习的功能，甚至有基于PostgreSQL来做推荐系统专用的数据库(RecDB)， 本文则是介绍一种通用、跨平台的解决方案，是一种不错的尝试。

欢迎关注我的公众号，alitrack

衡数提供下列服务，有意请留言

• PySpark培训

• Excel培训

• 数据挖掘咨询

• 数据挖掘外包

• 人才推荐