剑指数据仓库-项目篇（五）

一、进行指标计算

• 1.1、部署Superset
• 1.2、指标计算
• 1.3、物理删除和逻辑删除

二、数仓项目作业

一、进行指标计算

使用SQOOP把Hive中ads_bill_sum_count这张表的数据导入到MySQL中dw数据库中的ads_bill_sum_count表中，SQL如下：

sqoop export --connect jdbc:mysql://sz5i5j-01:3306/ruozedata_dw --username root --password 960210 --table ads_bill_sum_count --export-dir '/user/hive/warehouse/ruozedata_erp.db/ads_bill_sum_count' --fields-terminated-by "\t" --lines-terminated-by "\n" -m 1

那比如我们今明两天都执行了这个脚本，有两条相同的数据，如何处理：先到MySQL进行delete表删除再export导入；结果到MySQL需要考虑数据的重复性，假设现在只有一次

再使用SQOOP从Hive中把这张表ads_sku_sum_count_by_purchase_and_sell导到MySQL中去：

sqoop export --connect 'jdbc:mysql://sz5i5j-01:3306/ruozedata_dw' \
--username root \
--password 960210 \
--table ads_sku_sum_count_by_purchase_and_sell \
--export-dir '/user/hive/warehouse/ruozedata_erp.db/ads_sku_sum_count_by_purchase_and_sell' \
--fields-terminated-by "\t" \
--lines-terminated-by "\n" \
-m 1

sqoop提示okay，查看发现字符集乱码；根据J总提供的文档一通操作哦，加上在SQOOP导出的时候加上一句话，把字符集加上即可：?useUnicode=true&charset=utf-8

1.1、部署Superset

• https://blog.csdn.net/SparkOnYarn/article/details/105895168

建议在CentOS上部署使用superset，否则环境很难支持。

1.2、指标计算

• 添加如下两张表：schema才是我们真正的database
  
• 创建Charts图表：
  
• 如下别忘了保存
  
• add to a new dashboard
  

如何对Dashboard中的图表进行编辑：

• 单机Edit dashboard：
  

• 如下图右侧有很多的图表控件什么的可供选择，左边的框也是可以进行拖拽使用的；
  
  最新版superset应该是0.30.1会有一个样式可供选择：
  

• 一张表下如何存放几个指标，ADD chart to existing dashboard
  

Superset如何构建Dashboard：

Superset上计算最近30天的每天、每小时订单总量（含退货）

dim_time 最近30天是按照小时的，它的数据肯定是最全的，拿它作为主表；a：mysql建表dim_time.sql表，Hive也进行建表，dwd层没有ods层；dwd层的表可以通过爬虫也可以通过自建的出数据；

2019-12-28是varchar类型的，并不是timestamp类型；所以把时间给补齐，以这样的形式：2019-09-29 09:00:00，这样的话在图表中是很容易识别出来的。

Superset中制成曲线图，time的类型一定是timestamp类型的，Query中的查询语句进行修改：sum(bill_count)、sum(return_bill_count)，还需要设置每隔多久刷新数据

还有基于Superset的二次开发（python、flask），比如支持echart、生产上的报表支持水印（很重要的一点）、支持单点登录(一个ip一个浏览器)

1.3、物理删除和逻辑删除

DELETE发现：1、获取MySQL中的binlog文件 --> canal/maxwell --> kafka --> flume --> hive按天分区表（delete的数据）–> 凌晨1点ods_update抽取，
凌晨2点ods_update+dwd表的合并且作废，–> new dwd表
new dwd表+29号的delete分区数据做join
把删除的数据 new dwd表在delete存在的数据endday=29号

2、使用spark来进行重刷，mysql <==> dwd right join where mysql.pk is null的数据，把endday=29号的数据作废

3、全量重刷

4、主机器节点设置触发器，从节点设置的触发器是无效的，正常业务就算是物理删除，也会很少发生，除非是脏数据需要dba进行操作；锁表了解下
触发器是我们删除的数据会建立一张t_delete表 --> ods分区，按天分区 --> 参考1

目标就是发现delete的那些数据，把数据搬迁了操作，不管是通过sqoop还是通过binlog日志去搬都是可以的；

sku 50 12-28 今天
sku 70 12-29 明天
sku 80 12-30 后天

在12-29把28号的数据先delete掉的，29号只有29号的数据；30号也是把t表清空，只有30号的数据；数据想要一个追加应该怎样做。

第一种复杂：delete from t; 通过这个表去写sql；先按每天进行汇总，按天进行排序，做一个累加，第一行50，第二行50+20，第三行50+20+10；Spark中有累加的操作，可以用SQL来做，复杂度比较高。

第二种做法：MySQL建表的时候，skuname、skunub、day，每次删除的时候，delete from t where day=28;

sqoop进行追加，相当于是insert into

其余指标：

1、GMV成交总额（包含退货）

2、成交转换率（并不是下了计划的订单一样成交了）

3、复购率（一个月重复购物的百分比）

4、PV（页面浏览量）、UV（用户访问量）

5、最近7天内连续2天的活跃用户有哪些（场景：今天登明天登是可以的）******面试经常会被考察到
去重登录用户ruozedata_log这张表，根据创建时间使用SQL语法来写。

6、用户行为漏斗分析表（追踪用户从打开浏览商品到最终付款的一个行为表）

二、数仓项目作业

1、ERP业务了解
2、IDEA+MySQL去执行
3、Hadoop伪分布式+hive+sqoop基础环境
4、Dbeaver：连接Hive+MySQL
5、ods表数据 sqoop导数据
6、dwd表数据 sqoop导数据
7、Rundeck部署
8、Superset部署
9、dws表，sqoop导数据
10、ads表，sqoop导数据
11、mysql表 sqoop导数据
12、superset中的Dashboard可视化
13、整理数仓分层、分层流程图、维度建模、拉链表的设计
14、整理物理删除、hive to mysql的优化点
15、整理指标SQL：13个基础指标（分组求topN、行转列、列转行）+1个平移累积计算+1个 最近7天内连续两天的活跃用户

数仓分层、建表、写指标，拼接字符串、分割字符串