大数据学习之PB级音乐数据中心数仓综合项目（1）-理论知识和项目需求、歌曲热度与歌手热度排行

一、理论知识和项目需求

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.

45.

46.

47.

48.

49.

50.

51.

52.

53.

54.

55.

56.

57.

58.

59.

60.

61.

62.

63.

64.

65.

66.

67.

68.

69.

70.

71.

72.