hql语法参考文章:http://blog.csdn.net/acmilanvanbasten/article/details/17252673
一、单表select
1、and、sort by 、limit的使用
hive> select * from weather where city='hangzhou' and weath='fine' and minTemperat=-16 sort by pmvalue DESC limit 5;
//查询城市是hangzhou,天气fine并且最低温度是-16° 并且pm值最大的5条记录,结果如下
2014-05-23|07:34:58 China hangzhou fine -16 -10 496
2014-05-23|07:34:58 China hangzhou fine -16 -6 496
2014-05-23|07:34:58 China hangzhou fine -16 14 496
2014-05-23|07:34:58 China hangzhou fine -16 0 496
2014-05-23|07:34:58 China hangzhou fine -16 -8 496
Time taken: 29.266 seconds, Fetched: 5 row(s)
2、in
hive> select * from weather where city='hangzhou' and weath='fine' and minTemperat in (-16,-17) sort by pmvalue DESC limit 10;
2014-05-23|07:34:57 China hangzhou fine -17 -12 498
2014-05-23|07:34:57 China hangzhou fine -17 10 498
2014-05-23|07:34:57 China hangzhou fine -17 -14 498
2014-05-23|07:34:58 China hangzhou fine -17 -7 496
2014-05-23|07:34:58 China hangzhou fine -16 -6 496
2014-05-23|07:34:58 China hangzhou fine -16 6 496
2014-05-23|07:34:58 China hangzhou fine -16 -12 496
2014-05-23|07:34:58 China hangzhou fine -16 1 496
2014-05-23|07:34:58 China hangzhou fine -17 2 496
2014-05-23|07:34:58 China hangzhou fine -17 11 496
Time taken: 29.277 seconds, Fetched: 10 row(s)
3、group by
select * from weather where city='hangzhou' and weath='fine'
and minTemperat in (-16,-17) group by pmvalue;
4、把select的结果输出到文件中:
INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/selectResult'
select * from weather where city='hangzhou' and weath='fine' and
minTemperat in (-16,-17) sort by pmvalue DESC limit 100;
二、多表联合select
ps:准备工作,再创建两张表,一张是城市名称与区号的对照表,一张是pm值对应的空气状况表,表结构如下:
create table cityinfo
(name string, number string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
STORED AS TEXTFILE;
create table pminfo
(pmvalue string, pmlevel string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
STORED AS TEXTFILE;
1、3表联合查询,返回所有字段,前5个结果
select *
from cityinfo join weather on (cityinfo.name=weather.city)
join pminfo on (pminfo.pmvalue=weather.pmvalue)
where city='hangzhou' and weath='fine' and minTemperat=-16 limit 5
Job 1: Map: 2 Reduce: 1 Cumulative CPU: 3.34 sec HDFS Read: 229134 HDFS Write: 336 SUCCESS
Total MapReduce CPU Time Spent: 9 seconds 300 msec
OK
hangzhou 0571 2014-05-23|07:33:59 China hangzhou fine -16 11 0 0 A
hangzhou 0571 2014-05-23|07:35:44 China hangzhou fine -16 -5 0 0 A
hangzhou 0571 2014-05-23|07:35:44 China hangzhou fine -16 -14 0 0 A
hangzhou 0571 2014-05-23|07:35:44 China hangzhou fine -16 12 0 0 A
hangzhou 0571 2014-05-23|07:35:44 China hangzhou fine -16 18 0 0 A
Time taken: 34.448 seconds, Fetched: 5 row(s)
2、3表联合查询,使用别名,只返回部分列
select cy.number,wh.*,pm.pmlevel
from cityinfo cy join weather wh on (cy.name=wh.city)
join pminfo pm on (pm.pmvalue=wh.pmvalue)
where wh.city='hangzhou' and wh.weath='fine' and wh.minTemperat=-16 limit 5
Total MapReduce CPU Time Spent: 6 seconds 790 msec
OK
0571 2014-05-23|07:33:59 China hangzhou fine -16 11 0 A
0571 2014-05-23|07:35:44 China hangzhou fine -16 -5 0 A
0571 2014-05-23|07:35:44 China hangzhou fine -16 -14 0 A
0571 2014-05-23|07:35:44 China hangzhou fine -16 12 0 A
0571 2014-05-23|07:35:44 China hangzhou fine -16 18 0 A
3、LEFT,RIGHT 和 FULLOUTER 、聚合等高级特性
//待续
补充知识:join时mp如何工作?
join 时,每次map/reduce 任务的逻辑:
reducer 会缓存 join 序列中除了最后一个表的所有表的记录,再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中,应该把最大的那个表写在最后(否则会因为缓存浪费大量内存)。例如:
SELECT a.val, b.val, c.val FROM a
JOIN b ON (a.key = b.key1)JOIN c ON (c.key = b.key1)
所有表都使用同一个 join key(使用 1 次map/reduce 任务计算)。Reduce 端会缓存 a 表和 b 表的记录,然后每次取得一个 c 表的记录就计算一次 join 结果,类似的还有:
SELECT a.val, b.val, c.val FROMa
JOIN b ON (a.key = b.key1)JOIN c ON (c.key = b.key2)
这里用了 2 次 map/reduce 任务。第一次缓存 a 表,用 b 表序列化[王黎17] ;第二次缓存第一次 map/reduce 任务的结果,然后用 c 表序列化。
hive hql 语法参考网址:http://blog.csdn.net/hguisu/article/details/7256833