来源：大数据Hive 3.x教程
一、入门
Hive是基于Hadoop的一个数据仓库工具，提供类SQL查询功能，可将结构化数据文件映射为表。
本质：将HQL转化为MapReduce程序。
** 架构**：
二、数据定义（DDL）
2.1数据库
（1）创建数据库
语法：
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment（注释）]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...（kv键值对）)];
注：方括号内为修改内容。
eg：1.创建数据库，但不指定路径：
create database db_hive1;
默认路径：${hive.metastore.warehouse.dir}/database_name.db
2.创建数据库并指定路径：
create database db_hive2 location '/db_hive2';
3.创建带有dbproperties的数据库：
create database db_hive3 with dbproperties('create_date'='****-**-**');
（2）查询数据库
1.展示所有数据库
语法：SHOW DATABASES [LIKE 'identifier_with_wildcards'];
注：LIKE通配符（模糊查询）：*表示任意个任意字符，|表示或的关系 。
eg：show databases like 'db*';
只能查询到db为开头的数据库。
2.查看数据库信息
语法：DESCRIBE DATABASE [EXTENDED] db_name;
注：EXTENDED-是否展示详细信息。
（3）修改数据库
语法：
--修改dbproperties
ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);
--修改location（路径）
ALTER DATABASE database_name SET LOCATION hdfs_path;
--修改owner user（权限）
ALTER DATABASE database_name SET OWNER USER user_name;
（4）删除数据库
语法：DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];
RESTRICT（严格模式）若数据库不为空，则删除失败，默认为该模式CASCADE（级联模式）若数据库不为空，则会将库中的表一并删除
（5）切换数据库
语法：USE database_name;
2.2表
2.2.1普通建表
（1）创建表
语法：
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --声明表名
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
语法关键字说明
TEMPORARY（临时表）该表只在当前会话可见，会话结束，表则会被删除EXTERNAL（外部表）Hive只接管元数据，而不完全接管HDFS中的数据内部表（管理表）Hive完全接管该表，包括元数据和HDFS中的数据PARTITIONED BY创建分区表：依据hive表数据分到不同路径里面CLUSTERED BY ... SORTED BY...INTO ... BUCKETS创建分桶表ROW FORMAT（重点）使用SERDE序列化和反序列化每行数据STORED AS（重点）指定文件格式
ROW FORAMT语法一：
ROW FORAMT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char]
[NULL DEFINED AS char]
注：
- fields terminated by ：列分隔符
- **collection items terminated by **： map（每个键值对之间分隔符）、struct（字段之间分隔符）和array（数组之间分隔符）中（复杂数据类型）每个元素之间的分隔符
- **map keys terminated by **：map中的key与value的分隔符
- **lines terminated by **：行分隔符
语法二：SERDE关键字可用于指定其他内置SERDE或者用户自定义SERDE
ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]
1.数据类型
基本数据类型
类型
说明
定义
tinyint
1byte有符号整数
smallint
2byte有符号整数
int
4byte有符号整数
bigint
8byte有符号整数
boolean
布尔类型，true或者false
float
单精度浮点数
double
双精度浮点数
decimal
十进制精准数字类型
eg：decimal(16,2)（16代表整数位+小数位的个数，2代表小数位个数）
varchar
字符序列，需指定最大长度，最大长度的范围是[1,65535]
varchar(32)
string
字符串，无需指定最大长度
timestamp
时间类型
binary
二进制数据
复杂数据类型
类型说明定义****取值
array
数组是一组相同类型的值的集合array<string>arr[0]（取出数组第0个元素）mapmap是一组相同类型的键-值对集合map<string, int>map['key']struct结构体由多个属性组成，每个属性都有自己的属性名和数据类型struct<id:int, name:string>struct.id
2.类型转换
类型转换
隐式转换
不同类型会向两者之间的类型进行转换
官方说明：Allowed Implicit Conversions
显示转换
可用cast函数完成显示类型转换
显示转换
语法：cast(expr as <type>)
2.2.2 CTAS（Create Table As Select）建表
利用select查询语句返回的结果直接建表等操作。
语法：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name [AS select_statement]
2.2.3 Create Table Like语法
允许复刻一张已经存在的表结构。
（2）查看表
1.展示所有表
语法：SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards'];
2.查看表信息
语法：DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name
注：EXTENDED-展示详细信息；
FORMATTED：对详细信息进行格式化的展示。
（3）修改表
1.重命名表
语法：ALTER TABLE table_name RENAME TO new_table_name
2.修改列信息
增加列：增加新列，新列位置位于末尾
语法：ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
更新列：修改列名、数据类型、注释信息以及表位置
语法：ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
替换列：新列替换原有列
语法：ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
（4）删除表
语法：DROP TABLE [IF EXISTS] table_name;
（5）清空表
语法：TRUNCATE [TABLE] table_name
注：truncate负责内部表，不删除外部表数据
三、数据操作（DML）
（1）Load
if为本地导入，类似于put。
语法：LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)];
关键字说明
LOCAL本地加载数据/HDFS加载到HIVE
OVERWRITE
覆盖表数据PARTITION上传至目标分区
（2）INSERT
1.查询结果插入表
语法：INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement;
关键字说明
INTO追加结果至目标表OVERWRITE结果覆盖原数据
2.给定值插入表
语法：INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]
3.查询结果写入目标路径
语法：INSERT OVERWRITE [LOCAL] DIRECTORY directory [ROW FORMAT row_format] [STORED AS file_format] select_statement;
（3）导入导出
EXPORT将表数据导出至HDFS路径IMPORT将内容导入Hive
语法：
--导出
EXPORT TABLE tablename TO 'export_target_path'
--导入
IMPORT [EXTERNAL] TABLE new_or_original_tablename FROM 'source_path' [LOCATION 'import_target_path']
四、查询
（1）语法
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference -- 从什么表查
[WHERE where_condition] -- 过滤
[GROUP BY col_list] -- 分组查询
[HAVING col_list] -- 分组后过滤
[ORDER BY col_list] -- 排序
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
[LIMIT number] -- 限制输出的行数
（2）基本查询（Select...From）
1.全表查询
select * from tablename;
2.选择特定列查询
select key1, key2 from tablename;
注意：1.SQL大小写不敏感；2.SQL可写一/多行；3.关键字不能被缩写也不能分行；4.各子句一般要分行写；5.缩进提高可读性
3.列别名
eg:select key1 as key2 from tablename;
4.Limit：限制返回行数
select * from tablename limit 2,3; -- 表示从第2行开始，向下抓取3行
5.Where：将不满足条件的行过滤；where跟随from
eg：select * from tablename where key1 > 10;
6.关系运算函数
基本语法
操作符支持数据类型描述in（num1，num2）ALLin运算显示列表中的值A [not] like Bstring
通配符模式。如果A与B匹配，返回true；不匹配返回false。B的表达式说明：‘x%’--A必须以‘x’开头，‘%x’--A必须以‘x’结尾，‘%x%’--表示A包含‘x’。
A rlike B,A regexp BstringB为正则表达式。IF A匹配，则返回true；否则返回false。匹配使用JDK正则表达式借口实现。A [not] between B and CALL
IF A>=B and <=C，则为true，反之为false。
7.聚合函数
聚合函数
count(*)统计所有行数，包括nullcount(某列）该列一共多少行，不包含nullmax()求最大值，不包含null，除非所有值为null
min()
求最小值，不包含null，除非所有值为null
sum()
求和，不包含nullavg()求平均值，不包含null
（3）分组
1.Group By
eg：计算emp表每个部门的平均工资
select
t.deptno,
avg(t.sal) avg_sal
from emp t
group by t.deptno;
2.Having
where不能写分组聚合函数having可使用分组聚合函数，只用于group by分组统计语句
eg：求每个部门的平均薪水大于1000的部门
select
deptno,
avg(sal) avg_sal
from emp
group by deptno
having avg_sal > 1000;
（4）Join语句
1.等值Join
Hive支持的sql join语句，but只支持等值连接，不支持非等值连接
连接
内连接（join）进行连接的两表都存在与连接条件相匹配的数据才会被保留下来
左外连接
（left join）
join操作符左边表中符合where子句的所有记录将会被返回
右外连接
（right join）
join操作符右边表中符合where子句的所有记录将会被返回
满外连接
（full join）
返回所有表符合where的记录。IF字段没有符合条件的值，null代替多表连接连接n个表，至少需要n-1个连接条件笛卡尔集所有表中的所有行互相连接
union &
union all
join是左右关联，union和union all是上下拼接。
union去重，union all不去重。
拼接要求：1.两列个数相同；2.列类型一致
（5）排序
1.全局排序（Order By）：只有一个Reduce
Order By子句排序
asc（ascend）升序（默认）desc（descend）降序
Order By子句在select语句结尾。
eg：查询员工信息按工资升序排列
select * from emp
order by sal;
2.Reduce内部排序（Sort By）
每个Reduce内部进行排序，为每个reduce产生一个排序文件。
设置reduce个数
set mapreduce.job.reduces=3;
查看reduce个数
set mapreduce.job.reduces;
eg：根据部门编号降序查看员工信息
select * from emp
sort by deptno desc;
查询结果导入文件：按照部门编号降序排序
insert overwrite local directory '/opt/module/hive/datas/sortby-result'
select * from emp sort by deptno desc;
3.分区（Distribute By）
注：distribute by语句要写在sort by语句之前；mapreduce.job.reduces的值设置返回-1。
4.分区排序（Cluster By）
具有distribute by功能外还兼具sort by功能。但排序只能是升序排序，不能指定排序规则为asc或者desc。
--以下两种写法等价：
select * from tablename
cluster by deptno;
select * from tablename
distribute by deptno
sort by deptno;
五、函数
（1）简介
--查看系统内置函数
show functions;
--查看内置函数用法
desc function upper;
--查看内置函数详细信息
desc function extended upper;
（2）单行函数
1.算术运算函数
运算符描述A&BA和B按位取与A|BA和B按位取或A^BA和B按位取异或~AA按位取反
2.数值函数
round：四舍五入
select round(3.3);
--结果：3
ceil：向上取整
select ceil(3.1);
--结果：4
floor：向下取整
select floor(4.8);
--结果：4
3.字符串函数
substring：截取字符串
语法一语法二语法substring(stringA,int start)substring(stringA,int start,int len)返回值stringstring说明返回字符串A从start位置到结尾的字符串返回字符串A从start位置开始，长度为len的字符串
--eg：从第3个字符开始，向后获取2个字符
select substring("tablename",3,2);
--结果：bl
replace：替换
语法replace(string A, string B, string C)返回值string说明将字符串A中子字符串B替换C
select replace('table', 'a', 'A')
--结果：tAble
regexp_replace：正则替换
语法regexp_replace(stringA,stringB,stringC)返回值string说明将字符串A中的符合java正则表达式B的部分替换为C
select regexp_replace('100-200', '(\\d+)', 'num')
--结果：num-num
regexp：正则匹配
repeat：重复字符串
split：字符串切割
nvl：替换null值
concat：拼接字符串
concat_ws：以指定分隔符拼接字符串或者字符串数组
get_json_object：解析json字符串
4.日期函数
函数语法返回值说明unix_timestampunix_timestamp()bigint返回当前或指定时间的时间戳from_unixtimefrom_unixtime(bigint unixtime[, string format])string转化UNIX时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式current_date//当前时期current_timestamp//当前的日期加时间，并且精确的毫秒monthmonth (string date)int获取日期中的月dayday(string date)int获取日期中的日hourhour(string date)int获取日期中的时datediffdatediff(string enddate, string startdate)int两个日期相差的天数（结束日期减去开始日期的天数）date_adddate_add(string startdate, int days)string日期加天数date_subdate_sub (string startdate, int days)string日期减天数date_format//将标准日期解析成指定格式字符串
5.流程控制函数
case when：条件判断函数
语法一语法二函数case when a then b [when c then d]* [else e] endcase a when b then c [when d then e]* [else f] end返回值TRUETRUE说明如果a为true，则返回b；如果c为true，则返回d；否则返回e如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
IF
语法if（boolean testCondition, T valueTrue, T valueFalseOrNull）返回值TRUE说明条件判断（类似Java三元运算符）。当条件testCondition为true时，返回valueTrue；否则返回valueFalseOrNull
--eg：条件满足，输出正确
select if(10 > 5,'正确','错误');
--结果：TURE
6.集合函数
函数语法说明sizeeg：select size(friends) from test;集合中元素的个数mapmap (key1, value1, key2, value2, …)创建map集合，根据输入的key和value对构建map类型map_keys
eg:select map_keys(map('key1',1,'key2',2));
结果：["key1","key2"]
返回map中的keymap_values
eg:select map_values(map('key1',1,'key2',2));
结果：[1,2]
返回map中的valuearrayarray(val1, val2, …)根据输入的参数构建数组array类array_contains
eg：select array_contains(array('a','b','c','d'),'a');
结果：true
判断array中是否包含某个元素sort_array
eg：select sort_array(array('a','d','c'));
结果：["a","c","d"]
将array中的元素排序structstruct(val1, val2, val3, …)根据输入的参数构建结构体struct类named_struct
select named_struct('name','iop','age',15);
结果：{"name":"iop","age":15}
声明struct的属性和值
（3）聚合函数
1.collect_list 收集并形成list集合，结果不去重
2.collect_set 收集并形成set集合，结果去重
（4）炸裂函数
UDTF：接收一行数据，输出一行or多行数据
（5）窗口函数（开窗函数）
定义：窗口函数为每行数据划分一个窗口，继而对窗口范围内数据计算，最后将计算结果返回给该行。
常用窗口函数
聚合函数max、min、sum、avg、count跨行取值函数lead、lag、first_value、last_value排名函数rank、dense_rank、row_number
lead和lag：获取当前行的上/下边某行、某个字段的值
lag(字段名,偏移量,默认值)...
lead(字段名,偏移量,默认值)...
--注：不支持自定义窗口
first_value和last_value：获取窗口内某一列的第一个值/最后一个值
first_value(字段名,是否跳过null（T/F）)...
last_value(字段名,是否跳过null（T/F）)...
rank、dense_rank、row_number：计算排名
注：rank、dense_rank、row_number不支持自定义窗口
（6）自定义函数（UDF）
编程步骤：
1.继承Hive提供的类
org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
2.实现类抽象方法
3.hive命令行窗口创建函数
--添加jar
add jar linux_jar_path
--创建function
create [temporary] function [dbname.]function_name AS class_name;
4.hive命令行窗口删除函数
drop [temporary] function [if exists] [dbname.]function_name;
六、分区表和分区桶
（1）分区表
1.语法
创建分区表
create table dept_partition
(
deptno int, --部门编号
dname string, --部门名称
loc string --部门位置
)
partitioned by (day string)
row format delimited fields terminated by '\t';
写数据：load，insert
读数据:将分区字段看作表的伪列
2.基本操作
查看分区信息
show partitions dept_partition;
创建/删除多个分区
alter table dept_partition
add/drop partition(day='20240901') partition(day='20240902');;
3.二级分区表
4.动态分区
插入数据时分区由每行数据最后一个字段值动态决定
（2）分区桶
**分桶**针对**数据文件**，**分区**针对**数据存储路径**
基本原理：为每行数据计算一个指定字段的数据hash值，然后模以一个指定分桶数，最后将取模运算结果相同的行，写入同一个文件中，这个文件就称为一个分桶（bucket）。
版权归原作者 天启和风 所有， 如有侵权，请联系我们删除。