设为首页 加入收藏

TOP

Hive基础三(查询中常用的语法)
2018-12-07 01:26:00 】 浏览:10
Tags:Hive 基础 查询 常用 语法
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/login_sonata/article/details/75092590

相关链接:
Hive基础一(数据库,表,分区表,视图,导入导出数据)
Hive基础二(join原理和机制,join的几种类型,数据倾斜简单处理)
Hive基础三(查询中常用的语法)

Hive中的SELECT基础语法和标准SQL语法基本一致,支持WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查询等。

一,ORDER BY和SORT BY

ORDER BY根据key进行全局排序,由一个Reduce Task来完成。
SORT BY用于分区内排序,即有多个reduce,在每个Reduce任务内排序。
看下面的例子,原表数据为:

hive> select * from lxw1234_com;
OK
5
3
6
2
9
8
1

使用ORDER BY:

hive> select * from lxw1234_com order by id;
1
2
3
5
6
8
9

使用SORT BY:

hive> set mapred.reduce.tasks=2;
hive> select * from lxw1234_com sort by id;
2
5
6
9
1
3
8

设定了2个reduce,从结果可以看出,每个reduce内做了排序。如果reduce数为1,那么ORDER BY和SORT BY的结果是一样的。真实业务环境中,我们的需求大多需要使用ORDER BY全局排序来完成,默认是升序,降序是字段后加desc

二,DISTRIBUTE BY和CLUSTER BY

distribute by:按照指定的字段或表达式对数据进行划分,输出到对应的Reduce或者文件中,reduce数量大于1才能有效果。
cluster by:除了兼具distribute by的功能,还兼具sort by的排序功能。
使用distribute by:

hive> set mapred.reduce.tasks=2;
hive>INSERT overwrite LOCAL directory '/tmp/lxw1234/' 
SELECT id FROM lxw1234_com 
distribute BY id;

执行后在本地的/tmp/lxw1234目录中生成了000000_0和000001_0两个文件,是按照奇偶划分的(如果是按照length(id)划分,则长度相同的会划分在一个reduce):

$cat 000000_0
8
2
6
$cat 000001_0 
1
9
3
5

使用cluster by:

hive> set mapred.reduce.tasks=2;
hive> INSERT overwrite LOCAL directory '/tmp/lxw1234/' 
SELECT id FROM lxw1234_com 
CLUSTER BY id;
$cat 000000_0
2
6
8
$cat 000001_0
1
3
5
9

注意:使用cluster by之后,每个文件中的id都进行了排序,而distribute by没有。

三,group by的实现原理

以下面的语句为例,以省市为关键字进行分组:

select province,city,count(*) from table1 group by province,city;

思路:既然以省和市作为group by的字段,那就把他们的组合作为map的输出key,输出value置为1,经过shuffle之后,相同的省市组合会被合并,然后进入reduce,拆开省市组合作为两个字段,累加value作为count。
从Hive0.7.0开始HAVING被添加到Hive作为GROUP BY结果集的条件过滤。

四,distinct的实现原理

以下面的语句为例,以省为关键字进行分组,计算去重后的市的数量:

select province, count(distinct city) from table1 group by province;

思路:当只有一个distinct字段时,只需要将Group By字段和Distinct字段组合为map的输出key,value随意,经过shuffle之后,相同key合并,此时便完成了distinct操作(因为合并之后,相同的省后边的市肯定不同),然后先进入combine,把key拆开(省作为新的key,市作为新的value),再经过一次shuffle,相同的省合并,不同的市组成value-list,最后进入reduce即可。
如果是多个distinct呢?参看:
https://tech.meituan.com/hive-sql-to-mapreduce.html

五,UNION ALL和子查询

对两个查询结果进行并集操作,包括重复行,不进行排序,两个查询结果的字段必须一致。
下面这个例子还用了一种将子查询作为一个表的语法,叫做Common Table Expression(CTE):

with q1 as (select * from src where key= '5'),
q2 as (select * from src s2 where key = '4')
select * from q1 union all select * from q2;

子查询和标准SQL中的子查询语法和用法基本一致,需要注意的是,Hive中如果是从一个子查询进行SELECT查询,那么子查询必须设置一个别名:

SELECT col FROM (
SELECT a+b AS col
FROM t1
) t2

另外,从Hive0.13开始,在WHERE子句中也支持子查询,比如:

SELECT * FROM A
WHERE A.a IN (SELECT foo FROM B);
-----
SELECT A FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)


原文来自:http://lxw1234.com/archives/2015/07/365.htm


编程开发网
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇hive 空值、NULL判断 下一篇hive 如何去掉重复数据,显示第一..

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

array(4) { ["type"]=> int(8) ["message"]=> string(24) "Undefined variable: jobs" ["file"]=> string(32) "/mnt/wp/cppentry/do/bencandy.php" ["line"]=> int(214) }