使用 Athena (Presto) 分析本地 Oracle 数据库导出的数据

在传统企业客户，不管是前台的买卖数据库仍是后台的数据仓库，都会挑选运用 Oracle，它具备十分广泛的技能资料、社区资源和问题处理事例（各种踩坑的经历）；一起它还有广泛的用户根底，许多企业的技能栈都是围绕 Oracle 数据库构建开发和运维作业，保证事务的运用。比如金融职业的 Oracle 数据库主要事务场景会涉及到账务、资金和财物中心。

假如用户希望和事务直接相关的数据能够更持久的存储并且做一些离线的剖析，许多企业会构建自己的大数据剖析渠道，把数据存储到渠道进行剖析，就好比水从源头流入湖中，各种用户都能够来湖里获取、蒸馏和提纯这些水（数据）。下图是数据湖的一个典型逻辑架构，它是由多个大数据组件、云服务组成的一个解决计划，包括吸取层，处理/蒸馏层，保护层和数据洞察。

亚马逊云科技开发者社区 为开发者们供给全球的开发技能资源。这儿有技能文档、开发事例、技能专栏、训练视频、活动与竞赛等。协助中国开发者对接世界最前沿技能，观点，和项目，并将中国优异开发者或技能推荐给全球云社区。假如你还没有关注/收藏，看到这儿请一定不要匆匆划过，点这儿 让它成为你的技能宝库！

在水从源头流入湖中的吸取层，常见的场景是经过Apache Sqoop或DMS（亚马逊云科技数据搬迁服务）将数据从 RDBMS 导入到数据湖（HDFS/HDFS/Hive/HBase），转换成列式存储格局，适配运用的查询引擎和计算结构，经过上图中的数据洞察来供给事务大盘和各种报表，辅助事务团队做决策。

Amazon DMS（Database Migration Service）是一种 Web 服务，用于将数据从源数据存储搬迁到方针数据存储。能够在相同数据库引擎的源和方针节点之间搬迁，也可在不同数据库引擎的源和方针点之间搬迁。

Amazon Athena是一种基于 Presto 的交互式查询服务，运用规范 SQL 直接剖析 Amazon S3 中的数据。数据科学家和数据工程师常常会运用 Athena 进行数据剖析和交互式查询。Athena 是无服务器服务，能够主动扩展并履行并行查询，没有保护根底设施的作业，用户按照查询的数据量付费。

在企业环境下，混合云架构是一种常见的架构，理想环境下吸取层的传输场景是经过 Apache Sqoop 或 DMS 运用网络专线将本地 Oracle 数据库数据搬迁到 s3 存储桶，再进行数据剖析。

但受本地条件限制，许多用户是用 Oracle Datapump 或许 Exp/Imp 将数据导出后，再将本地数据传输到 s3 存储桶。本文经过论述运用上述两种不同的东西，介绍如何将数据导入 s3 存储桶，运用 Athena 做数据剖析。

1. 计划概述

计划一，运用 Oracle 数据泵将本地 Oracle 数据库数据导出，上传到s3存储桶，再由适用的 Amazon RDS for Oracle 加载数据，运用 DMS 服务将 RDS Oracle 数据库数据搬迁到 s3 存储桶，运用云上的大数据组件 Athena 进行剖析。

计划二，运用 Oracle Exp 将本地 Oracle 数据库数据导出，上传到 s3 存储桶，再在云上 EC2 装置 Oracle 对应版别的数据库，Imp 数据到 EC2 Oracle，运用 DMS 服务将 EC2 Oracle 数据库数据搬迁到 s3 存储桶，运用云上的大数据组件 Athena 进行剖析。

2. 运用的资源

3. 计划一 运用 Oracle Datadump 导出数据

Datapump 是从 Oracle 10g 中引入的功用，不管功用仍是压缩比，都比传统的 Exp/Imp 更有优势。相比较 Exp 和 Imp 作为客户端东西程序而言，Expdp 和 Impdp 是服务器端东西程序，只能在 Oracle 服务器端运用，不能在客户端运用。Data Pump 是将许多数据从 Oracle 搬迁到 Amazon RDS 数据库实例的首选方法。

3.1 最佳实践

当运用 Oracle 数据转储将数据导入到 Oracle 数据库实例时，建议运用以下最佳实践：

• 在 schema 或 table 形式中履行导入，以便导入特定架构和方针
• 请勿在 full 形式中导入

由于 Amazon RDS for Oracle 不允许拜访 SYS 或 SYSDBA 办理用户，所以在 full 形式中导入架构可能会损坏 Oracle 数据字典并影响数据库的稳定性。

3.2 预期方针

1. 在本地数据库新建一张表，并经过数据泵将该用户下的一切表导出
2. 将 dmp 文件上传到 s3 存储桶
3. 将 dmp 文件加载到 RDS Oracle 并能查询到表
4. 运用 DMS 将数据导出 Parquet 格局存在 s3，并能查询到表
5. 在 Athena 中查询表

3.3 本地 Oracle 数据库建表和导出

1. 在本地 DPADMIN 用户下新建一个表 dep，插入数据

2. 创立数据转储文件

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>null, version=>’12.0.0’);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'dpadmin.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.START_JOB(hdnl);

3. 上传数据转储文件到 s3 存储桶

4. 装备 RDS Oracle DB 选项组和 s3 存储桶的集成

RDS for Oracle 与 Amazon S3 集成，数据库实例必须能够拜访 Amazon S3 存储桶。

4.1 首先要 RDS 中创立一个选项组，进程如下

1. 在 RDS 选项组挑选创立组

2. 命名并挑选相应的引擎和版别

4.2 其次在选项组中添加和 s3 存储桶集成的选项，进程如下

1. 创立完结后再次翻开这个选项组，挑选 Add option

2. 在 Option name 挑选 S3_INTEGRATION，Version 挑选1.0，当即运用

4.3 然后将上述选项组运用到 RDS Oracle 中，进程如下

1. 回到 RDS Oracle，挑选 Configuration，能够看到现有的 Option Groups

2. 挑选 Modify –> Configuration

3. 把 Option group 挑选为方才新建的 s3-integration-group

4. 修正 DB 实例，当即运用，该进程不需求重启实例

5. 在 Configuration –> Option groups 下检查当前选项组，变更完结

4.4 最终将创立好的拜访 s3 存储桶的 Role 关联到 RDS，进程如下

1. 在 IAM –> 人物下，创立 RDS Role，附加能够拜访对应 s3 存储桶的策略

2. 在 RDS –> 数据库 –> Oracle –> Connectivity & security –> Manage IAM roles 下，挑选创立的 RDS_access_s3 人物和 S3_INTEGRATION 功用，添加人物

5. 运用 RDS Oracle DB 从 s3 导入数据

1. 在终端节点上，运用 DBveaver 东西办理 RDS Oracle。装备其衔接的 URL，用户名和口令

2. 将 ora-datadump 存储桶下 dump 目录中的一切文件下载到 DATA_PUMP_DIR 目录

SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
      p_bucket_name    =>  'ora-datadump', 
      p_s3_prefix      =>  'dump/', 
      p_directory_name =>  'DATA_PUMP_DIR') 
   AS TASK_ID FROM DUAL;

3. 经过显现使命的输出文件来检查成果

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime;

4. 运用 rdsadmin.rds_file_util.read_text_file 存储进程检查 bdump 文件的内容

SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-1656464372310-35.log'));

5. 从导入的转储文件中还原架构和数据

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => ‘IMPORT’, job_mode => ‘SCHEMA‘, job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘DPADMIN.DMP’, directory => ‘DATA_PUMP_DIR‘, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/

6. 查询表是否已导入

6. 创立 DMS RDS 终端节点和仿制实例

6.1 创立终端节点

1. 创立终端节点 RDS Oracle，只需挑选已有的实例即可

2. 装备终端节点 RDS Oracle 的用户名和口令

3. 创立终端节点为 s3

4. 装备终端节点 s3 的存储桶名和文件夹

5. 装备终端节点 s3 参数，运用以下额外衔接特点来指定输出文件的 Parquet 版别：

parquetVersion=PARQUET_2_0;

6.2 创立仿制实例

1. 创立仿制实例，命名并挑选实例类型

2. 装备仿制实例

6.3 创立并履行搬迁使命

1. 创立数据库搬迁使命，命名标识符，挑选创立的仿制实例，挑选源和方针终端节点

2. 装备数据库搬迁使命，挑选向导形式

3. 装备数据库搬迁使命表映像，架构名称为%，表名称为之前 Oracle 数据库创立的表 DEP；挑选“创立使命”

4. 调查数据库搬迁使命状况，从“正在运转”到“加载完结”

5. 检查 s3 终端节点的目录，能够看到生成 parquet 文件

7. 运用 Athena 剖析 Oracle Expdp 导出数据

7.1 Athena 操作进程

1. 先设置一下 Athena 查询成果的寄存方位，挑选 s3 存储桶的路径

2. 在 Default 数据库下创立表 dep，CREATE TABLE 语句必须包括分区详细信息，运用 LOCATION 指定分区数据的根方位，运转以下内容进行查询

CREATE EXTERNAL TABLE IF NOT EXISTS `default`.`dep` (
`dep_id` int,
`dep_name` string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1'
) 
LOCATION 's3://ora-datadump/output/expdp/DPADMIN/DEP/'

3. 查询 dep 表的成果

8. 计划二 运用 Oracle Exp 导出数据

受限于 Oracle 版别和运用习惯，许多用户还在运用 Exp/Imp，其更适合用于数据量较小且不需求 BINARY_FLOAT 和 BINARY_DOUBLE 等数据类型。导入进程会创立架构方针，十分适合用于包括小型表的数据库。

上述 RDS Oracle DBMS_DATAPUMP 仅适用于 Oracle Datadump 导出的 expdp 文件，而运用 Exp/Imp 东西导出的二进制文件无法直接导入 RDS Oracle。需求新建一台 EC2，装置 Oracle DB，Imp 导入二进制文件。

8.1 预期方针

1. 在本地数据库新建一张表，并经过 Exp 将该用户下的一切表导出
2. 将 dmp 文件上传到 s3 存储桶
3. 发动一台 EC2 Windows，并装置 Oracle 19c，装置进程请参阅 Oracle Database Installation
4. 将dmp文件导入到 EC2 Oracle，并能查询到表
5. 运用 DMS 将数据导出 Parquet 格局存在 s3，并能查询到表
6. 在 Athena 中查询表

9. 导出数据并上传到 s3 存储桶

1. EXP 导出数据和日志

exp dpadmin/oracle@orcl file=C:\app\oracle\oradata\exp\exp0628.dmp log=C:\app\oracle\oradata\exp\exp0628.log owner=(dpadmin)

2. 将日志上传到 s3 存储桶对应的文件夹

3. 在云上 EC2 Windows 上下载 Amazon CLI 客户端

C:\Users\Administrator>msiexec.exe /i https://amazoncli.amazonaws.com/AmazonCLIV2.msi
C:\Users\Administrator>amazon --version
amazon-cli/2.7.14 Python/3.9.11 Windows/10 exec-env/EC2 exe/AMD64 prompt/off

4. 选中 EC2 Windows，挑选 操作–>实例设置 –> 附加/替换 IAM 人物，挑选创立好的 S3_full_access Role（附加能够拜访对应 s3 存储桶的策略）

5. 运用 Amazon CLI 同步本地和 s3 存储桶的文件，将 Exp 导出的数据上传到 s3

C:\aws s3 sync s3://ora-datadump/expdump/ C:\app\oracle\admin\orcl\dpdump\exp

10. 运用 EC2 Oracle Imp 导入数据

1. 在测试用的 EC2 Oracle DB，先将 dep 表删除

2. 再将数据导入 orcl 数据库中，指定用户名和表名

imp dpadmin/oracle@orcl file=C:\app\oracle\oradata\exp\exp0628.dmp fromuser=dpadmin tables=(dep)

3. 验证导入的表

11. 创立 DMS 终端节点

11.1 创立终端节点

1. 创立终端节点 EC2 Oracle

2. 测试终端节点 EC2 Oracle 连通性

3.创立终端节点 s3

4. 装备终端节点 s3 参数，运用以下额外衔接特点来指定输出文件的 Parquet 版别：

parquetVersion=PARQUET_2_0;

11.2 创立并履行搬迁使命

1. 创立数据库搬迁使命，命名“使命标识符”，延用之前的仿制实例，挑选源和方针终端节点

2. 装备数据库搬迁使命，挑选向导形式

3. 装备数据库搬迁使命“表映像”，架构名称为%，表名称为之前创立的 DEP

4. 检查数据库搬迁使命状况

5. 检查 s3 终端节点的目录，能够看到生成 parquet 文件

12. 运用 Athena 剖析 Oracle Exp 导出数据

12.1 Athena 操作进程

1. 创立库 exp

2. CREATE TABLE 语句必须包括分区详细信息，运用 LOCATION 指定分区数据的根方位，运转以下内容并进行查询

CREATE EXTERNAL TABLE IF NOT EXISTS `exp`.`dep` (
`dep_id` int,
`dep_name` string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1'
) 
LOCATION 's3://ora-datadump/output/exp/DPADMIN/DEP/'

3. 查询 dep 表的成果

13. 总结

本文评论的是在混合云架构下将本地 Oracle 数据库数据上传到云上，运用云上的大数据东西进行剖析，这只是亚马逊云科技数据湖的一个运用场景。数据湖是由多个大数据组件和云服务组成的一个解决计划，能够存储结构化数据（如关系型数据库中的表），半结构化数据（如CSV、JSON)，非结构化数据（如文档、PDF）和二进制数据（如图片、音视频)。经过数据湖能够快速地存储、处理、剖析海量的数据，一起在安全合规的场景下运用多种多样的手法进行剖析。

14. 参阅资料

[1]运用 Amazon DMS 以 Parquet 格局将数据搬迁到 Amazon S3

[2]Amazon RDS for Oracle 与 Amazon S3集成

[3]运用 Oracle 数据泵将本地 Oracle 数据库搬迁到适用 Amazon RDS for Oracle

[4]运用 Oracle Data Pump 导入

[5]将数据导入 Amazon RDS 数据库实例

本篇作者

缪翰林

亚马逊云科技解决计划架构师，担任基于亚马逊云科技的计划咨询，设计和架构评估。在运维，DevOps 方面有丰厚的经历，现在侧重于大数据领域的研讨。

文章来历：dev.amazoncloud.cn/column/arti…