1

【oracle】关于21c版本中机器学习oml4py功能嵌入式python代码执行的研究整理 -利来国际app

darkathena 2021-11-14
228

前言

之前有写过一篇

在断断续续折腾了好久之后,总算可以用oracle官方提供的方案在oracle数据库中执行python代码了。但是仔细研究后,发现除了安装是个坑外,使用起来也是有相当多的限制,毕竟这个功能的目的是用来写机器学习的,不是纯粹的执行python代码。

所以我就写这篇文章记录下这个功能该怎么使用。

注意,本文不会大篇幅复制官方原文档的内容,请自行结合官方原文档食用。

官方原文档:

概览

创建函数

sys.pyqscriptcreate ( v_name varchar2 in --脚本名称 v_script clob in --脚本内容 v_global boolean in default --是否全局可用(即public) v_overwrite boolean in default) --是否覆盖

根据官方多个示例来看,v_script这个参数在形式上有两种
1

func = lambda: "hello world from a lambda!

2

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

并且在创建环节,如果python代码有误,是不会报错的,仅仅只是把这段文本保存进了数据库。
需要注意的有几点

  1. 由于这个参数是个字符串,需要注意单引号的转义
  2. 注意python代码的行首缩进规则,利来国际app官网示例有一部分格式化的代码是存在问题的
  3. 它必须是个函数(对象?),所以import 不能放在最前面

ora-20000: pyquery error
the script must define exactly one object

另外还有几点放在后面使用函数来说

删除函数

sys.pyqscriptdrop ( v_name varchar2 in --脚本名称 v_global boolean in default --是否全局 v_silent boolean in default) --是否"不显示"删除报错信息

这个没啥好说的,是物理删除 sys.pyq$script 这个表中的记录 ,一删就没了。多用户的时候注意下 v_global。

查看函数

select * from all_pyq_scripts;

可以看到名称、内容、所有者

使用函数

它提供了4种执行函数的方式,每种的区别在于传入参数

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
  1. exp_nam 为要调用的python函数名称
  2. out_qry 为查询输出的格式
    2.1 “xml”,最通用的,将python返回结果以xml格式的一个字符串返回到一个clob类型的字段中;如果是图片,则返回对应的base64编码
    2.2 “png”,将python返回的png图片的二进制数据返回到一个blob类型的字段中
    2.3 一个类似于’{“a”:“varchar2(100)”,“b”:“number”}'这样的json串,此方式必须保证python的返回值为以下几种之一:a pandas.dataframe, a numpy.ndarray, a tuple, or a list of tuples,并且返回的字段数量及类型必须都匹配。使用此方式时,是以一个表的形式返回数据,方便在数据库中使用。这种其实对python函数有了大幅限制。
    2.4 表或视图名称,必须是该用户有查询权限的
  3. par_qry 为要传入到exp_nam中指定的函数的参数,json格式,比如’{“modelname”:“linregr”,“datastorename”:“pymodel”,“oml_connect”:1}’,其中以"oml_"开头的为保留参数,起到一些特殊控制的作用
  4. inp_qry 为要传入到exp_nam中指定的函数的表或视图名称,必须是该用户有查询权限的
  5. row_num 为对应inp_qry中表或视图的行数,整数类型
  6. grp_col 为对应inp_qry中表或视图的分组字段,用逗号分割的字符串

可以看到exp_nam和out_qry是每种方式都必填的,其他几个都是要传到python函数的参数。

执行的方式都是下面这种形式

select * from table(pyqeval( null, 'xml', 'pyqfun2'));

数据存储

这里说的数据存储不是指的数据库中的表,而是指的机器学习计算的结果,比如模型数据。
比如官方的这个例子

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;

创建这个函数后,执行它,它会在“oml.ds.save”这里,把数据保存进去,然后在

select * from all_pyq_datastore_contents; select * from all_pyq_datastores;

这两个视图中都只有一条记录,显示该数据的相关参数,比如名称、模型、类、大小等

延伸一点

机器学习和数据查询完全是两个不同的思路,但oracle这个功能,在执行机器学习的操作时,都是以select的方式来执行函数,你既可以查询python吐出来的数据,也可以把数据库中的一张表当成样本丢到python里去进行模型训练,只是形式上都是"select xxx from table()"

这个功能可能有资源自动回收机制,有调用python的会话在inactive一段时间后,再执行python函数,会报

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

然后直接再执行一次,即可正常返回结果,从这里可以看到,它其实是在与操作系统中的程序在进行交互,这点我们其实也可以通过执行个python函数验证,比如获取当前执行路径或者生成一个文件到操作系统目录中去

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 ---- -------- /u02/config/cdb1/homes/oradb21home1/hs/log

然后我们进操作系统的这个“/u02/config/cdb1/homes/oradb21home1/hs/log”目录看看

果然在这里,另外我尝试生成文件到其他目录,有报权限不够,看看这个文件的属性

这说明使用oml4py,让oracle嵌入式执行python时,是以操作系统oracle用户的身份在操作系统中执行的,和dbms_mle的机制不一样,但这样其实更方便数据的交互。不过需要注意的是,这要更加防范恶意的sql注入,所以一定要把控好相关对象的执行权限。

而且由于它会自动回收,所以如果写自动化的程序时,一定要写此种异常的重试机制(oracle干嘛自己不把这个加进去~)。

既然知道了它与操作系统有关,当然就到了喜闻乐见的整活环节了。

整活

假设我直接把".py"文件放到操作系统中,然后在数据库写个函数,import这个文件,它能不能执行?

我把我上次写的解析sql中的表名那个项目的文件下载进操作系统

保存在了 “/list_table_sql-py” 目录,然后安装依赖库

pip3 install antlr4-python3-runtime

在数据库中创建python函数并执行它

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,[email protected] where 1=1"}', 'xml', 'pyqfun13')); name value ---- ----- tablenametab1tab2[email protected]

可以看到三个表名都解析出来了。

本篇完。

  • 本文作者:
  • 本文链接:
  • 利来手机国际的版权声明: 本博客所有文章除特别声明外,均采用 许可协议。转载请注明出处!
「喜欢文章,快来给作者赞赏墨值吧」
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论