前言
之前有寫過一篇
【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é)合官方原文檔食用。
概覽
創(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ù)庫。
需要注意的有幾點
- 由于這個參數(shù)是個字符串，需要注意單引號的轉(zhuǎn)義
- 注意python代碼的行首縮進(jìn)規(guī)則，官網(wǎng)示例有一部分格式化的代碼是存在問題的
- 它必須是個函數(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ù)
| pyqEval | pyqTableEval | pyqRowEval | pyqGroupEval |
|---|---|---|---|
| INP_QRY | INP_QRY | INP_QRY | |
| PAR_QRY | PAR_QRY | PAR_QRY | PAR_QRY |
| OUT_QRY | OUT_QRY | OUT_QRY | OUT_QRY |
| ROW_NUM | |||
| GRP_COL | |||
| EXP_NAM | EXP_NAM | EXP_NAM | EXP_NAM |
- EXP_NAM 為要調(diào)用的python函數(shù)名稱
- 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)限的 - PAR_QRY 為要傳入到EXP_NAM中指定的函數(shù)的參數(shù)，json格式，比如’{“modelName”:“l(fā)inregr”,“datastoreName”:“pymodel”,“oml_connect”:1}’，其中以"oml_"開頭的為保留參數(shù)，起到一些特殊控制的作用 About Special Control Arguments
- INP_QRY 為要傳入到EXP_NAM中指定的函數(shù)的表或視圖名稱,必須是該用戶有查詢權(quán)限的
- ROW_NUM 為對應(yīng)INP_QRY中表或視圖的行數(shù)，整數(shù)類型
- 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)載請注明出處！
