这里用到的nginx日志是网站的访问日志,比如日志格式:
180.173.250.74 - - [08/Jan/2015:12:38:08 +0800] "GET /avatar/xxx.png HTTP/1.1" 200 968 "http://www.iteblog.com/archives/994"
这条日志里面含有9列(为了展示的美观,我在这里面加了换行符),每列之间是用空格分割的,每列的含义分别是客户端访问IP、用户标示、用户、访问时间、请求页面(测试简写)
这样一来就可以匹配出每一列的值。而在Hive中我们是可以指定输入文件解析器(SerDe)的,并且在Hive中内置了一个org.apache.hadoop.hive.contrib.serde2.RegexSerDe正则解析器,我们可以直接使用它。所以整个建表语句可以这么写:
CREATE TABLE yyy_test.logs(
host STRING,
identity STRING,
user STRING,
time STRING,
request STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'input.regex' = '(\\d+\\.\\d+\\.\\d+\\.\\d+)\\s(-|[0-9]*)\\s(-|[0-9]*)\\s\\[(.*)\\]\\s\\"(.*)\\"',
'output.format.string' = '%1$s\t%2$s\t%3$s\t%4$s\t%5$s'
)
STORED AS TEXTFILE;
导入数据
load data inpath '/user/compass/yueyuanyang/tmp/4' into table yyy_test.logs
查看数据
select * from yyy_test.logs;
结果展示
例二:
采用简单的空格分割数据
数据格式:
180.173.250.74 - - [08/Jan/2015:12:38:08 +0800] "GET /avatar/xxx.png HTTP/1.1" 200 968 "http://www.iteblog.com/archives/994" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko)Chrome/34.0.1847.131 Safari/537.36"
建表语句:
CREATE TABLE yyy_test.logs(
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' = '([^ ]*)\\s([^ ]*)\\s([^ ]*)\\s(\\[.*\\])\\s(\\".*?\\")\\s(-|[0-9]*)\\s(-|[0-9]*)\\s(\\".*?\\")\\s(\\".*?\\")',
'output.format.string' = '%1$s\t%2$s\t%3$s\t%4$s\t%5$s\t%6$s\t%7$s\t%8$s\t%9$s'
)
STORED AS TEXTFILE;