前言

之前有寫過一篇
【ORACLE】在ORACLE數(shù)據(jù)庫中啟用機(jī)器學(xué)習(xí)功能（OML）以支持PYTHON腳本的運行

在斷斷續(xù)續(xù)折騰了好久之后，總算可以用oracle官方提供的方案在oracle數(shù)據(jù)庫中執(zhí)行python代碼了。但是仔細(xì)研究后，發(fā)現(xiàn)除了安裝是個坑外，使用起來也是有相當(dāng)多的限制，畢竟這個功能的目的是用來寫機(jī)器學(xué)習(xí)的，不是純粹的執(zhí)行python代碼。

所以我就寫這篇文章記錄下這個功能該怎么使用。

注意，本文不會大篇幅復(fù)制官方原文檔的內(nèi)容，請自行結(jié)合官方原文檔食用。

官方原文檔：https://docs.oracle.com/en/database/oracle/machine-learning/oml4py/1/mlpug/sql-embedded-python-execution1.html

概覽

創(chuàng)建函數(shù)

sys.pyqScriptCreate (
    V_NAME          VARCHAR2    IN  --腳本名稱
    V_SCRIPT        CLOB        IN  --腳本內(nèi)容
    V_GLOBAL        BOOLEAN     IN     DEFAULT  --是否全局可用（即public）
    V_OVERWRITE     BOOLEAN     IN     DEFAULT) --是否覆蓋

根據(jù)官方多個示例來看，V_SCRIPT這個參數(shù)在形式上有兩種
1

func = lambda: "Hello World from a lambda!

2

def func_name():
    import xxx
    ...
    return value

并且在創(chuàng)建環(huán)節(jié)，如果python代碼有誤，是不會報錯的，僅僅只是把這段文本保存進(jìn)了數(shù)據(jù)庫。
需要注意的有幾點

1. 由于這個參數(shù)是個字符串，需要注意單引號的轉(zhuǎn)義
2. 注意python代碼的行首縮進(jìn)規(guī)則，官網(wǎng)示例有一部分格式化的代碼是存在問題的
3. 它必須是個函數(shù)(對象？)，所以import 不能放在最前面

ORA-20000: PyQuery error
The script must define exactly one object

另外還有幾點放在后面使用函數(shù)來說

刪除函數(shù)

sys.pyqScriptDrop (
    V_NAME          VARCHAR2    IN    --腳本名稱
    V_GLOBAL        BOOLEAN     IN     DEFAULT  --是否全局
    V_SILENT        BOOLEAN     IN     DEFAULT) --是否"不顯示"刪除報錯信息

這個沒啥好說的，是物理刪除 sys.pyq$script 這個表中的記錄 ，一刪就沒了。多用戶的時候注意下 V_GLOBAL。

查看函數(shù)

select * from ALL_PYQ_SCRIPTS;

可以看到名稱、內(nèi)容、所有者

使用函數(shù)

它提供了4種執(zhí)行函數(shù)的方式，每種的區(qū)別在于傳入?yún)?shù)

1. EXP_NAM 為要調(diào)用的python函數(shù)名稱
2. OUT_QRY 為查詢輸出的格式
   2.1 “XML”,最通用的，將python返回結(jié)果以xml格式的一個字符串返回到一個clob類型的字段中；如果是圖片，則返回對應(yīng)的base64編碼
   2.2 “PNG”,將python返回的png圖片的二進(jìn)制數(shù)據(jù)返回到一個blob類型的字段中
   2.3 一個類似于’{“A”:“varchar2(100)”,“B”:“NUMBER”}'這樣的json串，此方式必須保證python的返回值為以下幾種之一：a pandas.DataFrame, a numpy.ndarray, a tuple, or a list of tuples，并且返回的字段數(shù)量及類型必須都匹配。使用此方式時，是以一個表的形式返回數(shù)據(jù)，方便在數(shù)據(jù)庫中使用。這種其實對python函數(shù)有了大幅限制。
   2.4 表或視圖名稱，必須是該用戶有查詢權(quán)限的
3. PAR_QRY 為要傳入到EXP_NAM中指定的函數(shù)的參數(shù)，json格式，比如’{“modelName”:“l(fā)inregr”,“datastoreName”:“pymodel”,“oml_connect”:1}’，其中以"oml_"開頭的為保留參數(shù)，起到一些特殊控制的作用 About Special Control Arguments
4. INP_QRY 為要傳入到EXP_NAM中指定的函數(shù)的表或視圖名稱,必須是該用戶有查詢權(quán)限的
5. ROW_NUM 為對應(yīng)INP_QRY中表或視圖的行數(shù)，整數(shù)類型
6. GRP_COL 為對應(yīng)INP_QRY中表或視圖的分組字段，用逗號分割的字符串

可以看到EXP_NAM和OUT_QRY是每種方式都必填的，其他幾個都是要傳到python函數(shù)的參數(shù)。

執(zhí)行的方式都是下面這種形式

SELECT * 
  FROM table(pyqEval( 
               NULL,
               'XML',
                'pyqFun2'));

數(shù)據(jù)存儲

這里說的數(shù)據(jù)存儲不是指的數(shù)據(jù)庫中的表，而是指的機(jī)器學(xué)習(xí)計算的結(jié)果，比如模型數(shù)據(jù)。
比如官方的這個例子

BEGIN
   sys.pyqScriptCreate('myLinearRegressionModel',
      'def fit_model(dat, modelName, datastoreName):
         import oml
         from sklearn import linear_model
         regr = linear_model.LinearRegression()
         regr.fit(dat.loc[:, ["Sepal_Length", "Sepal_Width", \
                             "Petal_Length"]], dat.loc[:,["Petal_Width"]])
         oml.ds.save(objs={modelName:regr}, name=datastoreName, overwrite=True)
         return str(regr)', 
       FALSE, TRUE); 
END;

創(chuàng)建這個函數(shù)后，執(zhí)行它，它會在“oml.ds.save”這里，把數(shù)據(jù)保存進(jìn)去，然后在

select * from ALL_PYQ_DATASTORE_CONTENTS;
select * from ALL_PYQ_DATASTORES;

這兩個視圖中都只有一條記錄，顯示該數(shù)據(jù)的相關(guān)參數(shù)，比如名稱、模型、類、大小等

延伸一點

機(jī)器學(xué)習(xí)和數(shù)據(jù)查詢完全是兩個不同的思路，但oracle這個功能，在執(zhí)行機(jī)器學(xué)習(xí)的操作時，都是以select的方式來執(zhí)行函數(shù)，你既可以查詢python吐出來的數(shù)據(jù)，也可以把數(shù)據(jù)庫中的一張表當(dāng)成樣本丟到python里去進(jìn)行模型訓(xùn)練，只是形式上都是"select xxx from table()"

這個功能可能有資源自動回收機(jī)制，有調(diào)用python的會話在inactive一段時間后，再執(zhí)行python函數(shù)，會報

ORA-28576: lost RPC connection to external procedure agent
ORA-06512: at “PYQSYS.PYQ$EVALIMPL_IN”, line 77
ORA-06512: at “PYQSYS.PYQ$EVALIMPL_IN”, line 74
ORA-06512: at “SYS.DBMS_SQL”, line 1766
ORA-06512: at “PYQSYS.PYQ$ETSTART”, line 159
ORA-06512: at “PYQSYS.PYQEVALIMPL”, line 51

然后直接再執(zhí)行一次，即可正常返回結(jié)果，從這里可以看到，它其實是在與操作系統(tǒng)中的程序在進(jìn)行交互，這點我們其實也可以通過執(zhí)行個python函數(shù)驗證，比如獲取當(dāng)前執(zhí)行路徑或者生成一個文件到操作系統(tǒng)目錄中去

BEGIN
   sys.pyqScriptCreate('pyqFun12', 
q'{def aaa():
    import os
    f = open('orapytest.txt', 'w')
    f.write('Hello, world!')
    f.close()    
    return os.getcwd()}'); 
END;
/
SELECT * FROM table(pyqEval(NULL, 'XML', 'pyqFun12'));

name value
---- --------
     <root><str>/u02/config/cdb1/homes/OraDB21Home1/hs/log</str></root>

然后我們進(jìn)操作系統(tǒng)的這個“/u02/config/cdb1/homes/OraDB21Home1/hs/log”目錄看看

果然在這里，另外我嘗試生成文件到其他目錄，有報權(quán)限不夠，看看這個文件的屬性

這說明使用oml4py,讓ORACLE嵌入式執(zhí)行python時，是以操作系統(tǒng)oracle用戶的身份在操作系統(tǒng)中執(zhí)行的，和dbms_mle的機(jī)制不一樣，但這樣其實更方便數(shù)據(jù)的交互。不過需要注意的是，這要更加防范惡意的sql注入，所以一定要把控好相關(guān)對象的執(zhí)行權(quán)限。

而且由于它會自動回收，所以如果寫自動化的程序時，一定要寫此種異常的重試機(jī)制（ORACLE干嘛自己不把這個加進(jìn)去~）。

既然知道了它與操作系統(tǒng)有關(guān)，當(dāng)然就到了喜聞樂見的整活環(huán)節(jié)了。

整活

假設(shè)我直接把".py"文件放到操作系統(tǒng)中，然后在數(shù)據(jù)庫寫個函數(shù)，import這個文件，它能不能執(zhí)行？

我把我上次寫的解析sql中的表名那個項目的文件下載進(jìn)操作系統(tǒng)
https://github.com/Dark-Athena/list_table_sql-py

保存在了 “/list_table_sql-py” 目錄，然后安裝依賴庫

pip3 install antlr4-python3-runtime

在數(shù)據(jù)庫中創(chuàng)建python函數(shù)并執(zhí)行它

BEGIN
   sys.pyqScriptCreate('pyqFun13', 
'def aaa(sql):
    import sys
    sys.path.append(''/list_table_sql-py'')
    from list_table_sql import list_table_sql as t
    return t(''{"sql":"''+sql+''","mode":"T"}'')
    '); 
END;
/

SELECT *
  FROM table(pyqEval('{"sql":"select abc,cdf x from tab1 a,tab2,tab3@dblink where 1=1"}',
                     'XML',
                     'pyqFun13'));

NAME	VALUE
----    -----
	<root><dict><item><key>tablename</key><value><item>tab1</item><item>tab2</item><item>tab3@dblink</item></value></item></dict></root>

可以看到三個表名都解析出來了。

本篇完。

• 本文作者： DarkAthena
• 本文鏈接： https://www.darkathena.top/archives/about-oml4py-part2
• 版權(quán)聲明： 本博客所有文章除特別聲明外，均采用CC BY-NC-SA 3.0 許可協(xié)議。轉(zhuǎn)載請注明出處！