前言
之前有寫過一篇
【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)載請注明出處!




