项目背景

本次的案例将某出行打车的日志数据来进行数据分析，例如：我们需要统计某一天订单量是多少、预约订单与非预约订单的占比是多少、不同时段订单占比等。通过对这些数据进行分析，了解到不同区域、不同时段运营情况。

架构方案

1、基于Hadoop的HDFS文件系统存储数据。

2、基于Hive构建数据仓库。

3、使用Zeppelin进行SQL指令交互

4、使用Sqoop导出分析后的数据到传统型数据库，便于后期应用

5、使用Superset来实现数据可视化展示

数据来源

数据主要来源于四个日志文件：

1、用户打车订单日志





2、用户取消订单日志





3、用户支付日志





4、用户评价日志

环境准备：

启动hadoop

启动hive

nohup /opt/module/apache-hive-3.1.2-bin/bin/hive --service metastore &

nohup /opt/module/apache-hive-3.1.2-bin/bin/hive --service hiveserver2 &

打开zeppelin

/opt/module/zeppelin-0.10.0-bin-all/bin/zeppelin-daemon.sh start

在浏览器输入hadoop102:8080（我的主机是hadoop102）

一、创建数据库

根据数仓分层体系架构，我们把数据仓库分为以下三层：

ODS层 ----源数据层

DW层 ----数据仓库层

APP层 ----应用层



一般，ODS层存放我们的源数据，即从业务系统直接拿过来的未经处理的，DW层存放我们处理后的数据，APP层存放我们计算后的统计指标。

所以我们创建三个数据库，来管理每一层的表数据

1、创建ods库

create database if not exists ods_didi;

2、创建dw库

create database if not exists dw_didi;

3、创建app库

create database if not exists app_didi;

4、查看结果

show databases;

二、创建表（ODS层）

1、创建打车订单表

create table if not exists ods_didi.t_user_order(

orderId string comment '订单id',

telephone string comment '打车用户手机',

lng string comment '用户发起打车的经度',

lat string comment '用户发起打车的纬度',

province string comment '所在省份',

city string comment '所在城市',

es_money double comment '预估打车费用',

gender string comment '用户信息 - 性别',

profession string comment '用户信息 - 行业',

age_range string comment '年龄段（70后、80后、...）',

tip double comment '小费',

subscribe int comment '是否预约（0 - 非预约、1 - 预约）',

sub_time string comment '预约时间',

is_agent int comment '是否代叫（0 - 本人、1 - 代叫）',

agent_telephone string comment '预约人手机',

order_time string comment '预约时间'

)

partitioned by (dt string comment '时间分区')

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

2、创建取消订单表

create table if not exists ods_didi.t_user_cancel_order(

orderId string comment '订单ID',

cstm_telephone string comment '客户联系电话',

lng string comment '取消订单的经度',

lat string comment '取消订单的纬度',

province string comment '所在省份',

city string comment '所在城市',

es_distance double comment '预估距离',

gender string comment '性别',

profession string comment '行业',

age_range string comment '年龄段',

reason int comment '取消订单原因（1 - 选择了其他交通方式、2 - 与司机达成一致，取消订单、3 - 投诉司机没来接我、4 - 已不需要用车、5 - 无理由取消订单）',

cancel_time string comment '取消时间'

)

partitioned by (dt string comment '时间分区')

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

3、创建订单支付表

create table if not exists ods_didi.t_user_pay_order(

id string comment '支付订单ID',

orderId string comment '订单ID',

lng string comment '目的地的经度（支付地址）',

lat string comment '目的地的纬度（支付地址）',

province string comment '省份',

city string comment '城市',

total_money double comment '车费总价',

real_pay_money double comment '实际支付总额',

passenger_additional_money double comment '乘客额外加价',

base_money double comment '车费合计',

has_coupon int comment '是否使用优惠券（0 - 不使用、1 - 使用）',

coupon_total double comment '优惠券合计',

pay_way int comment '支付方式（0 - 微信支付、1 - 支付宝支付、3 - QQ钱包支付、4 - 一网通银行卡支付）',

mileage double comment '里程（单位公里）',

pay_time string comment '支付时间'

)

partitioned by (dt string comment '时间分区')

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

4、创建用户评价表

create table if not exists ods_didi.t_user_evaluate(

id string comment '评价日志唯一ID',

orderId string comment '订单ID',

passenger_telephone string comment '用户电话',

passenger_province string comment '用户所在省份',

passenger_city string comment '用户所在城市',

eva_level int comment '评价等级（1 - 一颗星、... 5 - 五星）',

eva_time string comment '评价时间'

)

partitioned by (dt string comment '时间分区')

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

5、查看结果

use ods_didi;

show tables;

三、加载数据

1、加载打车订单日志

load data local inpath '/home/atguigu/data/order.csv' into table ods_didi.t_user_order partition (dt='2020-04-12');

2、加载取消订单日志

load data local inpath '/home/atguigu/data/cancel_order.csv' into table ods_didi.t_user_cancel_order partition (dt='2020-04-12');

3、加载支付日志

load data local inpath '/home/atguigu/data/pay.csv' into table ods_didi.t_user_pay_order partition (dt='2020-04-12');

4、加载评价日志

load data local inpath '/home/atguigu/data/evaluate.csv' into table ods_didi.t_user_evaluate partition (dt='2020-04-12');

5、查看结果

use ods_didi;

select * from t_user_order limit 5;

select * from t_user_cancel_order limit 5;

select * from t_user_pay_order limit 5;

select * from t_user_evaluate limit 5;

四、创建宽表（DW层）

创建宽表用于保存预处理后的数据。宽表的字段比原表多，所以叫宽表。



-- 创建宽表

create table if not exists dw_didi.t_user_order_wide(

orderId string comment '订单id',

telephone string comment '打车用户手机',

lng string comment '用户发起打车的经度',

lat string comment '用户发起打车的纬度',

province string comment '所在省份',

city string comment '所在城市',

es_money double comment '预估打车费用',

gender string comment '用户信息 - 性别',

profession string comment '用户信息 - 行业',

age_range string comment '年龄段（70后、80后、...）',

tip double comment '小费',

subscribe int comment '是否预约（0 - 非预约、1 - 预约）',

subscribe_name string comment '是否预约名称',

sub_time string comment '预约时间',

is_agent int comment '是否代叫（0 - 本人、1 - 代叫）',

is_agent_name string comment '是否代叫名称',

agent_telephone string comment '预约人手机',

order_date string comment '预约时间，

yyyy-MM-dd',order_year string comment '年',

order_month string comment '月',

order_day string comment '日',

order_hour string comment '小时',

order_time_range string comment '时间段',

order_time string comment '预约时间'

)

partitioned by (dt string comment '时间分区')

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

五、数据预处理

预处理需求：

1、过滤掉order_time长度小于8的数据，如果小于8，表示这条数据不合法，不应该参加统计。

2、将一些0、1表示的字段，处理为更容易理解的字段。例如：subscribe字段，0表示非预约、1表示预约。我们需要添加一个额外的字段，用来展示非预约和预约，这样将来我们分析的时候，跟容易看懂数据。

3、order_time字段为2020-4-12 1:15，为了将来更方便处理，我们统一使用类似 2020-04-12 01:15来表示，这样所有的order_time字段长度是一样的。并且将日期获取出来

4、为了方便将来按照年、月、日、小时统计，我们需要新增这几个字段。

5、后续要分析一天内，不同时段的订单量，我们需要在预处理过程中将订单对应的时间段提前计算出来。例如：1:00-5:00为凌晨。



这里有个小bug要注意一下：

SELECT DATE_FORMAT('2022-2-22 9:32','yyyy-MM-dd HH:mm:ss');会出错



SELECT DATE_FORMAT('2022-2-22 9:32:8','yyyy-MM-dd HH:mm:ss');正常



SELECT DATE_FORMAT('2022-2-22 9:32','yyyy-MM-dd HH:mm');也会出错



看来是要把秒补全了,我们可以这么做：

select date_format(concat('2022-2-22 9:32',':00'), 'yyyy-MM-dd HH:mm:ss');

-- 数据预处理
insert overwrite table dw_didi.t_user_order_wide partition(dt='2020-04-12')
select
orderId,
telephone,
lng,
lat,
province,
city,
es_money,
gender,
profession,
age_range,
tip,
subscribe,
case when subscribe = 0 then '非预约'
when subscribe = 1 then'预约'
end as subscribe_name, --展示预约和非预约
sub_time,
is_agent,
case when is_agent = 0 then '本人'
when is_agent = 1 then '代叫'
end as is_agent_name, --展示本人和代叫
agent_telephone,
date_format(order_time, 'yyyy-MM-dd') as order_date, --修改订单日期格式
year(date_format(order_time, 'yyyy-MM-dd')) as order_year, --提取年
month(date_format(order_time, 'yyyy-MM-dd')) as order_month, --提取月
day(date_format(order_time, 'yyyy-MM-dd')) as order_day, --提取日
hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) as order_hour, --提取小时
case when hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) > 1 and hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) <= 5 then '凌晨'
when hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) > 5 and hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) <= 8 then '早上'
when hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) > 8 and hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) <= 11 then '上午'
when hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) > 11 and hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) <= 13 then '中午'
when hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) > 13 and hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) <= 17 then '下午'
when hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) > 17 and hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) <= 19 then '晚上'
when hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) > 19 and hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) <= 20 then '半夜'
when hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) > 20 and hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) <= 24 then '深夜'
when hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) >= 0 and hour(date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss')) <= 1 then '凌晨'
else 'N/A'
end as order_time_range,
date_format(concat(order_time,':00'), 'yyyy-MM-dd HH:mm:ss') as order_time --修改订单时间格式
from ods_didi.t_user_order
where dt = '2020-04-12' and length(order_time) >= 8; --过滤order_time长度小于8的数据


-- 查看结果

use dw_didi;

select * from t_user_order_wide limit 5;

六、统计指标

1、订单总笔数

-- 建表（APP层）

create table if not exists app_didi.t_order_total(

date_val string comment '日期（年月日)',

count int comment '订单笔数'

)

partitioned by (month string comment '年月，yyyy-MM')

row format delimited fields terminated by ',';

-- 查询插入数据

insert overwrite table app_didi.t_order_total partition(mOnth='2020-04')

select

'2020-04-12',

count(orderid) as total_cnt

from dw_didi.t_user_order_wide

where dt = '2020-04-12';

-- 查看结果

select * from app_didi.t_order_total;

2、预约和非预约订单占比

-- 建表（APP层）

create table app_didi.t_order_subscribe_total (

date_val string ,

subscribe_name string,

count int

)

partitioned by (month string comment '年月yyyy-MM')

row format delimited fields terminated by ',';

-- 查询插入数据

insert overwrite table app_didi.t_order_subscribe_total partition(mOnth='2020-04')

select

'2020-04-12',

subscribe_name,

count(*)

from dw_didi.t_user_order_wide

where dt = '2020-04-12'

group by subscribe_name;

-- 查看结果

select * from app_didi.t_order_subscribe_total;

3、不同时段订单占比

-- 建表（APP层）

create table if not exists app_didi.t_order_timerange_total(

date_val string comment '日期',

timerange string comment '时间段',

count int comment '订单数量'

)

partitioned by (month string comment '年月，yyyy-MM')

row format delimited fields terminated by ',';

-- 查询插入数据

insert overwrite table app_didi.t_order_timerange_total partition(mOnth= '2020-04')

select

'2020-04-12',

order_time_range,

count(*) as order_cnt

from dw_didi.t_user_order_wide

where dt = '2020-04-12'

group by order_time_range;

-- 查看结果

select * from app_didi.t_order_timerange_total;

4、不同地域订单占比

-- 建表（APP层）

create table if not exists app_didi.t_order_province_total(

order_date string comment '日期',

province string comment '省份',

order_cnt int comment '订单数量'

)

partitioned by (month string comment '年月，yyyy-MM')

row format delimited fields terminated by ',';

-- 查询插入数据

insert overwrite table app_didi.t_order_province_total partition(mOnth= '2020-04')

select

'2020-04-12',

province,

count(*) as order_cnt

from dw_didi.t_user_order_wide

where dt = '2020-04-12'

group by province;

-- 查看结果

select * from app_didi.t_order_province_total;

5、不同年龄段/时段订单占比

-- 建表（APP层）

create table if not exists app_didi.t_order_age_and_time_range_total(

date_val string comment '日期',

age_range string comment '年龄段',

order_time_range string comment '时段',

count int comment '订单数量'

)

partitioned by (month string comment '年月，yyyy-MM')

row format delimited fields terminated by ',';

-- 查询插入数据

insert overwrite table app_didi.t_order_age_and_time_range_total partition(mOnth= '2020-04')

select

'2020-04-12',

age_range,

order_time_range,

count(*) as order_cnt

from dw_didi.t_user_order_wide

where dt = '2020-04-12'

group by age_range,order_time_range;

-- 查看结果

select * from app_didi.t_order_age_and_time_range_total;

七、sqoop导出数据

使用Sqoop导出APP层表到传统型数据库，便于后期应用

一）先在mysql中创建对应的目标数据库和表

mysql -u root -p

-- 创建目标数据库

create database if not exists app_didi;

-- 1.创建订单总笔数表

create table if not exists app_didi.t_order_total(

order_date date,

count int

);

-- 2.创建预约和非预约订单占比表

create table if not exists app_didi.t_order_subscribe_total(

order_date date ,

subscribe_name varchar(20) ,

count int

);

-- 3.创建不同时段订单占比表

create table if not exists app_didi.t_order_timerange_total(

order_date date ,

timerange varchar(20) ,

count int

);

-- 4.创建不同地域订单占比表

create table if not exists app_didi.t_order_province_total(

order_date date ,

province varchar(20) ,

count int

);

-- 5.创建不同年龄段/时段订单占比表

create table if not exists app_didi.t_order_age_and_time_range_total(

order_date date ,

age_range varchar(20) ,

order_time_range varchar(20) ,

count int

);

-- 查看结果

use app_didi;

show tables;

二）导出数据

cd /opt/module/sqoop-1.4.7.bin__hadoop-2.6.0

1、导出订单总笔数表

bin/sqoop export \
--connect jdbc:mysql://hadoop102:3306/app_didi"?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 456789 \
--table t_order_total \
--export-dir /user/hive/warehouse/app_didi.db/t_order_total/mOnth=2020-04


2、导出预约和非预约订单占比表

bin/sqoop export \
--connect jdbc:mysql://hadoop102:3306/app_didi"?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 456789 \
--table t_order_subscribe_total \
--export-dir /user/hive/warehouse/app_didi.db/t_order_subscribe_total/mOnth=2020-04


3、导出不同时段订单占比表

bin/sqoop export \
--connect jdbc:mysql://hadoop102:3306/app_didi"?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 456789 \
--table t_order_timerange_total \
--export-dir /user/hive/warehouse/app_didi.db/t_order_timerange_total/mOnth=2020-04


4、导出不同地域订单占比表

bin/sqoop export \
--connect jdbc:mysql://hadoop102:3306/app_didi"?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 456789 \
--table t_order_province_total \
--export-dir /user/hive/warehouse/app_didi.db/t_order_province_total/mOnth=2020-04


5、导出不同年龄段/时段订单占比表

bin/sqoop export \
--connect jdbc:mysql://hadoop102:3306/app_didi"?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 456789 \
--table t_order_age_and_time_range_total \
--export-dir /user/hive/warehouse/app_didi.db/t_order_age_and_time_range_total/mOnth=2020-04


三）到mysql中查看结果

mysql -u root -p

use app_didi;

select * from t_order_total;

select * from t_order_subscribe_total;

select * from t_order_timerange_total;

select * from t_order_province_total;

select * from t_order_age_and_time_range_total;

八、superset数据可视化

1、启动superset

source superset/bin/activate #激活环境

nohup superset run -h hadoop102 -p 8088 & #启动superset

浏览器输入hadoop102:8088 #打开浏览器

2、连接数据库

点击data，点击databases



点击添加database



点击mysql



填写mysql所在的主机、端口，要连接的数据库名，数据库用户名和登录密码，连接名，添加参数charset=UTF8（否则会出现中文乱码）



点击finish



这样我们的页面中就多了这一条连接

3、添加表数据

点击data，点击datasets



点击添加dataset



选择我们刚才创建的连接，选择库，选择表，点击add



这样我们的页面中就多了这一条记录



依次添加完其余4张表

4、创建图表

1)、订单总笔数

点击t_order_total



选择图表类型



选择big number，点击选择



时间字段，no filter不过滤



指标，选择字段count，选择函数sum



点击run query



这样就好了



修改标题



点击save保存



点击保存



这样就保存在charts里了

2)、预约和非预约订单占比

点击t_order_subscribe_total



选择饼图



按subscribe_name分组，选择字段count，选择函数sum



如图



设置格式

3)、不同时段订单占比

点击t_order_timerange_total



选择柱状图



指标



系列，即x轴



设置格式

4)、不同地域订单占比

点击t_order_province_total



选择柱状图



序列即x轴，按省进行划分



设置格式

5)、不同年龄段/时段订单占比

点击t_order_age_and_time_range_total



选择多环图



层级，里边那层选年龄，外边那层选时段



如图

5、创建看板

点击dashboards，点击添加



如图



点击charts



接下来我们就可以把制作好的图表拖到仪表盘中

最终效果如图