数据库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 支持的机器学习算法 - 统计学

表 8 支持的机器学习算法 - 其他算法

二、环境部署

DeepSQL环境包括编译数据库和安装算法库两个部分。

前提条件

• 环境中安装python2.7.12以上版本Python。
• 数据库需要开启对PL/Python存储过程的支持。
• 安装算法库需要拥有管理员权限的用户。

操作步骤

1. 检查部署Python环境。

安装前，请查看系统安装的python版本，当前DeepSQL需要python2.7.12以上版本的环境。

1. 如果当前系统python2版本高于2.7.12，可以直接安装python-devel包。
2. 如果版本过低，或者无法安装python-devel包，可以下载最新python2源码，手动配置编译python2，并配置环境变量。

算法库中，部分算法调用了python包，如numpy，pandas等。用户可以安装以下python库：

pip install numpy
pip install pandas
pip install scipy

须知：

1. 如果自行编译python，需要在configure脚本执行时加入–enable-shared参数。
2. 如果系统中的python2使用的是UCS4编码，自行编译python2时，还需要加入–enable-unicode=ucs4参数。
3. 可以在系统中自带的python2下执行：“import sys；print sys.maxunicode”并查看结果，如果结果是65535，说明系统默认的是ucs2；如果结果是1114111，说明用的ucs4编码。
4. 如果系统中内置的python2使用的ucs4，说明系统中的gdb，gstack等也会依赖ucs4。因此自行编译的python2在configure时，需要添加–enable-unicode=ucs4，否则后续使用gdb，gstack时，会遇到报错。

2. 编译部署数据库。

数据库需要开启对PL/Python存储过程的支持。默认编译数据库，不包含此模块。因此需要编译数据库时，在configure阶段，加入--with-python参数；
其他编译保持步骤不变；
编译完成后，需要重新gs_initdb；
默认PL/Python存储过程模块不被加载，请执行“CREATE EXTENSION plpythonu”来加载模块。

3. 算法库编译和安装。

算法库使用开源的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 < madlib.patch

编译命令如下：
./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/

4. 将算法库安装到数据库中。

a.进入{YOUR_MADLIB_INSTALL_FOLDER}路径。

b.进入bin文件夹。

c.执行如下命令。

   ./madpack -s <SCHEMA_NAME> -p opengauss -c <USER_NAME>@127.0.0.1:<PORT>/<DATABASE_NAME> 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 <module>
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 xgboost
pip install scikit-learn

• 如果使用xgboost算法：
  

pip install xgboost
pip install scikit-learn

• gbdt不需要额外安装其他库。

详细操作请参考后续章节描述。