数据仓库oracle导入文件,Oracle自治数据仓库ADWC从object storage中导入数据和XML DB相关操作...

最新推荐文章于 2023-05-11 16:00:00 发布

weixin_39793576

最新推荐文章于 2023-05-11 16:00:00 发布

阅读量225

点赞数

文章标签：数据仓库oracle导入文件

学习了下用DBMS_CLOUD包导入Oracle Object Storage文件中数据到数据库ADWC中。

1. 创建CREDENTIAL

BEGIN

DBMS_CLOUD.CREATE_CREDENTIAL(

credential_name => 'API_USER_CREDENTIAL',

username => 'username.sample',

password => '-b:#Ah.0h.GGWWrx6>:Kb'

);

END;

OCI(Oracle Cloud Infrastructure) 中credential：

username is your Oracle Cloud Infrastructure user name

password is your auth token

2. 在object storage中上传object

这里创建了一个bucket：ADWFINANCE，里面上传了“channel.csv”文件。

Oracle Cloud Infrastructure Object Storage URI Format

https://swiftobjectstorage..oraclecloud.com/v1///

最终我的地址：

https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/gse000xxxx/ADWFINANCE/channels.csv

3. 建表

本身csv文件中包含6列，这里只用3列

drop table CHANNELS;

CREATE TABLE CHANNELS

(channel_id char(1) ,

channel_desc varchar2(20),

channel_class varchar2(20)

-- CHANNEL_CLASS_ID char(2),

-- CHANNEL_TOTAL varchar(20),

-- CHANNEL_TOTAL_ID char(1)

);

4. copy data

BEGIN

DBMS_CLOUD.COPY_DATA(

table_name =>'CHANNELS',

credential_name =>'API_USER_CREDENTIAL',

file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/gse000xxxx/ADWFINANCE/channels.csv',

field_list => '

CHANNEL_ID CHAR,

CHANNEL_DESC char,

CHANNEL_CLASS char',

format => json_object('delimiter' value ',', 'recorddelimiter' value '''\\n''', 'skipheaders' value '1', 'quote' value '\"', 'rejectlimit' value '1000', 'trimspaces' value 'rtrim', 'ignoreblanklines' value 'true', 'ignoremissingcolumns' value 'true')

);

END;

select * from channels;

这里field_list可以不用，注意format中的值。format详细可参考dbmscloud-reference.

5.建立外部表

begin

dbms_cloud.create_external_table(

table_name => 'CHANNELS_EXT',

credential_name => 'API_USER_CREDENTIAL',

file_uri_list =>

'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/gse00015621/ADWFINANCE/channels.csv',

column_list => '

CHANNEL_ID CHAR(1),

CHANNEL_DESC VARCHAR2(20),

CHANNEL_CLASS VARCHAR2(20),

CHANNEL_CLASS_ID CHAR(2),

CHANNEL_TOTAL VARCHAR2(20),

CHANNEL_TOTAL_ID CHAR(1)',

);

end;

select * from CHANNELS_EXT;

drop table CHANNELS_EXT;

这里文件中6列都用到了。

6. 从"data_pump_dir"目录中删除文件

DBMS_CLOUD.DELETE_FILE (

directory_name IN VARCHAR2

file_name IN VARCHAR2);

这里目录只能是"data_pump_dir"。数据库全部目录:

select * from all_directories;

SYS DATA_PUMP_DIR /u03/dbfs/790B7A670C97CC1DE0531818000A839B/data/dpdump 92

SYS SQL_TCB_DIR /u03/dbfs/790B7A670C97CC1DE0531818000A839B/data/tcb_dir 92

7. LIST FILES

可以列出“data_pump_dir”目录中所有文件

DBMS_CLOUD.LIST_FILES (

directory_name IN VARCHAR2);

-- The only allowed value is data_pump_dir.

SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');

GRANT READ ON DIRECTORY data_pump_dir TO adwc_user;

8. PUT_OBJECT把ADWC中"data_pump_dir"下文件拷贝到Object Storage

DBMS_CLOUD.PUT_OBJECT (

credential_name IN VARCHAR2

object_uri IN VARCHAR2

directory_name IN VARCHAR2

file_name IN VARCHAR2);

这里文件大小限制为小于5GB。

9. 删除CREDENTIAL

-- removes an existing credential from Autonomous Data Warehouse.

DBMS_CLOUD.DROP_CREDENTIAL (

credential_name IN VARCHAR2);

10.数据加载记录

All data load operations done using the PL/SQL package DBMS_CLOUD are logged in the tables dba_load_operations and user_load_operations.

SELECT table_name, owner_name, type, status, start_time, update_time,

logfile_table, badfile_table FROM user_load_operations

WHERE type = 'COPY' and table_name = upper('channels');

11. 其它

这点是顺便加上的，刚好在看XML DB和sqlplus wallet连接相关内容，就记录在这里了。

11.1XMLDB相关

11.1.1 Generation of XML Data from Relational Data

SELECT e.channel_id, XMLElement ("CHANNEL",

XMLElement("CHANNEL_DESC", e.CHANNEL_DESC),

XMLElement("CHANNEL_CLASS", e.CHANNEL_CLASS)) AS "RESULT"

FROM channels e;

SELECT XMLElement("Channel",

XMLAttributes('http://www.w3.org/2001/XMLSchema' AS

"xmlns:xsi",

'http://www.oracle.com/channel.xsd' AS

"xsi:nonamespaceSchemaLocation"),

XMLForest(channel_id, CHANNEL_DESC, CHANNEL_CLASS)) AS "RESULT"

FROM channels;

11.1.2 从bfile中导入xml数据到数据库中

employee.xml

100

Steven

AD_PRES

17-6月 -2003

101

Neena

AD_VP

21-9月 -2005

...

创建表保存bfile

CREATE TABLE xml_tab (

id NUMBER,

xml_data XMLTYPE

);

INSERT INTO xml_tab

VALUES (1, XMLType(bfilename('DUMP_DIR', 'employee.xml'),

nls_charset_id('AL32UTF8')));

select * from xml_tab;

SELECT x.xml_data.getClobVal()

FROM xml_tab x;

将employee按照field展示

SELECT xt.*

FROM xml_tab x,

XMLTABLE('/employees/employee'

PASSING x.xml_data

COLUMNS

empno VARCHAR2(4) PATH 'empno',

ename VARCHAR2(10) PATH 'ename',

job VARCHAR2(9) PATH 'job',

hiredate VARCHAR2(11) PATH 'hiredate'

) xt;

查询结果如下：

812724cae798

1.JPG

参考Query and Update of XML Data.

11.2 sqlplus用wallet连接云数据库(ADWC&ATP)

11.2.1 从OCI中下载wallet

812724cae798

1.JPG

这里需要输入wallet密码。

11.2.2 解压下载的zip文件，修改里面的"sqlnet.ora"文件

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="D:\tmp\Wallet_DB201809271422")))

SSL_SERVER_DN_MATCH=yes

"D:\tmp\Wallet_DB201809271422"是解压后的路径。

添加环境变量"TNS_ADMIN"，值为这个路径。

11.2.3 下载最新的instant client(including SQLPLUS),至少12以上版本

11.2.4 用sqlplus连接ADWC

812724cae798

1.JPG

这里我使用的是power shell。

12. 结语

原创文章，需转载请留言和私信联系。如有疑问，欢迎留言或私信共同探讨。