hiveSQL语法

数据库操作

创建数据库

create database 库名;

create database if not exitst 库名; -创建之前先判断数据库是否已经存在，如果不存在才创建

create database 库名 location 'hdfs路径';  -创建数据库并指定它在hdfs上的存储位置

使用数据库

use database 库名;

查看数据库详细信息

desc database 库名;

删除数据库

drop database 库名;  -只能删除空数据库，如果数据库里面有数据表，就会报错

drop database 库名 cascade;  -强制删除数据库，包含数据库里面的表一起删除

数据表操作

创建数据库表

create [external] table [if not exitst] 表名(

列名 数据类型 [comment colcommrnt],...)

EXTERNAL，创建外部表 PARTITIONED BY， 分区表 CLUSTERED BY，分桶表 STORED AS，存储格式 LOCATION，存储位置

基础建表语法

尽管建表语法比较复杂，目前我们暂时未接触到分区、分桶等概念。 所以，创建一个简单的数据库表可以有如下SQL：

create table 表名(

列名 数据类型,

列名2 数据类型);

--创建一样简单的表

create table test(
id int,
name string,
gender string
);

数据类型

Hive中支持的数据类型还是比较多的  其中使用比较多的类型有：

简单类型

• 布尔类型：  boolean（只有true和flase两个值）
• 数字类型：  float（单精度小数）int（整数）double（双精度小数）
• 字符串类型： char（固定长度字符串） varchar（变长字符串） string（没有限制的字符串）
• 日期时间类型： date（年月日时分秒毫秒）在工作中不会有人用date类型，hive都是用string存储时间

复杂类型

• 数组类型： array<简单类型> 类似于python里面的列表。
• 如果要存储一个全部都是整数的数组    array<int>
• 映射类型： map<基本类型, 基本类型> 是一个键值对格式，类似与python里面字典
• 要存储的是科目的分数，例如数学多少分 语文多少分   map<string, int>

删除表

drop table 表名;

#删除刚刚创建的test表
drop table test;

表分类

hive中可以创建的表的类型有：

• 内部表
• 外部表
• 临时表
• 分区表
• 分桶表

内部表和外部表

create table 创建的表格都是内部表，通过 create external table 创建的表格，就是外部表。

内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定（默认：/user/hive/warehouse），用drop table语句删除内部表会直接删除元数据（metadata）及存储数据，因此内部表不适合和其他工具共享数据。

外部表是指表数据可以在任何位置，通过LOCATION关键字指定。 数据存储的不同也代表了这个表在理念是并不是Hive内部管理的，而是可以随意临时链接到外部数据上的。 所以，在删除外部表的时候， 仅仅是删除元数据（表的信息），不会删除数据本身。

内部表和外部表的区别

• 内部表可以用truncate table清空，也可以用drop table删除
• 外部表是不能通过truncate table执行清空操作的；
• 外部表通过drop table删除，只会删除mysql中表格的元数据，但是无法删除hdfs里面表格的文件夹以及数据文件的；

因此在数据仓库中，大数据的表格或者业务数据的表格，一般都会使用外部表进行创建和存储。

创建内部表

创建一个t1表

create table t1(
id int,
name string,
mobile char(11),
hobby array<string>,
score map<string,double>
);

往表格中插入数据：

1.只插入简单类型的字段数据

inset into 表(列名) values(值);

insert into t1(id,name,mobile) values(101,'curry','18878984708');
insert into t1(id,name,mobile) values(102,'kobe','12343547896');

hive数据库所有的sql操作都是高延迟的，如果操作的数据量小于128M，那么我们可以去启动本地设置的开关，加快数据的操作

本地模式开关：set hive.exec.mode.local.auto=true;

2.插入的数据包括了复杂类型

insert into 表(列名) select 查询;

insert into t1(id,name,mobile,hobby,score)
select 103,'james','15567589087',array('sing','rap'),
map('math',cast(98.5 as double),'chinese',cast(99 as double),'english',cast(92.5 as double));

hive数据库中，每一次的 insert 操作，在表格的文件夹里面，都会生成一个新的当前的数据文件。

表格的分隔符

• 表格字段之间的默认分隔符是 ^A，通过键盘的 ctrl + V + A 输出的，在数据库里面的编码格式是 \001
• 数组和映射类型，元素之间的分隔符是 ^B，通过键盘的 ctrl + V + B 输出的，在数据库里面的编码格式是 \002
• 映射类型里面，关键字和值之间，分隔符是 ^C，通过键盘的 ctrl + V + C 输出的，在数据库里面的编码格式是 \003

在创建表格的同时，强调分隔符的类型：

create table t2(
id int,
name string,
mobile char(11),
hobby array<string>,
score map<string,int>
) row format delimited fields terminated by  ',' 	--字段分隔符
collection items terminated by  '/' 	--元素分隔符
map keys terminated by  '-'; 	--键值对分隔符

插入数据

insert into t2 
select 101,'aa','18899997777',array('唱歌','跳舞'),map('语文',88,'数学',91);

将数据文件导入到hive表格中

在hive中比较少用 insert into 添加数据，我们一般都是用文件的方式来导入数据。

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

1.直接通过hadoop fs -put命令，从linux上传数据文件到hdfs对应的表格文件夹中：

hadoop fs -put /usr/t3.txt /user/hive/warehouse/myhive.db/t3

2.通过sql语句，进行数据的导入，在hive数据库里面运行的：

load data local inpath 'linux文件的位置和名字' into table 表名;

load data local inpath '/usr/t3.txt' into table t3;

3.将数据文件统一的上传到hdfs的其他的文件夹中，例如 /datas，然后再从hdfs的 /datas 里面加载到表格中

load data inpath 'hdfs文件的位置和名字' into table 表名;

hadoop fs -put /usr/t3.txt /datas/ --先把文件上传到hdfs

load data inpath '/datas/t3.txt' into table t3;

注：t3.txt文件的分隔符和t3表格的分隔符一定要一致，如果不一致，读取到的数据都是NULL

创建外部表

create external table t4_ex(
id int,
name string,
age int
);
insert into t4_ex values(101,'aa',18);

内部表和外部表的转换

将内部表转换成外部表：

alter table 内表 set tblproperties ('EXTERNAL'='TRUE');

alter table t3 set tblproperties('EXTERNAL'='TRUE');

将外部表转换成内部表：

alter table 外表 set tblproperties ('EXTERNAL'='FALSE');

alter table t3 set tblproperties ('EXTERNAL'='FALSE');

创建表格的时候，可以指定表格的文件夹在哪里

内部表和外部表都可以指定表格的文件夹的位置：location

create table t5(
id int,
name string,
age int
)location '/user/hive/warehouse/bigdata.db/t4_ex';

也可以用location让多个表格来共享同一个文件夹的数据：

create table t6(
id int,
name string,
age int
)location '/user/hive/warehouse/bigdata.db/t4_ex';

这种建表的方式是不会在hdfs上生成自己的表格文件夹的。

创建临时表

临时表的关键字：temporary table

create temporary table t6_tmp(
id int,
name string,
age int
);

• 临时表在当前的数据库中，不会在hdfs中创建表格的文件夹。
• 插入到临时表的数据可以被正常的使用。
• 如果退出登录重新进入hive数据库，那么临时表被删除掉了。
• 临时表的数据被存放在内存中的，如果退出内存就被清空了，临时表也被删除了。

创建分区表

分区表的目的：是为了加快表格查询和筛选数据的效率，是在where的阶段起到加速作用的

创建一个分区表：

create table 表名(

列名 数据类型

) partitioned by (表格中没有的新字段 数据类型);

create table stu_part(
stuid int,
stuname string,
age int
) partitioned by (class string)
row format delimited fields terminated by ',';

父子分区的结构：在hive里面语法没有限制分区必须是几层的结构，但项目中一般最多就是两层。

create table 表名(

列名 数据类型

) partitioned by (表格中没有的新字段1 数据类型, 新字段2 数据类型);

将不同的数据文件，导入到表格不同的分区中：

load data local inpath 'linux文件的位置和名字' into table 表名 partition (分区字段=分区值);

load data local inpath '/root/s2.dat' into table stu_part partition (class='002');

查看表格有哪些分区：show partitions

show partitions stu_part;

删除分区：drop partition

alter table stu_part drop partition (class='002');

直接创建一个空分区：add partition

alter table stu_part add partition (class='003');

a. 静态分区的导入方式

通过上面的load data的方法进行文件数据的导入，需要自己指定一个分区来导入文件的内容为静态分区导入。

现在有一个现成的表格，要将表格的内容导入到分区中

-- 创建一个普通的表格emp
create table emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal float,
comm float,
deptno int
) row format delimited fields terminated by ',';

-- 导入表格数据
load data local inpath '/root/emp.txt' into table emp;

接着创建一个emp的分区表，按照部门去创建分区：

-- 创建一个部门分区的emp表
create table emp_dept_part(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal float,
comm float
) partitioned by (deptno int)
row format delimited fields terminated by ',';

最后读取emp表的数据，按照部门，导入到emp_dept_part中不同的分区中：

• insert overwrite table 表名;          覆盖写入数据
• insert into table 表名;                   追加写入数据

insert overwrite table emp_dept_part partition (deptno=10)
select empno,ename,job,mgr,hiredate,sal,comm
from emp
where deptno=10;

insert overwrite table emp_dept_part partition (deptno=20)
select empno,ename,job,mgr,hiredate,sal,comm
from emp
where deptno=20;

b. 动态分区的导入方式

只能通过 insert overwrite / into table 来导入数据。不能通过load data导入数据

动态导入是根据select里面字段的内容，来自动的判断数据应该放在哪个分区中。

--写入数据之前，动态分区还需要两个开关
-- 动态分区的开关
set hive.exec.dynamic.partition=true;
-- 打开动态分区中的非严格模式
set hive.exec.dynamic.partition.mode=nonstrict;
-- 导入数据
insert overwrite table emp_dept_part partition (deptno)
select empno,ename,job,mgr,hiredate,sal,comm, 
deptno
from emp;

再举一个例子：

-- 创建一个emp的表格，用工资等级来分区
create table emp_sal_part(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal float,
comm float,
deptno int
) partitioned by (grade string)
row format delimited fields terminated by ',';

-- 导入emp的数据
insert overwrite table emp_sal_part partition (grade)
select emp.*, 
case when sal<2000 then 'C'
when sal<3000 then 'B'
else 'A'
end grade
from emp;

动态分区和静态分区的区别

1. 动态分区只有insert导入数据的方式，静态可以通过insert或者load data来导入数据
2. 动态分区在导入的时候不需要指定分区的值，通过select语句的字段来自动分区；静态分区需要自己指定分区值来导入数据
3. 动态分区在导入的时候需要打开动态开关以及非严格模式的开关
4. 在分区确定和数据量一样的情况下，静态分区导入的速度比动态更快一点，但是动态导入更加方便

创建分桶表

分区是当表格很大的时候，为了加快where的效率，并且进行更加精细化的数据的管理，例如每天分区，或者每个地区分区，每个类别分区等。

分桶表是在数据分组统计的时候（group by），或者是在进行表格联合查询的时候，进行查询加速使用的表格结构。

分桶的操作是根据哈希算法，将分桶字段转换成数字除以分桶的数量根据余数来进行分桶划分的。

create table 表名(

列名 数据类型

)clustered by (表格已经存在的字段) into 分桶数量 buckets;

--创建一个分桶表
create table emp_clu(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal float,
comm float,
deptno int
) clustered by (empno) into 4 buckets
row format delimited fields terminated by ',';

• 通过数据文件来导入数据到分桶表格中（分桶表的数据导入不能使用本地模式的）

load data local inpath '/root/emp.txt' into table emp_clu;

• 通过已有的表格进行分桶表数据的导入

insert overwrite table emp_clu select * from emp;

分区表和分桶表区别

1. 分区是用的新字段，分桶是已有字段
2. 分区是where加速，分桶是group by 和Join加速
3. 分区是划分不同的文件夹存储的，分桶是划分不同的文件来存储的
4. 分区是自己指定的规则，分桶是使用哈希算法

为什么分桶可以加快group by的聚合计算的速度：

假如有一个表格，只有一个文件，文件是128M，计算的时候将这个文件当成一个整体，使用一个map和一个reduce进行数据统计的；

分桶就是将这个128M的文件拆分成多个文件来存储，例如拆分成四个桶文件，那么计算的时候就会有4个reduce参与数据的计算和整合，那么数据的计算速度就会变快。

为什么分桶可以加快join的表连接的速度：

用于加速join的分桶，两个表格的分桶的数量，必须是相同的数量或者是成倍数关系的数量。

通过哈希的余数关系，减少join中笛卡尔积数量的结果。

一个表格的分桶数量是怎么决定的：

表格的总数据量大小/(blocksize*2)

假如有个表总数量是4个GB，那么 4096/256=16 桶

创建外部的分区表

create external table user_ext_part(
id int,
name string,
age int
) partitioned by (y int)
row format delimited fields terminated by ','
location '/datas';

datas文件夹的结构如下：

直接创建表格，指定了对应的目录，也读取不到数据，因为表格是个分区表，只有表格的结构，没有分区的结构。所以我们需要去新建分区，让分区的目录和分区的值一一对应。

alter table user_ext_part add partition (y=2020) location '/datas/20240701';
alter table user_ext_part add partition (y=2021) location '/datas/20240702';
alter table user_ext_part add partition (y=2022) location '/datas/20240703';

表格的存储方式以及对应的压缩格式

查看表格建表的基本情况：show create table 表名;

show create table t3;

• textfile存储类型

hive表格默认存储格式是textfile，存储的内容和格式其实就是一个有行和列构成的文本文档，textfile以行为单位存储的表格（行存表）

textfile比较适合中小型表格（几百万以上的数据，就是大型的表格）。

textfile就是可以直接通过load data的方式导入数据。

create table 表名(
  列名 数据类型
);

-- 也可以强调表格的存储类型
create table 表名(
  列名 数据类型
)stored as textfile;

对textfile表格的格式进行数据的压缩操作：

set hive.exec.compress.output=true;

set mapred.output.compress=true;

默认的压缩格式：deflate

除了默认的压缩格式，也可以支持 gzip 或者 bzip2 两种压缩

• parquet存储格式

parquet也叫做拼接文件。

在重复数据特别多的表格中，parquet可以节省很多的存储空间。

不能使用load data直接导入文本的数据。

create table emp_parquet(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal float,
comm float,
deptno int
)stored as parquet;

insert overwrite table emp_parquet select * from emp;

parquet格式的压缩类型叫做SNAPPY：

create table emp_parquet_ys(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal float,
comm float,
deptno int
) stored as parquet
tblproperties ('parquet.compression'='SNAPPY');

insert overwrite table emp_parquet_ys select * from emp;

• sequencefile存储格式

sequencefile叫做序列文件格式，占用的空间比textfile要稍大，查询速度比textfile要稍快。数据在数据库的内部是使用键值对的方式来存储的。

sequencefile不能直接通过load data导入数据。

create table emp_seq(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal float,
comm float,
deptno int
) stored as sequencefile;

insert overwrite table emp_seq select * from emp;

sequencefile的压缩格式叫做BLOCK。默认的存储叫做RECORD。

set mapred.output.compression.type=BLOCK;

create table emp_seq_ys(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal float,
comm float,
deptno int
) stored as sequencefile;

insert overwrite table emp_seq_ys
select * from emp;

• orc存储格式

中小型表格使用textfile，大型的表格就使用orc。

orc是一种数据列存的格式，查询的速度是最快的，表格自带压缩功能的，压缩格式叫做zlib，每一行数据单独压缩

create table emp_orc(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal float,
comm float,
deptno int
) stored as orc;

insert overwrite table emp_orc
select * from emp;

表格默认是不支持数据的更新update和删除delete的。

1.一个普通的表格，现在需要修改和删除数据：用insert overwrite table来覆盖

-- 修改SMITH工资为1000
insert overwrite table emp
select empno,ename,job,mgr,hiredate,1000,comm,deptno from emp where ename='SMITH'
union all
select * from emp where ename!='SMITH';

-- 删除SMITH的数据
insert overwrite table emp
select * from emp where ename!='SMITH';

2.如果一个表格一定有修改或者删除的场景，那么就创建成orc类型的表格

-- 先打开数据库支持事务的开关
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.support.concurrency=true;

create table emp_orc_2(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal float,
comm float,
deptno int
) stored as orc
tblproperties('transactional'='true');

insert overwrite table emp_orc_2
select * from emp;

update emp_orc_2 set sal=5000 where ename='ALLEN';
delete from emp_orc_2 where ename='WARD';

DDL语句

1.创建表格

create [external] [terporary] table 表名(
列名 数据类型
) partition by (分区字段 数据类型)
clustered by (分桶字段) into 分桶数量 buckets by '字段'
row format delimited fields terminated by '字段分隔符'
collection items terminated by '数组元素分隔符'
map keys terminated by '字典键值对分隔符'
location '创建文件夹的位置'
stored as 存储类型
tblproperties ('属性名'='值');

2.复制表格

--将查询的结果复制成一个表格
create table 新表 as select 查询;

--直接复制一个表格的结构为一个新的表（只有表结构没有数据）
create table 新表 like 要复制的表;

3.修改表格的结构

--修改字段的结构
1.新增字段
alter table 表名 add columns(列名 数据类型)；
给t10表新增gender字段和addr字段
alter table t10 add columns(gender char(3), addr string);

2.修改字段的名字和数据类型
alter table 表名 change 字段 新字段 新数据类型;
alter table t10 change mobile mobile char(11);
alter table t10 change id userid string;

3.不能删除表格的字段

4.重命名表格
alter table 表名 rename to  新表名;
alter table t10 rename to test10;

--修改表格的分区信息
1.新增分区
alter table 分区表 add partition(分区字段=分区值) [location 'hdfs路径'];
2.删除分区
alter table 分区表 drop partition(分区字段=分区值);
3.清空分区
alter table 分区表 truncate partition(分区字段=分区值);

4.清空整个表格

truncate table 表名;

5.删除表格

drop table 表名;

DML

新增、修改和删除数据。

其中update和delete是不能直接使用的，只能在打开了事务属性的orc表格中可以用。

1.在简单类型中插入一行数据

• insert into 表名(列名) values(值);

2.在复杂类型中插入一行数据

• insert into 表名(列名) select array(),map() ...;

3.将一个表格的数据覆盖写入到另一个表格中

• insert overwrite table 表名 [partition (分区字段=分区值)] select 查询;

4.将一个表格的数据追加写入到另一个表格中

• insert into table 表名 [partition (分区字段=分区值)] select 查询;

5.查询一次表格，对数据进行拆分分别写入到N个不同的表格中，进行分表的操作

create table emp_a like emp;
create table emp_b like emp;
create table emp_c like emp;

from emp
insert overwrite table emp_a select * where empno%3=0
insert overwrite table emp_b select * where empno%3=1
insert overwrite table emp_c select * where empno%3=2;

DQL

select 字段         4

from 表名           1

where 筛选         2

group by 分组     3

having 聚合筛选 5

order by 排序 6

limit 分页; 7

select deptno,sum(sal) s from emp group by deptno having s>2000;

• limit 分页查询

limit  开始行号，连续的取值范围

limit 4,5   从第5行开始，连续取5行数据（开始行号是从0开始的）

limit 3  相当于 limit 0,3   开始值是0，我们可以省略

--查询前三行数据
select * from emp limit 3;

--查询第3行到第5行
select * form emp limit 2,3;

• 排序的部分

hive中排序除了order by之外，还有 sort by，还有 cluster by，还有 distribute by。

--order by排序

不管表格有多大的数据量，都是将表格当成一个整体，放在一个reduce进程中进行排序的。order by排序很慢。

select * from 表 order by 列名 desc|asc;

--sort by排序

是在reduce中进行排序的，有几个reduce，就会在几个不同的范围内分别排序。当reduce等于1的时候，效果和order by 是一样的。

如何设置reduce的数量：

set mapred.reduce.tasks;          这个开关的默认值是-1，表示没有强制限制，reduce的数量是跟着分桶或者文件的多少来决定的。

select * from 表 sort by 列 desc|asc;

--distribute by排序

distribute by 其实就是在查询的过程中，对表格进行分桶的含义。

distribute by 是不能单独运行的，只能和 sort by 结合使用。

select * from 表 distribute by 分桶字段 sort by 排序字段 desc|asc;

-- cluster by排序

其实就是

select * from 表 distribute by 分桶字段 sort by 按照分桶字段进行排序 asc;

select * from 表 cluster by 列;

select * from emp distribute by sal sort by sal;
select * from emp cluster by sal;
两句话是一样的效果

多表查询

1.子查询嵌套

select * from (select deptno,sum(sal) s from emp group by deptno) t;

在hive中，嵌套的子查询，必须要有个别名。

2.多表联合查询

• cross join  笛卡尔积：显示两个表所有可能的连接情况
• inner join 内连接：查询出两个表格之间共有的数据部分
• left join  外连接之左连接：查询出两个表格的共有数据，然后显示出左边表格独有的数据。
• right join  外连接之右连接：查询出两个表格的共有数据，然后显示出右边表格独有的数据。
• full join 外连接之全连接：查询出两个表格的共有数据，然后分别显示左表和右表独有的数据。

在Hive中，多了一个左半连接的操作：在hive2.x的版本中，不能使用子查询嵌套的语法，用left semi join对嵌套进行语法的替换操作。

select * from a left semi join b on a.xx=b.xx;   这里的*号，代表的不是a和b表的所有字段，只代表a表的所有的字段。

-- 查询工资比MILLER要高的员工信息
select * from emp where sal>(select sal from emp where ename='MILLER');

select * from emp a left semi join emp b on a.ename!='MILLER' and b.ename='MILLER'
and a.sal>b.sal;

从查询的效率上讲，左半连接比嵌套查询更好一点。

函数

聚合函数

• max()  min()  sum()  avg()  count()

单行函数

1. 数字类型

abs() 求绝对值

round() 四舍五入保留小数精度；如果没有小数精度，只是保留整数的部分

• round(1.234545,3)  结果为1.235
• round(1.2345)  结果为1

floor()  查询离数字最近的小于它的整数，向下取整

• floor(1.999999)  结果为1

ceil()  查询离数字最近的大于它的整数，向上取整

• ceil(1.9999)  结果为2

power()  数字的幂运算

• power(3,4)  3的4次方，结果为81

随机函数：rand() 取随机的一个数字，范围是0-1

取余数，模运算：%

• select 10%3;  结果为1

中位数的计算：先对数组进行排序，取中间的值  percentile(列名, 0.5)

create table score(
stuid int,
score int
)row format delimited fields terminated by ',';

1,90
2,88
3,79
4,100
5,92

select percentile(score,0.5) from score;

2.字符串类型

substr()  连续截取字符串

• substr(字符串，开始位置，连续长度) 开始位置是负数，表示从后往前数
• substr(字符串，开始位置) 没有连续长度会取值到字符串的最后一位

length()  查看字符串的长度

replace()  字符串的替换

• replace(字符串, '旧数据', '新数据') 用新字符替换旧字符

regexp_replace()

translate()  用一串新的数据依次替换前面的一串旧数据

instr()

upper()

lower()

initcap()

lpad()/rpad()

trim()/ltrim()/rtrim()

concat()  拼接字符串。oracle里面只能拼接两个字段，hive中拼接的字段没有数量的限制，hive没有||的拼接符号了

select concat(empno,ename,job,mgr) from emp;

concat_ws()  拼接的字段只能是字符串类型

• concat_ws(连接符号, 拼接的字段)

select concat_ws(',',ename,job,hiredate) from emp;
select concat_ws(',',ename,job,hiredate,cast(sal as string)) from emp;

• concat_ws()不仅可以拼接多个字段，也可以拼接数组的类型

select concat_ws('-',array('aa','bb','cc'));

split(字符串, '切割符号')  拆分字符串,将一个字符串切割成一个数组

select split('aaa-bbb-ccc-ddd','-');

collect_list()  将一个列的数据，合并成一个数组字段

select collect_list(job) from emp; 

collect_set()  将一个列的数据，合并成一个数组字段并对数据进行去重的处理

select collect_set(job) from emp;

识别json数据的格式，并且对关键字的值进行提取：get_json_object(json字符串, 提取的路径描述)

get_json_object() 函数不管查询出来的数据样式是什么，类型都是字符串。

create table test11(
userid int,
userinfo string
);

1^A{"username":"aa","age":18,"gender":"male"}
2^A{"username":"bb","age":17,"gender":"female"}
3^A{"username":"cc","age":19,"gender":"male"}
4^A{"username":"dd","age":17,"gender":"female"}

select userid,
get_json_object(userinfo, '$.username')
from test11;

create table test12(
id int,
fruits string
)row format delimited fields terminated by '/';

1/{"dt":"20200101","datas":[{"name":"apple","price":1.8},{"name":"pear","price":2.1}]}
2/{"dt":"20200102","datas":[{"name":"apple","price":1.9},{"name":"pear","price":2.0}]}

查询每一天的所有的水果的price信息
select get_json_object(fruits, '$.datas[*].price') from test12;

3.时间日期类型

add_months()

months_between()

last_day()

next_day()

查询当前的年月日信息： current_date

select current_date;

查看当前的年月日时分秒毫秒信息： current_timestamp

select current_timestamp;

查看当前的时间戳数据，累计的秒数时间： unix_timestamp()

select unix_timestamp();

转换时间戳提取出时间的维度： from_unixtime()

select from_unixtime(1738574831,'yyyy-MM-dd HH:mm:ss');

进行日期天数的偏移： date_add()

select date_add('2024-8-2',5);
select date_add('2024-8-2',-5);

查询两个日期之间的天数间隔： datediff()

select datediff('2024-8-2','2024-7-28');

提取日期格式里面的时间维度：year() month() day() hour() minute() second()

select minute(current_timestamp);
select year(current_timestamp);

练习：使用时间函数，查询距离 2024-8-2 最近的上一个星期二的日期。

select date_add(next_day('2024-8-2','tue'),-7);

4.数据类型转换

cast(值 as 新的类型)

select cast(1.666 as string);
select cast(1.666 as int);
select cast('2024-8-1' as date);

5.数组类型

将多个数组合并转换成一个数组： array(值1，值2，值3..)

案例