HQL(Hive SQL) 之查询语句专题汇总

HQL(Hive SQL) 之查询语句专题汇总

1. 背景

1. hive本身是一个将sql语句转换为mapreduce 程序运行的转换器
2. hive可以把结构化数据转换到一张表上，并提供查询和分析的功能
3. 针对这个过程，hive从sql优化、从mapreduce优化这2个大的角度来提升hive的性能表现。
4. sql优化，这个基于现有的第三方组件是比较成熟的技术解决方案。但优化mapreduce则需要更多精力。因为mapreduce代码是根据模板，将sql经过这个模板，转换为mapreduce程序。
5. hive本身是一个数据仓库工具，所以大部分场景都是数据一次插入或者导入，多次查询和分析，再导出。
6. 所以数据查询是hive的重中之重！！！！

2. 基本查询语句

[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available
starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, …
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

2.1 全表和特定列查询

1. 全表查询（考虑到性能，不管是传统数据库还是HQL场景，都需要尽力避免使用全表查询）

select * from tb_test;

2. 选择特定列查询

# 一般为了性能，都会选择所需要字段进行查询，尽力尽力避免全表查询，特别HQL属于海量数据处理，全表查询会给集群带来巨大压力，耗时也会很久
select age, name from tb_test;

2.2 列别名

别名可以看作是一个简称，类似于C语言中的宏定义，可以将一个字段名、查询结果集都是用别名代称。
但需要注意，别名的定义要在使用前，所以需要注意sql语句的执行顺序

select
xxxx,yyyy
from
where
group by
having
limit
整体是先执行from，然后是where，然后是查询字段，然后是group by，having，limit。所以如果需要使用别名但不确定的话，先确定sql执行顺序。
使用嵌套子查询时，hql中使用括号包起来的部分请一定一定带上别名，否则会出错

2.3算数运算符

1. A+B A和B 相加
2. A-B A减去B
3. A*B A和B 相乘
4. A/B A除以B
5. A%B A对B取余
6. A&B A和B按位取与
7. A|B A和B按位取或
8. A^B A和B按位取异或
9. ~A A按位取反

select age+1 from tb_person;

2.4常用函数

1. 求行数

select count(*) as cnt from tb_person;

# count（1）比count(*)要快，如果要对字段计数，内部加distinct时谨慎，这样会比较耗时，建议改为分组后再count（1）
select count(1) as cnt from tb_person;

2. 求最大值

select max(age) as age_max from tb_person;

3. 求最小值

select min(age) from tb_person;

4. 求总和

select sum(salary) as total_salary from tb_person;

5. 求平均值

select avg(age) as age_average from tb_person;

注意，如果查询结果需要用到聚合函数，进行嵌套子查询时，一般都是在最后一层时再计算，这样可以有比较清晰的查询逻辑

2.5 limit语句

主要用于限制返回结果条数（行数）

# 返回10条
select * from tb_test limit 10;

# 第一个数字0，是指起始行数，一般从0行开始计算，第二个是返回的行数。大家在网页和app看到的分页加载就是使用limit来进行数据切割并返回的，而且大部分一页数据是10或者15条。
select * from tb_test limit 9, 10;

3. where语句

where的用法和正常的sql规范用法一致，属于限定条件，同时having也是一样，可以针对group by之后数据继续限定

1. 限定

select age, name from tb_person where age > 23;

2. 比较运算符，当出现限定时，则必然需要有比较条件来判断是否符合条件。这些运算符可以出现在join on和having中，join on之后跟字段匹配，但需要注意避免笛卡尔积出现。having则是分组后进一步限定。

1. A=B 基本数据类型 如果A等于B则返回TRUE，反之返回FALSE
2. A<=>B 基本数据类型 如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
3. A<>B, A!=B 基本数据类型 A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
4. A<B 基本数据类型 A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
5. A<=B 基本数据类型 A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
6. A>B 基本数据类型 A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
7. A>=B 基本数据类型 A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
8. A [NOT] BETWEEN B AND C 基本数据类型 如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
9. A IS NULL 所有数据类型 如果A等于NULL，则返回TRUE，反之返回FALSE
10. A IS NOT NULL 所有数据类型 如果A不等于NULL，则返回TRUE，反之返回FALSE
11. IN(数值1, 数值2) 所有数据类型 使用 IN运算显示列表中的值
12. A [NOT] LIKE B STRING 类型 B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
13. A RLIKE B, A REGEXP B STRING 类型 B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

select * from emp where sal =5000;

select * from emp where sal between 500 and 1000;

select * from emp where comm is null;

select * from emp where sal IN (1500, 5000);

3. like、rlike
   在进行条件过滤时，如果涉及到字符串的匹配和判断，则需要like和rlike正则判断。
   实际生产中，对于字符串的处理其实占据了相当部分的比例，所以如果可能，对于正则表达式还是可以花时间研究一下的。

• 使用like选择类似的值
• 选择条件中，可以使用 % 代表0或者多个字符
• 使用 _ 代表一个字符
• rlike就是regex like的意思，可以跟随一个java的正则表达式。注意正则表达式其实也是一套规范，不同编程语言中的正则表达式细节上是会有差异的。

select * from emp where sal LIKE '2%';

select * from emp where sal LIKE '_2%';

 select * from emp where sal RLIKE '[2]';

4. 逻辑运算符

1. AND 逻辑并
2. OR 逻辑或
3. NOT 逻辑否
   注意拼接join是，on不能跟or，hive中on不支持不等连接，也就是一般都是x join y on x.id=y.id

select * from emp where sal>1000 and deptno=30;

select * from emp where sal>1000 or deptno=30;

select * from emp where deptno not IN(30, 20);

4. 分组

1. group by, 可以传入单个或者多个字段进行分组。
   注意使用group by的时候，如果需要访问分组前数据，使用开窗函数会更加合适（聚合函数之后，加上over（），再在over中进行条件设定，如partion by order by limit等）
2. group by一般和聚合函数一起使用，对结果分组后，对每个组进行聚合操作

# 注意这里的字段访问，只能直接访问分组的字段t.deptno。其他都是通过聚合函数avg（）进行访问和处理的
select t.deptno, avg