rpt层建设实战，本地视频+md，review第1遍,220626，

RPT

• 名称：数据报表层（Report），其实就是我们所讲的数据应用层DA、APP。
• 功能：根据报表、专题分析的需求而计算生成的个性化数据。表结构与报表系统保持一致。
• 解释
  

  这一层存在的意义在于，如果报表系统需要一些及时高效的展示分析。我们可以在RPT层根据其需求提前把相关的字段、计算、统计做好，支撑报表系统的高效、便捷使用。

  对于其依赖的数据有两种实现方式

• 方式1：使用专业的BI报表软件直接读取数据仓库或数据集市的数据，然后自己根据需要展示的效果进行数据抽取、转换、拼接动作，
• 方式2：大数据开发工程师针对前端Top10展示的需求，提前把数据拼接好，返回前端直接页面渲染展示。

使用DataGrip在Hive中创建RPT层

注意，对于建库建表操作，需直接使用Hive，因为Presto只是一个数据分析的引擎，其语法不一定支持直接在Hive中建库建表。

知识点03：RPT层搭建–销售主题报表

• 需求一：门店月销售单量排行
  

  按月统计，各个门店的月销售单量。

  • 建表

实现

从销售主题统计宽表中，找出分组为store，并且时间粒度为month的进行排序即可。

需求二：日销售曲线

按天统计，总销售金额和销售单量。

• 建表

需求三：渠道销售占比

比如每天不同渠道的订单量占比。

也可以延伸为每周、每月、每个城市、每个品牌等等等。

• 处理思路

–在dm层的dm_sale表中
order_cnt 表示总订单量
miniapp_order_cnt 表示小程序订单量
android_order_cnt 安卓
ios_order_cnt ios订单量
pcweb_order_cnt  网站订单量
–所谓的占比就是
每个占order_cnt总订单量的比例 也就是进行除法运算

–最后需要注意的是
上述这几个订单量的字段  存储类型是bigint类型。
如果想要得出90.25这样的占比率  需要使用cast函数将bigInt转换成为decimal类型。

暂时看到：：：

需求三：渠道销售占比

知识点04：RPT层搭建–商品主题报表

• 需求一：商品销量topN
  

  统计出某天销量最多的top10商品

• 需求二：商品收藏topN
  

  统计出某天收藏量最多的top10商品

• 需求三：商品加入购物车topN
  

  统计出某天，购物车最多的top10商品

• 建表

–统计出某天销量最多的top10商品

–方式1 ：简单方式
select sku_id,order_count from yp_dm.dm_sku
order by order_count desc limit 10;

–方式2  ：复杂方式
–需求：找出销量最多的前10个  重复的算并列 但是总数只要10个。
with tmp as (select
sku_id,order_count,
rank() over(order by order_count desc) rn1,
dense_rank() over(order by order_count desc) rn2,
row_number() over(order by order_count desc) rn3
from yp_dm.dm_sku)

select * from tmp where rn <11;

rank 重复不连续

dense_rank 重复连续

row_number

sql实现

注意，这里为了最终展示效果，保证有数据，特意在时间dt上做了特殊处理。

本来是需要通过dt指定某一天数据的，这里忽略dt过滤 ，直接使用全部数据。

知识点05：RPT层搭建–用户主题报表

• 需求
  

  活跃会员数、新增会员数、新增消费会员数、总付费会员数、总会员数、会员活跃率等。

#首先，确定干活统计的日期，比如2019-05-08

#1、活跃会员数
活跃的业务解读：
1、在一个月中累积登录次数大于某个值 叫做活跃  比如累积登录次数 >20
2、该用户的末次登陆时间为昨天（今天统计的是昨天的），昨天来过 就表示活跃

本项目使用2：用户最后一次登录为统计日期之前一天的  表示活跃

#2、新增会员数
第一次登录为统计日期前一天的  表示新增

#3、新增消费会员数
首次支付时间为统计日期前一天的

#4、总付费会员数
支付次数大于0的

#5、总会员数

#6、会员活跃率
最后一次登录时间为统计日期前一天的表示这个人很活跃
除以总会员数即是会员活跃率

#7、总会员付费率
支付次数payment_count大于0次的表示该用户支付过  不管支付几次
除以总会员数即是总会员付费率

#8、会员新鲜度
在统计日期前一天中所有登录用户中，哪些是第一次登录的  占比即是新鲜度
login_date_last最后一次登录的时间
login_date_first 第一次登录的时间

知识点06：数据导出–RPT层数据至MySQL

• 新零售数仓架构图
  

  从数仓架构图上，感受为什么最终需要把拼接的数据导出存储在mysql。

  报表系统直接从hive数仓RPT层中读取数据使用可不可以可以但是没必要。

step1：presto配置连接MySQL

• 配置mysql Connector
  

  在每台Presto服务的etc/catalog目录下，新建文件mysql.properties，内容如下

• 重启presto集群
  

  /export/server/presto/bin/launcher restart

• Datagrip中验证是否可以连接MySQL
  

  在presto中根据自己的需要选择需要刷新的catalog、schema等。

step3：使用Presto在MySQL中建表

建表时使用Presto来操作，create语法和hive大体相同，只需要将hive中的string类型改为varchar。

另外文件格式、压缩格式这些mysql没有的配置去掉就可以了。

注意presto不能直接删除mysql的表或数据。

 

人类是视觉动物。
一般情况下，分析的数据通过表格和图形的方式来呈现更利于理解，我们常说用图表说话就是这个意思。
常用的数据图表包括饼图、柱形图、条形图、折线图、散点图、雷达图等，当然可以对这些图表进一步整理加工，使之变为我们所需要的图形，例如金字塔图、矩阵图、漏斗图等。
大多数情况下，人们更愿意接受图形这种数据展现方式，因为它能更加有效、直观地传递出分析所要表达的观点。记位，一般情况不，能用图说明问题的就不用表格，能用表格说明问题的就不要用文字。

报表是数据呈现的载体，功能也就是来展现数据的。
报表的作用在于呈现数字，数字呈现的作用在于展示现状，明确与目标的差距，进而用于后续改善方案及行动计划的指南。

问题2：企业中谁来负责报表/p>

产品经理、产品运营

BI工程师

 

开源技术实现前端报表、后端数据查询

• 前端
  

  主要指各种JS技术，可以在html页面上进行各种图形表格、页面布局的绘画。

  这当中有的js是已经制作好了各种图形表格，上手即用，有的需要自己重零绘制。

  比如：百度echarts 、highcharts、Vue.js等

后端

只要指的是能够从数据库、数据仓库等处查询数据、封装返回给前端页面展示。

常用的技术有：Java、Python、PHP等。

知识点08：新零售数据可视化–Java后端、Vue前端（了解）

详细操作见讲义。

• maven配置
• IDEA使用
• Nodejs安装
• WebStorem使用
• 前后端

• FineBI 是帆软软件有限公司推出的一款商业智能（Business Intelligence）产品。FineBI 是定位于自助大数据分析的 BI 工具，能够帮助企业的业务人员和数据分析师，开展以问题导向的探索式分析。接口调试

知识点10：自动化调度方案–工作流调度与oozie

• workflow工作流的概念
  • 工作流（Workflow），指“业务过程的部分或整体在计算机应用环境下的自动化”。
  • 工作流解决的主要问题是：为了实现某个业务目标，利用计算机软件在多个参与者之间按某种预定规则自动传递文档、信息或者任务。
  • 核心概念:依赖执行 周期重复执行
  • DAG（有向无环图）

Apache Oozie介绍

• Oozie是一个用来管理 Hadoop生态圈job的工作流调度系统。由Cloudera公司贡献给Apache。

• oozie本身单独使用极其不方便，配置文件极其繁琐，不管是使用shell命令提交工作流还是使用java API提交工作流，都需要编写大量繁琐的xml配置文件；
• 但是oozie和hue整合之后使用还是非常不错的，在hue页面上提供了拖拽功能，直接选择想要调度的脚本或者其他任务，还可以根据自己的需求编写定时规则。

• • oozie本身单独使用极其不方便，配置文件极其繁琐，不管是使用shell命令提交工作流还是使用java API提交工作流，都需要编写大量繁琐的xml配置文件；
  • 但是oozie和hue整合之后使用还是非常不错的，在hue页面上提供了拖拽功能，直接选择想要调度的脚本或者其他任务，还可以根据自己的需求编写定时规则。
• 栗子 1：在Hue上使用oozie提交一个shell脚本执行
  • step1：打开hue页面

    http://hadoop02:8889/hue 用户名、密码：hue

  • step2：上传一个shell脚本或者使用hue在线编写一个shell脚本

栗子2：针对栗子1的调度任务，配置周期定时执行coordinator。

刚才配置的workflow属于一次性的工作流，执行完就结束了。

可以配置coordinator来控制workflow的执行周期和触发频率

知识点11：自动化调度方案–shell基本知识回顾

#获取今天的日期
date
date +%Y%m%d

#获取指定日期的年月日格式输出
date -d “2014-11-12″ +%Y%m%d

#获取指定日期的星期（周几）格式输出
date –date=”2014-11-23” +%w

#获取上周日期（day,month,year,hour）
date -d “-1 week” +%Y%m%d

#获取昨天日期
date -d ‘-1 day’ “+%Y-%m-%d”
date –date=”-24 hour” +%Y%m%d

变量提取、反引号的功能

name=”allen”
echo ${name}

date
nowTime=date
echo ${nowTime}

date
nowTime=`date`
echo ${nowTime}

数字运算

#双小括号命令是用来执行数学表达式的，可以在其中进行各种逻辑运算、数学运算，也支持更多的运算符（如++、–等）

echo $(((5 * 2)))

i=5
echo $(((i=$i*2))) #10
echo $(((i=i*2)))  #20

# $((( )))的缩写。
echo $(((i*2))) #40
echo $((i*2)) #40

#shell脚本默认是按顺序串行执行的，使用&可以将一个命令放在后台运行，从而使shell脚本能够继续往后执行

sleep 5 &
echo “done”

sleep 5  #休眠5s
echo “done”

#上面的脚本执行后会立即打印出”done”，sleep命令被扔给后台执行，不会阻塞脚本执行。

#如果想要在进入下个循环前，必须等待上个后台命令执行完毕，可以使用wait命令
sleep 5 &
wait
echo “done”

#这样，需要等待5s后才能在屏幕上看到”done”。

shell动态传参

$1 $2 代表的是传递的第几个参数

$0 执行脚本的名字

$# 传递参数的总个数

$* 显示所有传入参数 以列表展示

[root@hadoop02 ~]# vim 4.sh
#!/bin/bash
echo “$1”
echo “$3”
echo “$0”
echo “$#”
echo “$*”

[root@hadoop02 ~]# sh 4.sh  11 22 33 44
11
33
4.sh
4
11 22 33 44

知识点12：自动化调度方案–脚本实现、调度实现

• 脚本实现
  

  脚本实现的关键是如何在shell建表中执行sqoop命令、hive sql文件、presto sql文件

  并且关于时间的地方不能写死，而是使用shell date命令来动态获取

  • 例子一：ODS数据导入
• #! /bin/bash
  SQOOP_HOME=/usr/bin/sqoop
  if [[ $1 == “” ]];then
  TD_DATE=`date -d ‘1 days ago’ “+%Y-%m-%d”`
  else
  TD_DATE=$1
  fi

  #上述这段shell是什么意思否看懂br>     如果用户不指定日期 默认采集当前天的前一天的数据。
  用户也可以根据自己需求传入指定日期作为参数  就采集指定那一天的数据。

首次执行脚本

循环执行脚本

1、通过sqoop的query查询把增量数据查询出来。

增量的范围是TD_DATE值的 00：00：00 至 23：59：59

2、判断的字段是

如果是仅新增同步，使用create_time创建时间即可

如果是新增和更新同步，需要使用create_time 和 update_time两个时间

3、这也要求在业务系统数据库设计的时候，需要有意识的增加如下字段

create_user

create_time

update_user

update_time

例子二：DWB层sql脚本执行

在shell中执行hive sql的方式有两种

bin/hive -e ‘sql语句’

bin/hive -f xxx.sql文件

 

#! /bin/bash
HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e ”
–分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
–=======订单宽表=======
insert into yp_dwb.dwb_order_detail partition (dt)
select
xxxxxxx
;

循环执行

#! /bin/bash
HIVE_HOME=/usr/bin/hive

#上个月1日
Last_Month_DATE=$(date -d “-1 month” +%Y-%m-01)

${HIVE_HOME} -S -e ”
–分区配置
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;

–=======订单宽表=======
–增量插入
insert overwrite table yp_dwb.dwb_order_detail partition (dt)
select
xxxxxx
— 读取上个月1日至今的数据
SUBSTRING(o.create_date,1,10) >= ‘${Last_Month_DATE}’ and o.start_date >= ‘${Last_Month_DATE}’;

例子三：Presto的sql如何在shell中执行

#! /bin/bash
#昨天
if [[ $1 == “” ]];then
TD_DATE=`date -d ‘1 days ago’ “+%Y-%m-%d”`
else
TD_DATE=$1
fi

PRESTO_HOME=/opt/cloudera/parcels/presto/bin/presto

${PRESTO_HOME} –catalog hive –server 172.17.0.202:8090 –execute ”
delete from yp_rpt.rpt_sale_store_cnt_month where date_time = ‘${TD_DATE}’;
…….
”

#改写上述的模板 练习一下如何使用shell执行presto sql

/export/server/presto/bin/presto –catalog hive –server hadoop01:8090 –execute “select * from yp_rpt.rpt_sale_store_cnt_month where date_time = ‘2021-03-17′”

来源：啊六六六