成交型网站建设方案,做会员卡网站,wordpress哪个版本稳定,安贞网站建设项目大致流程#xff1a;
1、项目业务背景
1.1 目的
本案例将某出行打车的日志数据来进行数据分析#xff0c;例如#xff1a;我们需要统计某一天订单量是多少、预约订单与非预约订单的占比是多少、不同时段订单占比等 数据海量 – 大数据 hive比MySQL慢很多
1.2 项目架…项目大致流程
1、项目业务背景
1.1 目的
本案例将某出行打车的日志数据来进行数据分析例如我们需要统计某一天订单量是多少、预约订单与非预约订单的占比是多少、不同时段订单占比等 数据海量 – 大数据 hive比MySQL慢很多
1.2 项目架构
用户打车的订单数据非常庞大。所以我们需要选择一个大规模数据的分布式文件系统来存储这些日志文件此处我们基于Hadoop的HDFS文件系统来存储数据。为了方便进行数据分析我们要将这些日志文件的数据映射为一张一张的表所以我们基于Hive来构建数据仓库。所有的数据都会在Hive下来几种进行管理。为了提高数据处理的性能。我们将基于MR引擎来进行数据开发。我们将使用Zeppelin来快速将数据进行SQL指令交互。我们使用Sqoop导出分析后的数据到传统型数据库便于后期应用我们使用fineBI来实现数据可视化展示
2、日志数据集介绍
四张表打车表取消订单表支付表评价表
1日志数据文件 处理的数据都是一些文本日志例如以下就是一部门用户打车的日志文件。 一行就是一条打车订单数据而且一条数据是以逗号来进行分隔的逗号分隔出来一个个的字段。 2用户取消订单日志 当用户取消订单时也会在系统后台产生一条日志。用户需求选择取消订单的原因。 3用户支付日志 用户点击确认支付后系统后台会将用户的支持信息保存为一条日志。 4用户评价日志 我们点击提交评价后系统后台也会产生一条日志。 3、数据仓库构建
面试问题数仓如何从0到1
我们的目标是分析用户打车的订单进行各类的指标计算指标例如订单的总数、订单的总支付金额等等。
思想可以将日志数据上传到HDFS保存下来每天都可以进行上传HDFS可以保存海量的数据。同时可以将HDFS中的数据文件对应到Hive的表中。但需要考虑一个问题就是业务系统的日志数据不一定是能够直接进行分析的
例如我们需要分析不同时段的订单占比凌晨有多少订单、早上有多少订单、上午有多少订单等。但是我们发现原始的日志文件中并没有区分该订单的是哪个时间段的字段。所以我们需要对日志文件的原始数据进行预处理才能进行分析。
我们会有这么几类数据要考虑
原始日志数据业务系统中保存的日志文件数据 ods预处理后的数据 dw分析结果数据 app
这些数据我们都通过Hive来进行处理因为Hive可以将数据映射为一张张的表然后就可以通过编写HQL来处理数据了简单、快捷、高效。为了区分以上这些数据我们将这些数据对应的表分别保存在不同的数据库中。
为了方便组织、管理上述的三类数据我们将数仓分成不同的层简单来说就是分别将三类不同的数据保存在Hive的不同数据库中。 1在hive构建三层数据仓库ods、dw、app
--1创建数据库 -- 1.1 创建ods库create database if not exists ods_didi;-- 1.2 创建dw库create database if not exists dw_didi;-- 1.3 创建app库create database if not exists app_didi;2在ods层创建四张表
--2创建表
-- 2.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.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 , ;
--2.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 , ; --2.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 , ;
comment—注释
show tables --查看一下4、数据分区表构建
3表数据加载 大规模数据的处理必须要构建分区。我们此处的需求每天都会进行数据分析采用的是T1的模式。
就是假设今天是2021-01-01那么1月1日的分析结果在第二天才能看到也就是2021-01-02查看到上一天的数据分析结果。此处我们采用最常用的分区方式使用日期来进行分区。
-3:给表加载数据
--3.1、创建本地路径上传源日志文件
mkdir -p /export/data/didi--3.2、通过load命令给表加载数据并指定分区
load data local inpath /export/data/didi/order.csv into table t_user_order partition (dt2020-04-12);
load data local inpath /export/data/didi/cancel_order.csv into table t_user_cancel_order partition (dt2020-04-12);
load data local inpath /export/data/didi/pay.csv into table t_user_pay_order partition (dt2020-04-12);
load data local inpath /export/data/didi/evaluate.csv into table t_user_evaluate partition (dt2020-04-12);
加载完数据后也是查看一下数据是否成功进入
select * from t_user_order limit 1;
select * from t_user_cancel_order limit 1;
select * from t_user_pay_order limit 1;
select * from t_user_evaluate limit 1;5、数据预处理
现在数据已经准备好了接下来需要对ods层中的数据进行预处理。
数据预处理是数据仓库开发中的一个重要环节。目的主要是让预处理后的数据更容易进行数据分析并且能够将一些非法的数据处理掉避免影响实际的统计结果。
需要在预处理之前考虑以下需求
过滤掉order_time长度小于8的数据如果小于8表示这条数据不合法不应该参加统计。—length(order_time)8将一些0、1表示的字段处理为更容易理解的字段。例如subscribe字段0表示非预约、1表示预约。我们需要添加一个额外的字段用来展示非预约和预约这样将来我们分析的时候跟容易看懂数据。—case whenorder_time字段为2020-4-12 1:15为了将来更方便处理我们统一使用类似 2020-04-12 01:15:00来表示这样所有的order_time字段长度是一样的。并且将日期获取出来 为了方便将来按照年、月、日、小时统计我们需要新增这几个字段。后续要分析一天内不同时段的订单量我们需要在预处理过程中将订单对应的时间段提前计算出来。例如1:00-5:00为凌晨。 多增加了7个字段—形成宽表包含以下字段
--4:数据预处理
--建表
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 , ;
--转宽表HQL语句
--------------------------------------
--date_format将字符串转为日期
select date_format(2020-1-1, yyyy-MM-dd); -- 2020-01-01
select date_format(2020-1-1 12:23, yyyy-MM-dd); -- 2020-01-01
select date_format(2020-1-1 12:23:35, yyyy-MM-dd); -- 2020-01-01select date_format(2020-1-1 1:1:1, yyyy-MM-dd HH:mm:ss); -- 2020-01-01 01:01:01select hour(date_format(2020-1-1 1:1:00, yyyy-MM-dd HH:mm:ss)); -- 2020-01-01 01:01:01--concat字符串的拼接
select concat(aaa,bbb,ccc);-- aaabbbccc
--length 获取字符串长度
select length(aaabbb); -- 6一天新增4500TB一个服务器的磁盘存储容量10T
生成宽表后往宽表中插入数据
如何将一个表的查询结果保存到另外一张表: insert overwrite table 表名1 select 字段 from 表名2
insert overwrite table dw_didi.t_user_order_wide partition(dt2020-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,date_format(concat(sub_time,:00), yyyy-MM-dd HH:mm:ss) as 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, -- 2020-1-1 ---2020-01-01year(date_format(order_time, yyyy-MM-dd)) as order_year, --2020month(date_format(order_time, yyyy-MM-dd)) as order_month, --12day(date_format(order_time, yyyy-MM-dd)) as order_day, --23hour(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/Aend 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;6、订单指标分析
1 总订单笔数
select count(orderid) as total_cnt
fromdw_didi.t_user_order_wide
wheredt 2020-04-12
;-- 创建保存日期对应订单笔数的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 ,
;
--加载数据到app表
insert overwrite table app_didi.t_order_total partition(month2020-04)
select 2020-04-12,count(orderid) as total_cnt
fromdw_didi.t_user_order_wide
wheredt 2020-04-12
;预约和非预约用户占比 需求: 求出预约用户订单所占的百分比:
select 预约订单总数 / 总订单数 from 预约统计订单数表,总订单数表 union all select 非预约订单总数 / 总订单数 from 非预约统计订单数表,总订单数表
select 2020-04-12,预约,concat(round(t1.total_cnt /t2.total_cnt *100,2),%) as subscribe
from (select count(orderid) as total_cntfromdw_didi.t_user_order_widewheresubscribe 1 and dt 2020-04-12 )t1,(select count(orderid) as total_cntfromdw_didi.t_user_order_widewheredt 2020-04-12)t2union all --将上边的查询结果和下边的查询结果进行合并select 2020-04-12,非预约,concat(round(t1.total_cnt /t2.total_cnt *100,2),%) as nosubscribe
from (select count(orderid) as total_cntfromdw_didi.t_user_order_widewheresubscribe 0 and dt 2020-04-12 )t1,(select count(orderid) as total_cntfromdw_didi.t_user_order_widewheredt 2020-04-12)t2
-- 创建保存日期对应订单笔数的app表
create table if not exists app_didi.t_order_subscribe_percent(date_val string comment 日期,subscribe_name string comment 是否预约,percent_val string comment 百分比
)partitioned by (month string comment 年月yyyy-MM)
row format delimited fields terminated by ,--加载数据到app表
insert overwrite table app_didi.t_order_subscribe_percent partition(month2020-04)
select 2020-04-12,预约,concat(round(t1.total_cnt /t2.total_cnt *100,2),%) as subscribe
from (select count(orderid) as total_cntfromdw_didi.t_user_order_widewheresubscribe 1 and dt 2020-04-12 )t1,(select count(orderid) as total_cntfromdw_didi.t_user_order_widewheredt 2020-04-12)t2union all
select 2020-04-12,非预约,concat(round(t1.total_cnt /t2.total_cnt *100,2),%) as nosubscribe
from (select count(orderid) as total_cntfromdw_didi.t_user_order_widewheresubscribe 0 and dt 2020-04-12 )t1,(select count(orderid) as total_cntfromdw_didi.t_user_order_widewheredt 2020-04-12)t2
3 不同时段的占比分析
--编写HQL语句
selectorder_time_range,count(*) as order_cnt
fromdw_didi.t_user_order_wide
wheredt 2020-04-12
group byorder_time_range
--创建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 ,
;--加载数据到APP表
insert overwrite table app_didi.t_order_timerange_total partition(month 2020-04)
select2020-04-12,order_time_range,count(*) as order_cnt
fromdw_didi.t_user_order_wide
wheredt 2020-04-12
group byorder_time_range
;
4 不同地域订单占比
--编写HQL ---方式1
selectprovince,count(*) as order_cnt
fromdw_didi.t_user_order_wide
wheredt 2020-04-12
group byprovince
;--编写HQL ---方式2
select * from
(select *,dense_rank() over(partition by province order by t.total_cnt desc) as rkfrom(select 2020-04-12,province,city,count(orderid) as total_cntfrom dw_didi.t_user_order_widegroup by province,city)t
)tt
where tt.rk 3;--创建APP表
create table if not exists app_didi.t_order_province_total(date_val string comment 日期,province string comment 省份,count int comment 订单数量
)
partitioned by (month string comment 年月yyyy-MM)
row format delimited fields terminated by ,
;--数据加载到APP表
insert overwrite table app_didi.t_order_province_total partition(month 2020-04)
select2020-04-12,province,count(*) as order_cnt
fromdw_didi.t_user_order_wide
wheredt 2020-04-12
group byprovince
order by order_cnt desc
;
5 不同年龄段不同时段订单占比
--不同年龄段的订单统计
select
2020-04-12,age_range,count(*)
from dw_didi.t_user_order_wide
where dt2020-04-12
group by age_range--不同时段的订单统计
select
2020-04-12,order_time_range,count(*)
from dw_didi.t_user_order_wide
where dt2020-04-12
group by order_time_range--不同年龄段不同时段的订单统计
select2020-04-12,age_range,order_time_range,count(*) as order_cnt
fromdw_didi.t_user_order_wide
wheredt 2020-04-12
group byage_range,order_time_range
;--创建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 ,
;--加载数据到APP表
insert overwrite table app_didi.t_order_age_and_time_range_total partition(month 2020-04)
select2020-04-12,age_range,order_time_range,count(*) as order_cnt
fromdw_didi.t_user_order_wide
wheredt 2020-04-12
group byage_range,order_time_range
;7、Sqoop数据导出
Sqoop安装
-- 准备工作#验证sqoop是否工作
/export/server/sqoop-1.4.7/bin/sqoop list-databases \
--connect jdbc:mysql://192.168.88.100:3306/ \
--username root \
--password 123456 --1:mysql创建目标数据库和目标表#创建目标数据库create database if not exists app_didi;#创建订单总笔数目标表create table if not exists app_didi.t_order_total(order_date date,count int);--2:导出订单总笔数表数据/export/server/sqoop-1.4.7/bin/sqoop export \--connect jdbc:mysql://192.168.88.100:3306/app_didi \--username root \--password 123456 \--table t_order_total \--export-dir /user/hive/warehouse/app_didi.db/t_order_total/month2020-04
8、finebi数据可视化
--Superset可视化superset run -h 192.168.88.100 -p 8099 --with-threads --reload --debuggermysqlpymysql://root:123456192.168.88.100/app_didi?charsetutf8
