mysql 重定义分隔符_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 [10/Oct/2000:13:55:36 -0700] "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" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[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;

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

分享到：

2011-02-17 14:47

浏览 4647

评论

1 楼

lvshuding

2011-08-05

学习了，正在找这方面的资料