文章目录
- 1 基本查询
- 2 排序
- 2.1 全局排序（Order By）
- 2.2 局部排序 Sort By 区内有序
- 2.3 Distribute By 分区（为Sort by 指定分区）
- 2.4 Cluster By 排序
- 4 常用查询函数
- 4.1 NVL 空字段赋值
- 4.2 CASE WHEN 语句
- 4.3 IF 语句
- 4.4 行转列
- 4.5 列转行
- 4.6 时间处理
- 5 自定义函数UDF
基本语句语法
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]创建数据
（1）创建部门表
create table if not exists dept(
deptno int,
dname string, loc int
)
row format delimited fields terminated by '\t';例
（2）创建员工表
create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
row format delimited fields terminated by '\t';例
（3）导入数据（使用load加载）
从本地HDFS导入数据
load data local inpath '/opt/module/datas/dept.txt' into table dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;1 基本查询
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。
1.全表查询
hive (default)> select * from emp;
hive (default)> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ;2.选择特定列查询
hive (default)> select empno, ename from emp;3.列别名（关键字‘AS’）
hive (default)> select ename AS name, deptno dn from emp;4.算术运算符
hive (default)> select sal +1 from emp;5.常用函数
计数 count(*)、最大值（max）、最小值（min）、总和（sum）、平均值（avg）
select count(*) cnt
from emp;select avg(sal) avg_sal
from emp;6.Limit语句
Limit语句用于限制返回的行数
select * from emp limit 3;7. Where语句
Where语句用于过滤
select * from emp where sal >1000;注意：where 子句中不能使用字段别名。
8.比较运算符（Between/In/ Is Null）
9.Like 和 Rlike
like: 选择类似的值
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
Rlike子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大 的语言来指定匹配条件。
10.逻辑运算符（And/Or/Not）
11.Group By语句
12.Having语句
13.Join语句
Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
内连接 join
左外连接 left join
右外连接 right join
满外连接（全连接） full join
多表连接
hive (default)>SELECT e.ename, d.deptno, l. loc_name
FROM emp e
JOIN dept d
ON d.deptno = e.deptno
JOIN location l
ON d.loc = l.loc;大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。
笛卡尔积
2 排序
2.1 全局排序（Order By）
Order By：全局排序，一个Reducer
对查询结果做全局排序，所有的数据都通过一个reduce进行处理的过程，对于大数据集，这个过程将消耗很大的时间来执行。默认是asc升序
hive (default)> select * from emp order by sal;
hive (default)> select * from emp order by sal desc;例
2.2 局部排序 Sort By 区内有序
Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排 序，此时可以使用 sort by。
Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集 来说不是排序。
每个MapReduce内部排序
- 对每个Reducer进行排序，不影响全局结果集
- 直接使用会将结果平均分配给每个文件(避免数据倾斜)
- 一般配合Distribute By使用
设置 reduce 个数
hive (default)> set mapreduce.job.reduces=3;查看设置 reduce 个数
hive (default)> set mapreduce.job.reduces;根据部门编号降序查看员工信息
hive (default)> select * from emp sort by deptno desc;
注：如果没有指定分区规则，则随机分区分配数据
将查询结果导入到文件中（按照部门编号降序排序）
sort by：是在每个reduce内部进行排序，是执行一个局部排序过程，可以保证每个reduce的输出数据都是有序的(但并非全局有效)，能够提高后面进行的全局排序的效率。默认是asc升序
例如：select * from table sort by salary desc;
注：使用sort by需要预先设置Reduce的数量大于1，这样才会做局部排序，如果Reduce数量是1，作用与order by一样，全局排序。
2.3 Distribute By 分区（为Sort by 指定分区）
Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。
distribute by 子句可以做这件事。
distribute by 类似MapReduce中的Partition分区（自定义分区），一般配合Sort By排序使用
对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。
注意：该语句需要写在 Sort By 语句之前！
例
按deptno分4个区，按照 sal 排序
-- 先设置reduce的个数
set mapreduce.job.reduces=3;
-- 先按照id值分区,再按照age值升序排序
insert overwrite local directory '/opt/datas/dis-out'
select * from student distribute by id sort by age;2.4 Cluster By 排序
当 Distribute By 和 Sort By 字段相同时，可以使用 Cluster By 方式
该排序只能是升序排序
-- 以下两种写法等价
select * from student cluster by grade;
select * from student distribute by grade sort by grade;4 常用查询函数
4.1 NVL 空字段赋值
NVL：给值为NULL的数据赋值，
格式：NVL( string1, replace_with)
如果string1为NULL，则返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。
-- 如果age为null,用18代替
select nvl(age,18) from student;
-- 替换的参数可以是字段,如果age为null,用id值代替
select nvl(age,id) from student;4.2 CASE WHEN 语句
需求
求出不同部门男女各多少人
结果
部门 男 女
A 2 1
B 1 2select
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from
emp_sex
group by
dept_id;4.3 IF 语句
-- 以下代码等价于上面的case when
select
dept_id,
sum(if(gender='男',1,0)) male_count,
sum(if(gender='女',1,0)) female_count
from emp_sex
group by dept_id;4.4 行转列
行转列：将多个列中的数据在一列中输出
列转行：将一列中的数据拆分成多行
Concat
concat(string1/col, string2/col, …)输入任意个字符串(或字段,可以为int类型等)，返回拼接后的结果
select concat(id,'-',name,'-',age)
from student;Concat_ws
concat_ws(separator, str1, str2, …)特殊形式的 concat()，参数只能为字符串，第一个参数为后面参数的分隔符
分隔符可以是与后面参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
select concat_ws('-', name, gender)
from student;Collect_set（聚合，返回数组类型）
collect_set(col)将某字段进行去重处理，返回array类型；该函数只接受基本数据类型
select collect_set(age)
from student;collect_set
collect_list，区别就是set去重，list不去重
需求
把星座和血型一样的人归类到一起
结果
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋select
t1.base,
concat_ws('|', collect_set(t1.name)) as name
from
(select name,concat(constellation, ",", blood_type) as base
from person_info) as t1
group by
t1.base;需求2
结果：
张三 语文,数学,英语 98,95,89
李四 语文,数学,英语 97,88,90select
stu_name,
concat_ws(',',collect_set(course)) as course,
concat_ws(',',collect_set(score)) as score
from student
group by stu_name4.5 列转行
行转列：将多个列中的数据在一列中输出
列转行：将一列中的数据拆分成多行
Explode
explode(col)将hive一列中复杂的 array 或 map 结构拆分成多行
-- 将上面collect_set后的结果使用explode拆分
select explode(ages)
from (
select collect_set(age) as ages
from student ) as n1;Lateral View
LATERAL VIEW udtf(expression) tableAlias AS columnAlias配合 split, explode 等UDTF一起使用，它能够将一列数据拆成多行数据，并且对拆分后的结果进行聚合
需求
假设有如下movies表，字段名分别为movie(string)和category(array<string>)
转换为
select movie,category_name
from movies
lateral view explode(category) table_tmp as category_name;
-- 结果:
--《疑犯追踪》 悬疑
--《疑犯追踪》 动作
--《疑犯追踪》 科幻
--《疑犯追踪》 剧情
--《海豹突击队》 动作
-- ...需求2
a b 1,2,3
c d 4,5,6转换为
a b 1
a b 2
a b 3
c d 4
c d 5
c d 6select col1, col2, col5
from test
lateral view explode(split(col3,',')) b AS col54.6 时间处理
Date_format
格式化时间 ，注意：只能匹配横杆 “-”
select date_format('2021-02-23','yyyy-MM-dd HH:mm:ss');
//结果: 2021-02-23 00:00:00Date_add
时间跟天数相加，天数可以为负
select date_add('2021-02-13', 10);
//结果: 2021-02-23Date_sub
时间跟天数相减，天数可以为负
select date_sub('2021-02-23', 10);
//结果: 2021-02-13Datediff
两个时间相减，结果为天数，注意：是参数1 - 参数2
时分秒不影响最后的结果
select datediff('2021-02-13', '2021-02-23');
//结果: -10
select datediff('2021-02-23', '2021-02-13');
//结果: 10
select datediff('2021-02-23 11:27:21','2021-02-13 17:15:12');
//结果: 105 自定义函数UDF
1）Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF 来方便的扩展。
2）当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
3）根据用户自定义函数类别分为以下三种：
- UDF（User-Defined-Function） 一进一出
- UDAF（User-Defined Aggregation Function） 聚集函数，多进一出 类似于：count/max/min
- UDTF（User-Defined Table-Generating Functions） 一进多出
如 lateral view explode()
4）编程步骤：
- 继承 Hive 提供的类
org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;- 实现类中的抽象方法
- 在 hive 的命令行窗口创建函数添加 jar
add jar linux_jar_path创建 function
create [temporary] function [dbname.]function_name AS class_name;- 在 hive 的命令行窗口删除函数
drop [temporary] function [if exists] [dbname.]function_name;
