DMS周期从RDS加载数据到OSS -

背景信息

说明如下内容以任务编排（老）功能操作示例。任务编排（老）功能已下线，详细信息，请参见【通知】下线任务编排（老）和数仓开发（老）功能。

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

DMS数据开发使用场景

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

离线数据：T+1报表。
实时数据：五分钟实时报表。
智能数据：直接对接AI计算等框架。
冷数据：冷数据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的表结构如下：

create table orders(
  order_id bigint,
  product_name varchar(32),
  price double,
  total_amount double,
  created_date date
);

demo_schema建立DLA-SQL如下：

CREATE DATABASE demo_schema
WITH DBPROPERTIES (
    catalog = 'oss',
    location = 'oss://xxxxxx/dla_demo/'
)

准备工作

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

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

实现任务流

建立新任务流。

在DMS中的数据工厂/任务编排里，建立一个新的任务流：rds_data_to_oss。
创建DLA-SQL任务节点。
在任务流rds_data_to_oss中，依次建立三个DLA-SQL任务节点：
1. 创建RDS同步schema：在DLA中创建scheme mapping到RDS。
2. 创建OSS备份表：在DLA-OSS建立备份表，用于存储历史数据。
3. 备份数据：实现前一天数据的备份。
实现创建RDS同步Schema节点。
创建RDS同步Schema节点用于创建一个指向RDS的scheme: dla_mysql_rds。目标数据库为DLA中的demo_schema。建立dla_mysql_rds可以实现DLA直接中RDS中读取数据。
```
CREATE SCHEMA if not exists dla_mysql_rds WITH DBPROPERTIES (
   CATALOG = 'mysql', 
   LOCATION = 'jdbc:mysql://xxxxxx.rds.aliyuncs.com:3306/dmstest',
   USER = 'dmstest',
   PASSWORD = 'xxxxxxxxx',
   INSTANCE_ID = 'xxxxxxxxx',
   VPC_ID = 'xxxxxxxxx'
 );

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

实现创建OSS备份表节点。

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

CREATE EXTERNAL TABLE oss_orders (
    order_id bigint,
    product_name varchar(32),
    price double,
    total_amount double,
    created_date date)
PARTITIONED BY (y string, m string, d string)
STORED AS TEXTFILE
LOCATION 'oss://xxxxxx/dla_demo/';

实现数据备份节点。
数据备份节点需要配置时间变量，编写备份SQL以及选择目标数据库为DLA的demo_schema。
- 配置时间变量。
  配置三个时间变量，它们分别是：
  - year：当前日期前一天的年份（格式为yyyy）
  - month：当前日期前一天的月份（格式为MM）
  - day：当前日期前一天的日（格式为dd）
- 数据备份步骤。
  数据备份节点实现步骤如下：
  - 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语句。
```
/* 创建临时表 */
CREATE EXTERNAL TABLE oss_orders_tmp (
    order_id bigint,
    product_name varchar(32),
    price double,
    total_amount double,
    created_date date) 
STORED AS TEXTFILE 
LOCATION 'oss://xxxxxx/dla_demo/y=${year}/m=${month}/d=${day}' 
TBLPROPERTIES('auto.create.location'= 'true');

/* 备份日数据 */
insert into oss_orders_tmp
SELECT * FROM mysql_rds_to_oss.orders 
where DATE_FORMAT(created_date, '%Y') = '${year}' and 
      DATE_FORMAT(created_date, '%m') = '${month}' and 
      DATE_FORMAT(created_date, '%d') = '${day}';

/* 更新备份表分区信息以及删除临时表 */
msck repair table oss_orders;
drop table oss_orders_tmp;
```
运行任务流。

点击左上角的试运行，运行任务流，在SQLConsole中查询DLA-OSS中的备份表oss_orders。
周期调度配置。

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

注意事项

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

总结

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

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