如何快速实现常见的报表分析-阿里云帮助中心

本文介绍基于数据开发功能，快速实现常见的报表分析诉求的操作步骤。

背景信息

说明如下最佳实践基于数仓开发（老）功能进行操作，但该功能已下线，不适用新版数仓开发功能。详细信息，请参见【通知】下线任务编排（老）和数仓开发（老）功能。

逐步成长的企业，离不开报表的需求，常见的报表场景有：

向老板汇报业务的运营状况、业绩。
数据分析师按不同维度分析业务场景，挖掘业务的增长点。
业务系统统计业务的汇总情况，如电商系统统计每日营收额，物流系统统计每日运单量等。

目前，经过多年的“大数据”洗脑，以上的需求往往被分发到大数据平台上做处理。但是如今，利用DMS中的数仓开发一样可以完成。

功能优势

使用DMS中的数仓开发做报表开发有如下优势：

线上线下统一管理。即业务库和分析库都在DMS中管理，无需另外跳转到大数据平台。
灵活切换分析引擎。根据不同的业务数据规模，自由选择DMS支持的数据库作为分析库，从小规模GB级的RDS到大规模PB级的ADB足以满足大部分场景的分析。
权限统一、数据安全。

准备工作

准备分析的业务库（这里采用TPCH的样例数据举例）。
通过权限申请模块，申请业务库的导出权限、分析库（这里采用RDS）的变更权限，字典信息需要查询权限。
创建任务流tpch_report。

步骤一：同步业务库的表到分析库

以使用跨库Spark SQL任务实现同步业务库的表到分析库操作举例。跨库的更多信息，请参见跨库查询。

创建跨库Spark SQL任务

登录数据管理DMS 5.0。
在顶部菜单栏中，选择集成与开发（DTS） > 数据开发 > 数仓开发（老）。
在数仓开发首页选择目标项目空间，单击进入空间。

说明若您还未创建项目空间，请创建数仓项目后再进行之后的操作。
创建跨库Spark SQL任务。
1. 在画布左侧任务类型列表中，拖拽跨库Spark SQL节点到画布空白区域。
2. 右键单击跨库Spark SQL节点，选择重命名，修改节点名称。
  此处修改为示例名称table_sync。
  说明单击画布空白区域，名称即可保存。
  节点即出现在画布中。
通过跨库Spark SQL拷贝数据。
1. 双击table_sync节点。
2. 选择引用的数据库，编写SQL语句。
  SQL编辑器中编写如下跨库Spark SQL语句：使用CREATE TABLE IF NOT EXISTS在分析库中创建test_orders表（包含o_orderkey、o_custkey、o_orderstatus、o_totalprice、o_orderdate、o_orderpriority、o_clerk、o_shippriority、o_comment等字段），然后使用INSERT INTO ... SELECT从业务库中拷贝对应字段的数据，WHERE条件通过${order_date}变量筛选指定日期的订单。
设置变量。
1. 单击节点配置页面右侧的变量设置。
2. 选择任务流常量页签，配置变量名及变量规则。
  右侧变量设置面板中选中任务流变量页签，将变量名设为order_date，变量规则的时间格式选择yyyy-MM-dd，偏移量设为减去1日（即取前一天的日期）。可通过底部增加变量按钮添加更多变量。

单击页面上方的预览，查看替换变量后的SQL逻辑是否正确。

CREATE TABLE IF NOT EXISTS dblink_28.company_analyse.orders (
  `o_orderkey` bigint,
  `o_custkey` bigint,
  `o_orderstatus` char(1),
  `o_totalprice` decimal(10,2),
  `o_orderdate` date,
  `o_orderpriority` char(15),
  `o_clerk` char(15),
  `o_shippriority` bigint,
  `o_comment` varchar(79)
);

INSERT INTO dblink_28.company_analyse.orders
SELECT o_orderkey,o_custkey,o_orderstatus,cast(o_totalprice as decimal(10,2)),o_orderdate,o_orderpriority,o_clerk,o_shippriority,o_comment
FROM hhhhh_46.company.orders
WHERE o_orderdate = '2019-09-20';

步骤二：在分析库做统计分析

统计每日订单的下单客户数、订单数、订单总额。

创建单实例SQL任务。
1. 在画布左侧任务类型列表中，拖拽单实例SQL到画布空白区域。
2. 右键单击单实例SQL，选择重命名，修改节点名称。
  
  此处修改的示例名称为daily_orders。节点出现在画布中，与已有的table_sync节点并列显示。

选择分析库，创建统计表daily_orders_summary，编写统计逻辑SQL。

说明编辑完成，系统自动保存。

/**统计结果表**/
CREATE TABLE IF NOT EXISTS daily_orders_summary (
    dt date not null,
    customer_count int not null default 0,
    order_count    int not null default 0,
    total_order_price decimal(10, 2) not null default 0,
    UNIQUE KEY `uk_dt` (`dt`)
);

/**清除当日统计，避免重跑时uk冲突**/
DELETE FROM daily_orders_summary WHERE dt = '${order_date}';

/**业务统计逻辑**/
INSERT INTO daily_orders_summary
SELECT o_orderdate, count(distinct(o_custkey)) as customer_count, count(*), sum(o_totalprice)
FROM orders
WHERE o_orderdate = '${order_date}'
GROUP BY o_orderdate;

联合lineitem表，统计每日订单的配件数、优惠总额、税总额。

在同步任务中加入lineitem表的同步的操作步骤，可参见步骤一：同步业务库的表到分析库。
参考daily_orders任务，完成相关统计逻辑。

步骤三：同步结果汇总表到业务库或BI库

使用跨库Spark SQL任务的操作步骤，可参见步骤一：同步业务库的表到分析库。

设置DAG图中的任务关系

将任务节点连接起来。

DAG图中包含4个任务节点：顶部为table_sync（数据同步），左侧为daily_orders（每日订单统计），右侧为daily_orders_lineitem（联合lineitem统计），底部为table_sync_back（结果回写）。依赖关系为：table_sync执行完成后，daily_orders和daily_orders_lineitem并行执行，两者均完成后再执行table_sync_back。

调度配置

在任务编辑页面下方，单击任务流信息。
在调度配置区域，打开开启调度开关，配置调度类型、生效时间等信息。
示例中，调度类型选择定时调度/周期调度，生效时间为1970-01-01至9999-01-01，调度周期选择日，具体时间设置为04:05，对应cron表达式00 05 04 * * ?。左侧基础属性中，任务流名称为跨库-数据报表，错误处理策略为完成运行中的任务，开发控制策略为跳过。

运行任务流并查看执行状态

运行任务流

在画布页面，单击上方的试运行，即可运行任务流，测试任务的正确性。

如果执行日志的最后一行出现status SUCCEEDED，表明任务试运行成功。
如果执行日志的最后一行出现status FAILED，表明任务试运行失败，在执行日志中查看执行失败的节点和原因，修改配置后重新尝试。

查看任务的执行状态

单击画布右上方前往运维，在任务流运维页面查看。

在任务流运维页面上方，查看任务流的创建时间、修改时间、调度配置情况等基本信息。
单击运行记录页签，选择调度触发或手动触发，查看任务流运行记录。
说明
- 调度触发：通过调度或指定时间的方式运行任务流。
- 手动触发：通过手动单击试运行的方式运行任务流。
- 单击状态列前的，查看任务流运行日志。
- 在操作列中，单击执行历史，查看任务流的操作时间、操作人员和操作内容。
- 在操作列中，对不同执行状态的任务流进行终止、重跑、暂停、恢复和置成功的操作。
  说明
  - 对于执行成功的任务流，可以进行重跑操作。
  - 对于执行失败的任务流，可以将该任务流运行记录的状态置为成功。
  - 对于执行中的任务流，可以终止或暂停任务流运行。
单击发布列表页签，查看任务流的版本ID，发布人，发布时间等信息。