一、数据来源
数据中心项目中包含业务系统数据和用户行为日志数据。
? 业务数据即业务系统产生的业务数据，例如：系统中产生的订单、登录、点歌、广告展示等数据。
?用户行为数据例如：用户在实体机器上操作的行为都是用户行为数据，点击、收藏、扫码等事件。
二、项目架构
离线处理:以 Spark 为主，其中很少使用了 SparkCore 的代码，主要使用 SparkSQL构建数据仓库。项目使用 airflow/Azkaban 进行调度，可以每日进行调度，也可以每月进行调度，每天定时触发调度。
实时处理:使用 SparkStreaming 实现实时处理。离线 N+1 的方式不能得到实时数据，运营活动中心有时需要实时的用户上线数据，针对客户端进行数据埋点，用户在客户端所有的行为都是事件，对事件进行埋点，当用户触发了一些事件时，判断用户是否满足了目标用户情况。
同时包括离线处理和实时处理的架构，称为lambda架构设计。
说明：
（1）将业务系统中的数据通过sqoop增量或者全量导入到数据仓库，关于用户日志的数据，由公司运维人员打包好，每天定时上传到HDFS集群，由我们来读取HDFS中的数据，然后使用SparkCore+SparkSQL对数据进行ETL，增量写入数据仓库。
（2）将数据仓库分为3层，为ODS层、EDS层和DM层。
三、数据仓库模型
数据仓库按照主题分为三个主题：用户、机器、内容（歌曲相关、歌手相关）。每个主题下面都有对应的表。数据仓库的设计分为三层，如下：
? ODS 层：
ODS 文件中是从业务数据库中抽取出来数据表的原数据， 数据从关系型数据库 MySQL中导入，转换成 Parquet 格式的文件存在 HDFS 中，后期方便使用 SparkSQL 处理。
ODS 层数据来源如下：
外部数据源：网易云爬取歌曲热度数据、歌手热度数据，爬取数据是 json 格式的数据。
内部数据源：主要有 MySQL 和客户端上传 json 数据。MySQL 使用 Sqoop 抽取数据到 HDFS 中，导入 ODS 层。客户端产生日志到客户端服务器，客户端服务器由运维人员每天将数据压缩成包导入到 HDFS 路径中，也就是 ODS 层。
? EDS 层：
EDS 层负责信息集成、轻度汇总类数据。简单理解就是将事务性的数据组织成便于分析的仓库维度建模类型的数据，做一些轻度聚合，类似 Hive 中的宽表。例如：将 ODS 层数据进行清洗，如果主题是用户主题，那么就按照用户 id 为粒度将数据组织在一起。如果主题是机器，那么就按照机器 id 为粒度将数据组织在一起。
以上 ODS 层和 EDS 层使用 Spark 代码处理数据，然后利用 SparkSQL 读取 ODS 层数据，保存到 Hive 的 EDS 层。
? DM 层：
DM 层的数据有一部分是存储在 Hive 表中，或者保存分析结果到 MySQL、HBase 等。EDS 层数据是 parquet 格式的数据，放在 Hive 的主要原因是后期使用 Kylin 查询一些业务，数据放 MySQL 的都是结果数据，放在 HBase 的原因是设涉及到大表的明细查询。
四、数据仓库命名规范
五、业务需求
======第一个业务需求：统计歌曲热度、歌手热度排行 ======
1日歌曲热度、歌手热度
7日歌曲热度、歌手热度
30日歌曲热度、歌手热度
1日、7日、30日歌曲的点唱数
1日、7日、30日歌曲的点赞数
1日、7日、30日歌曲的点唱用户数
1日、7日、30日歌曲的点唱订单数据
7日、30日歌曲最高点唱、点赞量
1日、7日、30日歌手的点唱数
1日、7日、30日歌手的点赞数
1日、7日、30日歌手的点唱用户数
1日、7日、30日歌手的点唱订单数据
7日、30日歌手最高点唱、点赞量
===== 需求分析=======
需要数据：
1).每天用户点播歌曲的日志数据，由运维人员每天上传到HDFS系统中。--数据来源于用户行为日志
2).歌曲基本信息表【歌曲对应歌手信息】--数据来源于原业务系统
===== 模型表设计 =======
ODS层：
TO_CLIENT_SONG_PLAY_OPERATE_REQ_D - 客户端歌曲播放表
TO_SONG_INFO_D - 歌库歌曲表
对ODS中的表数据进行清洗，得到EDS层数据库表
EDS层：
TW_SONG_BASEINFO_D - 歌曲基本信息日全量表
TW_SONG_FTUR_D - 歌曲特征日统计
TW_SONG_RSI_D - 歌曲影响力指数日统计
TW_SINGER_RSI_D - 歌手影响力指数日统计
DM层：mysql中
tm_song_rsi -歌曲影响力指数表
tm_singer_rsi -歌手影响力指数表
====== TW_SONG_FTUR_D 歌曲特征日统计 =====
1. 当日点唱量 SING_CNT
当日点赞量 SUPP_CNT
当日点唱用户数 USR_CNT
当日点唱订单数 ORDR_CNT
SELECT
songid , -- 歌曲id
count(songid), -- 歌曲当日点唱量
0 as supp_cnt, -- 歌曲当日点赞量
count(distinct uid), -- 当日点唱用户数
count(distinct order_id) --当日点唱订单数
FROM
TO_CLIENT_SONG_PLAY_OPERATE_REQ_D
WHERE data_dt = 当日日期
GROUP BY songid
2.近七天点唱量 RCT_7_SING_CNT
近七天点赞量 RCT_7_SUPP_CNT
近七天点唱用户数 RCT_7_USR_CNT
近七天点唱订单数 RCT_7_ORDR_CNT
SELECT
songid , -- 歌曲id
count(songid), -- 歌曲7日点唱量
0 as supp_cnt, -- 歌曲7日点赞量
count(distinct uid), -- 7日点唱用户数
count(distinct order_id) --7日点唱订单数
FROM
TO_CLIENT_SONG_PLAY_OPERATE_REQ_D
WHERE data_dt >= 前7天日期 and data_dt [/span>= 当前天日期
GROUP BY songid
3.近三十天点唱量 RCT_30_SING_CNT
近三十天点赞量 RCT_30_SUPP_CNT
近三十天点唱用户数 RCT_30_USR_CNT
近三十天点唱订单数 RCT_30_ORDR_CNT
SELECT
songid , -- 歌曲id
count(songid), -- 歌曲30日点唱量
0 as supp_cnt, -- 歌曲30日点赞量
count(distinct uid), -- 30日点唱用户数
count(distinct order_id) --30日点唱订单数
FROM
TO_CLIENT_SONG_PLAY_OPERATE_REQ_D
WHERE data_dt = 当前天日期
GROUP BY songid
4.近七天最高日点唱量 RCT_7_TOP_SING_CNT
近七天最高日点赞量 RCT_7_TOP_SUPP_CNT
近三十天最高日点唱量 RCT_30_TOP_SING_CNT
近三十天最高日点赞量 RCT_30_TOP_SUPP_CNT
SELECT
NBR, -- 歌曲id
max(CASE WHEN DATE_DT
max(CASE WHEN DATE_DT >= 前7天日期 THEN SUPP_CNT ELSE 0 END) as RCT_7_TOP_SUPP_CNT ，--近七天最高日点赞量
max(SING_CNT) as RCT_30_TOP_SING_CNT ,--近三十天最高日点唱量
max(SUPP_CNT) as RCT_30_TOP_SUPP_CNT -- 近三十天最高日点赞量
FROM
TW_SONG_FTUR_D
WHERE data_dt >= 前30天日期 and data_dt [/span>= 当前天日期
GROUP BY songid
===== 统计歌曲热度 TW_SONG_RSI_D ======
TW_SONG_RSI_D ：
周期（1/7/30） PERIOD
歌曲编号 NBR
歌曲名 NAME
近期歌曲热度 RSI
近期歌曲热度排名 RSI_RANK
数据日期 DATA_DT
TW_SONG_FTUR_D - 歌曲特征日统计
temp:
SELECT
NBR,NAME,(xxx) as 1RSI,(xxx) as 7RSI,(xxx) as 30RSI,data_dt
FROM
TW_SONG_FTUR_D
WHERE data_dt = 20201230
统计歌曲1日热度：
SELECT
"1日" as PERIOD，NBR，NAME，1RSI as RSI,row_number() over(partition by data_dt order by 1RSI desc) as RSI_RANK,DATA_DT
FROM temp
统计歌曲7日热度：
SELECT
"7日" as PERIOD，NBR，NAME，7RSI as RSI,row_number() over(partition by data_dt order by 7RSI desc) as RSI_RANK,DATA_DT
FROM temp
统计歌曲30日热度：
SELECT
"30日" as PERIOD，NBR，NAME，30RSI as RSI,row_number() over(partition by data_dt order by 30RSI desc) as RSI_RANK,DATA_DT
FROM temp
以上三个union 得到总结果
======= 统计歌手热度 TW_SINGER_RSI_D ======
TW_SINGER_RSI_D：
周期 PERIOD
歌手ID SINGER_ID
歌手名称 SINGER_NAME
近期歌手热度 RSI
近期歌手热度排名 RSI_RANK
数据日期 DATA_DT
TW_SONG_FTUR_D - 歌曲特征日统计
temp:
SELECT
SINGER1ID,
SINGER1,
sum(SING_CNT) as TOTAL_SING_CNT ,
sum(SUPP_CNT) as TOTAL_SUPP_CNT ,
sum(RCT_7_SING_CNT) as TOTAL_RCT_7_SING_CNT ,
sum(RCT_7_SUPP_CNT) as TOTAL_RCT_7_SUPP_CNT ,
sum(RCT_7_TOP_SING_CNT) as TOTAL_RCT_7_TOP_SING_CNT ,
sum(RCT_7_TOP_SUPP_CNT) as TOTAL_RCT_7_TOP_SUPP_CNT ,
sum(RCT_30_SING_CNT) as TOTAL_RCT_30_SING_CNT ,
sum(RCT_30_SUPP_CNT) as TOTAL_RCT_30_SUPP_CNT ,
sum(RCT_30_TOP_SING_CNT) as TOTAL_RCT_30_TOP_SING_CNT ,
sum(RCT_30_RCT_30_ORDR_CNT) as TOTAL_RCT_30_ORDR_CNT
FROM
TW_SONG_FTUR_D
WHERE data_dt = 20201230
GROUP BY SINGER1ID,SINGER1
temp2:
SELECT
SINGER1ID,SINGER1,(xxx) as 1RSI,(xxx) as 7RSI,(xxx) as 30RSI,data_dt
FROM
temp
统计歌手1日热度：
SELECT
"1日" as PERIOD，SINGER1ID as SINGER_ID，SINGER1 as SINGER_NAME，
1RSI as RSI,row_number() over(partition by data_dt order by 1RSI desc) as RSI_RANK
FROM temp2
统计歌手7日热度：
SELECT
"7日" as PERIOD，SINGER1ID as SINGER_ID，SINGER1 as SINGER_NAME，
7RSI as RSI,row_number() over(partition by data_dt order by 7RSI desc) as RSI_RANK
FROM temp2
统计歌手30日热度：
SELECT
"30日" as PERIOD，SINGER1ID as SINGER_ID，SINGER1 as SINGER_NAME，
30RSI as RSI,row_number() over(partition by data_dt order by 30RSI desc) as RSI_RANK
FROM temp2
以上三个union 得到总结果
1、用户上报的日志数据 currentday_clientlog.tar.gz 解压之后的部分数据格式如下：（中间用间隔）
1 157530235099712MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 2546, "mid": 99712, "adv_type": 4, "src_type": 2546, "uid": 0, "session_id": 48565, "event_id": 1, "time": 1575302349}3.0.1.152.4.4.30
2 157530235089316MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 2565, "mid": 89316, "adv_type": 4, "src_type": 2565, "uid": 0, "session_id": 33762, "event_id": 1, "time": 1575302348}3.0.1.122.4.4.26
3 157530235054398MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 2546, "mid": 54398, "adv_type": 4, "src_type": 2546, "uid": 0, "session_id": 31432, "event_id": 1, "time": 1575302349}3.0.1.142.4.4.30
4 157530235049120MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 2546, "mid": 49120, "adv_type": 4, "src_type": 2546, "uid": 0, "session_id": 25078, "event_id": 1, "time": 1575302350}3.0.1.152.4.4.30
5 157530235057563MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 2571, "mid": 57563, "adv_type": 4, "src_type": 2571, "uid": 0, "session_id": 13577, "event_id": 1, "time": 1575302349}3.0.1.152.4.4.30
6 157530235091764MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 2546, "mid": 91764, "adv_type": 4, "src_type": 2546, "uid": 0, "session_id": 7075, "event_id": 1, "time": 1575302348}3.0.1.122.4.4.26
7 157530235199695MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 2558, "mid": 99695, "adv_type": 4, "src_type": 2558, "uid": 0, "session_id": 27410, "event_id": 1, "time": 1575302350}3.0.1.152.4.4.30
8 157530235198105MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 1619, "mid": 98105, "adv_type": 4, "src_type": 1619, "uid": 0, "session_id": 1553, "event_id": 1, "time": 1575302349}3.0.1.152.4.4.30
9 157530235195059MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 2525, "mid": 95059, "adv_type": 2, "src_type": 0, "uid": 49915629, "session_id": 28419, "event_id": 1, "time": 1575302350}3.0.1.152.4.4.30
10 15753023511958MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 2546, "mid": 1958, "adv_type": 4, "src_type": 2546, "uid": 0, "session_id": 60656, "event_id": 1, "time": 1575302350}3.0.1.152.4.4.30
11 157530235152166MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 2565, "mid": 52166, "adv_type": 4, "src_type": 2565, "uid": 0, "session_id": 42636, "event_id": 1, "time": 1575302350}3.0.1.152.4.4.30
12 157530235191267MINIK_CLIENT_ADVERTISEMENT_RECORD{"src_verison": 2565, "mid": 91267, "adv_type": 4, "src_type": 2565, "uid": 0, "session_id": 81201, "event_id": 1, "time": 1575302349}3.0.1.142.4.4.30
............
2、歌曲基本信息表【歌曲对应歌手信息】中的数据来源于原业务系统数据库songdb。
数据库表song的建表sql及部分数据如下：（对应sql文件为 songdb.sql）
--
-- Table structure for song
--
DROP TABLE IF EXISTS song;
CREATE TABLE song (
source_id varchar(100) NOT NULL COMMENT '主键ID',
name varchar(100) DEFAULT NULL //代码效果参考：http://www.zidongmutanji.com/zsjx/496207.html
other_name varchar(100) DEFAULT NULL COMMENT '其他名字',
source int(11) DEFAULT NULL COMMENT '来源',
album varchar(100) DEFAULT NULL COMMENT '所属专辑',
product varchar(100) DEFAULT NULL COMMENT '发行公司',
language varchar(100) DEFAULT NULL COMMENT '歌曲语言',
video_format varchar(100) DEFAULT NULL COMMENT '视频风格',
duration int(11) DEFAULT NULL COMMENT '时长',
singer_info varchar(100) DEFAULT NULL COMMENT '歌手信息',
post_time varchar(100) DEFAULT NULL COMMENT '发行时间',
pinyin_first varchar(100) DEFAULT NULL COMMENT '歌曲首字母',
pinyin varchar(100) DEFAULT NULL COMMENT '歌曲全拼',
singing_type int(11) DEFAULT NULL COMMENT '演唱类型',
original_singer varchar(100) DEFAULT NULL COMMENT '原唱歌手',
lyricist varchar(100) DEFAULT NULL COMMENT '填词',
composer varchar(100) DEFAULT NULL //代码效果参考：http://www.zidongmutanji.com/zsjx/302911.html
bpm int(11) DEFAULT NULL COMMENT 'BPM值',
star_level int(11) DEFAULT NULL COMMENT '星级',
video_quality int(11) DEFAULT NULL COMMENT '视频画质',
video_make int(11) DEFAULT NULL COMMENT '视频制作方式',
video_feature int(11) DEFAULT NULL COMMENT '视频画面特征',
lyric_feature int(11) DEFAULT NULL COMMENT '歌词字母特点',
Image_quality int(11) DEFAULT NULL COMMENT '画质评价',
subtitles_type int(11) DEFAULT NULL COMMENT '字幕类型',
audio_format int(11) DEFAULT NULL COMMENT '音频格式',
original_sound_quality int(11) DEFAULT NULL COMMENT '原唱音质',
original_track int(11) DEFAULT NULL COMMENT '音轨',
original_track_vol int(11) DEFAULT NULL COMMENT '原唱音量',
accompany_version int(11) DEFAULT NULL COMMENT '伴唱版本',
accompany_quality int(11) DEFAULT NULL COMMENT '伴唱音质',
acc_track_vol int(11) DEFAULT //代码效果参考：http://www.zidongmutanji.com/bxxx/44218.html
accompany_track int(11) DEFAULT NULL COMMENT '伴唱音轨',
width int(11) DEFAULT NULL COMMENT '视频分辨率W',
height int(11) DEFAULT NULL COMMENT '视频分辨率H',
video_resolution int(11) DEFAULT NULL COMMENT '视频分辨率',
song_version int(11) DEFAULT NULL COMMENT '编曲版本',
authorized_company varchar(100) DEFAULT NULL COMMENT '授权公司',
status int(11) DEFAULT NULL COMMENT '状态',
publish_to varchar(100) DEFAULT NULL COMMENT '产品类型',
PRIMARY KEY (source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Records of song
--
INSERT INTO song VALUES ('10000001', 'Footprints In The Sand（live）', '沙滩上的脚印（live）', '1', '【{\"name\":\"《中国好声音第二季 第十五期》\",\"id\":\"5ce5ffdb2a0b8b6b4707263e\"}】', '', 'E', 'X', '242', '【{\"name\":\"萱萱\",\"id\":\"20342\"}】', '1.3810752E+12', 'FITSSTSDJYL', 'footprintsinthesandshatanshangdejiaoyinlive', '2', '【{\"name\":\"Leona Lewis\",\"id\":\"2734\"}】', '【{\"name\":\"Richard Page\",\"id\":\"1025472\"},{\"name\":\" Per Magnusson\",\"id\":\"1025473\"}】', '【{\"name\":\"David Kreuger\",\"id\":\"1025474\"},{\"name\":\"Simon Cowell\",\"id\":\"1025475\"}】', '0', '0', '2', '0', '0', '0', '0', '1', '3', '0', '1', '50', '2', '1', '50', '0', '1920', '1080', '1', '1', '', '3', '【2.0,8.0】');
INSERT INTO song VALUES ('10000002', '不许你注定一人', '', '1', '【{\"name\":\"《不许你注定一人》\",\"id\":\"5c19b89450057d3b20232f7a\"}】', '', 'C', 'M', '305', '【{\"name\":\"Dear Jane\",\"id\":\"1187\"}】', '1.3819392E+12', 'BXNZDYR', 'buxunizhudingyiren', '1', '【】', '【{\"name\":\" Howie@Dear Jane\",\"id\":\"1021658\"}】', '【{\"name\":\" Howie@Dear Jane\",\"id\":\"1021658\"}】', '0', '0', '1', '0', '0', '0', '0', '1', '3', '0', '1', '50', '1', '1', '50', '0', '1920', '1080', '1', '1', '', '3', '【8.0,2.0,9】');
INSERT INTO song VALUES ('10000003', '爱上你的美（live）', '', '1', '【{\"name\":\"《最美和声第一季 第13期》\",\"id\":\"5db140bda1b80f5bc4e8960f\"}】', '', 'M', 'M', '220', '【{\"name\":\"胡海泉\",\"id\":\"10306\"},{\"name\":\"丁于\",\"id\":\"6311\"}】', '1.3815072E+12', 'ASNDML', 'aishangnidemeilive', '1', '【】', '【{\"name\":\"胡海泉\",\"id\":\"1027408\"}】', '【{\"name\":\"Bob Crewe\",\"id\":\"1021234\"},{\"name\":\"Bob Gaudio\",\"id\":\"1021235\"}】', '0', '0', '2', '0', '0', '0', '0', '1', '3', '0', '1', '50', '1', '1', '50', '0', '1920', '1080', '1', '2', '', '3', '【2.0,8.0】');
INSERT INTO song VALUES ('10000004', '翅膀', '', '1', '【{\"name\":\"《最美和声 第13期》\",\"id\":{\"$oid\":\"5c19ae0e50057d3b202313e3\"}}】', '蓝色火焰', 'M', 'M', '196', '【{\"name\":\"胡海泉\",\"id\":\"10306\"},{\"name\":\"田斯斯\",\"id\":\"20343\"}】', '2013-10-31 00:00:00', 'CB', 'chibang', '0', '', '', '', '0', '0', '2', '0', '0', '0', '0', '0', '0', '0', '1', '50', '0', '0', '50', '0', '1920', '1080', '1', '0', '', '1', '【2.0,8.0】');
INSERT INTO song VALUES ('10000005', 'Baby I Love You', '', '1', '【{\"name\":\"《阿里郎》\",\"id\":\"5c19b20850057d3b20231e48\"}】', '', 'M', 'M', '194', '【{\"name\":\"金贵晟\",\"id\":\"20245\"}】', '1.3783968E+12', 'BILY', 'babyiloveyou', '1', '【】', '【{\"name\":\"代岳冬\",\"id\":\"1037983\"}】', '【{\"name\":\"金贵晟\",\"id\":\"20245\"}】', '0', '0', '1', '0', '0', '0', '0', '1', '3', '0', '1', '50', '1', '2', '50', '0', '1920', '1080', '1', '1', '', '3', '【2.0,8.0】');
INSERT INTO song VALUES ('10000007', 'My Little Friend', '', '1', '【{\"name\":\"《Say The Words》\",\"id\":\"5c19a78350057d3b2023025d\"}】', '', 'E', 'S', '340', '【{\"name\":\"曲婉婷\",\"id\":\"16277\"}】', '1.3569696E+12', 'MLF', 'mylittlefriend', '1', '【】', '【{\"name\":\"曲婉婷\",\"id\":\"16277\"}】', '【{\"name\":\"曲婉婷\",\"id\":\"16277\"}】', '0', '0', '1', '0', '0', '0', '0', '1', '3', '0', '1', '50', '1', '2', '50', '0', '1920', '1080', '1', '1', '', '3', '【2.0,8.0】');
INSERT INTO song VALUES ('10000008', 'X', '', '1', '【{\"name\":\"《痞得胖》\",\"id\":\"5c19adf550057d3b2023139f\"}】', '禾广娱乐', 'M', 'M', '328', '【{\"name\":\"怕胖团\",\"id\":\"20474\"}】', '2013-10-31 00:00:00', 'X', '', '1', '【】', '【{\"name\":\"闪亮\",\"id\":\"1020755\"}】', '【{\"name\":\"闪亮\",\"id\":\"1020755\"}】', '92', '5', '1', '0', '0', '0', '0', '1', '3', '0', '1', '50', '0', '1', '50', '2', '1920', '1080', '1', '0', '', '1', '【8.0,2.0,9】');
INSERT INTO song VALUES ('10000009', 'Melody(Live)', '', '1', '【{\"name\":\"《中国好声音第二季 第十五期》\",\"id\":\"5ce5ffdb2a0b8b6b4707263e\"}】', '', 'M', 'X', '205', '【{\"name\":\"萱萱\",\"id\":\"20342\"}】', '1.3810752E+12', 'ML', 'melodylive', '2', '【{\"name\":\" 陶喆 \",\"id\":\"1026474\"}】', '【{\"name\":\"娃娃\",\"id\":\"1023192\"},{\"name\":\" 陶喆 \",\"id\":\"1026474\"}】', '【{\"name\":\" 陶喆 \",\"id\":\"1026474\"}】', '0', '0', '2', '0', '0', '0', '0', '1', '3', '0', '1', '50', '2', '1', '50', '0', '1920', '1080', '1', '2', '', '3', '【2.0,8.0】');
INSERT INTO song VALUES ('10000010', '到底是哪个混蛋敢欺负我的朋友', '', '1', '《LANDIVA》', '', 'M', 'M', '229', '【{\"name\":\"蓝心湄\",\"id\":\"15575\"}】', '2013-11-01 00:00:00', 'DDSNGHDGQFWDPY', 'daodishinagehundanganqifuwodepengyou', '0', '', '', '', '0', '0', '1', '0', '0', '0', '0', '0', '0', '0', '1', '50', '0', '0', '49', '0', '1920', '1080', '1', '0', '', '1', '【2.0,8.0】');
INSERT INTO song VALUES ('10000011', '爱的海洋', '', '1', '【{\"name\":\"《Say The Words 我为你歌唱》\",\"id\":\"5c19ac9050057d3b20230fe3\"}】', '', 'M', 'M', '230', '【{\"name\":\"曲婉婷\",\"id\":\"16277\"}】', '1.3820256E+12', 'ADHY', 'aidehaiyang', '1', '【】', '【{\"name\":\"曲婉婷\",\"id\":\"16277\"}】', '【{\"name\":\"曲婉婷\",\"id\":\"16277\"}】', '0', '0', '1', '0', '0', '0', '0', '1', '3', '0', '1', '50', '1', '1', '50', '0', '1920', '1080', '1', '1', '', '3', '【8.0,2.0,9】');
INSERT INTO song VALUES ('10000012', '爱洗虾米', '', '1', '【{\"name\":\"《旺得福》\",\"id\":{\"$oid\":\"5c19a6c550057d3b20230062\"}}】', '亚神音乐', 'T', 'M', '273', '【{\"name\":\"旺福\",\"id\":\"9300\"}】', '2013-11-01 00:00:00', 'AXXM', 'aixixiami', '0', '', '', '', '0', '0', '2', '0', '0', '0', '0', '0', '0', '0', '1', '50', '0', '0', '50', '0', '1920', '1080', '1', '0', '', '1', '【2.0】');
INSERT INTO song VALUES ('10000013', '爱投罗网', '', '1', '【{\"name\":\"《狮子吼》\",\"id\":\"5c19b6a050057d3b20232a58\"}】', '', 'M', 'M', '319', '【{\"name\":\"罗志祥\",\"id\":\"1021663\"}】', '1.38384E+12', 'ATLW', 'aitouluowang', '1', '【】', '【{\"name\":\"李宗恩\",\"id\":\"1021817\"}】', '【{\"name\":\"Drew Ryan Scott\",\"id\":\"1027900\"},{\"name\":\"Jacim Persson\",\"id\":\"1031575\"}】', '0', '0', '1', '0', '0', '0', '0', '1', '3', '0', '1', '50', '1', '1', '50', '0', '1920', '1080', '1', '1', '', '3', '【8.0,2.0,9】');
将数据导入到mysql数据库的步骤：
（1）打开xshell，连接mysql数据库，创建songdb数据库
（2）打开Navigate，连接到192.168.179.14节点，运行sql文件songdb.sql
（3）查看导入结果
表数据如下：
表结构如下:
数据库表song里面的数据会导入到ODS层中的TO_SONG_INFO_D表中，TO_SONG_INFO_D表信息如下：
EDS层对应的表结构如下
3、数据仓库分层及数据流转处理流程
这里根据需求将表分成如下三层：
基于以上逻辑表建立物理模型如下：
（1） TO_CLIENT_SONG_PLAY_OPERATE_REQ_D 客户端歌曲播放表
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
)
partitioned by (data_dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '';
（2）TO_SONG_INFO_D 歌库歌曲表
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,
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 '';
（3）TW_SONG_BASEINFO_D 歌曲基本信息日全量表
CREATE EXTERNAL TABLE TW_SONG_BASEINFO_D(
NBR string,
NAME string,
SOURCE int,
ALBUM string,
PRDCT string,
LANG string,
VIDEO_FORMAT string,
DUR int,
SINGER1 string,
SINGER2 string,
SINGER1ID string,
SINGER2ID string,
MAC_TIME int,
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,
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 array[/span>string
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS PARQUET
LOCATION '';
（4）TW_SONG_FTUR_D 歌曲特征日统计表
CREATE EXTERNAL TABLE TW_SONG_FTUR_D(
NBR string,
NAME string,
SOURCE int,
ALBUM string,
PRDCT string,
LANG string,
VIDEO_FORMAT string,
DUR int,
SINGER1 string,
SINGER2 string,
SINGER1ID string,
SINGER2ID string,
MAC_TIME int,
SING_CNT int,
SUPP_CNT int,
USR_CNT int,
ORDR_CNT int,
RCT_7_SING_CNT int,
RCT_7_SUPP_CNT int,
RCT_7_TOP_SING_CNT int,
RCT_7_TOP_SUPP_CNT int,
RCT_7_USR_CNT int,
RCT_7_ORDR_CNT int,
RCT_30_SING_CNT int,
RCT_30_SUPP_CNT int,
RCT_30_TOP_SING_CNT int,
RCT_30_TOP_SUPP_CNT int,
RCT_30_USR_CNT int,
RCT_30_ORDR_CNT int
)
PARTITIONED BY (data_dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '';
（5）TW_SINGER_RSI_D 歌手影响力日统计表
CREATE EXTERNAL TABLE TW_SINGER_RSI_D(
PERIOD string,
SINGER_ID string,
SINGER_NAME string,
RSI string,
RSI_RANK int
)
PARTITIONED BY (data_dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '';
（6）TW_SONG_RSI_D 歌曲影响力日统计表
<span style="color:
