专栏首页不温卜火Hive快速入门系列(10) | Hive的查询语法

Hive快速入门系列(10) | Hive的查询语法

  我们光知道Hive,不会其语法怎么办呢?此篇博文专门为大家带来操作实例。

一. SELECT

 	基本的Select操作
	语法结构
SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
FROM table_reference
[WHERE where_condition] 
[GROUP BY col_list [HAVING condition]] 
[CLUSTER BY col_list 
  | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list] 
] 
[LIMIT number]

注: 1、order by 会对输入做全局排序,因此只有一个reducer,会导致当输入规模较大时,需要较长的计算时间。 2、sort by不是全局排序,其在数据进入reducer前完成排序。因此,如果用sort by进行排序,并且设置mapred.reduce.tasks>1,则sort by只保证每个reducer的输出有序,不保证全局有序。 3、distribute by(字段)根据指定的字段将数据分到不同的reducer,且分发算法是hash散列。 4、Cluster by(字段) 除了具有Distribute by的功能外,还会对该字段进行排序。

因此,如果分桶和sort字段是同一个时,此时,cluster by = distribute by + sort by

  • 1. 全表查询
select * from score;
  • 2. 选择特定列查询
select s_id ,c_id from score;
  • 3. 列别名 (1)重命名一个列。 (2)便于计算。 (3)紧跟列名,也可以在列名和别名之间加入关键字‘AS’
select s_id as myid ,c_id from score;

二. 常用函数

  • 1. 求总行数(count)
select count(1) from score;
  • 2. 求分数的最大值(max)
select max(s_score) from score;
  • 3. 求分数的最小值(min)
select min(s_score) from score;
  • 4. 求分数的总和(sum)
select sum(s_score) from score;
  • 5. 求分数的平均值(avg)
select avg(s_score) from score;

三. LIMIT语句

  典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

select * from score limit 3;

四. WHERE语句

  1. 使用WHERE 子句,将不满足条件的行过滤掉。
  2. WHERE 子句紧随 FROM 子句。 查询出分数大于60的数据
select * from score where s_score > 60;

五. 比较运算符(BETWEEN/IN/ IS NULL)

5.1 表格显示

下面表中描述了谓词操作符,这些操作符同样可以用于JOIN…ON和HAVING语句中。

操作符

支持的数据类型

描述

A=B

基本数据类型

如果A等于B则返回TRUE,反之返回FALSE

A<=>B

基本数据类型

如果A等于B则返回TRUE,反之返回FALSE

A<=>B

基本数据类型

如果A和B都为NULL,则返回TRUE,其他的和等号(=)操作符的结果一致,如果任一为NULL则结果为NULL

A<>B, A!=B

基本数据类型

A或者B为NULL则返回NULL;如果A不等于B,则返回TRUE,反之返回FALSE

A<B

基本数据类型

A或者B为NULL,则返回NULL;如果A小于B,则返回TRUE,反之返回FALSE

A<=B

基本数据类型

A或者B为NULL,则返回NULL;如果A小于等于B,则返回TRUE,反之返回FALSE

A>B

基本数据类型

A或者B为NULL,则返回NULL;如果A大于B,则返回TRUE,反之返回FALSE

A>=B

基本数据类型

A或者B为NULL,则返回NULL;如果A大于等于B,则返回TRUE,反之返回FALSE

A [NOT] BETWEEN B AND C

基本数据类型

如果A,B或者C任一为NULL,则结果为NULL。如果A的值大于等于B而且小于或等于C,则结果为TRUE,反之为FALSE。如果使用NOT关键字则可达到相反的效果。

A IS NULL

所有数据类型

如果A等于NULL,则返回TRUE,反之返回FALSE

A IS NOT NULL

所有数据类型

如果A不等于NULL,则返回TRUE,反之返回FALSE

IN(数值1, 数值2)

所有数据类型

使用 IN运算显示列表中的值

A [NOT] LIKE B

STRING 类型

B是一个SQL下的简单正则表达式,如果A与其匹配的话,则返回TRUE;反之返回FALSE。B的表达式说明如下:‘x%’表示A必须以字母‘x’开头,‘%x’表示A必须以字母’x’结尾,而‘%x%’表示A包含有字母’x’,可以位于开头,结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。

A RLIKE B, A REGEXP B

STRING 类型

B是一个正则表达式,如果A与其匹配,则返回TRUE;反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的,因为正则也依据其中的规则。例如,正则表达式必须和整个字符串A相匹配,而不是只需与其字符串匹配。

5.2 举例操作

  • 1. 查询分数等于80的所有的数据
select * from score where s_score = 80;
  • 2. 查询分数在80到100的所有数据
select * from score where s_score between 80 and 100;
  • 3. 查询成绩为空的所有数据
select * from score where s_score is null;
  • 4. 查询成绩是80和90的数据
select * from score where s_score in(80,90);

六. LIKE和RLIKE

使用LIKE运算选择类似的值 选择条件可以包含字符或数字: % 代表零个或多个字符(任意个字符)。 _ 代表一个字符。 RLIKE子句是Hive中这个功能的一个扩展,其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

  • 1. 查找以8开头的所有成绩
select * from score where s_score like '8%';
  • 2. 查找第二个数值为9的所有成绩数据
select * from score where s_score like '_9%';
  • 3. 查找成绩中含9的所有成绩数据
select * from score where s_score rlike '[9]';

逻辑运算符(AND/OR/NOT)

操作符

含义

AND

逻辑并

OR

逻辑或

NOT

逻辑否

  • 1. 查询成绩大于80,并且s_id是01的数据
select * from score where s_score >80 and s_id = '01';
  • 2. 查询成绩大于80,或者s_id 是01的数
select * from score where s_score > 80 or s_id = '01';
  • 3. 查询s_id 不是 01和02的学生
select * from score where s_id not in ('01','02');

七. 分组

7.1 GROUP BY语句

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

  • 1. 计算每个学生的平均分数
select s_id ,avg(s_score) from score group by s_id;
  • 2. 计算每个学生最高成绩
select s_id ,max(s_score) from score group by s_id;

7.2 HAVING语句

having与where不同点: (1)where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据。 (2)where后面不能写分组函数,而having后面可以使用分组函数。 (3)having只用于group by分组统计语句。

  • 1. 求每个学生的平均分数
select s_id ,avg(s_score) from score group by s_id;
  • 2. 求每个学生平均分数大于85的人
select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

八. JOIN语句

8.1 等值JOIN

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

  • 查询分数对应的姓名
SELECT s.s_id,s.s_score,stu.s_name,stu.s_birth  FROM score s LEFT JOIN student stu ON s.s_id = stu.s_id

8.2 表的别名

好处 (1)使用别名可以简化查询。 (2)使用表名前缀可以提高执行效率。

  • 合并老师与课程表
select * from techer t join course c on t.t_id = c.t_id;

8.3 内连接(INNER JOIN)

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

select * from techer t inner join course c on t.t_id = c.t_id;

8.4 左外连接(LEFT OUTER JOIN)

  左外连接:JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

  • 查询老师对应的课程
select * from techer t left join course c on t.t_id = c.t_id;

8.5 右外连接(RIGHT OUTER JOIN)

  右外连接:JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

select * from techer t right join course c on t.t_id = c.t_id;

8.6 满外连接(FULL OUTER JOIN)

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

SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;

8.7 多表连接

注 意 : 连 接 n 个 表 , 至 少 需 要 n − 1 个 连 接 条 件 。 例 如 : 连 接 三 个 表 , 至 少 需 要 两 个 连 接 条 件 。 \color{#FF0000}{注意:连接 n个表,至少需要n-1个连接条件。例如:连接三个表,至少需要两个连接条件。} 注意:连接n个表,至少需要n−1个连接条件。例如:连接三个表,至少需要两个连接条件。   多表连接查询,查询老师对应的课程,以及对应的分数,对应的学生

select * from techer t 
left join course c 
on t.t_id = c.t_id
left join score s 
on s.c_id = c.c_id
left join student stu 
on s.s_id = stu.s_id;

  大多数情况下,Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表techer和表course进行连接操作,然后会再启动一个MapReduce job将第一个MapReduce job的输出和表score;进行连接操作。

九. 排序

9.1 全局排序(Order By)

OrderBy:全局排序,一个reduce

1. 使用 ORDER BY 子句排序 ASC(ascend): 升序(默认) DESC(descend): 降序 2. ORDER BY 子句在SELECT语句的结尾。

  • 1. 查询学生的成绩,并按照分数降序排列
SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;
  • 2. 查询学生的成绩,并按照分数升序排列
SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score asc;

9.2 按照别名排序

  • 按照分数的平均值排序
select s_id ,avg(s_score) avg from score group by s_id order by avg;

9.3 多个列排序

  • 按照学生id和平均成绩进行排序
select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg;

9.4 每个MapReduce内部排序(Sort By)局部排序

Sort By:每个MapReduce内部进行排序,对全局结果集来说不是排序。

  • 1.设置reduce个数
set mapreduce.job.reduces=3;
  • 2. 查看设置reduce个数
set mapreduce.job.reduces;
  • 3. 查询成绩按照成绩降序排列 select * from score sort by s_score;
  • 4 . 将查询结果导入到文件中(按照成绩降序排列)
insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;

9.5 分区排序(DISTRIBUTE BY)

  Distribute By:类似MR中partition,进行分区,结合sort by使用。 注意,Hive要求DISTRIBUTEBY语句要写在SORTBY语句之前。对于distribute by进行测试,一定要分配多reduce进行处理,否则无法看到distribute by的效果。

先按照学生id进行分区,再按照学生成绩进行排序。

  • 1. 设置reduce的个数,将我们对应的s_id划分到对应的reduce当中去
set mapreduce.job.reduces=7;
  • 2. 通过distribute by 进行数据的分区
insert overwrite local directory '/export/servers/hivedatas/sort' select * from score distribute by s_id sort by s_score;

9.6 CLUSTER BY

  当distribute by和sort by字段相同时,可以使用cluster by方式。   cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序,不能指定排序规则为ASC或者DESC。

以下两种写法等价

select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;

本次的分享就到这里了

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Hive快速入门系列(9) | Hive表中数据的加载与导出

      将hive表中的数据导出到其他任意目录,例如linux本地磁盘,例如hdfs,例如mysql等等

    不温卜火
  • Hive快速入门系列(15) | Hive性能调优 [二] 表的优化

      将key相对分散,并且数据量小的表放在join的左边,这样可以有效减少内存溢出错误发生的几率;再进一步,可以使用map join让小的维度表(1000条以下...

    不温卜火
  • 爬虫入门经典(二十三) | fiddler抓包爬取QQ音乐

      ♥各位如果想要交流的话,可以加下QQ交流群:974178910,里面有各种你想要的学习资料。♥

    不温卜火
  • Hive SQL50道练习题

    – 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩: – (包括有成绩的和无成绩的)

    大数据技术与架构
  • SQL学习笔记之SQL查询练习1

    –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Co...

    Jetpropelledsnake21
  • 小学生SQL50题

    已知有如下4张表: 学生表: student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 课程表: cou...

    大数据真好玩
  • 大数据-Hive查询语法

    因此,如果分桶和sort字段是同一个时,此时, cluster by = distribute by + sort by 分桶表的作用:最大的作用是用来提高j...

    cwl_java
  • Msql面试zongjie

    说到数据库每次面试都会在sql语句上吃大亏,考察的问题无非是去重,连表查询,求最值,平均值等,看起来很简单吧,但是写起来还真有点困难,不会sql面试会大打折扣。...

    wencheng
  • hiveQL去重

    去重: 以id进行分组,然后取出每组的第一个 select * from (select *,row_number() over (partition by i...

    用户1225216
  • ElasticSearch入门实战1

    若与

扫码关注云+社区

领取腾讯云代金券