DMS日周月报表生成 - 数据管理 DMS

本文介绍DMS数据开发如何实现日周月数据报表（T+1数据报表）的生成，以及如何实现从RDS迁移数据到ADB、数据汇总的步骤。

背景信息

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

DMS数据开发使用场景

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

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

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中的数据做日汇总操作，并且存储到adb_db.adb_summary_table，接着根据adb_db.adb_summary_table的数据做周/月汇总操作。每天5点针对前一天的数据进行迁移和汇总。rds_db.rds_table的表结构如下：

CREATE TABLE rds_table(    
  id INT,     
  price DECIMAL(10,2),     
  trx_time TIMESTAMP
);

准备工作

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

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

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

RDS的dblink：dblink_rds。
ADB的dblink：dblink_adb。

实现任务流

以下为实现从RDS迁移数据到ADB以及数据汇总的步骤。

建立新任务流。

在DMS中的数据工厂/任务编排里，建立一个新的任务流：daily_weekly_monthly_report。
创建任务节点。
在任务流rds_dsql_adb中，依次建立五个任务节点：
1. ADB3.0-创建目标表（单实例SQL任务节点）：在ADB中创建两张表存储来RDS中的数据以及汇总数据。
2. DSQL-迁移数据（跨库SQL任务节点）：迁移RDS前一天数据到ADB中。
3. ADB3.0-按天汇总（单实例SQL任务节点）：汇总ADB中前一天的数据。
4. ADB3.0-按周汇总（单实例SQL任务节点）：汇总ADB中前一周的数据。
5. ADB3.0-按月汇总（单实例SQL任务节点）：汇总ADB中前一个月的数据。

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

ADB3.0-创建目标表任务节点是单实例SQL任务节点，其在ADB中创建四张表：adb_detail_table、adb_summary_table、adb_weekly_table以及adb_monthly_table，目标数据库为adb_db。adb_detail_table存储来自RDS的数据，adb_summary_table存储日汇总数据，adb_weekly_table存储周汇总数据，adb_monthly_table存储月汇总数据。

CREATE TABLE IF NOT EXISTS `adb_detail_table` (
  `dt` DATE, `id` INT, `price` DECIMAL(10,2), `trx_time` datetime
);

CREATE TABLE IF NOT EXISTS `adb_summary_table` (
  `dt` DATE, `total` DECIMAL(1000,2)
);

CREATE TABLE IF NOT EXISTS `adb_weekly_table` (
  `weekstart` DATE, `weekend` DATE, `total` DECIMAL(1000,2)
);

CREATE TABLE IF NOT EXISTS `adb_monthly_table` (
  `monthdate` DATE, `total` DECIMAL(1000,2)
);

创建DSQL-迁移数据任务节点。
DSQL-迁移数据任务节点是一个跨库SQL任务节点，实现该任务节点需要先配置时间变量，然后使用跨库SQL实现数据迁移。
- 配置时间变量。
  需要两个时间变量，它们分别是：
  - bizdate：当前日期前一天的日期（为系统默认变量）。
  - today：当前日期。
- 数据迁移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中。
```
INSERT INTO `dblink_adb`.`adb_db`.`adb_detail_table` (`dt`, `id`, `price`, `trx_time`) 
    SELECT '${bizdate}', `id`, `price`, `trx_time` 
  FROM `dblink_rds`.`rds_db`.`rds_table`
    WHERE `trx_time` >= '${bizdate}' 
      AND `trx_time` < '${today}';
```
创建ADB3.0-按天汇总任务节点。
ADB3.0-按天汇总任务节点使用bizdate变量从adb_db.adb_detail_table读取前一天的数据，然后做汇总操作，最后将汇总数据写入adb_db.adb_summary_table。该节点的目标数据库为ADB的adb_db。
```
INSERT INTO `adb_summary_table` (`dt`, `total`)
    SELECT `dt`, sum(`price`) AS total 
    FROM `adb_detail_table` 
  WHERE `dt` = '${bizdate}' 
  GROUP BY `dt`;
```
创建ADB3.0-按周汇总任务节点。
ADB3.0-按周汇总任务节点是一个单实例SQL任务节点，实现该任务节点需要先配置时间变量，然后使用时间变量和SQL实现按周汇总数据。
- 配置时间变量。
  需要三个时间变量，它们分别是：
  - bizdate：当前日期前一天的日期（为系统默认变量）。
  - today：当前日期。
  - weekstart: 当前日期前七天（bizdate~6天）。
- 按周汇总SQL。
  使用时间变量bizdate、today以及weekstart变量，实现从adb_summary_table中读取当前日期一周前的数据。同时通过判断today是不是星期一，来决定是否读取数据，从而实现按周汇总数据报表只在星期一时运行。
```
INSERT INTO `adb_weekly_table` (`weekstart`, `weekend`, `total`)
  SELECT *
  FROM(
    SELECT '${weekstart}', '${bizdate}', sum(`total`) AS total 
    FROM `adb_summary_table` 
    WHERE `dt` < '${today}' and 
          `dt` >= '${weekstart}' and
           date_format('${today}','%w') = 1) t
  WHERE t.total IS NOT null;
```
创建ADB3.0-按月汇总任务节点。
ADB3.0-按月汇总任务节点是一个单实例SQL任务节点，实现该任务节点需要先配置时间变量，然后使用时间变量和SQL实现按月汇总数据。
- 配置时间变量。
  需要三个时间变量，它们分别是：
  - bizdate：当前日期前一天的日期（为系统默认变量）。
  - today：当前日期。
  - monthdate：当前日期前一天所在的年月。
  - monthstart: 当前日期前一天所在月第一天的日期。
- 按月汇总SQL。
  使用时间变量bizdate、today、monthdate以及monthstart变量，实现从adb_summary_table中读取当前日期一个月前的数据。同时通过判断today是不是月份的第一天，来决定是否读取数据，从而实现按月汇总数据报表只在一月的第一天运行。
```
INSERT INTO `adb_monthly_table` (`monthdate`, `total`)
  SELECT *
  FROM(
    SELECT '${monthdate}', sum(`total`) AS total 
    FROM `adb_summary_table` 
    WHERE `dt` < '${today}' and 
          `dt` >= '${monthstart}' and
           date_format('${today}','%d') = 1) t
  WHERE t.total IS NOT null;
```
运行任务流。

点击左上角的试运行，运行任务流，在SQLConsole中查询ADB中adb_db的表adb_summary_table、adb_weekly_table以及adb_monthly_table。
周期调度配置。

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

注意事项

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的详细信息。