[Aliyun-Bigdata] 构建企业级数据分析平台 MaxComputer+DataWorks
ABC是一家销售公司,其客户可以通过电话、邮件或者网站下单订购该公司经营范围内的商品,并使用信用卡、银行卡、转账等方式付费。付费成功后,ABC公司会根据客户地址依据就近原则选择自己的货仓,指派合适的快递人员配送商品。
之前该公司规模较小,采取了传统公司的粗放式经营模式。随着业务的发展以及竞争的日益加剧,该公司意识到需要通过数据分析的方式提高竞争力。
您是ABC公司招聘来的第一个数据分析师,他们给你的title是CDO(首席数据官),上任后的要做的第一件事儿是帮助公司搭建一个数据分析系统,帮公司监控当前业务运转情况,让CEO能够轻松掌握公司的健康程度。CEO通过邮件表明了他最近关心的一些信息:
- 成交量和金额,以及最近的趋势是好是坏;
- 客户的变化情况;
- 库存,既不能断货,也不能滞压太多货、款
- 物流的效率
第二天,负责公司业务系统建设和运维的PM向你介绍了一下公司现在的业务系统状况,核心业务都运行在一个MySQL版的RDS上,丢给你了一张原有的系统ER图,并建议你不要自建系统,而直接使用阿里云的大数据平台(数加)去搭建数据分析平台。
你梳理了一下CEO的需求,快速规划了一个最终展现给他的草图,根据多年的经验,觉得这一张图基本上能回答了CEO的主要问题:
接下来需要做的是倒推要完成这些指标的展现,需要用到哪些业务系统的数据,通过PM提供的ER图,并实际去访问了一下业务系统的数据,你终于圈定了数据源:
- 订单表orders (成交量、成交金额)
- 客户表customers (保有量、新增量)
- 库存表stock、产品表dim_product (库存量、滞压资金)
- 派单表dispatch、配送表dilivery(派件数、送达及时率)
接着需要确定这些数据的获取方式(全量还是增量)、处理的时间粒度(多久处理一次)等,其中订单表、派单表、配送表均为增量获取,客户表、库存表、产品表均为全量获取。
非常明显,要做的任务分为二部分:
- 第一步,数据处理。首先进行数据同步,即将所需数据,从业务系统中同步到分析系统中(即标注为ODS的部分),接着进行数据加工:即将明细数据进行清洗、汇总,生成dw或者st数据。这两部分通常会被合称为数据ETL(Extract-Transform-Load)
- 第二步,数据展现。即把汇总数据通过图表等方式展现给CEO去看
要做的内容很明确了,工欲善其事必先利其器,花了一点时间了解数加平台的产品和功能,很快为上述流程确定了产品的选型:
首先,需要开通阿里云官网账号,然后,就可以把上述要做的内容完成就万事大吉了。
现在,所有要做的事儿都已经搞清楚了。在还没有其他数据分析师的情况下,只能靠CDO自己动手啦!
注意:实验首选chrome浏览器,其他浏览器在使用过程中可能会因为版本的原因有兼容性的问题;
本部分用户需要通过配置一个云数据库(RDS for MySQL),创建一个数据库abc_quickbi,然后使用该库模拟业务系统,即将附件中提供的6张表上传到该库中去。
具体步骤如下:
1. 在云中沙箱的实验目录中,点击 实验资源 小节,点击创建资源
【注】一旦开始创建资源,该实验就开始计时,并在到达实验规定的时长时,将自动结束实验并清除资源
2. 等待资源创建成功后,可在该页面看到类似于下图的信息,其中点击 前往控制台 按钮后将在一个新页面中连接到阿里云官网,登陆时需要的企业别名、子用户名称和密码需要在阿里云账号信息显示的内容中获取,实际动手的操作步骤会在此进行;另外,该数据库实例的地域为华东2(上海),这个信息实验中也会用到;
3. 点击复制控制台url,在浏览器隐身模式下或者换一浏览器登录(推荐chrome),在弹出的登陆页面中,依次填入对应的子用户名称 和 子用户密码,点击登录,进入阿里云官网的管理控制台。
4. 点击产品与服务,在下来菜单选中数据库,点击云数据库RDS版,进入RDS管理控制台首页。
5. 点击云数据库RDS,进入实例管理界面后,根据之前资源信息,选择同样的地域(本例地域为华东2)。由于在同一地域会创建多个RDS实例供不同实验账号使用,请在 实例名称 右侧的文本框中输入实验资源中的 子用户名称,将会找到分配给该账号的RDS实例,然后点击其右侧的管理:
6. 左侧导航栏中点击数据库管理,目前数据库列表为空,点击右侧的按钮创建数据库:
7. 输入数据库(DB)名称为abc_quickbi,点击确定,开始创建数据库,期间可以通过点击右上方的刷新按钮查看实时状态:
8. 点击左侧导航栏的账号管理,进入账号管理界面,点击创建账号
9. 填写账号配置信息,包括数据库账号为abc_user,密码为Abc_user123,重复输入一次确认密码,同时将数据库abc_quickbi的读写权限授权给该用户,点击确定,开始创建用户(普通用户)
10. 点击登录数据库,在DMS(数据库管理工具)页面填写相关登录信息后登陆数据库
填写DMS页的登录信息,(RDS实例链接地址在实验资源中有显示,默认填写完成),数据库的用户名和密码是刚刚创建的数据库账号及其密码,如下图所示。先进行测试连接,然后点击登录,进入数据库的管理页面,(注:DMS显示名可以自定义填写,出现设置白名单以及其他权限页面直接点击关闭即可)。
【注】首次使用DMS需要 激活授权 后,然后在RDS中重新点击 登录数据库。
11. 点击SQLConsole,选中单库查询,选择已登录数据库,打开SQL操作窗口
12. 在附件下载中下载data.zip文件到本地并解压,将解压的文件crt_src_tables.sql中的内容复制到SQL窗口,点击执行(或者使用快捷键F8),完成表的创建
13. 点击数据方案,选中数据导入,打开数据导入页面
14. 在导入页中选择 批量数据导入,点击选择数据库,上传解压的文件orders_dyn.sql,点击确定准备加载数据
点击执行变更,立即执行进行数据加载
导入完成后,进行下一步查询
15. 完成后回到SQL窗口,输入
select * from orders
查看结果,查看的主要项为:该表中是否已有数据,在显示的前100条记录中,是否有空值(null)等
16. 重复14和15两步,将剩余的5个文件逐个上传到该数据库中,包括:
customers_dyn.sql :对应的查询结果命令是select * from customers
dilivery_dyn.sql :对应的查询结果命令是select * from dilivery
dim_product.sql :对应的查询结果命令是select * from dim_product
dispatch_dyn.sql :对应的查询结果命令是select * from dispatch
stock_dyn.sql:对应的查询结果命令是select * from stock
【注】本实验中将会使用该数据库中的表作为报表分析的源数据,请务必完成此准备工作,方可进行后续步骤。
本小节主要内容:登陆DataWorks,开通DataWorks服务,并配置业务系统的数据源。
1. 登陆后进入管理控制台,依次点击大数据(数加)-> DataWorks,进入 大数据开发套件 概览页
2. 在DataWorks控制台的概览页,当前账号中已创建一个项目。通过如下步骤,配置业务系统的数据源。
1) 点击左侧栏工作空间列表,找到已创建的项目,点击 数据集成 。
2)进入数据集成界面后,点击左侧列表中的数据源,进入数据源配置页面,右侧面板中显示的即为当前该项目中已有的数据源。
【注意】odps_first为默认添加的数据源,它表示当前项目对应的MaxCompute的数据源,可简单的理解为当前项目对应的数据存储、计算服务。
3)点击页面右上角 新增数据源 -> MySQL,配置一个RDS的数据源,配置信息为实验资源中显示的RDS实例。
4)在弹出的对话框中,输入如下信息,并点击 测试连通性 ,成功后点击 确定 。
- 数据源类型:阿里云数据库(RDS)
- 数据源名称:rds_ebuzi_yq
- RDS实例ID:输入实验资源中已创建RDS实例的ID
- RDS实例购买者ID:输入实验资源中的企业别名
- 数据库名称:abc_quickbi
- 用户名:abc_user
- 密码:Abc_user123
-
5. 至此,准备工作完毕。
-
第 3 章:数据任务开发
-
3.1 建表
本章的主要内容:在分析系统中,建表,并配置、开发ETL的任务。本小节主要内容:在分析系统中,创建表。
1. 在DataWorks主页面上方找到数据开发的模块,点击 数据开发 。
2、点击【临时查询】里的新建节点,选择ODPS SQL
3. 给SQL指定名字create_tables,类型请选定ODPS SQL。完成后,点击 提交 。
4. 通过如下步骤,在脚本中,执行 create_tables.sql 。
1)从附件下载中,下载文件 create_tables.sql,并拷贝文件中的sql语句到DataIDE的 creat_tables 脚本文件中。
2)在 create_tables 文件上方菜单中,点击 保存 。完成后,点击 运行
3)等待一段时间,完成表创建。
5. 通过如下步骤,验证实验中使用的表是否全部创建完成。
1)点击【临时查询】里的新建节点,选择ODPS SQL
2)在test脚本文件中,执行如下命令,查看建表结果:
show tables;
用户可以在执行结果中,查看到7张数据表:customers;dilivery;dim_product;dispatch;orders;st_buzi_all;stock。
3.2 创建数据同步的任务七张表数据产生的粒度和方式:
本小节主要内容:根据上表中提供的信息,创建数据同步任务。
1、在数据开发页面,选择业务流程,新建业务流程。
2. 在弹出窗口中,填写配置信息:名称 为 sync,完成后点击 新建 。
3. 通过如下步骤,完成数据同步任务的配置:
1)在数据集成,新建数据集成节点命名:dim_product,选择数据同步,指定之前配置的RDS的数据源 rds_ebuzi_yq(mysql),选中表 dim_product ,可以通过点击 数据预览 ,查看样例数据。(可也双击节点,进行配置)
2)在数据去向页面,目标数据源指定为 odps_first ,表指定为 dim_product ,清理规则 必须 指定为 写入前清理已有数据insert overwrite
3)在字段映射页面,配置字段映射关系。本例中由于源表和目标表的字段名称和顺序都一致,所以自动匹配的结果即为最终结果。
4)在通道控制页面,主要是配置作业速率上限和出错记录上限。本例中保持缺省值即可。
5)在保存预览页面,检查一下配置内容有无错误,有则改之,无则继续。点击 保存。
6)设置调度配置,点击右侧栏的 调度配置 ,调度周期 设置为 天 ,具体时间 为 凌晨4点 。
在【调度依赖】处,点击 使用项目(工作空间)根节点
7)完成如上设置后,依次点击 保存 -> 提交 。完成提交后,点击右上角的 运维 ,运行该任务。
4. 通过如下步骤,运行数据同步任务,并查看运行结果。
1)在任务视图页面 - 周期任务,找到要运行的任务dim_product,点击右侧的 测试,去触发任务的测试运行。
右击测试 或者 点击测试
2)在弹出的提示对话框中,直接点击 确认 。
3)运行几秒种后,刷新页面,将会看到任务已经执行成功。
5. 通过如下步骤,检查数据同步结果。
1)返回数据开发页面。
2)双击并打开脚本文件 test。(可用已有,也可新建临时查询)
3)在右侧的脚本编辑页面,删除现有内容并输入如下命令,查看表 dim_product 的同步结果。
select * from dim_product
点击运行 。等待一段时间,可以在下方的 结果 中查看到表dim_product 同步结果。
6. 通过如下步骤,同步数据orders表。
1)点击左上角的数据开发。在菜单中,选择上一步的数据集成,拖动一个新的【数据同步】组件到画布 。
2)在弹出窗口中,填写配置信息:名称为 orders,完成后点击 创建 。
3)创建数据同步节点。
由于增量加载时orders表为分区表,在数据同步任务的来源界面中,需要设置如下配置信息和过滤条件:
数据源:rds_ebuzi_yq(mysql) 表:orders 过滤条件:date_format(order_time,'%Y%m%d')= '${bdp.system.bizdate}'
4)在选择目标页面中,配置如下信息:
数据源:odps_first(odps) 表:orders 分区键信息:ds= '${bdp.system.bizdate}' 清理规则:使用 写入前保留已有数据 insert into
5)字段映射、通道控制页面,均使用默认配置。在预览保存页面,点击 保存。
6)在调度配置栏中,修改 调度周期 为 天 ,具体时间为 04 时 05 分。
7)完成如上配置后,选择使用工作空间根节点。依次点击顶部的 保存,提交 和 前往运维 。
8)在弹出的运维中心页面,参考 步骤4 ,运行同步任务orders。等待一段时间,页面显示运行成功。
9)参考 步骤5 ,在test脚本文件中,运行如下命令,查看同步的表orders的运行结果。
set odps.sql.allow.fullscan=true;
Select * from orders;
7. 参考 步骤6 ,新建数据同步任务customers和stock,并进行测试。
1)配置 数据同步 任务 customers ,详细信息参考如下:
源表:RDS中的customers 过滤条件:date_format(gen_date,'%Y%m%d')= '${bdp.system.bizdate}' 目标表:odps_first中的customers 清理规则:使用 写入前保留已有数据 insert into 使用工作空间根节点 调度周期:设置为天,具体时间为 04时10分
2)完成如上配置并 保存 和 提交 后,点击 前往运维 执行同步任务,运行成功后如下图所示。
3)在test脚本文件中,执行如下命令,并查看同步后的customers表中数据。
Select * from customers;
4)配置 数据同步 任务 stock ,详细信息参考如下:
源表:RDS中的stock 过滤条件:date_format(last_update_time,'%Y%m%d')<='${bdp.system.bizdate}' 目标表:odps_first中的stock 清理规则:写入前清理已有数据Insert Overwrite 字段映射:点击字段 last_update_time 左侧的节点连接到右侧字段 update_time 的节点。 使用工作空间根节点 调度周期为天,具体时间为 04时15分
5)完成如上配置并 保存 和 提交 后,点击 前往运维 执行同步任务。
6)在test脚本文件中,执行如下命令,并查看同步后的stock表中数据。
Select * from stock;
7)至此,完成了4个节点任务的配置开发和测试。
8. 通过如下步骤,新建工作流任务 log 。
1)点击业务流程 sync
2)在 任务开发的画布上,拖入 虚节点 ,名称为 start 。
3)拖入两个 数据同步 任务,名称分别为 dilivery 和dispatch 。
4)然后,使用鼠标左键,分别用连线连接 start 与 dilivery ;start 与 dispatch 。
7)流程面板中, 配置流程任务的【虚节点】的调度信息,具体时间为:04点20分 。
依赖选择使用工作空间根节点。 此后日调度就可以通过虚节点来控制执行。
8)保存、提交后前往运维,测试任务。等待一段时间后,工作流任务 log 完成。
注意测试的时候都要进行测试
10)至此,完成4个节点同步任务和1个任务流同步任务的所有操作。
3.3 创建数据汇总加工的任务本小节主要内容:创建数据汇总的节点任务,将所有的数据汇总到一个表中,并通过补数据的方式,导入增量表的历史数据。
1. 通过如下步骤,新建一个节点任务 gen_st_buzi_all 。
1)拖拽组件,类型为【 ODPS_SQL 】,名称为 gen_st_buzi_all 。完成后,点击 创建 。
2)双击编写具体的SQL,产生st_buzi_all中的数据。在右侧文本页面,输入 gen_st_buzi_all.sql (请从左侧的 附件下载 中获取)中的全部代码,
3)点击调度配置,配置具体时间为 04时20分 。添加依赖任务:点击 上游任务 右侧的 搜索对话框,依次选择之前创建的六个任务:stock;orders;dilivery;dim_product;customers;dispatch;
【推荐】这里Dataworks 2.0 提供了自动解析功能,可根据sql语句,自动寻找并关联依赖。
4)因为脚本中用到了一个自定义时间变量yyyy_mm_dd,需要在参数配置中指定该变量的取值。点击调度配置 ,给变量 yyyy_mm_dd 赋值为 ${yyyy-mm-dd} 。
5)依次点击 保存、提交 和 前往运维, 在运行页面,右侧点击 测试 ,运行成功后如下图所示。
6)在test脚本中,运行如下命令,查看汇总表 st_buzi_all 中的数据。
set odps.sql.allow.fullscan=true; select * from st_buzi_all;
7)至此,所有的数据同步、汇总任务均已配置完成。正常情况下,每日4点到4点20分之间会调度一次我们配置好的数据同步和数据加工的任务,成功运行后,我们需要的数据就会正常产生。
2. 通过如下步骤,使用补数据的功能,处理历史数据。
1)进入 运维中心
2)在运维中心的页面,点击最左侧的 周期任务
3)点击任务 stock
4)在右侧任务视图面板中,选中 虚节点 ,点击 右键 ,并选择 补数据 。
5)在弹出的补数据节点页面中,点击全选,指定业务日期(指定最近一周的时间范围,并且截止时间是前一天的日期;例如本次操作的时间是2018-12-26,因此时间范围选择的是2018-12-18至2018-12-25),点击 确认。
6)在补数据页面将会看到这正在执行的补数据任务。
【注】由于需要补最近一周的数据,并调用所有的任务节点进行数据处理,时间一般需要10分钟左右,所以无需等待,可以先操作后续的步骤。
第 4 章:报表开发4.1 配置报表开发的环境1. 保留 运维中心 的页面窗口,以便于后续查看补数据任务的执行状态。在浏览器新的窗口中访问如下地址,进入QuickBI的管理控制台。如果QuickBI服务尚未购买,需要先点击 标准版30天试用申请,然后再点击 进入QuickBI标准版 进入QuickBI的管理控制台:
https://das.base.shuju.aliyun.com/console.htm?spm=5176.2020520001.1001.180.25uPK8
【注意】用户也可以返回阿里云管理控制台页面,点击顶部 产品与服务->大数据->Quick BI ,并在 QuickBI 的管理控制台 开通并购买QuickBI服务。
2. 增加数据源。依次点击 工作空间 - 数据源 ,进入数据管理页面
3. 点击 新建 ,并选择导入的数据源为 来自DataIDE 。
【注】如果系统中有其他的数据源和数据集,请先删除已存在的所有数据源和数据集,以免后续操作有冲突!
4. 选中需要导入的数据源,点击 导入 。(若提示导入失败,请下载附件中的 st_buzi_all.csv 文件进行本地CSV上传,进行下一步即可 )
5. 在数据源的列表中点击刚导入的数据源,在右侧列表中选择需要的表(st_buzi_all),点击 创建数据集 。
6.创建的数据集将会在 我的数据集 的列表中显示:
4.2 报表设计整体运营情况的报表,基于数据表st_buzi_all设计和创建,希望的报表样子如下:
1. 依次点击 仪表板 - 新建,创建数据展示的仪表板:
2. 点击右上角的 选择数据集 ,选中 st_buzi_all 。
3. 将默认图表删除。
4. 单击 查询条件 ,添加一个查询条件到画布。
5. 依次单击数据图表中的柱状图、柱状图、仪表盘、线图、四个指标看板,并使用鼠标调整图形,结果如下:
6. 指定报表名称 业务大盘监控,然后,点击 保存 。
7. 点击查询条件,配置数据信息,包括源数据集选择 st_buzi_all,字段选择 buzi_date(day),
点击字段右侧,设置过滤条件
同源关联选择两个柱图和一个线图:
8. 修改标签为 日期 ,同时选定区间粒度 、时间区间。
设定时间筛选范围,相对时间
9. 点击柱图A,配置数据信息:维度 为 buzi_date(day),指标 为 order_cnt,颜色图例指定 ds 字段,点击 更新 。
【说明】柱图A 位置请参考 步骤5 中的图片。
10. 点击 样式 ,修改 标题 为 七日订单数趋势 。
11. 点击 柱图B ,配置数据信息,维度 为 buzi_date(day),指标 为 total_amt, 颜色图例设置为 ds,点击更新
【说明】柱图B 位置请参考 步骤5 中的图片。
12. 对柱图B,点击样式,修改标题为 七日成交金额趋势 。
13. 点击仪表盘,将数据集中的度量 curr_cust_cnt 拖到 指标角度/度量,将数据集中的维度 buzi_date(day) 拖入到 过滤器 中,点击红圈标示出来的过滤器配置的 漏斗图标,进行下一步的过滤器配置。
【说明】仪表盘 位置请参考 步骤5 中的图片。
14. 在设置过滤器页面,配置过滤条件。然后点击 更新。
15. 设置仪表盘的样式。标题为客户保有量,勾掉 显示图例,点击【添加】按钮增加区间信息,区间起始值为 10 ,结束值为 1000 ,点击更新
16. 点击线图,配置数据信息,维度为buzi_date(day),度量 为 new_cust_cnt
【说明】线图 位置请参考 步骤5 中的图片。
17. 配置样式。在样式面板中,标题设为:七日新增用户数趋势,选择堆积面积图,勾选 面积、堆积
18. 点击指标看板1,配置数据信息。度量 选 stock_item_cnt ,将维度 buzi_date(day) 拖入过滤器。
【说明】看板1 位置请参考 步骤5 中的图片。
19. 配置过滤器。选择日期、相对时间,相对时间设置 为 昨天 ,点击 确定 和 更新。
20. 配置样式。标题设为:当前库存数,卡片设置对齐方式第一个即可,每行显示 1 个。
21. 对于看板2、看板3、看板4均进行类似的配置,配置信息分别为:
【说明】看板2、看板3、看板4 位置请参考 步骤5 中的图片。
看板2
度量:stock_amt 标题:当前库存金额 过滤字段:buzi_date(day) 过滤条件:昨天看板4
度量:succ_sent_rate 标题:当日及时到达率 过滤字段:buzi_date(day) 过滤条件:昨天 对齐方式:第一个,每行个数1个看板3
度量:sent_prd_cnt 标题:当日派送件数 过滤字段:buzi_date(day) 过滤条件:昨天 对齐方式:第一个,每行个数1个- 对齐方式:第一个,每行个数1个
-
4.3 查看效果
1. 配置完成后点击保存,然后点击预览,查看效果。
- 2. 点击查询条件中的日历,选择查询日期的开始日期和截止日期。选择开始日期为当前时间一周前的时间,与补数据任务的开始时间保持一致。完成之后,再选择截止日期(注意两者之间时间差为7天),与补数据任务的结束时间保持一致,然后点击 确认 。
-
查看结果如下:
【注】如果显示的数据少于七天,请返回DataIDE的运维中心模块中去查看所有的补数据任务是否成功完成。
-
可点击手机预览
至此,我们完成了报表分析所有的实验操作!