6.4.8 笛卡尔集

1）笛卡尔集会在下面条件下产生

（1）省略连接条件

（2）连接条件无效

（3）所有表中的所有行互相连接

2）案例实操

hive (default)>

select

empno,

dname

from emp, dept;

6.4.9 联合（union & union all）

1）union&union all上下拼接

union和union all都是上下拼接sql的结果，这点是和join有区别的，join是左右关联，union和union all是上下拼接。union去重，union all不去重。

union和union all在上下拼接sql结果时有两个要求：

（1）两个sql的结果，列的个数必须相同

（2）两个sql的结果，上下所对应列的类型必须一致

2）案例实操

将员工表30部门的员工信息和40部门的员工信息，利用union进行拼接显示。

hive (default)>

select

*

from emp

where deptno=30

union

select

*

from emp

where deptno=40;

6.5 排序

6.5.1 全局排序（Order By）

Order By：全局排序，只有一个Reduce。

1）使用Order By子句排序

asc（ascend）：升序（默认）

desc（descend）：降序

2）Order By子句在select语句的结尾

3）基础案例实操

（1）查询员工信息按工资升序排列

hive (default)>

select

*

from emp

order by sal;

（2）查询员工信息按工资降序排列

hive (default)>

select

*

from emp

order by sal desc;

4）按照别名排序案例实操

按照员工薪水的2倍排序。

hive (default)>

select

ename,

sal * 2 twosal

from emp

order by twosal;

5）多个列排序案例实操

按照部门和工资升序排序。

hive (default)>

select

ename,

deptno,

sal

from emp

order by deptno, sal;

6.5.2 每个Reduce内部排序（Sort By）

Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用Sort by。

Sort by为每个reduce产生一个排序文件。每个Reduce内部进行排序，对全局结果集来说不是排序。

1）设置reduce个数

hive (default)> set mapreduce.job.reduces=3;

2）查看设置reduce个数

hive (default)> set mapreduce.job.reduces;

3）根据部门编号降序查看员工信息

hive (default)>

select

*

from emp

sort by deptno desc;

4）将查询结果导入到文件中（按照部门编号降序排序）

hive (default)> insert overwrite local directory '/opt/module/hive/datas/sortby-result'

select * from emp sort by deptno desc;

6.5.3 分区（Distribute By）

Distribute By：在有些情况下，我们需要控制某个特定行应该到哪个Reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似MapReduce中partition（自定义分区），进行分区，结合sort by使用。

1）案例实操：

（1）先按照部门编号分区，再按照员工编号薪资排序

hive (default)> set mapreduce.job.reduces=3;

hive (default)>

insert overwrite local directory

'/opt/module/hive/datas/distribute-result'

select

*

from emp

distribute by deptno

sort by sal desc;

注意：

distribute by的分区规则是根据分区字段的hash码与reduce的个数进行相除后，余数相同的分到一个区。

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

演示完以后mapreduce.job.reduces的值要设置回-1，否则下面分区or分桶表load跑MapReduce的时候会报错。

6.5.4 分区排序（Cluster By）

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

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为asc或者desc。

（1）以下两种写法等价

hive (default)>

select

*

from emp

cluster by deptno;

hive (default)>

select

*

from emp

distribute by deptno

sort by deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

第8章函数

8.1 函数简介

Hive会将常用的逻辑封装成函数给用户进行使用，类似于Java中的函数。

好处：避免用户反复写逻辑，可以直接拿来使用。

重点：用户需要知道函数叫什么，能做什么。

Hive提供了大量的内置函数，按照其特点可大致分为如下几类：单行函数、聚合函数、炸裂函数、窗口函数。

以下命令可用于查询所有内置函数的相关信息。

1）查看系统内置函数

hive> show functions;

2）查看内置函数用法

hive> desc function upper;

3）查看内置函数详细信息

hive> desc function extended upper;

8.2 单行函数

单行函数的特点是一进一出，即输入一行，输出一行。

单行函数按照功能可分为如下几类: 日期函数、字符串函数、集合函数、数学函数、流程控制函数等。

8.2.1 算术运算函数

案例实操：查询出所有员工的薪水后加1显示。

hive (default)> select sal + 1 from emp;

8.2.2 数值函数

1）round：四舍五入

hive> select round(3.3); 3

2）ceil：向上取整

hive> select ceil(3.1) ; 4

3）floor：向下取整

hive> select floor(4.8); 4

8.2.3 字符串函数

1）substring：截取字符串

语法一：substring(string A, int start)

返回值：string

说明：返回字符串A从start位置到结尾的字符串

语法二：substring(string A, int start, int len)

返回值：string

说明：返回字符串A从start位置开始，长度为len的字符串

案例实操：

（1）获取第二个字符以后的所有字符

hive> select substring("atguigu",2);

输出：

tguigu

（2）获取倒数第三个字符以后的所有字符

hive> select substring("atguigu",-3);

输出：

igu

（3）从第3个字符开始，向后获取2个字符

hive> select substring("atguigu",3,2);

输出：

骚戴理解：主要这里是从下标1开始计算的

2）replace ：替换

语法：replace(string A, string B, string C)

返回值：string

说明：将字符串A中的子字符串B替换为C。

hive> select replace('atguigu', 'a', 'A')

输出：

hive> Atguigu

3）regexp_replace：正则替换

语法：regexp_replace(string A, string B, string C)

返回值：string

说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符。

案例实操：

hive> select regexp_replace('100-200', '(\\d+)', 'num')

输出：

hive> num-num

4）regexp：正则匹配

语法：字符串 regexp 正则表达式

返回值：boolean

说明：若字符串符合正则表达式，则返回true，否则返回false。

（1）正则匹配成功，输出true

hive> select 'dfsaaaa' regexp 'dfsa+'

输出：

hive> true

（2）正则匹配失败，输出false

hive> select 'dfsaaaa' regexp 'dfsb+';

输出：

hive> false

5）repeat：重复字符串

语法：repeat(string A, int n)

返回值：string

说明：将字符串A重复n遍。

hive> select repeat('123', 3);

输出：

hive> 123123123

6）split ：字符串切割

语法：split(string str, string pat)

返回值：array

说明：按照正则表达式pat匹配到的内容分割str，分割后的字符串，以数组的形式返回。

hive> select split('a-b-c-d','-');

输出：

hive> ["a","b","c","d"]

7）nvl ：替换null值

语法：nvl(A,B)

说明：若A的值不为null，则返回A，否则返回B。

hive> select nvl(null,1);

输出：

hive> 1

8）concat ：拼接字符串

语法：concat(string A, string B, string C, ……)

返回：string

说明：将A,B,C……等字符拼接为一个字符串

hive> select concat('beijing','-','shanghai','-','shenzhen');

输出：

hive> beijing-shanghai-shenzhen

9）concat_ws：以指定分隔符拼接字符串或者字符串数组

语法：concat_ws(string A, string…| array(string))

返回值：string

说明：使用分隔符A拼接多个字符串，或者一个数组的所有元素。

hive>select concat_ws('-','beijing','shanghai','shenzhen');

输出：

hive> beijing-shanghai-shenzhen

hive> select concat_ws('-',array('beijing','shenzhen','shanghai'));

输出：

hive> beijing-shanghai-shenzhen

10）get_json_object：解析json字符串

语法：get_json_object(string json_string, string path)

返回值：string

说明：解析json的字符串json_string，返回path指定的内容。如果输入的json字符串无效，那么返回NULL。

案例实操：

（1）获取json数组里面的json具体数据

hive> select get_json_object('[{"name":"大海海","sex":"男","age":"25"},{"name":"小宋宋","sex":"男","age":"47"}]','$.[0].name');

输出：

hive> 大海海

（2）获取json数组里面的数据

hive> select get_json_object('[{"name":"大海海","sex":"男","age":"25"},{"name":"小宋宋","sex":"男","age":"47"}]','$.[0]');

输出：

hive> {"name":"大海海","sex":"男","age":"25"}

8.2.4 日期函数

1）unix_timestamp：返回当前或指定时间的时间戳

语法：unix_timestamp()

返回值：bigint

案例实操：

hive> select unix_timestamp('2022/08/08 08-08-08','yyyy/MM/dd HH-mm-ss');

输出：

1659946088

说明：-前面是日期后面是指，日期传进来的具体格式

2）from_unixtime：转化UNIX时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式

语法：from_unixtime(bigint unixtime[, string format])

返回值：string

案例实操：

hive> select from_unixtime(1659946088);

输出：

2022-08-08 08:08:08

3）current_date：当前日期

hive> select current_date;

输出：

2022-07-11

4）current_timestamp：当前的日期加时间，并且精确的毫秒

hive> select current_timestamp;

输出：

2022-07-11 15:32:22.402

5）month：获取日期中的月

语法：month (string date)

返回值：int

案例实操：

hive> select month('2022-08-08 08:08:08');

输出：

8

6）day：获取日期中的日

语法：day (string date)

返回值：int

案例实操：

hive> select day('2022-08-08 08:08:08')

输出：

8

7）hour：获取日期中的小时

语法：hour (string date)

返回值：int

案例实操：

hive> select hour('2022-08-08 08:08:08');

输出：

8

8）datediff：两个日期相差的天数（结束日期减去开始日期的天数）

语法：datediff(string enddate, string startdate)

返回值：int

案例实操：

hive> select datediff('2021-08-08','2022-10-09');

输出：

-427

9）date_add：日期加天数

语法：date_add(string startdate, int days)

返回值：string

说明：返回开始日期 startdate 增加 days 天后的日期

案例实操：

hive> select date_add('2022-08-08',2);

输出：

2022-08-10

10）date_sub：日期减天数

语法：date_sub (string startdate, int days)

返回值：string

说明：返回开始日期startdate减少days天后的日期。

案例实操：

hive> select date_sub('2022-08-08',2);

输出：

2022-08-06

11）date_format:将标准日期解析成指定格式字符串

hive> select date_format('2022-08-08','yyyy年-MM月-dd日')

输出：

2022年-08月-08日