Hive的查询语法

1.基本查询

• 注意
  • SQL 语言大小写不敏感
  • SQL 可以写在一行或者多行
  • 关键字不能被缩写也不能分行
  • 各子句一般要分行写
  • 使用缩进提高语句的可读性

1.1 查询全表和特定列

• 全表查询

select * from stu;

• 选择特定列查询

select id,name from stu;

1.2 列起别名

• 重命名一个列

  • 紧跟列名，也可以在列名和别名之间加入关键字 ‘as’

• 案例实操

select id,name as stuName from stu;

1.3 常用函数

• 求总行数（count）

 select count(*) cnt from score;

• 求分数的最大值（max）

select max(s_score) from score;

• 求分数的最小值（min）

select min(s_score) from score;

• 求分数的总和（sum）

select sum(s_score) from score;

• 求分数的平均值（avg）

select avg(s_score) from score;

1.4 limit 语句

• imit子句用于限制返回的行数。

 select  * from score limit 5;

1.5 where 语句

• 使用 where 子句，将不满足条件的行过滤掉
• where 子句紧随from子句
• 案例实操

select * from score where s_score > 60;

1.6 算术运算符

运算符	描述
A+B	A和B 相加
A-B	A减去B
A*B	A和B 相乘
A/B	A除以B
A%B	A对B取余
A&B	A和B按位取与
A|B	A和B按位取或
A^B	A和B按位取异或
~A	A按位取反

1.7 比较运算符

操作符	支持的数据类型	描述
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关键字则可达到相反的效果。like不是正则，而是通配符
A RLIKE B, A REGEXP B	STRING 类型	B是一个正则表达式，如果A与其匹配，则返回true；反之返回false。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

1.8 逻辑运算符

操作符	操作	描述
A AND B	逻辑并	如果A和B都是true则为true，否则false
A OR B	逻辑或	如果A或B或两者都是true则为true，否则false
NOT A	逻辑否	如果A为false则为true,否则false

2 分组

2.1 Group By 语句

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

• Group By时select后的字段要么是分组字段、要么是聚合函数

• 查询非分组字段会报错

• 案例实操：

  • 计算每个学生的平均分数

  select s_id, avg(s_score) from score group by s_id;
  

  • 计算每个学生最高的分数

  select s_id, max(s_score) from score group by s_id;
  

2.2 Having语句

• having 与 where 不同点

  • where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据
  • where后面不能写聚合函数，而having后面可以使用聚合函数
  • having只用于group by分组统计语句

• 案例实操

  • 求每个学生的平均分数

  select s_id, avg(s_score) from score group by s_id;
  

  • 求每个学生平均分数大于60的人

  select s_id, avg(s_score) as avgScore from score group by s_id having avgScore > 60;
  等价于
  select s_id, avg(s_score) as avgScore from score group by s_id having avg(s_score) > 60;
  

3 join语句

3.1 等值 join

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

• 案例实操

  • 根据学生和成绩表，查询学生姓名对应的成绩

  select * from stu left join score on stu.id = score.s_id;
  

3.2 表的别名

• 好处

  • 使用别名可以简化查询。
  • 使用表名前缀可以提高执行效率。

• 案例实操：合并老师与课程表

  -- hive当中创建course表并加载数据
  create table course (c_id string, c_name string, t_id string) 
  row format delimited fields terminated by '\t';
  
  load data local inpath '/kkb/install/hivedatas/course.csv' overwrite into table course;
  
  select * from teacher t join course c on t.t_id = c.t_id;
  

3.3 内连接 inner join

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

  • join默认是inner join

• 案例实操

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

3.4 左外连接 left outer join

• 左外连接：

  • join操作符左边表中符合where子句的所有记录将会被返回。
  • 右边表的指定字段没有符合条件的值的话，那么就使用null值替代。

• 案例实操：查询老师对应的课程

   select * from teacher t left outer join course c on t.t_id = c.t_id;
  

3.5 右外连接 right outer join

• 右外连接：

  • join操作符右边表中符合where子句的所有记录将会被返回。
  • 左边表的指定字段没有符合条件的值的话，那么就使用null值替代。

• 案例实操

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

3.6 满外连接 full outer join

• 满外连接：

  • 将会返回所有表中符合where语句条件的所有记录。
  • 如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代。

• 案例实操

  select * from teacher t full outer join course c on t.t_id = c.t_id;
  

3.7 多表连接

• 多个表使用join进行连接

• ==注意：==连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

• 案例实操

  • 多表连接查询，查询商品对应的品牌和类别信息

  select * from products p
  left join brands b on p.brand_id = b.id 
  left join categorys c on p.category_id = c.id;
  

4 排序

• 官网文档

4.1 order by 全局排序

• 全局排序，只有一个reduce

• 使用 ORDER BY 子句排序

  • asc ( ascend) 升序 (默认)
  • desc (descend) 降序

• order by 子句在select语句的结尾

• 案例实操

  • 查询学生的成绩，并按照分数降序排列

  select * from score s order by s_score desc ;
  

4.2 按照别名排序

• 按照学生分数的平均值排序

  select s_id, avg(s_score) avgscore from score group by s_id order by avgscore desc; 
  

4.3 每个MapReduce内部排序（Sort By）局部排序

• sort by：每个reducer内部有序排序（局部有序），对全局结果集来说并非全局有序。

• 设置reduce个数

  set mapreduce.job.reduces=3;
  

• 查看reduce的个数

  set mapreduce.job.reduces;
  

• 查询成绩按照成绩降序排列

  select * from score s sort by s.s_score;
  

• 将查询结果导入到文件中（按照成绩降序排列）

  insert overwrite local directory '/kkb/install/hivedatas/sort' select * from score s sort by s.s_score;
  

4.4 distribute by 分区排序

• distribute by：

  • 类似MR中partition，采集hash算法，在map端将查询的结果中hash值相同的结果分发到对应的reduce文件中。
  • 结合sort by使用。

• 注意

  • Hive要求 distribute by 语句要写在 sort by 语句之前。

• 案例实操

  • 先按照学生 sid 进行分区，再按照学生成绩进行排序

  • 设置reduce的个数

  set mapreduce.job.reduces=3;
  

  • 通过distribute by 进行数据的分区,，将不同的sid 划分到对应的reduce当中去

  insert overwrite local directory '/kkb/install/hivedatas/distribute' select * from score distribute by s_id sort by s_score;
  

4.5 cluster by

• 当distribute by和sort by字段相同时，可以使用cluster by方式代替

• 除了distribute by 的功能外，还会对该字段进行排序，所以cluster by s_score = distribute by s_score + sort by s_score

  --以下两种写法等价
  insert overwrite local directory '/kkb/install/hivedatas/distribute_sort' select * from score distribute by s_score sort by s_score;
  
  insert overwrite local directory '/kkb/install/hivedatas/cluster' select * from score  cluster by s_score;