本文将介绍DMS数据开发如何实现从RDS备份历史数据到OSS的步骤。

背景信息

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

DMS数据开发使用场景

DMS的数据开发能够用于多种场景,包括:
  • 离线数据:T+1报表。
  • 实时数据:五分钟实时报表。
  • 智能数据:直接对接AI计算等框架。
  • 冷数据:冷数据OSS定期备份。
  • 事务数据:大批量数据定期删除、更新。
2

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/'
)
3

准备工作

首先,我们需要在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());
4

实现任务流

  1. 建立新任务流。
    在DMS中的数据工厂/任务编排里,建立一个新的任务流:rds_data_to_oss。5
  2. 创建DLA-SQL任务节点。

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

    1. 创建RDS同步schema:在DLA中创建scheme mapping到RDS。
    2. 创建OSS备份表:在DLA-OSS建立备份表,用于存储历史数据。
    3. 备份数据:实现前一天数据的备份。
    6
  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地址段。

    7
  4. 实现创建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/';
    8
  5. 实现数据备份节点。

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

    • 配置时间变量。
      配置三个时间变量,它们分别是:
      • year:当前日期前一天的年份(格式为yyyy)
      • month:当前日期前一天的月份(格式为MM)
      • day:当前日期前一天的日(格式为dd)
      8
    • 数据备份步骤。
      数据备份节点实现步骤如下:
      • 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;
  6. 运行任务流。

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

    运行任务流
  7. 周期调度配置。

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

    周期任务配置

注意事项

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

总结

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

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