关于数据库:使用-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 方面有丰盛的教训，目前侧重于大数据畛域的钻研。

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