HIVE查询语法

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

常用函数

• 求总数(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;

• 分页查询(limit)

select * from score limit 5;

运算符

• 算术运算符

  运算符	描述
  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按位取反

• 比较运算符

  操作符	支持的数据类型	描述
  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相匹配，而不是只需与其字符串匹配。

• 逻辑运算符

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

分组

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

-- 计算每个学生的平均分数
select s_id, avg(s_score) from score group by s_id;

• Having 语句 针对查询结果中的列 发挥作用，筛选数据，只用于group by 分组统计语句
• having 后边可以使用聚合函数

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;

join语句

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

• 内连接 inner join(join 默认使用inner join)
  • 只有进行连接的两个表中都存在于连接条件相匹配的数据才会被保留下来。

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

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

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

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

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

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

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

排序

• order by 全局排序
  • order by 子句在selected语句的结尾

select * from score s order by s_score desc;

• 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 '/bigdata/logs/sort' select * from score s sort by s.s_score;

• distribute by 分区排序
  • 类似MR中的partition，采用hash算法，在map端将查询的结果中hash值相同的结果分发到对应的reduce文件中，可以结合sort by 使用。写在sort by 语句之前

-- 先按照学生sid分区，在按照学生成绩进行排序
-- 设置reduce的个数
set mapreduce.job.reduces=3;
-- 通过distribute by 进行数据的分区，将不同sid划分到对应的reduce当中
insert overwrite local directory '/bigdata/logs/distribute' select * from score distribute by s_id sort by s_score;

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

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

insert overwrite local directory '/bigdata/logs/cluster' select * from score cluster by s_score;