网络日志的Hive简单查询

测试数据

[root@master hive]# cat ./tmp_data/weblog.data
1c13c719fbfd87f49522f189413c6ba /gybfokxyojgtwrq.html   2012-05-10  21:17:32    169.65.197.63
e42fe5714cb4402f81e5dce8da1755  /hpipbodlxgt.html   2012-05-10  21:12:04    166.22.84.135
[root@master hive]# cat ./tmp_data/ip_to_country.txt 
169.65.197.63   United States
166.22.84.135   United Kingdom

建表语句

[root@master hive]# cat /opt/weblog.hive 
create external table weblog_entries
( md5 string,
url string,
request_date string,
request_time string,
ip string
)
row format delimited fields terminated by '\t'
lines terminated by '\n'
location '/data/hive/weblog';
[root@master hive]# cat /opt/ip_to_country.hive 
create table ip_to_country(
ip string,
country string
)
row format delimited fields terminated by '\t'
lines terminated by '\n'
location '/data/hive/ip_to_country';

加载数据

hive> load data local inpath './tmp_data/weblog.data' into table weblog_entries;
hive> load data local inpath './tmp_data/ip_to_country.txt' into table ip_to_country;

连接查询

hive> select wle.*, itc.country FROM weblog_entries wle join ip_to_country itc on wle.ip = itc.ip;

使用查询结果创建新表

hive> create table weblog_entries_with_url_length as select url, request_date, request_time, length(url) as url_length from weblog_entries;