上一篇介紹了 8.6 AI查詢時間預測的相關內容，本篇我們介紹“8.7 DeepSQL、8.8 小結”的相關精彩內容介紹。

8.7 DeepSQL

前面提到的功能均為AI4DB領域，AI與數(shù)據(jù)庫結合還有另外一個大方向，即DB4AI。在本章中，我們將介紹openGauss的DB4AI能力，探索通過數(shù)據(jù)庫來高效驅動AI任務的新途徑。

使用場景

數(shù)據(jù)庫DB4AI功能的實現(xiàn)，即在數(shù)據(jù)庫內實現(xiàn)AI算法，以更好的支撐大數(shù)據(jù)的快速分析和計算。目前openGauss的DB4AI能力通過DeepSQL特性來呈現(xiàn)。這里提供了一整套基于SQL的機器學習、數(shù)據(jù)挖掘以及統(tǒng)計學的算法，用戶可以直接使用SQL語句進行機器學習工作。DeepSQL能夠抽象出端到端的、從數(shù)據(jù)到模型的數(shù)據(jù)研發(fā)過程，配合底層的計算引擎及數(shù)據(jù)庫自動優(yōu)化，讓具備基礎SQL知識的用戶即可完成大部分的機器學習模型訓練及預測任務。整個分析和處理都運行在數(shù)據(jù)庫引擎中，用戶可以直接分析和處理數(shù)據(jù)庫內的數(shù)據(jù)，不需要在數(shù)據(jù)庫和其他平臺之間進行數(shù)據(jù)傳遞，避免在多個環(huán)境之間進行不必要的數(shù)據(jù)移動，并且整合了碎片化的數(shù)據(jù)開發(fā)技術棧。

現(xiàn)有技術

如今，學術界與工業(yè)界在DB4AI這個方向已經(jīng)了取得了許多成果。很多傳統(tǒng)的商業(yè)關系數(shù)據(jù)庫都已經(jīng)支持了DB4AI能力，通過內置AI組件適配數(shù)據(jù)庫內的數(shù)據(jù)處理和環(huán)境，可以對數(shù)據(jù)庫存儲的數(shù)據(jù)進行處理，最大程度地減少數(shù)據(jù)移動的花費。同時，很多云數(shù)據(jù)庫、云計算數(shù)據(jù)分析平臺也都具備DB4AI能力。同時還可能具備Python、R語言等接口，便于數(shù)據(jù)分析人員快速入門。
在DB4AI領域，同樣具備很出色的開源軟件，例如Apache頂級開源項目MADlib。它兼容PostgreSQL數(shù)據(jù)庫，很多基于PostgreSQL數(shù)據(jù)庫源碼基線進行開發(fā)的數(shù)據(jù)庫也可以很容易進行適配。MADlib可以為結構化和非結構化數(shù)據(jù)提供統(tǒng)計和機器學習的方法，并利用聚集函數(shù)實現(xiàn)在分布式數(shù)據(jù)庫上的并行化計算。MADlib支持多種機器學習、數(shù)據(jù)挖掘算法，例如回歸、分類、聚類、統(tǒng)計、圖算法等，累計支持的算法達到70多個，在目前發(fā)布的1.17版本中MADlib支持深度學習。MADlib使用類SQL語法作為對外接口，通過創(chuàng)建UDF（user-defined function，用戶自定義函數(shù)）的方式將AI任務集成到數(shù)據(jù)庫中。
當前openGauss的DB4AI模塊，兼容開源的MADlib，在原始MADlib開源軟件的基礎上進行了互相適配和增強，性能相比在PostgreSQL數(shù)據(jù)庫上運行的MADlib性能更優(yōu)。同時，openGauss基于MADlib框架，實現(xiàn)了其他工業(yè)級的、常用的算法，例如XGBoost、Prophet、GBDT以及推薦系統(tǒng)等。與此同時，openGauss還具備原生的AI執(zhí)行計劃與執(zhí)行算子，該部分特性會在后續(xù)版本中開源。因此，本章內容主要介紹openGauss是如何兼容MADlib的。

關鍵源碼解析

1. MADLib的項目結構

MADlib的文件結構及說明如表8-16所示，MADlib的代碼可通過其官方網(wǎng)站獲取：https://madlib.apache.org/。

2. MADlib在openGauss上的執(zhí)行流程

用戶通過調用UDF即可進行模型的訓練和預測，相關的結果會保存在表中，存儲在數(shù)據(jù)庫上。以訓練過程為例，MADlib在openGauss上執(zhí)行的整體流程如圖8-22所示。

基于MADlib框架的擴展

前文展示了MADlib各個模塊的功能和作用，從結構上看，用戶可以針對自己的算法進行擴展。前文中提到的XGBoost、GBDT和Prophet三個算法是我們在原來基礎上擴展的算法。本小節(jié)將以自研的GBDT模塊為例，介紹基于MADlib框架的擴展。

GBDT文件結構如表8-17所示。

在sql_in文件中，定義上層SQL-like接口，使用PL/pgSQL或者PL/python實現(xiàn)。
在SQL層中定義UDF函數(shù)，下述代碼實現(xiàn)了類似重載的功能。

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;

其中，輸入表、輸出表、特征等必備信息需要用戶指定。其他參數(shù)提供缺省的參數(shù)，比如權重weights，如果用戶沒有指定自定義參數(shù)，程序會用默認的參數(shù)進行運算。
在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函數(shù)最終會調用到一個PL/python函數(shù)。
“PythonFunction(gbdt, gbdt, gbdt_fit)”是固定的用法，這也是一個封裝的m4宏，會在編譯安裝的時候，會進行宏替換。
PythonFunction中，第一個參數(shù)是文件夾名，第二個參數(shù)是文件名，第三個參數(shù)是函數(shù)名。PythonFunction宏會被替換為“from gdbt.gdbt import gbdt_fit”語句。所以要保證文件路徑和函數(shù)正確。
在python層中，實現(xiàn)訓練函數(shù)，代碼如下：

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層實現(xiàn)預測函數(shù)，代碼如下：

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文件中，定義相應的業(yè)務代碼，用python實現(xiàn)相應處理邏輯。
在安裝階段，sql_in和py_in會被GNU m4解析為正常的python和sql文件。這里需要指出的是，當前MADlib框架只支持python2版本，因此，上述代碼實現(xiàn)也是基于python2完成的。

MADlib在openGauss上的使用示例

這里以通過支持向量機算法進行房價分類為例，演示具體的使用方法。
（1） 數(shù)據(jù)集準備，代碼如下：

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和兼容性參數(shù)，代碼如下：

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

② 使用默認的參數(shù)進行訓練，分類的條件為‘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 onSELECT * FROM houses_svm;/x off

結果如下：

-[ RECORD 1 ]------+-----------------------------------------------------------------coef               | {.113989576847,-.00226133300602,-.0676303607996,.00179440841072}loss               | .614496714256667norm_of_gradient   | 108.171180769224num_iterations     | 100num_rows_processed | 15num_rows_skipped   | 0dep_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） 其他參數(shù)
除了指定不同的核方法外，還可以指定迭代次數(shù)、初始參數(shù)，比如init_stepsize，max_iter，class_weight等。

演進路線

openGauss當前通過兼容開源的Apache MADlib機器學習庫來具備機器學習能力。通過對原有MADlib框架的適配，openGauss實現(xiàn)了多種自定義的工程化算法擴展。
除兼容業(yè)界標桿PostgreSQL系的Apache MADlib來獲得它的業(yè)務生態(tài)外，openGauss也在自研原生的DB4AI引擎，并支持端到端的全流程AI能力，這包括模型管理、超參數(shù)優(yōu)化、原生的SQL-like語法、數(shù)據(jù)庫原生的AI算子與執(zhí)行計劃等，性能相比MADlib具有5倍以上的提升。該功能將在后續(xù)逐步開源。

8.8 小結

本章中，介紹了openGauss團隊在AI與數(shù)據(jù)庫結合中的探索，并重點介紹了AI4DB中的參數(shù)自調優(yōu)、索引推薦、異常檢測、查詢時間預測、慢SQL發(fā)現(xiàn)等特性，以及openGauss的DB4AI功能。無論從哪個方面講，AI與數(shù)據(jù)庫的結合遠不止于此，此處介紹的這些功能也僅是一個開端，在openGauss的AI功能上還有很多事情要做、還有很多路要走。包括AI與優(yōu)化器的進一步結合；打造全流程的AI自治能力，實現(xiàn)全場景的故障發(fā)現(xiàn)與自動修復；利用AI改造數(shù)據(jù)庫內的算法與邏輯等都是演進的方向。
雖然AI與數(shù)據(jù)庫結合已經(jīng)取得了長遠的進步，但是還面臨著如下的挑戰(zhàn)。
（1） 算力問題：額外的AI計算產(chǎn)生的算力代價如何解決？會不會導致性能下降。
（2） 算法問題：使用AI算法與數(shù)據(jù)庫結合是否會帶來顯著的收益？算法額外開銷是否很大？算法能否泛化，適用到普適場景中？選擇什么樣的算法更能解決實際問題？
（3） 數(shù)據(jù)問題：如何安全的提取和存儲AI模型訓練所需要的數(shù)據(jù)，如何面對數(shù)據(jù)冷熱分類和加載啟動問題？
上述問題在很大程度上是一個權衡問題，既要充分利用AI創(chuàng)造的靈感，又要充分繼承和發(fā)揚數(shù)據(jù)庫現(xiàn)有的理論與實踐，這也是openGauss團隊不斷探索的方向。

感謝大家學習第8章 AI技術中“8.7 DeepSQL、8.8 小結”的精彩內容，下一篇我們開啟“第9章 安全管理源碼解析”的相關內容的介紹。
敬請期待。