在Klustron中进行机器学习

本文目标：

每家公司都期望通过使用机器学习以最小的代价改进其决策流程。对于大多数数据库来说，用户需要在数据库之外执行机器学习过程。然而在Klustron并非如此，由于Klustron包含对其他语言的多个扩展。用户无需离开Klustron即可训练和使用机器学习算法。

在Klustron中运行机器学习算法有一系列优势。首先，相比于在PostgreSQL中运行机器学习算法，在Klustron中运行机器学习算法不会受限于单台服务器的硬件资源限制，可以使用大量服务器的计算资源，来使用更大规模的数据集更快速递完成算法执行。这依赖于Klustron的多层级并行执行机制，该机制让Klustron可以用多台服务器的CPU和内存来执行同一个机器学习任务。

另外，相比于在数据库之外运行机器学习算法，在数据库系统中有完整的基础设施应对数据规模较大的问题，包括临时表，buffer pool，索引等。如果在数据库之外运行机器学习算法，要么需要手动实现这些基础设施（复杂度很高），要么就只能在内存中运行并且受限于数据量大于内存导致操作系统频繁做换页带来的性能严重下降等问题。

并且在数据库中运行机器学习算法，避免了跨网络节点传输大量数据的问题，也会有利于提升机器学习算法的性能。

Klustron不仅支持通过python等存储过程调用pytorch等流行的机器学习算法库来运行机器学习算法，还将在1.3版本中支持常用的机器学习算法扩展PostgresML和Apache MadLib以便实现更加强大和高性能的机器学习算法。

PostgreSQL生态的各种机器学习算法库和扩展原本在PostgreSQL中受限于单机硬件资源限制，在Klustron中就不再受限，可以处理更大量的数据，并且性能更高，耗时更短。并且这些机器学习算法都会受益于Klustron的多层级并行查询处理能力，实现更高的性能。

因此Klustron充分放大了这些in-database机器学习组件的能力，给用户带来强劲和高性能的机器学习处理能力。 下面让我们看一下如何使用PLPython直接在Klustron中执行Kmeans（最流行的无监督学习算法之一）。

01导入样本数据

我们将使用鸢尾花(iris)的样本数据，可以从下面的链接获取到iris.data的数据。

https://archive.ics.uci.edu/ml/machine-learning-databases/iris/iris.data

并将其放到目录/kunlun下。

然后通过pgsql客户端登录计算节点，创建用户和数据库。

psql -h 192.168.40.152 -p 47001 postgres
create user kunlun_test with password 'kunlun';
create database testdb owner kunlun_test;
grant all privileges on database testdb to kunlun_test;
alter user kunlun_test with superuser;
\q

用户kunlun_test登录testdb，创建测试表iris，并导入数据。

psql -h 192.168.40.152 -p 47001 -U kunlun_test testdb
create table iris (
sepal_length REAL,
sepal_width REAL,
petal_length REAL,
petal_width REAL,
species varchar(20)
);
\copy iris from '/kunlun/iris.data' delimiter ',';

02 安装依赖包

2.1 安装Python3

如果系统没有python3,则需要安装python3。

yum install python3
yum install python3-devel

验证python3是否成功安装。

python3 -V
pip3 -V

设置python和pip的软链接，配置之后可以直接使用python、pip启动。

ln -s /etc/alternatives/python3 /usr/bin/python
ln -s /etc/alternatives/pip3 /usr/bin/pip

2.2 安装PL/Python扩展

Klustron从版本1.3.1开始开箱支持plpython3的扩展。

2.3 安装Python的其他包

su - kunlun
pip install --user scikit-learn
pip install --user pandas

03 在Klustron中运行Kmeans算法

3.1 创建plpython3的扩展

psql -h 192.168.40.152 -p 47001 -U kunlun_test testdb
CREATE EXTENSION plpython3u;

3.2 创建kmeans函数

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 pickle import dumps
from pandas import pandas as pd

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).apply(pd.to_numeric, errors="ignore")
kmeans = KMeans(n_clusters=clus_num, random_state=0).fit(df._get_numeric_data())
return dumps(kmeans)
 
$$ LANGUAGE plpython3u;

该函数首先从传递的列中生成一个字符串，或者如果传递的是空数组，则将其替换为 *，最后我们使用 PL/Python 的执行函数构建查询。构建并执行查询后，我们需要将其转换为DataFrame并将数值变量转换为数值类型（默认情况下它们可能会被解释为其他类型）。然后，我们调用 kmeans，其中传递的输入组数量作为参数传递，作为您想要获取的簇的数量。最后，我们将其转储到 Pickle 中并返回存储在 Pickle 中的对象。Pickling 对于稍后恢复模型是必要的，否则 Python 将无法直接从来自 Klustron 的字节数组恢复 kmeans 对象。

3.3 存储模型

创建模型之后，还需要将其存储到表中，方便之后调用模型进行预测。

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

调用之前创建的kmeans函数，将生成的Kmeans模型存放到表中。

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

3.4 展示模型信息

我们可以通过select * from models;来展示模型的信息，但是结果没有什么可读性。

我们知道所有 kmeans 对象都有一个属性“cluster_centers_”，这是存储质心的位置。质心是每个组的平均向量，即每个组中每个变量的平均值。本质上，它们存储为 numpy 数组，但由于 plpython 无法处理 numpy 数组，我们需要将它们转换为列表的列表。这就是为什么返回的对象是列出每一行的输出，产生一个列表列表，其中每个子列表代表一个组的质心。通过创建下面的函数获取质心。

CREATE OR replace FUNCTION get_kmeans_centroids(model_table text, model_column text, model_id int)
RETURNS SETOF real[] AS
$$
from pandas import DataFrame
from pickle 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 plpython3u;

最后使用下面的SQL获取3个cluster所对应的质心。

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

3.5 使用模型进行预测

预测的方法是，传入一组变量，然后判断其对应的组。可以创建下面的预测函数。

CREATE OR replace FUNCTION predict_kmeans(model_table text, model_column text, model_id int,
input_values real[]) RETURNS int[] AS
$$
from pickle 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 plpython3u;

传入一组鸢尾花的花瓣和萼片的长宽值，判断其所属于的品种。

select predict_kmeans('models','model',1,array[[0.5,0.5,0.5,0.5]]);

传入花萼的长宽以及花瓣的长宽，通过Kmeans模型进行预测判断其属于杂色鸢尾花。

END