一、理论知识和项目需求
1.课程介绍
2.数据库与ER建模_数据库三范式
3.数据库与ER建模_ER实体关系模型
4.数据库与维度建模_数据仓库(DATA WAREHOUSE)
5.数据库与维度建模_数据库与数据仓库区别
6.数据库与维度建模_数据仓库的发展历程
7.数据库与维度建模_维度建模
8.数据库与维度建模_维度建模案例
9.数据库与维度建模_数仓分层设计一
10.数据库与维度建模_数仓分层设计二
11.数据库与维度建模_数仓分层设计案例
12.项目需求_项目介绍
数据中心项目中包含业务系统数据和用户行为日志数据。
业务数据即业务系统产生的业务数据，例如：系统中产生的订单、登录、点歌、广告展示等数据。
1
用户行为数据例如：用户在实体机器上操作的行为都是用户行为数据，点击、收藏、扫码等事件。
2
公司针对以上数据进行分析的结果主要有两个应用：
一是针对
BI
系统，商业智能中主要展示更多的报表给公司的运营人员参照。例如：每日歌曲点唱量，每日营收，
机器分布，实时
pv,uv
，用户留存率、漏斗模型等。
1
另一个应用是数据服务，数据服务主要是针对分析后结果数据以接口的形式提供给业务系统来访问，例如
:
推荐系
统，根据歌曲来推荐歌曲，根据歌手来推荐歌曲或者根据用户来推荐歌曲。
2
本项目模块划分：
歌曲热度与歌手热度排行
1
机器相信信息统计
2
日活跃用户统计
3
商户营收统计
4
地区营收统计
5
实时统计所有用户的
PV/UV
6
实时统计歌曲热度
13.项目需求_项目架构
14.项目需求_集群配置_项目人数_周期
15.项目需求_数据来源及采集
以上两种设备产生的数据可以分为两类数据：
是产生的订单数据，会记录到业务数据库。后期直接通过
sqoop
直接抽取
MySQL
中的数据到
HDFS
。
1
是通过
http
请求，上传到专门采集数据的日志服务器上，每天由运维人员将数据打包上传到数据中心平台某个目
录下，然后由定时任务定时来执行
Spark
任务拉取数据，上传至
HDFS
中。这里读取压缩数据使用
SparkCore
进行
处理，处理之后将数据以
parquet
格式或者
json
格式存储在
HDFS
中即可。
16.项目需求_数仓模型设计
内部数据源：主要有
MySQL
和客户端上传
json
数据。
MySQL
使用
Sqoop
抽取
数据到
HDFS
中，导入
ODS
层。客户端产生日志到客户端服务器，客户端服务
器由运维人员每天将数据压缩成包导入到
HDFS
路径中，然后再经过
SparkCore
和
SparkSQL
处理后导入
ODS
层。
EDS(Enterprise Data Store)/DW(Data Warehouse )
层：
EDS
层负责信息集成、轻度汇总类数据。简单理解就是将事务性的数据组织
成便于分析的仓库维度建模类型的数据，做一些轻度聚合，类似
Hive
中的宽
表。例如：将
ODS
层数据进行清洗，如果主题是用户主题，那么就按照用户
id
为粒度将数据组织在一起。如果主题是机器，那么就按照机器
id
为粒度将
数据组织在一起。
以上
ODS
层和
EDS
层使用
Spark
代码处理数据，然后利用
SparkSQL
读取
ODS
层数据，保存到
Hive
的
EDS
层。
DM(Data Mart)
层：
DM
层的数据有一部分是存储在
Hive
表中，或者保存分析结果到
MySQL
、
HBase
等。
EDS
层数据是
parquet
格式的数据，放在
Hive
的主要原因是后期使
用
Kylin
查询一些业务，数据放
MySQL
的都是结果数据，放在
HBase
的原因
是涉及到大表的明细查询。
以上数据仓库模型的设计表对应关系都在
“
数据仓库模型
.xlsx”
文件中。
二、歌曲热度与歌手热度排行
17.歌曲热度与歌手热度排行_需求描述
18.歌曲热度与歌手热度排行_需求分析一
要完成昨日的歌曲热度与歌手热度分析，需要以下两类数据：
用户在机器上的点歌行为数据
这部分数据是用户在各个机器上当天的点歌播放行为数据，这些数据是运
维每天零点打包以
gz
压缩文件的方式上传到
HDFS
平台，这里我们假定将
数据
currentday_clientlog.tar.gz
每天凌晨定时上传到
HDFS
路径
hdfs://mycluster/logdata
中，这里在企业中应当上传到某个以天名称的结构目录下，通过
Spark
数
据清洗将数据存放到
Hive
数仓
ODS
层中。关于用户在机器上的点歌行为数
据参照
“
事件上报协议
.docx”
文档。
日志内容格式：
日志时间
&
机器
id&
事件类型
&{json}&
机器版本
&
机器
UI
软件版本
终端向服务器通知歌曲播放操作
(
事件类型中的一种
)
35
CMD
名称
MINIK_CLIENT_SONG_PLAY_OPERATE_REQ
访问方式
终端主动请求
参数类型
json
json
属性
是否
必须
说明
optrate_type
是
0:
点歌
, 1:
切歌
,2:
歌曲开始播放
,3:
歌曲播放完成
,4:
录音试听开始
,5:
录音试听切
歌
,6:
录音试听完成
songid
是
歌曲
ID
uid
是
用户
ID
（无用户则为
0
）
session_id
是
局数
ID
mid
是
机器
ID
dur_time
是
总时长单位秒（
operate_type:0
时此值为
0
）
play_time
是
播放时长单位秒
consume_type
是
消费类型：
0
免费；
1
付费
songname
是
歌曲名
pkg_id
是
套餐
ID
类型
order_id
是
订单号
.
订单号组成规则
,
类型
+_(
下划线
)+
由
支付类型
自定义的订单
.
补偿套餐
的
order_ID
是上局的
comsum_id
19.歌曲热度与歌手热度排行_需求分析二
20.歌曲热度与歌手热度排行_需求分析三
21.歌曲热度与歌手热度排行_DATAGRIP安装激活
22.歌曲热度与歌手热度排行_配置HIVESERVER2
23.歌曲热度与歌手热度排行_DATAGRIP配置
24.歌曲热度与歌手热度排行_模型设计
hive
>
create database
baizhan_music;
OK
Time taken:
0.664
seconds
hive
>
use
baizhan_music;
OK
Time taken:
0.053
second
1
2
3
4
5
6
CREATE EXTERNAL TABLE IF NOT EXISTS
`TO_CLIENT_SONG_PLAY_OPERATE_REQ_D`
(
`SONGID` string,
`MID` string,
`OPTRATE_TYPE` string,
`UID` string,
`CONSUME_TYPE` string,
`DUR_TIME` string,
`SESSION_ID` string,
`SONGNAME` string,
`PKG_ID` string,
`ORDER_ID` string
1
2
3
4
5
6
7
8
9
10
11
49
2
TO_SONG_INFO_D
歌库歌曲表
)
partitioned by
(
data_dt string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY
'\t'
LOCATION
'hdfs://mycluster/user/hive_remote/warehouse/dat
a/song/TO_CLIENT_SONG_PLAY_OPERATE_REQ_D'
;
12
13
14
15
CREATE EXTERNAL TABLE
`TO_SONG_INFO_D`
(
`NBR` string,
`NAME` string,
`OTHER_NAME` string,
`SOURCE` int,
`ALBUM` string,
`PRDCT` string,
`LANG` string,
`VIDEO_FORMAT` string,
`DUR` int,
`SINGER_INFO` string,
`POST_TIME` string,
`PINYIN_FST` string,
`PINYIN` string,
`SING_TYPE` int,
`ORI_SINGER` string,
`LYRICIST` string,
`COMPOSER` string,
`BPM_VAL` int,
`STAR_LEVEL` int,
`VIDEO_QLTY` int,
`VIDEO_MK` int,
`VIDEO_FTUR` int,
`LYRIC_FTUR` int,
`IMG_QLTY` int,
`SUBTITLES_TYPE` int,
`AUDIO_FMT` int,
`ORI_SOUND_QLTY` int,
50
`ORI_TRK` int,
`ORI_TRK_VOL` int,
`ACC_VER` int,
`ACC_QLTY` int,
`ACC_TRK_VOL` int,
`ACC_TRK` int,
`WIDTH` int,
`HEIGHT` int,
`VIDEO_RSVL` int,
`SONG_VER` int,
`AUTH_CO` string,
`STATE` int,
`PRDCT_TYPE` string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY
'\t'
LOCATION
'hdfs://mycluster/user/hive_remote/warehouse/dat
a/song/TO_SONG_INFO_D'
25.歌曲热度与歌手热度排行_SQOOP数量全量同步
26.歌曲热度与歌手热度排行_SQOOP数量全量同步解决BUG
27.歌曲热度与歌手热度排行_项目搭建
28.歌曲热度与歌手热度排行_歌曲播放日志解析思路
29.歌曲热度与歌手热度排行_歌曲播放日志解析编码一
30.歌曲热度与歌手热度排行_歌曲播放日志解析编码二
31.歌曲热度与歌手热度排行_歌曲播放日志解析编码三
32.歌曲热度与歌手热度排行_歌曲播放日志解析编码四
33.歌曲热度与歌手热度排行_歌曲播放日志解析编码五
34.歌曲热度与歌手热度排行_歌曲播放日志解析编码六
35.歌曲基本信息TO至TW思路分析
36.歌曲基本信息TO至TW编码一
37.歌曲基本信息TO至TW编码二
38.歌曲基本信息TO至TW编码三
39.歌曲基本信息TO至TW编码四
40.歌曲基本信息TO至TW编码五
41.歌曲基本信息TO至TW编码六
42.歌曲基本信息TO至TW编码七
43.歌曲基本信息TO至TW编码测试
44.歌曲特征N日统计思路分析
45.歌曲特征N日统计编码一
46.歌曲特征N日统计编码二
47.歌曲特征N日统计编码三
48.歌曲特征N日统计编码四
49.歌曲特征N日统计编码五
50.歌曲特征N日统计编码六
