openGauss数据库源码解析系列文章——AI技术（六）：DeepSQL

Gauss松鼠会 2022-02-18

1017

Gauss松鼠会

学习探索分享数据库前沿知识和技术共建数据库技术交流圈

关注

上篇图文，我们分享了AI技术——AI查询时间预测的精彩内容，本篇将详细介绍AI技术——DeepSQL的相关内容。

8.7.1 使用场景

数据库DB4AI功能的实现，即在数据库内实现AI算法，以更好的支撑大数据的快速分析和计算。目前openGauss的DB4AI能力通过DeepSQL特性来呈现。这里提供了一整套基于SQL的机器学习、数据挖掘以及统计学的算法，用户可以直接使用SQL语句进行机器学习工作。DeepSQL能够抽象出端到端的、从数据到模型的数据研发过程，配合底层的计算引擎及数据库自动优化，让具备基础SQL知识的用户即可完成大部分的机器学习模型训练及预测任务。整个分析和处理都运行在数据库引擎中，用户可以直接分析和处理数据库内的数据，不需要在数据库和其他平台之间进行数据传递，避免在多个环境之间进行不必要的数据移动，并且整合了碎片化的数据开发技术栈。

8.7.2 现有技术

如今，学术界与工业界在DB4AI这个方向已经了取得了许多成果。很多传统的商业关系数据库都已经支持了DB4AI能力，通过内置AI组件适配数据库内的数据处理和环境，可以对数据库存储的数据进行处理，最大程度地减少数据移动的花费。同时，很多云数据库、云计算数据分析平台也都具备DB4AI能力。同时还可能具备Python、R语言等接口，便于数据分析人员快速入门。

在DB4AI领域，同样具备很出色的开源软件，例如Apache顶级开源项目MADlib。它兼容PostgreSQL数据库，很多基于PostgreSQL数据库源码基线进行开发的数据库也可以很容易进行适配。MADlib可以为结构化和非结构化数据提供统计和机器学习的方法，并利用聚集函数实现在分布式数据库上的并行化计算。MADlib支持多种机器学习、数据挖掘算法，例如回归、分类、聚类、统计、图算法等，累计支持的算法达到70多个，在目前发布的1.17版本中MADlib支持深度学习。MADlib使用类SQL语法作为对外接口，通过创建UDF（user-defined function，用户自定义函数）的方式将AI任务集成到数据库中。

当前openGauss的DB4AI模块，兼容开源的MADlib，在原始MADlib开源软件的基础上进行了互相适配和增强，性能相比在PostgreSQL数据库上运行的MADlib性能更优。同时，openGauss基于MADlib框架，实现了其他工业级的、常用的算法，例如XGBoost、Prophet、GBDT以及推荐系统等。与此同时，openGauss还具备原生的AI执行计划与执行算子，该部分特性会在后续版本中开源。因此，本章内容主要介绍openGauss是如何兼容MADlib的。

8.7.3 关键源码解析

1. MADLib的项目结构

MADlib的文件结构及说明如表1所示，MADlib的代码可通过其官方网站获取：https://madlib.apache.org/。

表1 MADlib的主要文件结构

文件结构	说明
cmake	-	Cmake相关文件
	/array_ops	数组array操作模块
/kmeans	Kmeans相关模块
/sketch	词频统计处理相关模块
/stemmer	词干处理相关模块
/svec	稀疏矩阵相关模块
/svec_util	稀疏矩阵依赖模块
/utils	其他公共模块
src/bin	-	工具模块，用于安装、卸载、部署等
src/bin/madpack	-	数据库交互模块
src/dbal	-	词干处理相关模块
src/libstemmer	-	工具依赖文件
src/madpack	-	里面包含公共的模块
src/modules	-	关联规则算法
/assoc_rules	包括凸算法的实现
/convex	包括条件随机场算法
/crf	弹性网络算法
/elastic_net	广义线性模型
/glm	隐狄利克雷分配
/lda	线性代数操作
/linalg	线性系统模块
/linear_systems	概率模块
/prob	决策树和随机森林
/recursive_partitioning	回归算法
/regress	采样模块
/sample	数理统计类模块
/stats	时间序列
/utilities	包含pg,gaussdb平台相关接口
src/ports	-	接口，链接db
src/ports/postgres	-	针对pg系，相关算法
/dbconnector	关联规则算法
/modules	贝叶斯算法
/modules/bayes	共轭梯度法
/modules/conjugate_gradient	包括多层感知机
/modules/convex	条件随机场
/modules/crf	弹性网络
/modules/elastic_net	Prophet时序预测
/modules/gbdt	Gdbt算法
/modules/glm	广义线性模型
/modules/graph	图模型
/modules/kmeans	Kmeans算法
/modules/knn	Knn算法
/modules/lda	隐狄利克雷分配
/modules/linalg	线性代数操作
/modules/linear_systems	线性系统模块
/modules/pca	PCA降维
/modules/prob	概率模块
/modules/recursive_partitioning	决策树和随机森林
/modules/sample	回归算法
/modules/stats	采样模块
/modules/summary	数理统计类模块
/modules/svm	描述性统计的汇总函数
/modules/tsa	Svm算法
/modules/validation	时间序列
/modules/xgboost_gs	交叉验证
src/utils	-	Xgboost算法

2. MADlib在openGauss上的执行流程

用户通过调用UDF即可进行模型的训练和预测，相关的结果会保存在表中，存储在数据库上。以训练过程为例，MADlib在openGauss上执行的整体流程如图1所示。

图1 MADlib在openGauss上训练模型的流程图

8.7.4 基于MADlib框架的扩展

前文展示了MADlib各个模块的功能和作用，从结构上看，用户可以针对自己的算法进行扩展。前文中提到的XGBoost、GBDT和Prophet三个算法是我们在原来基础上扩展的算法。本小节将以自研的GBDT模块为例，介绍基于MADlib框架的扩展。

GBDT目录结构

GBDT文件结构如表2所示。

表2 GBDT算法的主要文件结构

文件结构	说明
gbdt/gbdt.py_in	python代码
gbdt/gbdt.sql_in	存储过程代码
gbdt/test/gbdt.sql	测试代码

在sql_in文件中，定义上层SQL-like接口，使用PL/pgSQL或者PL/python实现。

在SQL层中定义UDF函数，下述代码实现了类似重载的功能。

CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.gbdt_train(
    training_table_name         TEXT,
    output_table_name           TEXT,
    id_col_name                 TEXT,
    dependent_variable          TEXT,
    list_of_features            TEXT,
    list_of_features_to_exclude TEXT,
    weights                     TEXT
)
RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.gbdt_train($1, $2, $3, $4, $5, $6, $7, 30::INTEGER);
$$ LANGUAGE sql VOLATILE;
 
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.gbdt_train(
    training_table_name         TEXT,
    output_table_name           TEXT,
    id_col_name                 TEXT,
    dependent_variable          TEXT,
    list_of_features            TEXT,
    list_of_features_to_exclude TEXT
)
RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.gbdt_train($1, $2, $3, $4, $5, $6, NULL::TEXT);
$$ LANGUAGE sql VOLATILE;
 
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.gbdt_train(
    training_table_name         TEXT,
    output_table_name           TEXT,
    id_col_name                 TEXT,
    dependent_variable          TEXT,
    list_of_features            TEXT
)
RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.gbdt_train($1, $2, $3, $4, $5, NULL::TEXT);
$$ LANGUAGE sql VOLATILE;

其中，输入表、输出表、特征等必备信息需要用户指定。其他参数提供缺省的参数，比如权重weights，如果用户没有指定自定义参数，程序会用默认的参数进行运算。

在SQL层定义PL/python接口，代码如下：

CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.gbdt_train(
    training_table_name         TEXT,
    output_table_name           TEXT,
    id_col_name                 TEXT,
    dependent_variable          TEXT,
    list_of_features            TEXT,
    list_of_features_to_exclude TEXT,
    weights                     TEXT,
    num_trees                   INTEGER,
    num_random_features         INTEGER,
    max_tree_depth              INTEGER,
    min_split                   INTEGER,
    min_bucket                  INTEGER,
    num_bins                    INTEGER,
    null_handling_params        TEXT,
    is_classification           BOOLEAN,
    predict_dt_prob             TEXT,
    learning_rate               DOUBLE PRECISION,
    verbose                     BOOLEAN,
    sample_ratio                DOUBLE PRECISION
)
RETURNS VOID AS $$
PythonFunction(gbdt, gbdt, gbdt_fit)
$$ LANGUAGE plpythonu VOLATILE;

PL/pgSQL或者SQL函数最终会调用到一个PL/python函数。

“PythonFunction(gbdt, gbdt, gbdt_fit)”是固定的用法，这也是一个封装的m4宏，会在编译安装的时候，会进行宏替换。

PythonFunction中，第一个参数是文件夹名，第二个参数是文件名，第三个参数是函数名。PythonFunction宏会被替换为“from gdbt.gdbt import gbdt_fit”语句。所以要保证文件路径和函数正确。

在python层中，实现训练函数，代码如下：

def gbdt_fit(schema_madlib,training_table_name, output_table_name,
        id_col_name, dependent_variable, list_of_features,
        list_of_features_to_exclude, weights,
        num_trees, num_random_features,
        max_tree_depth, min_split, min_bucket, num_bins,
        null_handling_params, is_classification,
        predict_dt_prob = None, learning_rate = None,
        verbose=False, **kwargs):
     …
    plpy.execute("""ALTER TABLE {training_table_name} DROP COLUMN IF EXISTS gradient CASCADE
                """.format(training_table_name=training_table_name))
                            
    create_summary_table(output_table_name, null_proxy, bins['cat_features'],
                        bins['con_features'], learning_rate, is_classification, predict_dt_prob,
                        num_trees, training_table_name)

在python层实现预测函数，代码如下：

def gbdt_predict(schema_madlib, test_table_name, model_table_name, output_table_name, id_col_name, **kwargs):
 
    num_tree = plpy.execute("""SELECT COUNT(*) AS count FROM {model_table_name}""".format(**locals()))[0]['count']
    if num_tree == 0:
        plpy.error("The GBDT-method has no trees")
    
 
    elements = plpy.execute("""SELECT * FROM {model_table_name}_summary""".format(**locals()))[0]
…

在py_in文件中，定义相应的业务代码，用python实现相应处理逻辑。

在安装阶段，sql_in和py_in会被GNU m4解析为正常的python和sql文件。这里需要指出的是，当前MADlib框架只支持python2版本，因此，上述代码实现也是基于python2完成的。

8.7.5 MADlib在openGauss上的使用示例

这里以通过支持向量机算法进行房价分类为例，演示具体的使用方法。

（1）数据集准备，代码如下：

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),
 …
(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);

（2）模型训练

① 训练前配置相应schema和兼容性参数，代码如下：

SET search_path="$user",public,madlib;
SET behavior_compat_options = 'bind_procedure_searchpath';

② 使用默认的参数进行训练，分类的条件为‘price < 100000’，SQL语句如下：

DROP TABLE IF EXISTS houses_svm, houses_svm_summary;
SELECT madlib.svm_classification('public.houses','public.houses_svm','price < 100000','ARRAY[1, tax, bath, size]');

（3）查看模型，代码如下：

\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}

（4）进行预测，代码如下：

DROP TABLE IF EXISTS houses_pred;
SELECT madlib.svm_predict('public.houses_svm','public.houses','id','public.houses_pred');

（5）查看预测结果，代码如下：

SELECT *, price < 100000 AS actual FROM houses JOIN houses_pred USING (id) ORDER BY id;

结果如下：

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
 …
 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 < 100000);

结果如下：

count
-------
     3
(1 row)

（6）使用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 < 100000','ARRAY[1, tax, bath, size]','gaussian','n_components=10', '', 'init_stepsize=1, max_iter=200' );

进行预测，并查看训练结果。

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 < 100000);

结果如下：

count
-------+    
0
(1 row)

（7）其他参数

除了指定不同的核方法外，还可以指定迭代次数、初始参数，比如init_stepsize，max_iter，class_weight等。

8.7.6 演进路线

openGauss当前通过兼容开源的Apache MADlib机器学习库来具备机器学习能力。通过对原有MADlib框架的适配，openGauss实现了多种自定义的工程化算法扩展。

除兼容业界标杆PostgreSQL系的Apache MADlib来获得它的业务生态外，openGauss也在自研原生的DB4AI引擎，并支持端到端的全流程AI能力，这包括模型管理、超参数优化、原生的SQL-like语法、数据库原生的AI算子与执行计划等，性能相比MADlib具有5倍以上的提升。该功能将在后续逐步开源。

8.8 小结

本章中，介绍了openGauss团队在AI与数据库结合中的探索，并重点介绍了AI4DB中的参数自调优、索引推荐、异常检测、查询时间预测、慢SQL发现等特性，以及openGauss的DB4AI功能。无论从哪个方面讲，AI与数据库的结合远不止于此，此处介绍的这些功能也仅是一个开端，在openGauss的AI功能上还有很多事情要做、还有很多路要走。包括AI与优化器的进一步结合；打造全流程的AI自治能力，实现全场景的故障发现与自动修复；利用AI改造数据库内的算法与逻辑等都是演进的方向。

虽然AI与数据库结合已经取得了长远的进步，但是还面临着如下的挑战。

（1）算力问题：额外的AI计算产生的算力代价如何解决？会不会导致性能下降。

（2）算法问题：使用AI算法与数据库结合是否会带来显著的收益？算法额外开销是否很大？算法能否泛化，适用到普适场景中？选择什么样的算法更能解决实际问题？

（3）数据问题：如何安全的提取和存储AI模型训练所需要的数据，如何面对数据冷热分类和加载启动问题？

上述问题在很大程度上是一个权衡问题，既要充分利用AI创造的灵感，又要充分继承和发扬数据库现有的理论与实践，这也是openGauss团队不断探索的方向。

- END -

Gauss松鼠会

汇集数据库从业人员及爱好者

互助解决问题共建数据库技术交流圈

ai opengauss

最后修改时间：2022-02-24 18:36:58

文章转载自Gauss松鼠会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

openGauss源码解析（共35篇）

详细的解析openGauss源码，帮助开发者了解openGauss内部实现逻辑。

openGauss数据库源码解析系列文章——AI技术（六）：DeepSQL

上篇图文，我们分享了AI技术——AI查询时间预测的精彩内容，本篇将详细介绍AI技术——DeepSQL的相关内容。

8.7.1 使用场景

8.7.2 现有技术

8.7.3 关键源码解析

1. MADLib的项目结构

8.7.4 基于MADlib框架的扩展

GBDT目录结构

8.7.5 MADlib在openGauss上的使用示例

（1） 数据集准备，代码如下：

（2） 模型训练

（3） 查看模型，代码如下：

（4） 进行预测，代码如下：

（5） 查看预测结果，代码如下：

（6） 使用svm其他核进行训练，代码如下：

（7） 其他参数

8.7.6 演进路线

8.8 小结

（1） 算力问题：额外的AI计算产生的算力代价如何解决？会不会导致性能下降。

（2） 算法问题：使用AI算法与数据库结合是否会带来显著的收益？算法额外开销是否很大？算法能否泛化，适用到普适场景中？选择什么样的算法更能解决实际问题？

（3） 数据问题：如何安全的提取和存储AI模型训练所需要的数据，如何面对数据冷热分类和加载启动问题？

文章被以下合辑收录

评论

（1）数据集准备，代码如下：

（2）模型训练

（3）查看模型，代码如下：

（4）进行预测，代码如下：

（5）查看预测结果，代码如下：

（6）使用svm其他核进行训练，代码如下：

（7）其他参数

（1）算力问题：额外的AI计算产生的算力代价如何解决？会不会导致性能下降。

（2）算法问题：使用AI算法与数据库结合是否会带来显著的收益？算法额外开销是否很大？算法能否泛化，适用到普适场景中？选择什么样的算法更能解决实际问题？

（3）数据问题：如何安全的提取和存储AI模型训练所需要的数据，如何面对数据冷热分类和加载启动问题？