前言
参考
https://oracle-base.com/articles/21c/dbms_cloud-package
压了好久,准备开始写,发现有人发过类似的了,
https://blog.csdn.net/stevensxiao/article/details/118578785
但和我的操作有点不一样,所以我还是写一篇吧。
本文标题本来打算叫
【ORACLE】使用dbms_cloud包以外部表的形式读取对象存储服务中的文件
然后以腾讯云cos为例,使用ORACLE自带的功能,用cos里的文件创建个外部表。
但是过程有点曲折,此文写完时还没搞定,所以文章标题只能先变一下,之后如果有方案了,就再写一篇。
安装dbms_cloud
官方文档
https://support.oracle.com/epmos/faces/DocContentDisplay?id=2748362.1
我没mos账号了,看不到,只能看TIM的文章了
首先,我本地的Oracle版本为19.3,在数据库里面和rdbms/admin中都没有dbms_cloud包,根据此篇文章所说
https://oracle-base.com/articles/21c/dbms_cloud-installation
在19c版本是有的,所以我想可能是我这个版本低了,果然在升级到最新版本19.12后,rdbms/admin下有了dbms_cloud.sql文件
【ORACLE】记录一次windows环境ORACLE19.3打补丁升到19.12版本
然后就是安装,但是肯定不止这一个包,应该还有一些依赖对象,还是看了下这篇文章
https://oracle-base.com/articles/21c/dbms_cloud-installation
TIM给了个很长的安装脚本,是用来给PDB和所有CDB安装的,但我这台是单节点单实例,没必要整这么复杂,就只复制了他下面那段
REM Create the Catalog objects
@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_catalog.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_views.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_catalog.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_types.sql
REM Create the Package Spec
@$ORACLE_HOME/rdbms/admin/prvt_cloud_core.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_task.plb
@$ORACLE_HOME/rdbms/admin/dbms_cloud_capability.sql
@$ORACLE_HOME/rdbms/admin/prvt_cloud_request.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal.plb
@$ORACLE_HOME/rdbms/admin/dbms_cloud.sql
@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int.plb
REM Create the Package Body
@$ORACLE_HOME/rdbms/admin/prvt_cloud_core_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_task_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_capability_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_request_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int_body.plb
-- Create the metadata
@$ORACLE_HOME/rdbms/admin/dbms_cloud_metadata.sql
alter session set current_schema=sys;
@$ORACLE_HOME/rdbms/admin/sqlsessend.sql
直接用sys登录sqlplus,然后粘贴
命令一路滚下来没有一个报错,完美
创建wallet
由于我手头上没visa和万事达,没办法申请免费的oralce或aws对象存储。找了下国内的,现在面向个人的对象存储外网下行流量几乎全部收费,要么就是强制加广告的,只能先用下相对还算便宜的腾讯云cos了,也不一定能成功,毕竟网上没有ORACLE访问cos的文章。
所以接下来就是根据腾讯云cos的证书来制作一个wallet
下载证书后,打开wallet manager,点新建,
输入口令,需要8位,这里测试,我口令设置成了 asdf1234,点击确定
点否
操作,导入信任证书
选择包含证书的文件
注意,我这里只导入了两个证书,我之前在【AIO】使用ORACLE数据库存储过程发送加密email(SSL) 这篇文章中说过,在12c以上版本,不要导入末级证书,否则会报错
另存为,这里我保存在了 H:\wallet_cos
然后,为了节省后面的操作,可以把自动登录给勾上,
再保存,就可以关闭这个程序了,我们可以看到此时在这个文件夹下有4个文件(有时候只有两个,有两个lck是临时锁定文件,一般不用管)
测试证书是否有效
select utl_http.request(url => 'https://first-1307489902.cos.ap-shanghai.myqcloud.com',
wallet_path => 'file:H:\wallet_cos',
wallet_password => 'asdf1234')
from dual;
能查出东西就行
如果直接就开始上传文件的话,会提示
这个和之前说过的utl_http.set_wallet又有点不一样,这次dbms_cloud是把证书路径设置成了数据库的一个参数,需要使用alter命令进行设置
alter database property set ssl_wallet='H:\wallet_cos'
这是因为oracle干了件这样的事
这是想说以后就只要做一个wallet了,啥证书都往这一个wallet里添加?这里先不评价这玩意。
创建认证
然后还要创建一个登陆认证,这两个值是对象存储服务提供的,相当于加密的用户名和密码
begin
dbms_cloud.create_credential (
credential_name => 'obj_store_cred',
username => 'AKID**********************7JGzU',
password => 'MTK*************************DVYNxNf'
);
end;
/
上传
设置好后,测试上传
begin
dbms_cloud.put_object (
credential_name => 'obj_store_cred',
object_uri => 'https://first-1307489902.cos.ap-shanghai.myqcloud.com/test_upload.txt',
directory_name => 'PY_FILE',
file_name => 'test_upload.txt');
end;
/
上传失败,开始研究
提示,不支持的对象存储URI,emmm,调试下,看到底是哪里不对,然后一路跟踪到这里
进了NO_DATA_FOUND了,因为dbms_cloud_store表里没数据(在打dbms_cloud_metadata.sql的时候,校验了CON_NAME必须是CDB$ROOT才会插表,可以手工再补上),但是根据这个逻辑看,就算把腾讯云cos加进去了,它也不支持,因为已经限定了必须是ORACLE/AMAZON/AZURE这三家。真不知道oracle官方竟然也有这种不考虑扩展的case when写法,甚至还直接按照厂家分开写了多个uri转换过程,离谱的是,还用强制的域名格式做则匹配来解析。。。
那如果我直接把这官方代码改了,不知道能不能过?
首先,得分析一下,这些代码到底在干啥,是不是都是有必要的?
把dbms_cloud_request这个包大致代码都过了一下,发现都没啥特殊操作,就是常见的utl_http操作,针对每个厂家要求的api请求格式,各种转换组装请求包,然后再把包post出去,获得一个返回值。就这?
如果oracle这功能就这的话,还研究干什么?
稍等,
不还有外部表么?
我们先跳过dbms_cloud_request这个包,因为如果要支持非这三个厂家的对象存储的话,请求肯定得自己重新写,因为api都有些区别,(好像目前京东的oss倒是和亚马逊s3完全一样,其他的暂时没去研究),大不了给腾讯云cos免费写个oracle版本的sdk....
接下来看外部表是怎么实现的,
调用过程创建外部表
begin
dbms_cloud.create_external_table(
table_name => 'emp_ext',
credential_name => 'obj_store_cred',
file_uri_list => 'https://first-1307489902.cos.ap-shanghai.myqcloud.com/test_dir/test_upload.csv',
column_list => 'empno number(4),
ename varchar2(10),
job varchar2(9)',
format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
);
end;
/
创建不会报错,然后直接查询,
提示
还是和上面一样,不支持腾讯云cos,然后我们看下这个表的ddl
select dbms_metadata.get_ddl(object_type => 'TABLE',name =>'EMP_EXT' ) FROM DUAL;
CREATE TABLE "SYS"."EMP_EXT"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "DATA_PUMP_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY DETECTED NEWLINE NOLOGFILE NOBADFILE NODISCARDFILE READSIZE=10000000 CREDENTIAL 'obj_store_cred'
FIELDS TERMINATED BY '|' MISSING FIELD VALUES ARE NULL REMOVE_QUOTES NOTRIM
)
LOCATION
( 'https://first-1307489902.cos.ap-shanghai.myqcloud.com/test_dir/test_upload.csv'
)
)
REJECT LIMIT 0
PARALLEL
好像和普通的外部表区别不大,就多了个CREDENTIAL,难道把文件名改成远程文件名它就自动走dbms_cloud去读数据去了?
写个查询丢调试窗口调下看看
declare
l number;
begin
select count(1) into l from EMP_EXT;
end;
/
果然,走到dbms_cloud及其相关的包里面去了,调试第一步就直接就到了
declare
metadata clob;
m_obj json_object_t;
begin
metadata := dbms_cloud_internal.get_metadata(dbms_assert.enquote_name(:1,
FALSE),
:2,
:3);
m_obj := json_object_t.parse(metadata);
:4 := m_obj.get_Number('Content-Length');
end;
这个表上也没有触发器,已经涉及ORACLE不在PLSQL内的原生支持的特性了,而且中间还有使用外部的C库,如果不改dbms_cloud包,腾讯云cos的文件就不能当外部表用。自己另外写包的话,顶多搞个上传下载文件,虽然转个弯也能实现外部表,比如查询的时候触发请求,获得内容后写入到本地当成外部表用,或者直接在内存里解析并转换成table类型,但这不是毕竟不是这个包的原生机制呀,只要一建这个url的外部表,它就会强制走dbms_cloud。看来oracle还是还是弄出了点东西。
但是改dbms_cloud包会存在风险,因为ORACLE一旦对这个包升级,版本就覆盖了,而且如果oracle改了机制,就没办法合代码了。
最后
不过,我还是准备试一试,万一打开一条路子,这个还是挺有用的。试想下:
oracle定时在数据库内,把大量数据传到了云上的对象存储里,中间不需要再进行其他任何服务器,节省了大量的网络带宽和存储,然后云上进行大数据计算,得到一个结果,然后这个结果以外部表的形式,在oracle里可以直接进行查询。如果oracle也部署在云上,就完全不涉及到外网下行流量。
待办+1
“他挖一行”
20211024更新
问题解决,腾讯云cos的api和s3其实也是保持一致的,所以只要执行以下sql即可
insert into dbms_cloud_store
(cloud_type, base_uri_pattern, version, status)
values
('AMAZON_S3', '%myqcloud%', '', 1);
commit;
阿里云据说也是支持S3标准
insert into dbms_cloud_store
(cloud_type, base_uri_pattern, version, status)
values
('AMAZON_S3', '%aliyuncs%', '', 1);
commit;
但上传对象的时候,提示
ORA-20400: Request failed with status HTTP 400 - https://darkathenafirst.oss-cn-shanghai.aliyuncs.com/test_upload.csv
Error response - <?xml version="1.0" encoding="UTF-8"?>
<Error>
<Code>NotImplemented</Code>
<Message>Aws MultiChunkedEncoding is not supported.</Message>
<RequestId>617570EC0D39F73938D0BEDF</RequestId>
<HostId>darkathenafirst.oss-cn-shanghai.aliyuncs.com</HostId>
</Error>
ORA-06512: 在 "SYS.DBMS_CLOUD", line 917
ORA-06512: 在 "SYS.DBMS_CLOUD", line 2470
可能是由于这里,
但阿里云不是宣称兼容么?到阿里云社区一搜
https://developer.aliyun.com/ask/321872?spm=a2c6h.13066354.0.0.525633b5RSKe8R
有人.net也报了这个错,这锅得阿里背了.
搜到来自各种工具同样的报错
https://forum.duplicati.com/t/alibaba-cloud-oss-aliyun-error/2414
https://github.com/gomods/athens/issues/1296
看样子这个问题已经很久了,我要去踢下阿里屁股了。
AWS官方文档
https://docs.aws.amazon.com/AmazonS3/latest/API/sigv4-streaming.html