Skip to content

Latest commit

 

History

History
326 lines (222 loc) · 13.2 KB

20231027_01.md

File metadata and controls

326 lines (222 loc) · 13.2 KB
Raw

沉浸式学习PostgreSQL|PolarDB 22: 用KMeans 数据聚集算法进行无监督学习和数据分类分析

作者

digoal

日期

2023-10-27

标签

PostgreSQL , PolarDB , 数据库 , 教学

背景

欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.

  • 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.

本文的实验可以使用永久免费的阿里云云起实验室来完成.

如果你本地有docker环境也可以把镜像拉到本地来做实验:

x86_64机器使用以下docker image:

ARM机器使用以下docker image:

业务场景1 介绍: 用KMeans 数据聚集算法进行无监督学习和数据分类分析

每家公司都有机会通过使用机器学习以最小的努力改进其决策流程。然而，缺点是对于大多数 DBMS，您需要在数据库之外执行机器学习过程。PostgreSQL|PolarDB 中并非如此。

由于 PostgreSQL|PolarDB 包含对其他语言的多个扩展。您无需离开 PostgreSQL|PolarDB 即可训练和使用机器学习算法。

让我们看一下如何使用 PLPython 直接在 PostgreSQL|PolarDB 中执行 Kmeans（最流行的无监督学习算法之一）。

实现和对照

传统数据库无法实现库内kmeans聚集.

传统方法 设计和实验

无.

PolarDB|PG新方法1 设计和实验

1、安装依赖包

# 进入容器
docker exec -ti pg bash  

# 进入postgres用户
su - postgres  

# 换国内源
pip3 config set global.index-url https://mirrors.aliyun.com/pypi/simple/

# 安装依赖库
pip3 install pandas  
pip3 install scikit-learn

2、导入测试数据, 采用公开可用的 iris 数据集。

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

CREATE TABLE iris(  
  sepal_length REAL,  
  sepal_width REAL,  
  petal_length REAL,  
  petal_width REAL,  
  species varchar(20)  
);  
  
copy iris from stdin with (format csv);  
--  拷贝以上网页内容 
\.

现在我们已经有了要使用的数据，让我们跳转到 kmean 的核心函数。

3、安装plpython3u

psql  
  
create language plpython3u;

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

参数说明:

  • input_table: 数据集表名, 用于聚集训练
  • columns: 列名
  • clus_num: 产生多少个聚集点
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  
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).convert_dtypes(convert_floating =True)  
kmeans = KMeans(n_clusters=clus_num, random_state=0).fit(df._get_numeric_data())  
return dumps(kmeans)  
   
$$ LANGUAGE plpython3u;

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

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

最后一行指定扩展语言：在本例中，我们使用 python3，使用名为 plpython3u 的扩展语言

5、存储模型

创建一个模型而不用它做任何事情是没有多大意义的。因此，我们需要存储它。

为此，我们首先创建一个模型表：

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

在这种情况下，我们的表只有一个主键和一个字节数组字段，这是序列化的实际模型。请注意，它与我们定义的 kmeans 返回的数据类型相同。

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

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

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

6、显示模型信息

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

从型号中选择*；

因此，我们需要返回 Python 来显示有关模型的有用信息。这是我们将要使用的函数：

参数说明:

  • model_table: 模型表名称
  • model_column: 模型列二进制值
  • model_id: 模型ID
CREATE OR replace FUNCTION get_kmeans_centroids(model_table text, model_column text, model_id int) RETURNS 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 = list(map(list, model.cluster_centers_))  
return ret  
   
$$ LANGUAGE plpython3u;

让我们从头开始：我们再次传递包含模型的表和保存二进制文件的列。输出由 cpickle 的加载函数读取（在这里您可以看到 plpython3u 查询的结果如何加载到 Python 中）。

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

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

我们来看看它返回了什么：

select get_kmeans_centroids('models','model',1);  
  
  
{{4.39355,1.43387,5.90161,2.74839}, {1.464,0.244,5.006,3.418}, {5.74211,2.07105,6.85,3.07368}}

大括号括起来的每个元素代表一个组(因为前面训练的时候指定了3个中心点, 所以这里返回的也是3个中心点)，值是其均值向量。

7、做出预测

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

参数说明:

  • model_table: 模型表名称
  • model_column: 模型列二进制值
  • model_id: 模型ID
  • input_values: 输入向量
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;

与上一个函数相比，我们添加了一个输入参数 (input_values)，传递表示我们想要基于聚类获取组的案例的输入值（每个变量一个值）。

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

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

     species     | predict_kmeans   
-----------------+----------------  
 Iris-setosa     | {0}  
 Iris-setosa     | {0}  
 Iris-setosa     | {0}  
 Iris-setosa     | {0}  
 Iris-setosa     | {0}  
 Iris-setosa     | {0}  
 Iris-setosa     | {0}  
 Iris-setosa     | {0}  
 Iris-setosa     | {0}  
 Iris-setosa     | {0}  
 ...  
 Iris-versicolor | {0}  
 Iris-versicolor | {0}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {0}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {0}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {0}  
 Iris-virginica  | {2}  
 Iris-virginica  | {0}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}  
 Iris-virginica  | {2}

正如您所看到的，相关群体与其所属物种密切相关。0,1,2代表了分类ID, 表示这个点和对应的质心最近.

8、结论

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

对照

无.

知识点

python

kmeans

plpython

思考

结合大模型, 将文本、图像等可以转换为向量, PolarDB|PostgreSQL可以作为向量数据库存储向量. 使用本文提到的方法, 我们可以对向量进行无监督学习生成模型, 然后对新增数据再进行归类分析.

参考

期望 PostgreSQL|开源PolarDB 增加什么功能?

PolarDB 云原生分布式开源数据库

PolarDB 学习图谱: 训练营、培训认证、在线互动实验、解决方案、内核开发公开课、生态合作、写心得拿奖品

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

digoal's wechat

购买PolarDB云服务折扣活动进行中, 55元起

About 德哥