文档章节

HiveQL:查询

LeeHH
 LeeHH
发布于 2017/02/10 18:59
字数 2253
阅读 154
收藏 0

SELECT...FROM语句

select是SQL中的射影算子,from子句标识了从哪个表、视图或嵌套查询中选择记录:

hive> select name,salary from employees;

也可以写成,两者是等价的,但是下面这种写法在涉及到多个不同的表时会很有用:

hive> select e.name,e.salary from employees e;

可以使用列值来进行计算(比如现在要打印雇员的全大写名字,对应的薪水,需要缴纳的税收以及扣除税收后的税后薪资):

hive> select upper(name),salary,deductions['Taxes'],
round(salary*(1-deductions['Taxes'])) from employees;

Hive支持所有典型的算数运算符,从加减乘除到求余(%)取与(&)取或(|)取亦或(^)取反(~)都支持。

此外,Hive还有内置的数学函数,比如求平方根的sqrt(double d),求模的pmod(int i1,int i2),还有求sin,cos,tan等等的函数,在此不一样列举。具体的函数可以查看我的另一篇博客:

https://my.oschina.net/lonelycode/blog/835411

Limit语句

limit子句用来限制返回的行数:

hive> select upper(name),salary,deductions['Taxes'],
round(salary*(1-deductions['Taxes'])) from employees
limit 2;

列别名

前面的示例查询语句可以认为是返回一个由新列组成的新的关系,其中有些新产生的结果列对于表employees来说是不存在的,通常有必要给这些新产生的列起一个名称,也就是别名:

hive> select upper(name),salary,deductions['Taxes'],
round(salary*(1-deductions['Taxes'])) as salary_minus_taxes
from employees limit 2;

嵌套SELECT语句

从下面的嵌套查询语句可以看出,我们给前面的结果集起了一个别名 e,在这个语句外面嵌套了name和salary_minus_taxes两个字段,同时约束后者的值要大于70000。

hive> from(
select upper(name),salary,deductions["Taxes"] as taxes,
round(salary*(1-deductions["Taxes"])) as salary_minus_taxes
from employees
) e
select e.name,e.salary_minus_taxes
where e.salary_minus_taxes > 70000;

CASE...WHEN...THEN句式

该语句与if条件语句类似,用于处理单个列的查询结果:

hive> select name,salary,
case
when salary<50000.0 then 'low'
when salary>=50000.0 and salary<70000.0 then 'middle'
when salary>=70000.0 and salary<100000.0 then 'high'
else 'very high'
end as bracket from employees;

WHERE语句

select语句用于选取字段,where语句用于过滤条件,两者结合使用可以查找到符合过滤条件的记录。前面已经用过很多次了,这里就不举例子了。

LIKE和RLIKE

like是个标准的SQL的操作符,它可以让我们通过字符串的开头或结尾,以及制定特定的子字符串,或当子字符串出现在字符串内的任何位置时进行匹配:

假设like语句是这样的 A [NOT] like B :

B的表达式说明如下:'x%'表示A必须以字母‘x’开头,‘%x’表示A必须以字母x结尾,而‘%x%’表示A包含有字母‘x’,可以位于开头,结尾或者字符串中间。类似地,下划线‘_’匹配单个字符。B必须要和整个字符串A相匹配才行。

以下的语句,第一条表示匹配以Ave.结尾的street,第二条匹配O开头的city,第三条匹配street中含有Chi字符串的street。

hive> select name,address.street from employees where address.street like '%Ave.';

hive> select name,address.city from employees where address.city like 'O%';

hive> select name,address.street from employees where address.street like '%Chi%';

rlike子句是Hive中这个功能的一个扩展,其可以通过Java的正则表达式这个更强大的语言来指定匹配条件:

hive> select name,address.street 
from employees where address.street rlike '.*(Chicago|Ontario).*';

上面的例子中点号(.)表示和任意的字符串匹配,星号(*)表示重复“左边的字符串”零次到无数次(上面的例子,左边的字符串指的是点号),加起来的意思和下面的例子一样:

hive> select name,address from employees
where address.street like '%Chicago%' or address.street like '%Ontario%';

GROUP BY语句

group by语句通常会和聚合函数一起使用,按照一个或者多个列对结果进行分组,然后对每个组执行聚合操作:

hive> select year(ymd),avg(price_close) from stocks
where exchange='NASSAAQ' and symbol='AAPL'
group by year(ymd);

HAVING语句

having子句允许用户通过一个简单的语法完成原本需要通过子查询才能对group by语句产生的分组进行条件过滤的任务:

hive> select year(ymd),avg(price_close) from stocks
where exchange='NASDAQ' and symbol='AAPL'
group by year(ymd)
having avg(price_close)>50.0;

JOIN语句

Hive支持通常的SQL JOIN语句,但是只支持等值连接。

INNER JOIN(内连接)

内连接中,只有进行连接的两个表中都存在与连接标准相匹配的数据才会被保留下来:

hive> select a.ymd,a.price_close,b.price_close
from stocks a join stocks b on a.ymd=b.ymd
where a.symbol='AAPL' and b.symbol='IBM';

OUTER JOIN(外连接)

LEFT OUTER JOIN(左外连接)

在这种join连接操作中,join操作符左边表中符合where子句的所有记录将会被返回,右边表如果没有符合on后面连接条件的记录则返回NULL:

hive> select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s left outer join dividends d on s.ymd=d.ymd and s.symbol=d.symbol
where s.symbol='AAPL';

RIGHT OUTER JOIN(右外连接)

右外连接刚好和左外连接反过来,join操作符右边表符合where子句的所有记录将会被返回,左边表如果没有符合on后面连接条件的记录则会返回NULL:

FULL OUTER JOIN(完全外连接)

完全外连接将会返回所有表中符合where语句条件的所有记录,如果任一表的指定字段没有符合条件的值的话,那么就使用NULL值替代:

hive> select s.ymd,s.symbol,s.price_close,d.dividend
from dividends d full outer join stocks s on d.ymd=s.ymd and d.symbol=s.symbol
where s.symbol='AAPL';

LEFT SEMI-JOIN(左半开连接)

左半开连接会返回左边表的记录,前提是其记录对于右边表满足on语句中的判定条件。以下是一条sql语句,在Hive中是不支持的:

mysql> select s.ymd,s.symbol,s.price_close from stocks s
where s.ymd,s.symbol in 
select(d.ymd,d.symbol from dividends d);

不过我们可以用下面的left semi-join语法来达到同样的目的:

hive> select s.ymd,s.symbol,s.price_close
from stocks s left semi join dividends d on s.ymd=d.ymd and s.symbol=d.symbol;

笛卡尔积JOIN

笛卡尔积是一种连接,表示左边表的行数乘以右边表的行数等于笛卡尔结果集的大小(也就是说如果左边有5行数据,右边有6行数据,那么产生的结果将会是30行数据):

hive> select * from stocks join dividends;

map-side JOIN

如果所有表中只有一张表是小表,那么可以在最大的表通过mapper的时候将小表完全放到内存中。Hive可以在map端执行连接过程(称为map-side JOIN),这是因为Hive可以和内存中的小表进行逐一匹配,从而省略掉常规连接操作所需要的reduce过程。

ORDER BY和SORT BY

Hive中的order by和mysql的定义是一样的,其会对查询结果集执行一个全局排序:

hive> select s.ymd,s.symbol,s.price_close
from stocks s
order by s.ymd ASC,s.symbol DESC;

Hive中增加了一个可供选择的方式,也就是sort by,其只会在每个reducer中对数据进行排序,也就是执行一个局部排序过程。这可以保证每个reducer的输出数据都是有序的,这样可以提高后面进行的全局排序的效率:

hive> select s.ymd,s.symbol,s.price_close
from stocks s
sort by s.ymd,s.symbol desc;

含有SORT BY的DISTRIBUTE BY

distribute by控制map的输出在reducer中是如何划分的。

假设我们希望具有相同symbol的数据在一起处理。那么我们可以使用distribute by来保证相同的symbol会分发到同一个reducer中进行处理,然后使用sort by来按照我们的期望进行排序:

hive> select s.ymd,s.symbol,s.price_close
from stocks s
distribute by s.symbol
sort by s.symbol ASC,s.ymd ASC;

CLUSTER BY

在前面的例子里,s.symbol列被用在了distribute by语句中,而s.symbol列和s.ymd位于SORT BY语句中。如果这两个语句中涉及到的列完全相同,而且采用的是升序排序方式,那么就可以直接使用cluster by,它相当于前面两个句子的简写方式:

hive> select s.ymd,s.symbol,s.price_close
from stocks s
cluster by s.symbol;

类型转换

用case关键字可以进行类型转换,转换的语法是cast(value as type):

hive> select name,salary from employees
where cast(salary as float)<100000.0;

抽样查询

Hive可以对表进行分桶抽样

假设numbers表只有number字段,其值为1到10:

hive> select * from numbers tablesample(bucket 3 out of 10 on rand()) s;

UNION ALL

union all 可以将两个或多个表进行合并。每一个union子查询都必须具有相同的列,而且对应的每个字段的字段类型必须是一致的:

hive> select log.ymd,log.level,log.message
from(
select l1.ymd,l1.level,
l1.message,'Log1' as source
from log1 l1
union all
select l2.ymd,l2.level,
l2.message,'Log2' as source
from log1 l2
) log
sort by log.ymd ASC;

 

© 著作权归作者所有

上一篇: HiveQL:视图
下一篇: Hive的函数集合
LeeHH
粉丝 1
博文 37
码字总数 37428
作品 0
深圳
程序员
私信 提问
Impala SQL 语言参考

Impala SQL 语言参考 Cloudera Impala 的查询语言是基于 SQL 的。为了保护用户在技能和查询设计方面的已有投资,Impala 提供与 Hive 查询语言(HiveQL)的高度兼容: 因为使用与 Hive 记录表结...

weiqingbin
2013/12/20
11.1K
1
Hive学习总结之四:HiveQL查询操作

第三部分:HiveQL 查询操作 Hive的SQL操作 •基本的Select 操作 SELECT [ALL | DISTINCT] select_expr, select_expr, … FROM table_reference [WHERE where_condition] [GROUP BY col_list ......

酷帅CoolBash
2013/01/30
1K
0
Hive架构及Hive On Spark

Hive的所有数据都存在HDFS中. (1)Table:每个表都对应在HDFS中的目录下,数据是经过序列化后存储在该目录中。同时Hive也支持表中的数据存储在其他类型的文件系统中,如NFS或本地文件系统。 (2...

雪童子
2015/10/23
0
0
Impala 和 Hive 之间 SQL 区别(翻译)

Impala 和 Hive 之间 SQL 区别 当前版本的 Impala(1.2.3)不支持以下在 HiveQL 中可用的 SQL 特性: 非标量数据类型如 maps, arrays, structs 可扩展机制(Extensibility mechanisms)例如 TRAN...

weiqingbin
2014/01/01
6.9K
0
Hadoop Hive sql 语法详解

Hive 是基于Hadoop 构建的一套数据仓库分析系统,它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据,可以将结构化的数据文件映射为一张数据库表,并提供完整的SQL查询功...

luanpeng825485697
2018/05/30
0
0

没有更多内容

加载失败,请刷新页面

加载更多

如何编写高质量的 JS 函数(1) -- 敲山震虎篇

本文首发于 vivo互联网技术 微信公众号 链接:https://mp.weixin.qq.com/s/7lCK9cHmunvYlbm7Xi7JxQ 作者:杨昆 一千个读者,有一千个哈姆雷特。 此系列文章将会从函数的执行机制、鲁棒性、函...

vivo互联网技术
38分钟前
5
0
学会这5个Excel技巧,让你拒绝加班

在网上,随处都可以看到Excel技巧,估计已看腻了吧?但下面5个Excel技巧会让你相见恨晚。关键的是它们个个还很实用 图一 技巧1:快速删除边框 有时当我们处理数据需要去掉边框,按Ctrl+Shif...

干货趣分享
今天
11
0
JS基础-该如何理解原型、原型链?

JS的原型、原型链一直是比较难理解的内容,不少初学者甚至有一定经验的老鸟都不一定能完全说清楚,更多的"很可能"是一知半解,而这部分内容又是JS的核心内容,想要技术进阶的话肯定不能对这个...

OBKoro1
今天
9
0
高防CDN的出现是为了解决网站的哪些问题?

高防CDN是为了更好的服务网络而出现的,是通过高防DNS来实现的。高防CDN是通过智能化的系统判断来路,再反馈给用户,可以减轻用户使用过程的复杂程度。通过智能DNS解析,能让网站访问者连接到...

云漫网络Ruan
今天
15
0
OSChina 周一乱弹 —— 熟悉的味道,难道这就是恋爱的感觉

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @xiaoshiyue :好久没分享歌了分享张碧晨的单曲《今后我与自己流浪》 《今后我与自己流浪》- 张碧晨 手机党少年们想听歌,请使劲儿戳(这里)...

小小编辑
今天
3.3K
25

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部