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

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

时间：2023-03-17 11:48:30浏览次数：69  
标签：存储 RDS Presto s3 数据库 Oracle 数据 Athena

在传统企业客户，无论是前台的交易数据库还是后台的数据仓库，都会选择使用 Oracle，它具备非常广泛的技术资料、社区资源和问题处理案例（各种踩坑的经验）；同时它还有广泛的用户基础，很多企业的技术栈都是围绕 Oracle 数据库构建开发和运维工作，保障业务的使用。比如金融行业的 Oracle 数据库主要业务场景会涉及到账务、资金和资产中心。

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

 

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

 

 

 

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

image.png

 

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

image.png

 

Amazon Athena 是一种基于 Presto 的交互式查询服务，使用标准 SQL 直接分析 Amazon S3 中的数据。数据科学家和数据工程师经常会使用 Athena 进行数据分析和交互式查询。Athena 是无服务器服务，可以自动扩展并执行并行查询，没有维护基础设施的工作，用户按照查询的数据量付费。

image.png

 

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

image.png

 

但受本地条件限制，很多用户是用 Oracle Datapump 或者 Exp/Imp 将数据导出后，再将本地数据传输到 s3 存储桶。本文通过阐述使用上述两种不同的工具，介绍如何将数据导入 s3 存储桶，使用 Athena 做数据分析。

 

1. 方案概述

方案一，使用 Oracle 数据泵将本地 Oracle 数据库数据导出，上传到s3存储桶，再由适用的 Amazon RDS for Oracle 加载数据，利用 DMS服务将 RDS Oracle 数据库数据迁移到 s3 存储桶，使用云上的大数据组件 Athena 进行分析。

image.png

 

方案二，使用 Oracle Exp 将本地 Oracle 数据库数据导出，上传到 s3 存储桶，再在云上 EC2 安装 Oracle 对应版本的数据库，Imp 数据到 EC2 Oracle，使用 DMS 服务将 EC2 Oracle 数据库数据迁移到 s3 存储桶，利用云上的大数据组件 Athena 进行分析。

image.png

 

 

2. 使用的资源

image.png

 

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 中查询表

image.png

 

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

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

image.png

 

  1. 创建数据转储文件
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);

　　

 

 

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

image.png

 

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

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

 

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

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

image.png

 

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

image.png

 

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

 

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

image.png

 

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

image.png

 

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

 

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

image.png

 

  1. 选择 Modify –> Configuration

image.png

 

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

image.png

 

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

image.png

 

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

image.png

 

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

 

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

image.png

 

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

image.png

 

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

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

image.png

 

  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. 从导入的转储文件中还原架构和数据
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;
/

　　

 

 

  1. 查询表是否已导入

image.png

 

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

6.1 创建终端节点

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

image.png

 

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

image.png

 

  1. 创建终端节点为 s3

image.png

 

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

image.png

 

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

parquetVersion=PARQUET_2_0;

image.png

 

6.2 创建复制实例

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

image.png

 

 

  1. 配置复制实例

image.png

 

 

6.3 创建并执行迁移任务

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

image.png

 

 

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

image.png

 

 

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

image.png

 

 

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

image.png

 

 

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

image.png

 

 

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

7.1 Athena 操作步骤

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

image.png

 

  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 表的结果

image.png

 

 

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 中查询表

image.png

 

 

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 存储桶对应的文件夹

image.png

 

  1. 在云上 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

　　

 

 

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

image.pngimage.png

 

  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 表删除

image.png

 

  1. 再将数据导入 orcl 数据库中，指定用户名和表名
imp dpadmin/oracle@orcl file=C:\app\oracle\oradata\exp\exp0628.dmp fromuser=dpadmin tables=(dep)

　　

 

 

  1. 验证导入的表

image.png

 

 

11. 创建 DMS 终端节点

11.1 创建终端节点

  1. 创建终端节点 EC2 Oracle

image.png

 

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

image.png

 

3.创建终端节点 s3

image.png

 

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

　　

 

 

11.2 创建并执行迁移任务

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

image.png

 

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

image.png

 

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

image.png

 

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

image.png

 

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

image.png

 

 

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

12.1 Athena 操作步骤

  1. 创建库 exp

image.png

 

  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 表的结果

image.png

 

 

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 数据库实例

 

本篇作者

image.png

缪翰林

亚马逊云科技解决方案架构师，负责基于亚马逊云科技的方案咨询，设计和架构评估。在运维，DevOps 方面有丰富的经验，目前侧重于大数据领域的研究。

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

标签：存储,RDS,Presto,s3,数据库,Oracle,数据,Athena
From： https://www.cnblogs.com/AmazonwebService/p/17226087.html

相关文章

  • oracle 插入语句可能出现的错误
    >ORA-00984:columnnotallowedherevalues里不能出现字段，不能使用双引号   >ORA-00917:missingcomma      如果在linux中sqlplus执行明明使用......
  • oracle,mysql,sqlserver的in的个数限制
    sqlwherein()里面的个数限制:1、oracleOracle9i中个数不能超过256,Oracle10g个数不能超过10002、sqlserver中个数最大只能到5w3、mysql没有限制，只限制了整......
  • Oracle SQL Developer 提示无法安装某些模块错误
    如果博友遇到启动时弹出警告--无法安装某些模块（Warning-couldnotinstallsomemodules）:之后是一大堆的包解决方法：把C:\Users\${你自己的用户名}\AppData\Roaming\S......
  • oracle语句单据查询很快，使用union all 后很慢处理
    2023年3月16日10:19:39 某客户现场，开发人员添加了一汇总报表，使用unionall进行了语句链接，单独执行语句均没有问题，但是使用unionall后执行速度特别慢语句类似select......
  • oracle的sid
    oracle的sid什么是oracle?平常所说的Oracle或Oracle数据库指的是Oracle 数据库管理系统.Oracle数据库管理系统是管理数据库访问的计算机软件(Oracledatabaseman......
  • 玩转oracle 10 g实战教程
    玩转oracle10g实战教程主讲：韩顺平email：[email protected]第1讲0.引言1.为什么要学习oracle2.oracle的安装,启动及卸载√3.介绍ora......
  • oracle删除alert_orcl.log
    数据库正在运行着，如何清理太大的alert.log文件？不能用mv方法.官方建议：HowDoYouReduceTheSizeOfAnAlertLogThatIsTooLargeToEditwhiletheDBisrunning?(......
  • oracle vm 的nat，主机直接访问内网，forward地址转发
     使用oracle的vm7版本内部虚拟化主机的ip，为10.0.2.4通过主机ip的90口，访问内部的80口作映射通过主机22口映射，为内部的22口，用来登录ssh  作转发规则  ......
  • oracle怎么增加表空间大小
    转至：https://blog.csdn.net/m0_54434140/article/details/126536817第一步：查看表空间的名字及文件所在位置：selecttablespace_name,file_id,file_name,round(bytes/(......
  • Oracle 删除表空间回收磁盘空间
    转至：https://blog.csdn.net/catoop/article/details/1287981321.查询表空间和表空间数据库文件路径SQL>select*fromdba_data_files;2.删除表空间SQL>droptabl......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99