使用 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，插入数据

1. 创建数据转储文件

DECLAREhdnl NUMBER;BEGINhdnl := 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);

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

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

RDS for Oracle 与 Amazon S3 集成，数据库实例必须可以访问 Amazon S3 存储桶。

4.1 首先要 RDS 中创建一个选项组，步骤如下

1. 在 RDS 选项组选择创建组

1. 命名并选择相应的引擎和版本

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

1. 创建完成后再次打开这个选项组，选择 Add option

1. 在 Option name 选择 S3_INTEGRATION，Version 选择 1.0，立即应用

4.3 然后将上述选项组应用到 RDS Oracle 中，步骤如下

1. 回到 RDS Oracle，选择 Configuration，可以看到现有的 Option Groups

1. 选择 Modify –> Configuration

3. 把 Option group 选择为刚才新建的 s3-integration-group

1. 修改 DB 实例，立即应用，该过程不需要重启实例

1. 在 Configuration –> Option groups 下查看当前选项组，变更完成

4.4 最后将创建好的访问 s3 存储桶的 Role 关联到 RDS，步骤如下

1. 在 IAM –> 角色下，创建 RDS Role，附加可以访问对应 s3 存储桶的策略

1. 在 RDS –> 数据库 –> Oracle –> Connectivity & security –> Manage IAM roles 下，选择创建的 RDS_access_s3 角色和 S3_INTEGRATION 功能，添加角色

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

1. 在终端节点上，使用 DBveaver 工具管理 RDS Oracle。配置其连接的 URL，用户名和口令

1. 将 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;

1. 通过显示任务的输出文件来查看结果

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

1. 使用 rdsadmin.rds_file_util.read_text_file 存储过程查看 bdump 文件的内容

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

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

DECLAREhdnl NUMBER;BEGINhdnl := 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;/

1. 查询表是否已导入

6. 创建 DMS RDS 终端节点和复制实例

6.1 创建终端节点

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

1. 配置终端节点 RDS Oracle 的用户名和口令

1. 创建终端节点为 s3

1. 配置终端节点 s3 的存储桶名和文件夹

1. 配置终端节点 s3 参数，使用以下额外连接属性来指定输出文件的 Parquet 版本：

parquetVersion=PARQUET_2_0;

6.2 创建复制实例

1. 创建复制实例，命名并选择实例类型

1. 配置复制实例

6.3 创建并执行迁移任务

1. 创建数据库迁移任务，命名标识符，选择创建的复制实例，选择源和目标终端节点

1. 配置数据库迁移任务，选择向导模式

1. 配置数据库迁移任务表映像，架构名称为 %，表名称为之前 Oracle 数据库创建的表 DEP；选择“创建任务”

1. 观察数据库迁移任务状态，从“正在运行”到“加载完成”

1. 查看 s3 终端节点的目录，可以看到生成 parquet 文件

7. 使用 Athena 分析 Oracle Expdp 导出数据

7.1 Athena 操作步骤

1. 先设置一下 Athena 查询结果的存放位置，选择 s3 存储桶的路径

1. 在 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/'

1. 查询 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)

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

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

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

1. 选中 EC2 Windows，选择 操作–>实例设置 –> 附加/替换 IAM 角色，选择创建好的 S3_full_access Role（附加可以访问对应 s3 存储桶的策略）

1. 使用 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 表删除

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

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

1. 验证导入的表

11. 创建 DMS 终端节点

11.1 创建终端节点

1. 创建终端节点 EC2 Oracle

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

3. 创建终端节点 s3

1. 配置终端节点 s3 参数，使用以下额外连接属性来指定输出文件的 Parquet 版本：

parquetVersion=PARQUET_2_0;

11.2 创建并执行迁移任务

1. 创建数据库迁移任务，命名“任务标识符”，延用之前的复制实例，选择源和目标终端节点

1. 配置数据库迁移任务，选择向导模式

1. 配置数据库迁移任务“表映像”，架构名称为 %，表名称为之前创建的 DEP

1. 查看数据库迁移任务状态

1. 查看 s3 终端节点的目录，可以看到生成 parquet 文件

12. 使用 Athena 分析 Oracle Exp 导出数据

12.1 Athena 操作步骤

1. 创建库 exp

1. 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/'

1. 查询 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 方面有丰富的经验，目前侧重于大数据领域的研究。

文章来源：https://dev.amazoncloud.cn/column/article/6309b67576658473a321ffce