hive学习(2)--- hive sql 常用语法
来源:互联网 发布:软件学报 小木虫 编辑:程序博客网 时间:2024/06/03 18:31
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
- hive学习(2)--- hive sql 常用语法
- Hive的常用语法(Hive QL)
- hive 常用语法sql、join分析
- hive sql语法解读
- Hive sql 语法解读
- Hive sql语法详解
- Hive sql语法详解
- Hive sql语法详解
- Hive Sql 语法
- HIVE SQL 基本语法
- Hive基础sql语法
- Hive基础sql语法
- hadoop hive sql语法解释(转)
- hadoop hive sql(HQL)语法解释
- Hive Sql 常用语句
- hive常用sql整理
- hive 常用sql
- hive学习笔记-hive的基本语法
- apxs为apache动态添加so
- django notes 4 template tags and filters
- linux系统samba共享
- 哈希与多级哈希
- MySQL 导入导出外部文件
- hive学习(2)--- hive sql 常用语法
- 用java代码新建布局
- acmer必看的26个对acm态度
- 排序整理[quick sort]
- AVPlayer就可以播放在线音频
- Codeforces Round #244 (Div. 2)(强连通分量,后缀数组)
- 关于音频芯片MICIN和LINEIN,HPOUT和OUT的区别
- Qt Qlabel显示图片新手教程
- c++-++运算符重载