黑猴子的家：Hive 网站日志分析案例

1、需求

（1）日志的字段描述如下

日志字段 字段描述
remote_addr 客户端的ip地址（如果中间有代理服务器那么这里显示的ip就为代理服务器的ip地址）
remote_user 用于记录远程客户端的用户名称（一般为“-”）
time_local 用于记录访问时间和时区
request 用于记录请求的URL以及请求方法
status 响应状态码
body_bytes_sent 给客户端发送的文件主体内容大小
request_body 为post的数据
http_referer 可以记录用户是从哪个链接访问过来的
http_user_agent 用户所使用的代理（一般为浏览器）
http_x_forwarded_for 可以记录客户端IP，通过代理服务器来记录客户端的ip地址
host 服务器主机名称

（2）需求一：IP地址
依据ip地址确定区域，定向营销

（3）需求二：访问时间
分析用户访问网站的时间段
针对销售来说，合理安排值班，销售课程。

2、需求分析

（1）准备一个原表
（2）针对不同的业务需求创建不同的子表
    * 数据存储格式orc/parquet
    * 数据压缩  snappy
    * map output 数据压缩 snappy
    * 外部表
    * 分区表（不演示）

3、数据输入

https://github.com/liufengji/hive_data.git

4、原表处理

1）创建表

create table IF NOT EXISTS default.victor_log (
remote_addr string,
remote_user string,
time_local string,
request string,
status string,
body_bytes_sent string,
request_body string,
http_referer string,
http_user_agent string,
http_x_forwarded_for string,
host string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
stored as textfile ;

2）向表中导入数据

load data local inpath '/opt/module/datas/victor.log' into table default.victor_log ;

3）查看有多少条数据

select count(*) from victor_log ;

4）查询表的数据结构

select * from victor_log limit 5 ;

5）查看官网正则表达式
https://cwiki.apache.org/confluence/display/Hive/GettingStarted
https://issues.apache.org/jira/browse/HIVE-662

CREATE TABLE serde_regex(
 host STRING,
 identity STRING,
 user STRING,
 time STRING,
 request STRING,
 status STRING,
 size STRING,
 referer STRING,
 agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))?",
  "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
)
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH "../data/files/apache.access.log" 
INTO TABLE serde_regex;

LOAD DATA LOCAL INPATH "../data/files/apache.access.2.log" 
INTO TABLE serde_regex;

SELECT * FROM serde_regex ORDER BY time;

校验正则表达式是否书写正确（全部为黄色即为正确）

http://wpjam.qiniudn.com/tool/regexpal/

6）根据正则表达式再次创建表

drop table if exists default.victor_log ;
create table IF NOT EXISTS default.victor_log (
remote_addr string,
remote_user string,
time_local string,
request string,
status string,
body_bytes_sent string,
request_body string,
http_referer string,
http_user_agent string,
http_x_forwarded_for string,
host string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "(\"[^ ]*\") (\"-|[^ ]*\") (\"[^\]]*\") (\"[^\"]*\") (\"[0-9]*\") (\"[0-9]*\") (-|[^ ]*) (\"[^ ]*\") (\"[^\"]*\") (-|[^ ]*) (\"[^ ]*\")"
)
STORED AS TEXTFILE;

7）向表中导入数据

load data local inpath '/opt/module/datas/victor.log' 
into table default.victor_log ;

8）查询表的数据结构

select * from victor_log limit 5 ;

5、针对不同的业务创建不同的子表

1）创建一个业务需求相关的表

drop table if exists default.victor_comm ;
create table IF NOT EXISTS default.victor_comm (
remote_addr string,
time_local string,
request string,
http_referer string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="SNAPPY");

2）向表中导入数据

insert into table default.victor_comm select remote_addr, time_local,
request,http_referer from  default.victor_log ;

3）查看表中数据是否正常

select * from victor_comm limit 5 ;

6、数据清洗之定义UDF去除双引号

1）编写UDF函数

import org.apache.hadoop.hive.ql.exec.UDF;

public class RemoveQuotes extends UDF{

    public String evaluate(final String s) {
        
        if (s == null) {
            return null;
        }
        
        return s.toString().replace("\"", "");
    }
}

2）导出jar包，并导入到虚拟机上
3）添加UDF的jar包
4）添加自定义UDF到hive

add jar /opt/module/jars/removequotes.jar ;

create temporary function my_removequotes as "com.victor.hive.RemoveQuotes" ;

5）调用udf函数去除表中含义的双引号

insert overwrite table default.victor_comm select my_removequotes(remote_addr),
my_removequotes(time_local), my_removequotes(request), my_removequotes(http_referer) 
from  default.bf_log_src ;

6）查询几条数据校验一下

select * from victor_comm limit 5 ;

7、数据清洗之定义UDF转换日期时间

1）编写日期转换的UDF

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import org.apache.hadoop.hive.ql.exec.UDF;

public class DateTransform extends UDF {

    private final SimpleDateFormat inputFormat = 
                            new SimpleDateFormat("dd/MMM/yyyy:HH:mm:ss", Locale.ENGLISH);
    private final SimpleDateFormat outputFormat = new SimpleDateFormat("yyyyMMddHHmmss");

    public String evaluate(final String inputDate) {
        
        if (inputDate == null) {
            return null;
        }
    
        Date parseDate;
        String outputDate = null;
        try {
            // 解析
            parseDate = inputFormat.parse(inputDate);
            
            // 转换
            outputDate = outputFormat.format(parseDate);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        
        return outputDate;
    }
}

2）导出jar包，并导入到虚拟机上
3）添加UDF的jar包
4）添加自定义UDF到hive

add jar /opt/module/jars/DateTransform.jar ;
create temporary function my_datetransform as " com.victor.hive.DateTransform" ;

5）调用udf函数处理日期时间转换

insert overwrite table default.victor_comm select my_removequotes(remote_addr),
my_datetransform(my_removequotes(time_local)), my_removequotes(request),
my_removequotes(http_referer) from  default.victor_log ;

6）查询几条数据校验一下

select * from victor_comm limit 5 ;

8、业务具体处理

1）分析看视频的时间

select t.hour, count(*) cnt from
(select substring(time_local,9,2) hour from victor_comm ) t
group by t.hour order by cnt desc ;

2）分析IP地址，判断哪个地域

select t.prex_ip, count(*) cnt from
(
select substring(remote_addr,1,7) prex_ip from victor_comm
) t
group by t.prex_ip order by  cnt desc limit 20 ;
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,607评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,047评论 2赞 379
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,496评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,405评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,400评论 5赞 364
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,479评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,883评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,535评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,743评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,544评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,612评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,309评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,881评论 3赞 306
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,891评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,136评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,783评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,316评论 2赞 342

推荐阅读更多精彩内容