【Hive中篇： 一篇文章带你了解表的静态分区，动态分区! 分桶！Hive sql的内置函数！复杂数据类型！hive的简单查询语句！】

前言：
💞💞大家好，我是书生♡，本篇文章主要分享的是大数据开发中hive的相关技术，表的静态分区，动态分区，分桶！Hive sql的内置函数！复杂数据类型！以及hive的简单查询语句！希望大家看完这篇文章会有所帮助。也希望大家能够多多关注博主。博主会持续更新更多的优质好文！！！
💥💥下一篇博客会持续为大家讲解hive中的相关技术。
💞💞前路漫漫，希望大家坚持下去，不忘初心，成为一名优秀的程序员

个人主页⭐： 书生♡
gitee主页🙋‍♂：闲客
专栏主页💞：大数据开发
博客领域💥：大数据开发，java编程，前端，算法，Python
写作风格💞：超前知识点，干货，思路讲解，通俗易懂
支持博主💖：关注⭐，点赞💖、收藏⭐、留言💬

本篇文章是依赖于上一边文章的大家对于hive的定义，使用，部署安装又不懂的可以浏览我的上一篇文章。【Hive上篇： 一篇文章带你使用Hive！深入了解Hive！学会Hive！】

1.表的分区

在大数据中，最常用的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个小的文件就会很容易了

同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天，或者每小时进行切分成一个个的小的文件，这样去操作小的文件就会容易得多了。

分区的概念：
  将整个表的数据在存储时，按照 ”分区键的列值“ 划分成多个子目录来存储。区从形式上可以理解为文件夹

1.1 单个分区（静态分区）

在公司会产生大量数据，数据存储在hdfs上时，需要对数据进行拆分，在进行数据查询时就可以快速查询到需要的内容

如果需要进行数据的分区操作，就需要再建表的时候指定分区字段
单个分区是创建单个目录

我们在分区的时候，一般是在创建表的时候定义一个分区字段。

我们使用**partitioned by (分区字段 类型)**
注意：分区字段不要和表中字段重复，也就是说分区字段是，不同于表中字段的

-- 创建表指定分区，对原始数据进行分区保存
create table score_part
(
    name   string,
    course string,
    score  int
) partitioned by (dt string)
    row format delimited fields terminated by '\t';

-- 将原始数据表的数据写入新的分区表中
-- 静态写入分区数据 需要手动自己指定分区数值
desc formatted score_part;


-- 在加载数据时手动指定分区名称(静态加载)
load data local inpath '/root/hive_data/score.txt' into table score_part partition (dt = '2023');
load data local inpath '/root/hive_data/score.txt' into table score_part partition (dt = '2024');

1.2 多级分区（静态分区）

多级分区表,其实就是指定多个分区字段,多个分区字段之间,按照先后顺序具备层级关系
多个分区可以将数据拆分多个目录存储

create table score_part_01
(
    name   string,
    course string,
    score  int
) partitioned by (year string,month string,day string)
    row format delimited fields terminated by '\t';
-- 2. 查看多级分区表的详细信息
desc formatted score_part_01;

-- 3. 向指定的分区目录中插入数据
-- 多级分区中 如果指定分区名称,必须将所有的分区字段全部赋值
-- 语法: load data local inpath '路径' into table 表名 partition (year = '2023', month = '11', day = '06');
load data local inpath '/root/hive_data/score.txt' into table score_part_01 partition (year = '2024',month = '01',day = '01');
load data local inpath '/root/hive_data/score.txt' into table score_part_01 partition (year = '2024',month = '01',day = '02');
load data local inpath '/root/hive_data/score.txt' into table score_part_01 partition (year='2023',month='01',day='01');

查看文件分布情况可知, 相同年份的数据会在同一个目录中, 该目录下会放置月份目录, 月份目录下存放日期目录, 分组字段目录间具备层级关系

1.3 分区的增删改查

添加：

-- 添加一个新的分区
-- 语法: alter table 表名 add partition (year = '2025', month = '01', day = '08');
alter table score_part_01 add partition (year = '2025', month = '01', day = '08');
-- 可以一次添加多个分区
-- 语法: alter table 表名 add partition() partition(); ==> 不需要,分隔
alter table score_part_01 add partition (year = '2026', month = '01', day = '08')partition (year = '2027', month = '01', day = '08');

我们新添加的分区，里面是没有内容的

删除，修改:

-- 创建一个分区表
create table tb_student(
    id int,
    name string,
    gender string,
    age int,
    cls string
)partitioned by (cls_p string)
row format delimited fields terminated by ',';

-- show查看分区表的分区信息
show partitions tb_student;

-- 修改分区名
-- 语法: alter table 表名 partition(xxx='老名') rename to partition (xxx='新名');
ALTER TABLE tb_student PARTITION(cls_p='CS') RENAME TO PARTITION(cls_p='CS2');
alter table score_part_01  partition(year = '2026', month = '01', day = '08') rename to partition (year = '2021', month = '01', day = '08');

-- 删除分区
-- 语法: alter table 表名 drop partition(分区字段);
alter table tb_student drop partition(cls_p='CS')

1.4 动态分区（重点）

• 动态分区的定义：
  动态分区是Hive中一种特殊的分区方式，它允许在加载数据时根据数据的某些列的值自动创建分区。相比于静态分区，动态分区更加灵活和自动化。
• 动态分区的作用：
  动态分区的主要作用是简化分区管理和数据加载的过程。通过使用动态分区，用户可以根据数据的某些列的值自动创建分区，而无需手动定义和管理每个分区。这样可以减少手动操作的工作量，并且更适用于处理大量分区的情况。

-- 创建动态分区表   ，同理分区字段不是表内的字段
drop table user_partition;
create table user_partition(
    id int,
    name string
)partitioned by (dt int)
row format delimited fields terminated by ',';
-- 开启动态分区功能 （添加动态分区之前要先开启动态分区的设置）
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
--  动态加载数
-- 语法: insert into table 分区表 partition(分区字段) select ...
insert into table user_partition partition(dt) select id,name,dt from test03.test_user;

动态分区是通过insert into 插入的方式进行加载的
注意：
这里的查询字段要与动态分区表- -对应

2.表的分桶

分桶和分区一样，也是一种通过改变表的存储模式，从而完成对表优化的一种调优方式
但和分区不同，分区是将表拆分到不同的子文件夹中进行存储，而分桶是将表拆分到固定数量的不同文件中进行存储。

• 什么是分桶
  分桶是相对分区进行更细粒度的划分。桶是通过对指定列进行哈希计算来实现的，通过哈希值将一个列名下的数据切分为一组桶，并使每个桶对应于该列名下的一个存储文件。
  注意：
  在hdfs目录上，桶是以文件的形式存在的，而不是像分区那样以文件夹的形式存在。

• 为什么要分桶
  在分区数量过于庞大以至于可能导致文件系统崩溃时，我们就需要使用分桶来解决问题了。
  分区中的数据可以被进一步拆分成桶，不同于分区对列直接进行拆分，桶往往使用列的哈希值对数据打散，并分发到各个不同的桶中从而完成数据的分桶过程。

区别：
分区 将数据拆分不同目录下存储
分桶 将数据拆分成不同文件进行存储

无论是分区，还是分桶，本质都是对数据的拆分存储，作用是为了提升查询的效率。

2.1 (文件方式)数据加载到分桶表

使用分桶时，一般都是已经存在了一个原始数据表，为了提升原始数据速度，将原始数据在重新写入一个分桶表

– clustered by (分桶字段) into 分桶数 buckets
– 注意:创建分桶表时,分桶字段必须是<表中存在的字段>

create table course_bucket
(
    c_id     int,
    c_course string,
    t_name   string
) clustered by (c_id) into 3 buckets
    row format delimited fields terminated by '\t';
-- 查看分桶表的详细信息
desc formatted course_bucket;
--  向分桶表中插入数据
-- 使用load data 进行数据导入, 但是这种方式在老版本的hive中使用存在问题 (不会分桶,而是将所有数据加载到一个桶中.)
load data local inpath '/root/hive_data/course.txt' into table course_bucket;

2.2 (sql方式)数据加载到分桶表在这里插入代码片

-- 1. 清空表数据,方便方式2的验证
truncate table course_bucket;
-- 2. 先创建一个普通表,将数据全部加载进去, 再查询普通表,将数据加载到分桶表中.
create table course
(
    c_id     int,
    c_course string,
    t_name   string
) row format delimited fields terminated by '\t';
-- 步骤2: 向非分桶表中加载数据文件course.txt
-- load data local inpath 路径 into table 表名;
load data local inpath '/root/hive_data/course.txt' into table course;
-- 步骤3: 校验非分桶表中的数据是否加载完成
select *
from course;
-- 3. 查询非分桶表中的数据内容,加载到分桶表中
-- insert into 表名 select * from 表名;
insert into course_bucket
select *
from course;
-- 4. 查看分桶表中的数据内容
select *
from course_bucket;

2.3分桶原理

数据按照hash取余的方式进行拆分，哈希值取余分桶写入到不同的文件中

hash(分桶字段)%分桶数=余数

Hash算法是一种数据加密算法，其原理我们不去详细讨论，我们只需要知道其主要特征：
同样的值被Hash加密后的结果是一致的
比如字符串“binzi”被Hash后的结果是93742710（仅作为示意），那么无论计算多少次，字符串“binzi”的结果都会是93742710。
比如字符串“bigdata”被Hash后的结果是-114692950（仅作为示意），那么无论计算多少次，字符串“bigdata”的结果都会是-114692950。
基于如上特征，在辅以有3个分桶文件的基础上，将Hash的结果基于3取模（除以3 取余数）
那么，可以得到如下结果：
无论什么数据，得到的取模结果均是：0、1、2 其中一个
同样的数据得到的结果一致，如’binzi’ hash取模结果是0，无论计算多少次，它的取模结果都是0

思考:如果直接将数据文件上传到分桶表目录中,能否映射成功呢? 可以映射成功,但是没有分桶效果

2.4 分桶排序表

clustered by (分桶字段) sorted by (排序字段 desc|asc) into n buckets;
结论: 按照id 进行分桶(哈希取模法), 同时在桶内按照age进行降序排列

create table students_buck_sort(
         id     int,
     name   string,
     gender string,
     age    int,
     class  string
) clustered by (id) sorted by (age) into 3 buckets
row format delimited fields terminated by ',';
-- 2. 查询表的元数据详情
-- 3. 向分桶排序表中导入数据
-- load data local inpath '路径' into table 表名;
load data local inpath '/root/hive_data/students.txt' into table students_buck_sort;
-- 4. 查看数据内容是否映射成功
select *
from students_buck_sort;
-- 5. 查看hdfs中的数据文件看是否分桶并排序成功,排序规则如何.
-- 结论: 按照id 进行分桶(哈希取模法), 同时在桶内按照age进行降序排列

2.5 分桶排序查询

注意: (1)分桶排序查询,不一定查询分桶表 (2)分桶排序查询,不会修改原有表的数据值

• 无论有多少个reduce任务, order by 最终只能使用一个reduce任务进行全局排序
• <指定字段分桶> 并 <指定字段排序> 需要使用 ==> distribute by (仅分桶) + sort by (仅排序)

create table students
(
    id     int,
    name   string,
    gender string,
    age    int,
    class  string
) row format delimited fields terminated by ',';
-- 2. 向表中导入数据
load data local inpath '/root/hive_data/students.txt' into table students;
-- 3. 查看数据表中数据是否映射成功
select *
from students;
-- 4. 使用order by age 全局排序
select *
from students order by age;
-- 5. 使用cluster by age 进行分桶排序
-- 我们此时使用cluster by 也是全局排序,因为此时只有1个桶
select *
from students cluster by age;
-- 6. 修改reduce任务的数量 将其修改为3
-- 默认情况下,reduce任务的数量为-1 ,代表根据业务自行判断reduce任务数量. 一般默认为1
-- set mapreduce.job.reduces;
set mapreduce.job.reduces
-- 将reduce任务数量设置为3
-- set mapreduce.job.reduces = 2;
set mapreduce.job.reduces = 3;

-- 7. 修改reduce任务数量为3后进行分桶排序
select *
from students cluster by age;
-- 8. 修改reduce任务数量为3后进行全局order by 排序
select *
from students order by age;
-- todo 结论 无论有多少个reduce任务, order by 最终只能使用一个reduce任务进行全局排序
--  所以开发中数据体量过大不建议使用order by
--  mapred --daemon start historyserver 开启历史服务器查看reduce数量

-- 9. distribute by (仅分桶) + sort by (仅排序)
-- cluster by 具有局限性, 分桶和排序字段必须相同,且只能使用升序排列
-- <指定字段分桶> 并 <指定字段排序> 需要使用 ==> distribute by (仅分桶) + sort by (仅排序)
-- 按照gender分桶 按照age降序排序(需要设置reduce个数为2)
select *
from students distribute by id sort by age DESC ;

2.6 分桶主要使用场景

多表关联，为了提升多表关联的查询效率，可以将关联的表数据按照相同的关联字段，进行分桶，保持分桶个数一致，或是倍数关系，可以将系统数据放在同一个余数文件中，提升了关联效率

分桶还可以进行随机采样
可以通过随机采样减少计算量

3. 复杂数据类型

hive在对hdfs上的文件数据进行读取和写入数据时，会调用的mapreudce的方法有两类

• 前面我们已经学习了hive的自定义分隔符格式: ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘指定分隔符’
  在Hive的建表语句中相关的语法为：
  serde 就是序列化和反序列化机制,(文件读写的规则)
  • 序列化: 将内存中的数据转换为二进制写入到磁盘中
  • 反序列化: 将磁盘中的二进制数据读取到内存当中形成数据
• 默认的序列化机制是lazysampleserde 使用delimited 代替
• 指定了文件中的字段分隔符, 换行符, 集合间的分隔符, map间的kv分隔符.
• 默认的序列化机制,可以满足日常工作百分之九十以上的需求.

3.1 默认序列化器 delimited

在进行表定义时指定row format delimited

row format delimited -- delimited 代表我们使用的是默认的lazysampleserde
        fields terminated by '\t' -- 以\t作为字段之间的分隔符
        collection items terminated by ';' -- 集合元素之间的分隔符
        map keys terminated by ':' -- map类型 kv之间的分隔符
        lines terminated by '\n';   -- 换行符

3.2 array数组类型数据

array数据类型,就是一个数据值中保存多个数据类型相同的数据内容

-- 创建一个用户表
create table array_tb
(
    name    string,
    address array<string>
) row format delimited fields terminated by '\t'
    collection items terminated by ',';
-- 2. 查询表详情
desc array_tb;
desc formatted array_tb;
-- 3. 向表中导入数据
load data local inpath '/root/hive_data/data_for_array_type.txt' into table array_tb;
-- 4. 查询数据是否插入成功
select *
from array_tb;
-- 5. 数组类型数据的使用

-- 需求1: 获取所有的用户 和用户去过的第二个城市 address[1]
select name, address[1]
from array_tb;

-- 需求2: 查寻所有的用户分别去过几个城市 size(address)
select name, size(address)
from array_tb;

-- 需求3: 筛选所有用户中,去过上海的用户 array_contains(address, 'shanghai')
select *
from array_tb
where array_contains(address, 'shanghai');

-- 判断数组数据中是是否包含这个数据
select array_contains(`array`('小明', '小芳', '小丽', '小胖'), '小红'); -- false
select array_contains(`array`('小明', '小芳', '小丽', '小胖'), '小丽');  -- true

--  对于array类型数据进行排序
select sort_array(`array`(1, 5, 4, 2, 3)); -- [1,2,3,4,5]

3.3 struct类型数据

struct数据类型和map类型有些相似,但是struct数据类型中,多个键值对中,我们可以手动指定键名

数据：
1,张三#20#男

1-先按照逗号拆分数据
2-在将张三#20#男 按照数组拆分 [张三,20,男]
3-在对数组中的数据转为map形式 {‘name’:张三,‘age’:20,‘gender’:男}

create table tb_user_new_struct(
    id int,
    `user` struct<name:string,age:int,gender:string>
)row format delimited
fields terminated by ','
collection items terminated by '#'; -- [张三,20,男]

select * from tb_user_new_struct;

-- 1. 创建一个数据表 struct_db 
create table struct_db
(
    id   int,
    info struct<name:string, age:int> -- 结构体的泛型 <第一个值的键:数据类型, 第二个值的键:数据类型...>
) row format delimited fields terminated by '#'
    collection items terminated by ':';
-- 2. 将数据导入到表中
load data local inpath '/root/hive_data/data_for_struct_type.txt' into table struct_db;

-- 3. 查询表的元数据详情
select *
from struct_db;
-- 4. 查看表中的数据内容

-- 5. struct类型基本使用
-- 需求1: 获取所有学员的id和姓名 字段名.key值
select id, info.name
from struct_db;

3.4 map类型数据

数据：
1,孙悟空,53,西部大镖客:288-大圣娶亲:888-全息碎片:0-至尊宝:888-地狱火:1688
2,鲁班七号,54,木偶奇遇记:288-福禄兄弟:288-黑桃队长:60-电玩小子:2288-星空梦想:0
3,后裔,53,精灵王:288-阿尔法小队:588-辉光之辰:888-黄金射手座:1688-如梦令:1314
4,铠,52,龙域领主:288-曙光守护者:1776
5,韩信,52,飞衡:1788-逐梦之影:888-白龙吟:1188-教廷特使:0-街头霸王:888

1-按照field进行字段分割 分隔符是,
2-按照数组进行字段分割 分隔符是 - [‘西部大镖客:288’,‘大圣娶亲:888’,全息碎片:0,至尊宝:888,地狱火:1688]
3-在将数组的key：value结构数据转为map {‘西部大镖客’:288,‘大圣娶亲’:888}

create table map_tb
(
    id      int,
    name    string,
    members map<string, string>, -- 在map类型数据中也必须使用泛型, 尖括号内第一个类型是key的类型,第二个类型是value的类型
    age     int
) row format delimited fields terminated by ','
    collection items terminated by '#'
    map keys terminated by ':';
-- 2. 将数据导入到表中
-- load data local inpath '路径' into table 表名;
load data local inpath '/root/hive_data/data_for_map_type.txt' into table map_tb;
-- 3. 查看表的元数据详情
desc map_tb;
-- 4. 查看数据是否上传成功
select *
from map_tb;
-- 5. map类型的使用方法
-- 需求1: 获取当前学员id 和每个家庭成员的姓名 map_values()
select id, name, map_values(members)
from map_tb;
-- 需求2: 找到有哥哥的学员  map字段[键] 可以获取值 members['brother']
-- 如果获取的键不存在则返回null 所以如果值不是null则证明有哥哥
select *
from map_tb
where members['brother'] is not null;

-- 需求3: 获取家庭成员为4人的学员 size()
select *
from map_tb
where size(members) + 1 = 4;
-- 可以一次性获取map类型数据中所有的key 和所有的 value
-- 我们使用 map_keys(字段) 和 map_values(字段) 可以获取key 和value的数组
select map_keys(members), map_values(members)
from map_tb;


-- 如果想要判断一个数据是否在map类型中
select array_contains(map_keys(`map`('name', '小明', 'age', '18')), '小明'); -- false
select array_contains(map_values(`map`('name', '小明', 'age', '18')), '小明'); -- true

-- 5. 获取map类型数据中,所有的键 和所有的值
select map_keys(`map`('name', '小明', 'age', '18'));
select map_values(`map`('name', '小明', 'age', '18'));

3.5 json数据解析

数据：

{“device”:“device_30”,“deviceType”:“kafka”,“signal”:98.0,“time”:1616817201390}
{“device”:“device_40”,“deviceType”:“route”,“signal”:99.0,“time”:1616817201887}
{“device”:“device_21”,“deviceType”:“bigdata”,“signal”:77.0,“time”:1616817202142}
{“device”:“device_31”,“deviceType”:“kafka”,“signal”:98.0,“time”:1616817202405}
{“device”:“device_20”,“deviceType”:“bigdata”,“signal”:12.0,“time”:1616817202513}
{“device”:“device_54”,“deviceType”:“bigdata”,“signal”:14.0,“time”:1616817202913}
{“device”:“device_10”,“deviceType”:“db”,“signal”:39.0,“time”:1616817203356}
{“device”:“device_94”,“deviceType”:“bigdata”,“signal”:59.0,“time”:1616817203771}
{“device”:“device_32”,“deviceType”:“kafka”,“signal”:52.0,“time”:1616817204010}
{“device”:“device_21”,“deviceType”:“bigdata”,“signal”:85.0,“time”:1616817204229}
{“device”:“device_74”,“deviceType”:“bigdata”,“signal”:27.0,“time”:1616817204720}
{“device”:“device_91”,“deviceType”:“bigdata”,“signal”:50.0,“time”:1616817205164}
{“device”:“device_62”,“deviceType”:“db”,“signal”:89.0,“time”:1616817205328}

-- 前置条件：创建一个string类型的
--创建表 tb_json_test
create table tb_json_test
(
    json string
);

3.5.1 get_json_object解析

-- 1. 使用get_json_object 进行解析 获取 deviceType 数据
-- 语法: get_json_object(json数据, '$.key值')
select get_json_object(json, '$.deviceType')
from tb_json_test;
--  2. 获取 device deviceType signal等多个字段数据
select json,
       get_json_object(json, '$.device')     as device,
       get_json_object(json, '$.deviceType') as deviceType,
       get_json_object(json, '$.signal')     as signal
from tb_json_test;

3.5.2 json_tuple解析

-- get_json_objcet 一次仅能解析一个字段,效率低
-- 1. 尝试使用json_tuple完成json解析
-- 语法: json_tuple(json数据, 'key值1','key值2')
select json_tuple(json, 'device') as device
from tb_json_test;
--  2. 获取 device deviceType signal等多个字段数据
-- json_tuple(json, 'device', 'deviceType') as (device, deviceType)
select json_tuple(json, 'device', 'deviceType', 'signal') as (device, deviceType, signal)
from tb_json_test;

3.5.2 serde机制

使用serde机制在建表时完成json

可以将json文件中的数据key设为字段，将value值解析为对应的行数据

-- 数据同上
create table tb_json_test2
(
    device     string,
    deviceType string,
    signal     double,
    `time`     bigint
)
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe';

– 这种方式一劳永逸,一般整个数据文件都是json的时候我们使用serde方法,
– 如果只有其中的一两个字段是json则使用get_json_object 和json_tuple

3.6 复杂类型array、map、struct总结

4. 内置函数

-- 查看hive中的所有内置函数
show functions;
-- 来查看函数的使用方式
desc function extended 函数名;

4.1 字符串操作函数

• 计算字符串长度
  length（字段）
• 字符串拼接
  concat
  concat_ws
• 字符串切割
  split
• 字符串截取
  substr（字段）
  substr（字段，起始的字符位置数,截取的字符个数）
• 字符串替换
  regexp_replace(字段,‘原始字符’,‘替换的新字符’)
• lower 将数据转换为小写字母,
  upper 将数据转换为大写字母

create table tb_stu
(
    id          int,
    name        string,
    gender      string,
    age         int,
    create_time timestamp,
    height      decimal(5, 2)
);
insert into tb_stu
values (1, '张三', '男', 20, '2024-04-25 18:12:30', 175.85),
       (2, '李四', '男', 21, '2024-04-25 18:25:15', 100.25);

select *
from tb_stu;
select *
from tb_stu;
-- 计算字符串长度  统计字符个数
select name, length(name)
from tb_stu;
-- 拼接多个字符 没有拼接字符
select name, gender, concat(name, gender)
from tb_stu;
-- 拼接多个字符 以‘：’分割
select name, gender, concat_ws(':', name, gender)
from tb_stu;
-- 切割 切割后的数据是数组
select create_time, split(create_time, '-')
from tb_stu;
-- 数组取值
select create_time, split(create_time, '-')[0]
from tb_stu;
-- 截取 取字符串中指定长度的字符数据
-- substr(create_time,起始的字符位置数,截取的字符个数)
select create_time, substr(create_time, 1, 4)
from tb_stu;
-- 替换
select create_time, regexp_replace(create_time, '-', '/')
from tb_stu;
-- 方法嵌套使用
-- trim 去除字符串的前后空格   '  张三  '
select create_time, substr(trim(name), 1, 4)
from tb_stu;
-- 4. lower 将数据转换为小写字母,  upper 将数据转换为大写字母
-- lower('字符串');
-- upper('字符串');
select lower("AAAAADc");
select upper("aaabbcc");

4.2 数字函数

select 1+1;
select 1-1;
select 1*10;

-- 取余
select 2%5;
-- 取整
select round(3.12);
-- 取整，保留两位小数
select round(3.1214,2);
-- 四舍五入
select round(3.1294,2);
-- 向上取整数
select ceil(3.14);
-- 向下取整数
select floor(3.14);
-- 次方计算
select pow(2,3);
-- 1. rand 随机数 获取从0-1的随机数
select rand();
-- 可以再rand中填写随机数因子,保证每次随机的数据完全一样
select rand(72);

4.3 条件判断函数

单个条件判断我们使用 if 语句
多个条件判断我们使用 case when 语句

• if()
• case
  when 条件1 then 结果
  when 条件2 then 结果
  when 条件3 then 结果
  else
  前面条件都不成返回的结果
  end 字段名称

-- if判断
-- if(判断条件,成立返回的结果,不成立返回的结果)  单个条件判断
select gender,if(gender=0,'男','女') from tb_user;
-- 多个分类条件判断 case when
-- 10-18 少年  18-40 青年  40-65 中年  大于 65 老年
select age,
       case
        when age <=18 then '少年'
        when age >18 and age <=40 then '青年'
        when age >40 and age <=65 then '中年'
        when age >65  then '老年'
        end as age_new
from tb_user;
-- 查看方法的使用形式
desc function extended nullif;

-- 2. 空值判断  is null  is not null
-- null类型数据,无法进行比较判断和等值判断 返回数据均为null
select null = null; -- null
select null is null; -- true
select null is not null; -- false
select 12 is not null; -- true

-- 3. nvl 传入一个数据,赋值一个默认值,如果该数据不为空,则返回该数据,如果该数据为空则返回默认值
select nvl(12, '默认值');
select nvl(null, '空值');

-- 处理空值可以使用该函数
select id,nvl(name,'未知'),dt from test_user;

-- 4. coalesce 传入多个数据,返回第一个不为空的数据
select coalesce(null, 2, 3, 4, 5);

select coalesce(null, null, 3, 4, 5);

-- 如果数据中所有的数据都为空,则返回null
select coalesce(null, null, null);

-- 使用场景
-- 需求: 从orders表中 获取订单的支付时间paytime,如果订单未支付,则获取订单的创建时间createtime
select orderid,
       totalmoney,
       coalesce(paytime, createtime) as create_or_pay_time
from orders;

4.4 日期类型操作

-- 获取当前日期
select `current_date`();
-- 获取当前日期时间
select `current_timestamp`();
-- 获取unix时间（时间戳） 从1970年1月1号0时0分0秒 到现在过去了多少秒
select unix_timestamp();

-- 将时间数据转换为时间戳
-- 日期时间转为unix
select unix_timestamp('2023-10-01 15:30:28');
-- 将unix时间转为日期时间
select from_unixtime(12390886789);

-- 年月日的取值
select year('2023-10-01 15:30:28');
select month('2023-10-01 15:30:28');
select day('2023-10-01 15:30:28');
select dayofmonth('2023-10-12 15:30:28');
select dayofweek('2023-10-12 15:30:28');
select hour('2023-10-12 15:30:28');
select minute('2023-10-12 15:30:28');
select second('2023-10-12 15:30:28');

--  to_date将数据转换为日期类型
-- 将时间类型数据转换为日期数据 to_date(current_timestamp())
select to_date(current_timestamp());
-- 2023-10-13
-- 将时间类型字符串转换为日期数据 , 进行转换时,字符串一定要是表准时间格式的.否则无法转换
select to_date('2023-11-25 11:30:22'); -- 2023-11-25
select to_date('2023年11月25日 11时30分22秒');-- NULL
-- 将日期类型字符串转换为日期数据
select to_date('2033-11-22'); -- 2033-11-22
-- 标准格式的时间类型字符串,和时间类型数据时可以等价使用的.
-- 如果时间或日期类型格式不正确则返回NULL

- 5. datediff 获取两个时间的时间差(天数差)
-- 本质上是计算两个日期间的天数差, 前边的时间减去后边的时间
select datediff('2022-11-20', '2022-11-25'); -- -5
-- 可以使用时间类型进行比较,但是最终只使用日期部分数据进行计算
select datediff('2023-01-01 00:00:00','2022-12-31 23:59:59');
-- 可以让日期类型和时间类型进行比较
select datediff('2023-01-01 00:00:00','2022-11-20');

-- 6. 时间偏移
-- date_add
select date_add('2022-11-20', 3);
select date_add('2022-11-20 22:16:22', 3);
-- date_sub
select date_sub('2022-11-20', 3);
select date_sub('2022-11-20 22:16:22', 3);

-- date_add 和date_sub都可以使用负数偏移量
select date_add('2022-11-20', -3);
select date_sub('2022-11-20', -3);
-- 所以两种偏移方式,我们记忆一种即可

-- 9. date_format 将时间类型数据,或标准时间类型字符串,转换为指定格式的时间字符串
-- 将日期数据格式化 date_format(current_date(), 'yyyy年MM月dd日');
select date_format(`current_date`(),'yyyy-MM-dd');
-- 将日期时间数据格式化 date_format(current_timestamp(), 'yyyy年MM月dd日 HH时mm分ss秒');
select date_format(`current_timestamp`(),'yyyy-MM-dd HH:mm:ss');
-- 将时间类型字符串格式化 date_format('2022-11-22 13:22:11', 'yyyy年MM月dd日 HH时mm分ss秒');
select date_format('2022-11-22 13:22:11','yyyy-MM-dd HH:mm:ss');
select date_format('2022-11-22 13:22:11','yyyy年MM月dd日 HH时mm分ss秒');
-- 将日期类型字符串格式化 date_format('2022-11-22', 'yyyy年MM月dd日 HH时mm分ss秒');
select date_format('2022-11-22','yyyy年MM月dd日 HH时mm分ss秒');

4.5 类型转化

-- 在hive中数据类型要求不严格,所以我们进行类型转换的时候比较少
-- 但是后续进入presto学习时,对于类型要求极其严格,所以必须进行数据类型转换
-- 1. 数据类型转换函数 cast(1 as string)
select cast(1 as string);
-- 在我们使用cast函数时,最多的就是转换时间类型
select '2022-11-12 08:23:12';
select cast('2022-11-12 08:23:12' as timestamp);
select cast('2022-11-12 08:23:12' as date);

-- 当数据无法转换为该类型时,会返回null 不会报错
-- select cast('2022-11-12 08:23:12' as int);

-- hive中类型转换应用
select concat_ws('-', `array`('传智', '教育', '股份', '我没有'));

-- Argument 2 of function CONCAT_WS must be "string or array<string>", but "array<int>" was found.
select concat_ws('-', `array`(1, 2));
select concat_ws('-', `array`(cast(1 as string), cast(2 as string)));

-- 字段类型不符合计算需求，可以进行类型转化
-- 隐式转化  hive会自动判断进行转化数据然后计算
select '123'+'456';
-- 手动指定转化
select cast('123' as int) + cast('456' as int);

-- 转化只是在计算时进行，并不会改变字段本身类型
select cast(blood as bigint) from itcast.tb_hero;

4.6 字符串数据转json，array，map操作

• 将字符串数据切割转为数组数据

create table tb_user(
    id int,
    name string,
    hobby string
)row format delimited fields terminated by ',';

select id,name,split(hobby,'-') from tb_user;

• 将字符串数据切割转为map数据

-- 使用map方法
select `map`('name','张三','age',29);

create table tb_hero(
    id int,
    name string,
    blood int,
    skin string
)row format delimited fields terminated by ',';
-- 西部大镖客:288-大圣娶亲:888-全息碎片:0-至尊宝:888-地狱火:1688 --> {'西部大镖客':288,'大圣娶亲':88}
select id,name,blood,map(split(split(skin,'-')[0],":")[0], cast(split(split(skin,'-')[0],":")[1] as int),split(split(skin,'-')[1],":")[0],cast(split(split(skin,'-')[1],":")[1] as int))from tb_hero;

4.7 其他函数

• 哈希函数

-- 1. 获取数据的哈希值 - hive中和java中哈希值求出来不一样
select hash('chuanzhi');  -- 1993223422

• 获取系统参数（了解）

-- 2. 获取一些系统参数
select current_user(), current_groups(), current_database(), logged_in_user(), version();

• 蒙板函数 使用特定字符,将字符进行遮盖, 大写字母用X代替,小写字母用x代替,数字用n代替,其他字符不遮盖

-- 蒙板函数 使用特定字符,将字符进行遮盖, 大写字母用X代替,小写字母用x代替,数字用n代替,其他字符不遮盖
select mask('CHUANzhi123小明%%$$');
-- 自定义遮盖字符
select mask('CHUANzhi123小明%%$$', '大', '小', '数');

select mask_first_n('CHUANzhi123', 3); -- 遮盖前n个字符
select mask_last_n('CHUANzhi123', 3); --遮盖后n个字符
select mask_show_first_n('CHUANzhi123', 3); -- 显示前
-- n个字符
select mask_show_last_n('CHUANzhi123', 3); --显示 后n个字符

-- 蒙板函数用的不多, 因为这个函数主要是用于数据脱敏的.
-- 但是大数据开发一般情况下拿到的就是二手数据, 也就是脱敏后的数据.

5. hive中的查询语句

对表进行查询计算

select 字段  from 表;

select 字段1,字段2,字段3,常量值,内置函数计算 from tb

5.1 单表查询计算

5.1.1 where条件查询

语法：
select 字段1,字段2,字段3,常量值,内置函数计算 from tb where 过滤条件

• 比较大小
  字段 = 数值 判断字段和数值是否相等
  字段 > 数值
  字段 < 数值
  字段 >= 数值
  字段 <= 数值
  字段 != 数值

-- 大小比较
-- 年龄大于19岁
select * from tb_stu where age >19;
-- 查询性别为女性的学生信息
select * from tb_stu where gender='女';
-- 查询学科不是IS的学生信息
select * from tb_stu where cls !='IS';

• 判断空值
  字段 is null 字段为空
  字段 is not null

-- 空值判断
insert into tb_stu values(9023,null,'男',20,'MA');
select * from tb_stu where name is not null;
select * from tb_stu where name is null;

select * from tb_stu where name !=''; -- 空字符过滤是会将null值一起过滤掉
select * from tb_stu where name =''; -- 相等判断是,空字符是不会过滤出null值的

• 范围判断

1. 字段 between 数值1 and 数值2
   字段 >=数值 and 字段 <=数值
2. 字段 in (数值1,数值2…) 字段的值等于任意一个值就返回结果

-- 范围判断
select * from tb_stu where age between 20 and 25;
select * from tb_stu where age in(19,22);
select * from tb_stu where age not in(19,22);

• 模糊查询
  字段 like ‘% _ 数据’ % 可以匹配任意多个 _ 匹配任意一个字符
  字段 rlink ‘正则表达式’

create table tb_stu2(
    id int,
    name string,
    gender string,
    age int,
    cls string,
    email string
)row format delimited fields terminated by ',';

select * from tb_stu2;
-- like的模糊查询
-- 查询姓名为刘的学生
select * from tb_stu where name like '刘%'; -- % 代表任意多个字符
-- 查询姓名为刘的学生 名字个数时2个字的
select * from tb_stu where name like '刘_';
select * from tb_stu where name like '刘__'; -- 查询三个字的

-- rlike 的正则表达式
-- 表的是就是通过不同的符号来表示不同的数据进行匹配
-- \\d 匹配数字的表达式   \\w  匹配字符字母  \\s 匹配空格
select * from tb_stu2;
-- ^ 表是什么开头
select * from tb_stu2 where email rlike '^\\d'; -- 表是以数字开头
select * from tb_stu2 where email rlike '^\\w';
select * from tb_stu2 where email rlike '^\\S';

-- ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$

select email,split(email,'@')[1] from tb_stu2;
select email,split(split(email,'@')[1],'\\.')[0] from tb_stu2;

• 与或非
  条件1 and 条件2 and 条件3 … 多个条件都成立，返回对应的行数据
  条件1 or 条件2 or 条件3 … 多个条件满足任意一个，返回对应的行数据

-- 与 多个条件都成立
select * from  tb_stu;
-- 查询性别为男性，学科是is的
select * from  tb_stu where gender='男' and cls = 'IS';
-- 查询性别为男性或学科是is的
select * from  tb_stu where gender='男' or cls = 'IS';

5.1.2 聚合计算 sum，count

使用聚合函数对字段进行计算，跟MySQL使用方法一样

select * from tb_stu;
select sum(age) from tb_stu2;
select count(*) from tb_stu where name is not null;
select avg(age) from tb_stu2;
select max(age) from tb_stu;
select min(age) from tb_stu;

5.1.3 分组聚合 group by

使用group对字段进行分组，跟MySQL使用方法一样

select sum(age) from tb_stu group by gender;
select sum(age),gender from tb_stu group by gender;

5.1.4 排序

order by 全局排序

select * from tb_stu order by age; -- 默认是升序 从小到大
select * from tb_stu order by age desc ; -- 降序 从大到小

5.1.5 分页limit

-- 分页
select * from tb_stu limit 5;
-- 页数 m  每页数量是n   (m-1)*n,n
select * from tb_stu limit 10,5; 

  当我们踏足信息技术的浩瀚领域，每一次探索、每一次学习都仿佛开启了一扇新的知识之门。在这个充满变革和创新的时代，IT技术博客不仅是我们分享知识的平台，更是我们交流思想、碰撞灵感的场所。💞💞💞
  感谢您与我一同走过了这段充满智慧的旅程。无论您是初入IT领域的新手，还是经验丰富的技术专家，我们都希望通过这些文章能够为您带来启发和帮助。技术的海洋无边无际，而我们的探索之路永无止境。💕💕💕
  在未来的日子里，让我们继续携手前行，共同探索IT技术的无限可能。愿您的技术之路越走越宽广，愿我们的博客成为您前行路上的明灯。再次感谢您的陪伴与支持，期待与您在下一个技术浪潮中再次相遇🤞🤞🤞