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

8.7 DeepSQL

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

使用場景

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

現有技術

如今，學術界與工業界在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的，

關鍵原始碼決議

1. MADLib的專案結構

MADlib的檔案結構及說明如表8-16所示，MADlib的代碼可通過其官方網站獲取：https://madlib.apache.org/，

2. MADlib在openGauss上的執行流程

用戶通過呼叫UDF即可進行模型的訓練和預測，相關的結果會保存在表中，存盤在資料庫上，以訓練程序為例，MADlib在openGauss上執行的整體流程如圖8-22所示，

基于MADlib框架的擴展

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

GBDT檔案結構如表8-17所示，

在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完成的，

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等，

演進路線

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團隊不斷探索的方向，

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