hive自定义分隔符和处理Apache日志
自定义分隔符1.日志格式
2010-05-31 10:50:17|61.132.4.82|http://www.360buy.com/product/201185.html
分隔符是“ | ”
2.创建自定义分隔符的数据表
CREATE TABLE click_test ( time string, ip string, url string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\|' STORED AS TEXTFILE;
3.加载数据
LOAD DATA LOCAL INPATH '/data/clicktest_20110217.txt' OVERWRITE INTO TABLE click_test;
4.查询数据
select * from click_test;
处理Apache日志
1.日志格式
127.0.0.1 - frank "GET /apache_pb.gif HTTP/1.0" 200 2326
2.创建数据库
CREATE TABLE apachelog (
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" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|*) (-|*)(?: ([^ \"]*|\".*\") ([^ \"]*|\".*\"))?",
"output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
)
STORED AS TEXTFILE;
3.加载数据
hive> LOAD DATA LOCAL INPATH "./examples/files/apache.access.log" INTO TABLE apachelog;
hive> LOAD DATA LOCAL INPATH "./examples/files/apache.access.2.log" INTO TABLE apachelog;
4.查询数据
hive> select * from apachelog order by time;
使用order by查询后，会报错：
Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hive.contrib.serde2.RegexSerDe
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
启动时，需要添加hive_contrib.jar
hive --auxpath ./hive/lib/hive_contrib.jar
参考http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL
页:
[1]