全部产品
云市场
云游戏

DMS周期从RDS加载数据到OSS

更新时间:2020-07-27 17:52:19

背景知识

DMS是阿里云提供的数据操作、数据安全管理以及数据开发的web服务平台。DMS提供数据库客户端的功能,支持多种数据源(MySQL,PostgreSQL,SQLServer,Oracle,Redis以及MongoDB等),实现统一的权限管理,支持数据库稳定的变更,同时集成了数据开发功能(包括数仓开发模式和任务编排模式)。1

DMS数据开发使用场景

DMS的数据开发能够用于多种场景,包括:

  1. 离线数据:T+1报表
  2. 实时数据:五分钟实时报表
  3. 智能数据:直接对接AI计算等框架
  4. 冷数据:冷数据OSS定期备份
  5. 事务数据:大批量数据定期删除、更新

2本文档将介绍DMS数据开发如何实现冷数据(过期历史数据)定期备份到OSS。

DMS冷数据(过期历史数据)备份

出于系统稳定性,性能以及成本的考虑,RDS中的数据都有生命周期,冷数据以及过期历史数据需要定期从RDS中移除。这些冷数据以及过期历史数据可能在未来其他场景中应用到,比如:历史记录查询,长期数据挖掘等等,不能直接被删除,所以需要将这些数据存储到更加便宜的介质中。阿里云的DLA-OSS提供了类SQL的写入/读取语言,使用低成本的存储OSS,可以实现这类历史数据、冷数据的存储。

DMS提供的数据库管理能力能够同时管理RDS以及DLA-OSS,一站式地实现从RDS备份数据到DLA-OSS中。同时,DMS提供的任务编排功能,能够定期备份RDS中的冷数据到DLA-OSS。

案例背景

本文档提供一个具体案例,该案例将RDS中一个表orders中的数据,按天备份数据到DLA-OSS中的demo_schema。每天5点备份前一天的数据。orders的表结构如下:

  1. create table orders(
  2. order_id bigint,
  3. product_name varchar(32),
  4. price double,
  5. total_amount double,
  6. created_date date
  7. );

demo_schema建立DLA-SQL如下:

  1. CREATE DATABASE demo_schema
  2. WITH DBPROPERTIES (
  3. catalog = 'oss',
  4. location = 'oss://xxxxxx/dla_demo/'
  5. )

3

准备工作

首先,我们需要在orders表中准备一些数据,这些数据通过SQLConsole直接插入:

  1. insert into orders values(1, 'product1', 1.0, 10.9, date_add(curdate(), interval -1 day));
  2. insert into orders values(2, 'product1', 2.0, 20.9, date_add(curdate(), interval -1 day));
  3. insert into orders values(3, 'product1', 3.0, 30.9, date_add(curdate(), interval -1 day));
  4. insert into orders values(4, 'product1', 4.0, 40.9, date_add(curdate(), interval -1 day));
  5. insert into orders values(5, 'product1', 5.0, 50.9, curdate());
  6. insert into orders values(6, 'product1', 6.0, 60.9, curdate());
  7. insert into orders values(7, 'product1', 7.0, 70.9, curdate());
  8. insert into orders values(8, 'product1', 8.0, 80.9, curdate());

4

实现任务流

本章节介绍如何实现从RDS备份历史数据到OSS的步骤。

建立新任务流

在DMS中的数据工厂/任务编排里,建立一个新的任务流:rds_data_to_oss。5

创建DLA-SQL任务节点

在任务流rds_data_to_oss中,依次建立三个DLA-SQL任务节点:

  1. 创建RDS同步schema:在DLA中创建scheme mapping到RDS。
  2. 创建OSS备份表:在DLA-OSS建立备份表,用于存储历史数据。
  3. 备份数据:实现前一天数据的备份。

6

实现创建RDS同步Schema节点

创建RDS同步Schema节点用于创建一个指向RDS的scheme: dla_mysql_rds。目标数据库为DLA中的demo_schema。建立dla_mysql_rds可以实现DLA直接中RDS中读取数据。

  1. CREATE SCHEMA if not exists dla_mysql_rds WITH DBPROPERTIES (
  2. CATALOG = 'mysql',
  3. LOCATION = 'jdbc:mysql://xxxxxx.rds.aliyuncs.com:3306/dmstest',
  4. USER = 'dmstest',
  5. PASSWORD = 'xxxxxxxxx',
  6. INSTANCE_ID = 'xxxxxxxxx',
  7. VPC_ID = 'xxxxxxxxx'
  8. );
  9. msck repair database dla_mysql_rds;

创建RDS同步Schema节点用于创建一个指向RDS的schema之前,需要将IP地址段100.104.0.0/16加入到RDS的白名单列表中。由于RDS实例位于VPC内,默认情况下DLA无法访问VPC中的资源。为了让DLA能访问RDS,需要利用VPC反向访问技术,即在RDS白名单中添加100.104.0.0/16 IP地址段。

7

实现创建OSS备份表节点

创建OSS备份表节点在DLA-OSS中创建存储来自RDS表orders中数据的备份表oss_orders,该表的结构与RDS中的orders表完全一致,oss_orders为分区表,按照年/月/日(y/m/d)分区:

  1. CREATE EXTERNAL TABLE oss_orders (
  2. order_id bigint,
  3. product_name varchar(32),
  4. price double,
  5. total_amount double,
  6. created_date date)
  7. PARTITIONED BY (y string, m string, d string)
  8. STORED AS TEXTFILE
  9. LOCATION 'oss://xxxxxx/dla_demo/';

8

实现数据备份节点

数据备份节点需要配置时间变量,编写备份SQL以及选择目标数据库为DLA的demo_schema。

配置时间变量

配置三个时间变量,它们分别是:

  • year:当前日期前一天的年份(格式为yyyy)
  • month:当前日期前一天的月份(格式为MM)
  • day:当前日期前一天的日(格式为dd)

9

数据备份步骤

数据备份节点实现步骤如下:

  • DLA OSS中创建临时表

临时表oss_orders_tmp映射位置为oss_orders所在OSS之下的年/月/日目录中,临时表自动成为oss_orders一个分区。
临时表与oss_orders结构一致。
临时表所在具体位置与当前日期有关。

  • 备份日数据

直接使用insert-select SQL语句从dla_mysql_rds.orders中读取数据,写入OSS中的临时表。

  • 更新分区信息以及删除临时表

更新oss_orders元数据信息,加载临时表数据到oss_orders。删除临时表oss_orders_tmp。

数据备份SQL语句

  1. /* 创建临时表 */
  2. CREATE EXTERNAL TABLE oss_orders_tmp (
  3. order_id bigint,
  4. product_name varchar(32),
  5. price double,
  6. total_amount double,
  7. created_date date)
  8. STORED AS TEXTFILE
  9. LOCATION 'oss://xxxxxx/dla_demo/y=${year}/m=${month}/d=${day}'
  10. TBLPROPERTIES('auto.create.location'= 'true');
  11. /* 备份日数据 */
  12. insert into oss_orders_tmp
  13. SELECT * FROM mysql_rds_to_oss.orders
  14. where DATE_FORMAT(created_date, '%Y') = '${year}' and
  15. DATE_FORMAT(created_date, '%m') = '${month}' and
  16. DATE_FORMAT(created_date, '%d') = '${day}';
  17. /* 更新备份表分区信息以及删除临时表 */
  18. msck repair table oss_orders;
  19. drop table oss_orders_tmp;

运行任务流

点击左上角的试运行,运行任务流,在SQLConsole中查询DLA-OSS中的备份表oss_orders。

10

周期调度配置

试运行确定任务流正确以后,点击任务流空白处,调出调度配置页面,设置每天凌晨5点定期调度运行该任务流。11

注意事项

  • RDS中的orders表,以及DLA OSS中的demo_schema需要事先准备好。
  • orders表中需要有一列或者多列记录数据插入的时间。
  • orders表数据插入在安全协同模式下,需要申请更改权限以及调整安全规则以允许在SQLConsole中运行。
  • 所有任务节点的SQL内容在安全协同模式下,需要申请权限以及调整安全规则以便其正常运行。

总结

本文档介绍了在DMS中从RDS中周期备份历史数据到DLA-OSS中用例的详细步骤,实现了RDS的过期历史数据周期备份的目标。该用例能够有效保障RDS的稳定性和性能,同时降低了数据存储成本,它体现了DMS在冷数据备份方面强大的能力。

正如前文介绍,DMS数据开发功能能够用于多种场景,OSS冷数据备份只是其中一个,点击DMS文档,将了解更多关于DMS的详细信息。