Hive 数据实战
需求
- remote_addr 用户IP
1.用于根据地址确认区域
2.用于统计来自同一个(外网)用户的访问数量 - time_local 用户访问时间
1.分析用户访问时间段
2.合理安排客服上班时间 - request 用户请求的URL
1.统计用户最感兴趣的内容
2.用户最容易发现的区域/内容 - http_referer 用户跳转过来的网站
1.了解客户的来源
2.统计广告投放
一、创建原表
- 数据存储格式 默认:TEXTFILE
- 数据压缩 默认:ZLIB
- map output 数据压缩 none
- 默认:管理表
创建原表 bf_log_src
drop table if exists default.bf_log_src;
create table if not exists default.bf_log_src(
remote_addr string,
remote_user string,
time_local string,
request string,
status string,
body_bytes_send string,
request_body string,
http_referer string,
http_user_agent string,
http_x_forworded_for string,
host string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES ("input.regex" = "(\"[^ ]*\") (\"[^ ]*\") (\"[^\"]*\") (\"[^\"]*\") (\"[0-9]*\") (\"[0-9]*\") ([^ ]*) (\"[^ ]*\") (\"[^\"]*\") (\"[^ ]*\") (\"[^ ]*\")");
加载数据
load data local inpath '/opt/datas/bf.log' into table bf_log_src;
二、根据业务需求创建子表
- 数据存储格式 ORCfile | parquet
- 数据压缩 snappy
- map output 数据压缩 snappy
- 外部表
- 分区表
创建子表 bf_log_comm
drop table if exists default.bf_log_comm;
create EXTERNAL table if not exists default.bf_log_comm(
remote_addr string,
time_local string,
request string,
http_referer string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
stored as orc
location '/user/hive/warehouse/bf_log_comm/'
tblproperties ("orc.compression"="snappy") ;
加载数据
insert into table default.bf_log_comm select remote_addr,time_local,request,http_referer from default.bf_log_src;
三.数据清洗
3.1 删除引号
3.2 更改日期
定义方法参考:Hive 中的 UDF
注意:输入数据类型定义为Text会导致编码错误,改为String即可解决问题
# 添加正则表达式支持包
add jar /opt/softwares/hive-1.2.2/lib/hive-contrib-1.2.2.jar;
# 添加 UDF jar 包
add jar /opt/datas/RemoveDoubleQuotationMarks.jar;
# 创建临时方法
create temporary function my_rm_marks as "com.cenzhongman.hive.udf.RemoveDoubleQuotationMarks";
create temporary function ChangeDate as "com.cenzhongman.hive.udf.ChangeDate";
替换数据
insert overwrite table default.bf_log_comm select my_rm_marks(remote_addr),ChangeDate(my_rm_marks(time_local)),my_rm_marks(request),my_rm_marks(http_referer) from default.bf_log_src;
四、使用自带函数分析数据
desc function extended substring ;
#分析时间段
select t.hour,count(*) cnt from
(select substring(time_local,9,2) hour from bf_log_comm) t
group by t.hour order by cnt desc;
#分析IP地址
select t.pre_ip,count(*) cnt from (select substring(remote_addr,1,7) pre_ip from bf_log_comm) t group by t.pre_ip order by cnt;
岑忠满的博客新站点
http://cenzm.xyz