全部产品
云市场

DMS数据迁移以及T+1报表生成

更新时间:2020-05-29 21:58:36

背景知识

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

1

DMS数据开发使用场景

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

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

2本文档将介绍DMS数据开发如何实现周期数据同步与数据报表(T+1数据报表)。

DMS周期数据同步与数据报表

如今由于数据量越来越大,也不再会出现一款数据库包揽所有功能的状况,数据库开始出现了两大类:OLTP和OLAP。OLTP数据库主要用于处理事务数据,如订单数据,这类数据库以阿里云的RDS for MySQL为代表;OLAP数据库主要用于分析数据,如日常分析报表生成,这类数据库以阿里云的ADB for MySQL为代表。在OLAP数据库进行数据分析之前,需要先将数据按照日期中从RDS for MySQL中同步到ADB for MySQL中,然后进行数据分析,生成数据报表。

DMS提供的数据库管理能力能够同时管理RDS for MySQL以及ADB for MySQL,一站式地实现从RDS for MySQL同步数据到ADB for MySQL和数据报表生成。同时,DMS提供的任务编排功能,能够定期实现数据同步功能和数据报表生成。

案例背景

本文档提供一个具体案例,该案例将RDS中一个表rds_db.rds_table中的数据,按天迁移到到ADB中的adb_db.adb_detail_table中,然后对adb_db.adb_detail_table中的数据做汇总操作。每天5点针对前一天的数据进行迁移和汇总。rds_db.rds_table的表结构如下:

  1. CREATE TABLE rds_table(
  2. id INT,
  3. price DECIMAL(10,2),
  4. trx_time TIMESTAMP
  5. );

3

准备工作

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

  1. insert into rds_table values(1, 10.9, date_add(now(), interval -1 day));
  2. insert into rds_table values(2, 20.9, date_add(now(), interval -1 day));
  3. insert into rds_table values(3, 30.9, date_add(now(), interval -1 day));
  4. insert into rds_table values(4, 40.9, date_add(now(), interval -1 day));
  5. insert into rds_table values(5, 50.9, now());
  6. insert into rds_table values(6, 60.9, now());
  7. insert into rds_table values(7, 70.9, now());
  8. insert into rds_table values(8, 80.9, now());

4另外,需要为RDS和ADB配置dblink。dblink配置在DMS实例列表中的编辑实例菜单中,选择高级信息。配置信息如下:

  • RDS的dblink:dblink_rds
  • ADB的dblink:dblink_adb

5

6

实现任务流

本章节介绍如何实现从RDS迁移数据到ADB以及数据汇总的步骤。

建立新任务流

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

创建任务节点

在任务流rds_dsql_adb中,依次建立三个任务节点:

  1. ADB3.0-创建目标表(单实例SQL任务节点):在ADB中创建两张表存储来RDS中的数据以及汇总数据
  2. DSQL-迁移数据(跨库SQL任务节点):迁移RDS前一天数据到ADB中
  3. ADB3.0-按天汇总(单实例SQL任务节点):汇总ADB中前一天的数据

8

创建ADB3.0-创建目标表任务节点

ADB3.0-创建目标表任务节点是单实例SQL任务节点,其在ADB中创建两张表:adb_detail_table和adb_summary_table,目标数据库为adb_db。adb_detail_table存储来自RDS的数据,adb_summary_table存储汇总数据。

  1. CREATE TABLE IF NOT EXISTS `adb_detail_table` (
  2. `dt` DATE,
  3. `id` INT,
  4. `price` DECIMAL(10,2),
  5. `trx_time` datetime
  6. );
  7. CREATE TABLE IF NOT EXISTS `adb_summary_table` (
  8. `dt` DATE,
  9. `total` DECIMAL(1000,2)
  10. );

9

创建DSQL-迁移数据任务节点

DSQL-迁移数据任务节点是一个跨库SQL任务节点,实现该任务节点需要先配置时间变量,然后使用跨库SQL实现数据迁移。

配置时间变量

需要两个时间变量,它们分别是:

  • bizdate:当前日期前一天的日期(为系统默认变量)
  • today:当前日期

10

数据迁移SQL

使用跨库SQL实现数据迁移,SQL语句从RDS(dblink_rds)中的rds_db.rds_table读取前一天的数据,然后写入ADB(dblink_adb)中的adb_db.adb_detail_table。选取前一天数据的方法是通过配置好的时间变量bizdate和today。每次运行时,只有trx_time在bizdate和today之前的数据才会被读取,然后写入ADB中。

  1. INSERT INTO `dblink_adb`.`adb_db`.`adb_detail_table` (`dt`, `id`, `price`, `trx_time`)
  2. SELECT '${bizdate}', `id`, `price`, `trx_time`
  3. FROM `dblink_rds`.`rds_db`.`rds_table`
  4. WHERE `trx_time` >= '${bizdate}'
  5. AND `trx_time` < '${today}';

11

创建ADB3.0-按天汇总任务节点

ADB3.0-按天汇总任务节点使用bizdate变量从adb_db.adb_detail_table读取前一天的数据,然后做汇总操作,最后将汇总数据写入adb_db.adb_summary_table。该节点的目标数据库为ADB的adb_db。

  1. INSERT INTO `adb_summary_table` (`dt`, `total`)
  2. SELECT `dt`, sum(`price`) AS total
  3. FROM `adb_detail_table`
  4. WHERE `dt` = '${bizdate}'
  5. GROUP BY `dt`;

12

运行任务流

点击左上角的试运行,运行任务流,在SQLConsole中查询ADB中adb_db的表adb_summary_table。13

周期调度配置

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

注意事项

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

总结

本文档介绍了在DMS中从RDS中周期迁移数据到ADB中并生成汇总报表用例的详细步骤。该用例能够定期自动生成RDS T+1数据报表,提高了RDS中数据的使用效率,节省用户的时间,它体现了DMS在数据迁移以及T+1报表方面强大的能力。

正如前文介绍,DMS数据开发功能能够用于多种场景,RDS数据迁移以及数据报表生成只是其中一个,点击DMS文档,将了解更多关于DMS的详细信息。