数据仓库与数据挖掘- 期末课程设计-游戏日志分析

大作业题目

游戏日志分析

• 目的：

1.运用所学的知识，通过搭建日志分析平台，让游戏公司进行科学的运营。

2.通过此实验掌握一些基本的操作

二、环境：

1. 安装VMware Workstation Pro、搭建hadoop集群、hive能正常运行、安装xsell

2.物理机上安装mysql、Navicat Premium 15、FineBI、配置jupyter lab环境

三、内容：

项目背景 

 某游戏上线以后运营效果没有达到预期目标，希望通过搭建日志分析平台，进行科学的运营。

需求概述 

为该游戏提供实时数据统计分析服务，监控版本质量、渠道状况、用户画像属性及用户细分行为，通过数据可视 化展现，协助产品运营决策。作为项目的一期需要完成数据离线统计分析服务。

数据结构

订单表

用户表

游戏登录记录表

基本需求

一、游戏概况

1、用户详情：完整的用户信息 （需求一）

2、游戏用户概况（需求二）

(1) 用户分布情况：累积玩家分布情况

(2) 人均使用时长：累计时长 / 累计用户

(3) 次均使用时长：累计时长 / 累计启动次数

3、总收入统计和每日收入统计（需求三）

二、首日、第三日用户每日留存率（需求四）

统计首日新用户每日的用户留存率，第三日（2017-01-03）新用户接下来几天的每日用户留存率。

例：首日用户第3 日留存率 = 首日登录的用户，未来三天内任意一天登录的用户/首日总用户数

首日用户第5 日留存率 = 首日登录的用户，未来五天内任意一天登录的用户/首日总用户数

三、每日用户数据分析（需求五）

每日新用户数和老用户数

四、TopN（需求六）

1、游戏时长Top10

2、游戏玩家充值Top10

五、数据可视化

使用个人版帆软BI或其他可视化工具如Echars等进行可视化呈现

要求：

1、数仓层级明确，3张源表对应3张ods层级表、3张DWD层级表。根据业务需求及数据汇聚需求，形成DWS层级表。将最终进行可视化呈现的数据放至ADM层级表中。

2、将数据使用sqoop从MysQL导入至Hive ODS层中

3、按数仓各层级要求，结合数仓建设理念，完成对数仓各层级的建设

4、生成最后应用层数据后，将数据导出至个人电脑MysQL中，可视化呈现的数据源使用个人电脑

5、根据最后数据情况，为此游戏公司提出建议。

6、可对项目仔细分析后，开发除基础需求外的额外需求的功能实现。

四、步骤（包含运行结果）：

第一、把game.sql从自己的盘中导入mysql应用中。

1.打开命令提示符窗口

2.用mysql -uroot -p123456命令进入mysql

3.show databases;查看数据库

4.use game;使用数据库

5.使用source D:/sql/game.sql命令把game.sql解压，此过程时间较长需要耐心等待，如图所示。（建议下载Navicat Premium 15应用导入数据）

传入完成后打开mysql应用可以看到如下内容即为导入成功，分别可以查看game_user（用户表）,order（订单表）,region（行政区化表）,registration_recond（登录表）表

第二、基本数据准备（ods层）

1.1创建数据库game并查看是否创建成功

1.2.在beeline中创建用户表ods_order（用户登陆表）

create table ods_order(order_id varchar(36),

use_id int,

amount int,

drder_time string,

is_success char(1))

ROW FORMAT DELIMITED

FIELDS TERMINATED BY "\t";

1.3.使用sqoop将mysql中的order数据导入hive中的ods_order中

sqoop import \

                --connect jdbc:mysql://192.168.36.1:3306/game \

                --username root \

                --password 123456 \

                --table order\

                --fields-terminated-by '\t' \

                --hive-database game\

                --hive-table ods_order\

                -m 1 \

                --hive-import \

                --target-dir /temp

导入成功的标志如图所示

1.4.查看数据是否导入成功结果展示如图所示

查看表代码

select * from odrer_table

2.1创建用户表

2、创建用户表，代码如下：

create table ods_game_user(user_id int,

device_id Varchar(60),

name Varchar(20),

idcard Varchar(18))

ROW FORMAT DELIMITED

FIELDS TERMINATED BY "\t";

2.2使用sqoop将mysql中的game_user数据导入hive中的ods_game_user中

sqoop import \

                --connect jdbc:mysql://192.168.36.1:3306/game \

                --username root \

                --password 123456 \

                --table game_user\

                --fields-terminated-by '\t' \

                --hive-database game\

                --hive-table ods_game_user\

                -m 1 \

                --hive-import \

                --target-dir /temp

2.3查看数据是否导入成功结果展示如图所示

3.1创建登陆表

create table ods_registration_record(

device_id Varchar(60),

os Varchar(20),

os_version Varchar(20),

start_time string,

end_time string,

duration string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY "\t";

3.2使用sqoop将mysql中的registration_recordr数据导入hive中的ods_registration_record中

sqoop import \

                --connect jdbc:mysql://192.168.36.1:3306/game \

                --username root \

                --password 123456 \

                --table registration_record\

                --fields-terminated-by '\t' \

                --hive-database game\

                --hive-table ods_registration_record\

                -m 1 \

                --hive-import \

                --target-dir /temp

3.3查看数据是否导入成功结果展示如图所示

4.1创建行政地区表

create table ods_region(

id int,diqu varchar(40))

ROW FORMAT DELIMITED

FIELDS TERMINATED BY "\t";

4.2使用sqoop将mysql中的region数据导入hive中的ods_region中

sqoop import \

                --connect jdbc:mysql://192.168.36.1:3306/game \

                --username root \

                --password 123456 \

                --table region\

                --fields-terminated-by '\t' \

                --hive-database game\

                --hive-table ods_region\

                -m 1 \

                --hive-import \

                --target-dir /temp

4.3查看数据是否导入成功结果展示如图所示

5.在game数据库中所建的四张表如图所示

第三、数据清洗（DWD层）

1.用户详情：完整的用户信息 （需求一）

1.1将登录表和用户表连接起来创建dwd_game_user表获得用户基本信息

create table

dwd_game_user

as select

ods_game_user.user_id,

ods_game_user.device_id,

ods_game_user.name,

ods_game_user.idcard,

ods_region.diqu,

(case when substr(ods_game_user.idcard,17,1)%2==0

then '女' else '男' end) sex,

substr(ods_game_user.idcard,7,6) year_month,

year(current_date())-substr(ods_game_user.idcard,7,4) age

from ods_game_user

left join ods_region  

on substr(ods_game_user.idcard,1,6)=ods_region.id;

1.2.查看dwd_game_user表是否有信息，并看信息是否有误（由于表比较长，这里切成两张图展示）

2.1在ods_order表中插入所需要的用户信息数据建立dwd_order表

create table

dwd_order

as select

ods_order.order_id,

ods_order.use_id,

ods_game_user.user_id,

ods_game_user.name,

ods_game_user.idcard,

ods_order.amount,  

ods_order.drder_time,

ods_order.is_success

from ods_order

left join ods_game_user  

on ods_order.use_id=ods_game_user.user_id;

2.2结果展示如图所示

3.1 在ods_registration_record表中插入用户信息数据建立dwd_registration_record表

create table

dwd_registration_record

as select

ods_registration_record.device_id,

ods_registration_record.os,

ods_registration_record.os_version,

ods_registration_record.start_time,

ods_registration_record.end_time,

ods_registration_record.duration,

ods_game_user.user_id,

ods_game_user.name,

ods_game_user.idcard

from ods_registration_record

left join ods_game_user  

on ods_registration_record.device_id=ods_game_user.device_id;

3.2结果展示如图所示

4.建立的三张dwd表如图所示

第四、1.游戏用户概况（需求二）dwm层

1.1用户分布情况：累积玩家分布情况

create table dwm_users_fenbu as

select  diqu,count(diqu) as num_people from dwd_game_user group by diqu;

select *from dwm_users_fenbu limit 6;

1.2人均使用时长：累计时长 / 累计用户

select sum(duration)/count(user_id) as dwm_avgtime from  dwd_registration_record;

1.3次均使用时长：累计时长 / 累计启动次数

select sum(duration)/count(device_id) as dwm_tow_time from dwd_registration_record;

2.总收入统计和每日收入统计（需求三）

2.1总收入

select sum(amount)

from dwd_order where dwd_order.is_success=='1';

2.2每日收入

create table dws_day_amount1 as

select to_date(drder_time),sum(amount)

from ods_order

group by to_date(drder_time);

select *from dws_day_amount1 limit 6;

第五、首日、第三日用户每日留存率（需求四）

统计首日新用户每日的用户留存率，第三日（2017-01-03）新用户接下来几天的每日用户留存率。

例：首日用户第3 日留存率 = 首日登录的用户，未来三天内任意一天登录的用户/首日总用户数

首日用户第5 日留存率 = 首日登录的用户，未来五天内任意一天登录的用户/首日总用户数

1.1创建新的用户登陆表

create table dwd_login as

select device_id,substr(start_time,1,10) login_time

from ods_registration_record;

1.2创建首日新用户登陆表

create table dwd_new_user_login as

select distinct(device_id),

login_time from dwd_login

where login_time='2017-01-01';

1.3创建老用户登陆表

create table dwd_old_user_login as

select a.device_id,

min(b.login_time) log_in_time

from dwd_new_user_login a

left join dwd_login b

on a.device_id=b.device_id

where b.login_time>'2017-01-01'

group by a.device_id;

1.3.1老用户登录表如图所示

2.1创建每日留存率表

create table dwm_retention_day as

select

count(a.device_id) as monday,

sum(if(datediff(b.log_in_time,a.login_time)=1,1,0)) / count(a.device_id) as tuesday,

sum(if(datediff(b.log_in_time,a.login_time)<3,1,0)) / count(a.device_id) as wednesday,

sum(if(datediff(b.log_in_time,a.login_time)<4,1,0)) / count(a.device_id) as thursday,

sum(if(datediff(b.log_in_time,a.login_time)<5,1,0)) / count(a.device_id) as friday,

sum(if(datediff(b.log_in_time,a.login_time)<6,1,0)) / count(a.device_id) as saturday,

sum(if(datediff(b.log_in_time,a.login_time)<7,1,0)) / count(a.device_id) as sunday

from dwd_new_user_login as a

left join  dwd_old_user_login as b

on  a.device_id==b.device_id;

2.2每日留存率结果如图所示

第六、每日用户数据分析（需求五）

每日新用户数和老用户数

1. 每天新用户

create table dwm_new_user as

select

idcard,

min(substr(drder_time,1,10)) as log_time

from dwd_order

group by idcard:

1.1查看结果如图所示

1.2统计每天新用户

create table dwm_day_new_user as

select log_time,

count(idcard) as day_new_user

from dwm_new_user

group by log_time;

1.3统计每日总的人数

create table dwm_total_user as

select substr(start_time,1,10) as start_time,

count(distinct device_id) as day_sumuser

from ods_registration_record

group by substr(start_time,1,10);

1.4把每日总的人数、新用户、老用户都统计出来

create table dws_new_old_user as

select

a.start_time,

a.day_sumuser,

b.day_new_user,

a.day_sumuser-b.day_new_user day_old_user

from dwm_total_user a

left join

dwm_day_new_user b

on a.start_time==b.log_time;

1.5.结果如图所示

第七、TopN（需求六）

游戏时长Top10

create table dws_game_time as

select device_id,sum(duration) top

from dwd_registration_record

group by device_id;

2.游戏玩家充值Top10

create table dws_game_amount as

select use_id,sum(amount) amount

from dwd_order

where dwd_order.is_success=='1'

group by use_id;

第八、数据可视化

使用个人版帆软BI或其他可视化工具如Echars等进行可视化呈现

（一）、使用个人版帆软BI进行可视化

1.安装Navicat Premium 15并连接mysql中的数据库game（安装过程省略）

2.连接成功后如图，有此数据库

3.命令提示符打开mysql（mysql – u root -p123456）并使用数据库game

4.Sqoop导出，将hive中的表导入mysql中，或Navicat Premium 15中（mysql与Navicat Premium 15数据同步）

4.1在Navicat Premium 15中建表与hive中相应的表结构字段保持一致

4.2.sqoop导出通用语句

4.3如dws_game_time表导出如图所示

4.3.1导出成功的标志如图所示

4.3.2在Navicat Premium 15中查看数据导出是否正常，正常情况如图所示

4.3.3把所需表按此方法分别导出，导出的所有表如图所示

5. 用FineBI连接mysql，连接成功如图所示

6.在MyProject中添加表，选择数据库连接，选择表

7.进入如下界面进行可视化相关操作

8.可视化展示如图所示

（二）使用jupyter lab工具实现可视化

1.1将此表导出到自己D盘中

1.2.在本地中如图所示

1.3.读取数据如图

1.4.代码如图所示

1.5.可视化展示如图

2.1读取每日累计金额表如图

2.2代码如图

2.3可视化如图所示

3.1对每日新用户和消费金额进行分栏可视化（代码如图）

3.2可视化展示如图所示

第九、建议

单从每日新用户和每日累计金额上看，新用户相对较多的集中在第5日和第7日，这两日的累计金额相对也较大，是盈利的很好机会，公司应该多次研究客流量，从而来获得最大收益，还可以根据用户的偏好，提供更多的服务，从而赚取更多的收益。而其他几日新用户及消费金额都相对较少，可能这几天大都是上班时间，大家都忙于工作，但是也有很多用户是闲暇的，那么公司应当多设法几招，多研究如何吸引这些用户来消费，从而赚取更多的金额。

• 总结：通过此次游戏项目分析，我掌握了hive的一些基本操作，巩固了上课所学的知识，并学会了一些可视化运用工具、也了解了这个游戏公司的一些弊端，并给出相应的建议。