本文详细介绍了HiveSQL的各种语法，包括数据类型、DDL操作、DML操作、查询技巧及执行计划调优。重点讲解了如何创建数据库、表、动态分区，以及数据导入导出。同时，探讨了SQL查询中的连接、分组、函数使用，以及执行计划的分析和优化策略，如列裁剪、CBO、MapJoin等。通过对参数设置和执行计划的调整，实现HiveSQL性能的提升。
目录
3.3. 在所有执行MR的节点上, 最大一共可以创建多少个动态分区. 默认1000
3.4. 在每个执行MR的节点上，最大可以创建多少个动态分区
一. 数据类型对比
1. 基本数据类型
| Hive | MySql | Java | 长度 | 示例 |
| tinyint | tinyint | byte | 1byte有符号整数 | 2 |
| smalint | smalint | short | 2byte有符号整数 | 20 |
| int | int | int | 4byte有符号整数 | 20 |
| bigint | bigint | long | 8byte有符号整数 | 20 |
| boolean | 无 | boolean | 布尔类型, true或者false | true / false |
| float | float | float | 单精度浮点数 | 3.14159 |
| double | double | double | 双精度浮点数 | 3.14159 |
| string | varchar | string | 字符系列 可以指定字符集 可以使用单引号或者双引号 | 'now is the time' "for all good men" |
| timestamp | timestamp | 时间类型 | ||
| binary | binary | 字节数组 |
hive小数保存 decimal(有效位,保留小数位)
2. 集合数据类型
| 数据类型 | 描述 | 语法示例 |
| struct | 可以通过“点”符号访问元素内容。可以理解为java类访问属性 例如，如果某个列的数据类型是struct{first string, last string} 那么第1个元素可以通过字段.first来引用。 | struct<street:string,city:string> |
| map | map是一组键-值对元组集合, 使用数组表示法可以访问数据 例如: 某列数据类型是map, 键->值对是'first'->'John'和'last'->'Doe' 可以通过字段名['last']获取最后一个元素 | map<string,int> |
| array | 具有相同类型和名称的变量的集合 这些变量称为数组的元素 每个数组元素都有一个编号, 编号从零开始 例如: 数组[‘John’, ‘Doe’]，第2个元素可以通过数组名[1]进行引用 | array<string> |
二. DDL数据定义
1. 数据库
1.1. 创建数据库
注意: SQL语句内部不能有水平制表符号 ,否则无法识别SQL
create database [if not exists] database_name -- 数据库的名称
[comment database_comment] -- 数据库的注释
[location hdfs_path] -- 指定数据保存hdfs的位置
[with dbproperties (property_name=property_value, ...)]; -- 数据库的属性;-- 示例
create database if not exists test_db comment "Just for test_db" location '/abcd' with dbproperties("aaa"="bbb");1.2. 显示数据库
-- 查询所有数据库
show databases;
-- 过滤名称模糊匹配查询数据库
show databases like 'db_hive*';1.3. 查看数据库详情
-- 显示数据库信息
desc database database_name;
-- 示数据库属性parameters详细信息
desc database extended database_name;1.4. 切换数据库
use database_name;1.5. 修改数据库
数据库的其他元数据信息都是不可更改的,包括数据库名和数据库所在的目录位置,只能修改dbproperties属性值
-- 暂时只有新增/更新属性,没有删除属性的api
alter database database_name set dbproperties('createtime'='20230230');1.6. 删除数据库
-- 删除数据库
drop database database_name;
-- 删除钱判断是否存在
drop database [if exists] database_name;
-- 如果数据库不为空，可以采用cascade有判头命令，强制删库跑路
drop database database_name cascade;2. 表
2.1. 创建表模板
create [external] -- 1.内外部表
table [if not exists] table_name -- 2.操作前检查是否存在
[(col_name data_type [comment col_comment], ...)] -- 3.列名称与列数据类型与备注,名称不支持中文
[comment table_comment] -- 4.表备注
[partitioned by (col_name data_type [comment col_comment], ...)] -- 5.分区
[clustered by (col_name, col_name, ...) -- 6.分桶
[sorted by (col_name [asc|desc], ...)] into num_buckets buckets] -- 7.不常用,分桶后,对分桶中的一个或多个列另外排序
[row format row_format] -- 8.指定数据解析形式
[stored as file_format] -- 9.文件存储类型
[location hdfs_path] -- 10.外部表指定在HDFS上的存储位置
[tblproperties (property_name=property_value, ...)] -- 11.表属性
[as select_statement] -- 12.通过查询结果创建表
[like table_name] -- 13.拷贝表结构2.2. 创建内部表与外部表
内外部表 关键词 external
在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据
-- 创建内部表,drop删除表会将元数据与实际数据都删除
create table student(id int,name string);
-- 创建外部表,drop删除表只会删除元数据
create external table student(id int,name string)
location '/outside/teacher'; -- 指定外部存储路径;
2.3. 创建表指定数据格式
row format -- 分隔符关键字
delimited fields terminated by ',' -- 字段之间的分隔符
collection items terminated by '_' -- 集合元素之间分隔符
map keys terminated by ':' -- map分隔符
lines terminated by '\n'; -- 多条数据之间的分隔符2.4. 拷贝表结构
拷贝表结构
create table new_table_name like old_table_name; -- 结构完全一致,包含注释和分割符2.5. 创建分区表
-- 一级分区
create table my_partition_tb(id int,name string)
partitioned by(part string) -- 声明分区字段名称,分区字段不能在表中用属性创建,只能用partitioned by创建,往分区中写入数据需要指定分区,分区导入数据,会在表目录下生成文件名为"分区名称=分区值"的一个目录
row format delimited fields terminated by '\t';
-- 二级分区
create table my_partition_tb(id int,name string)
partitioned by(part1 string,part1 string) -- 声明分区字段名称,二级分区有子分区,对应子文件夹
row format delimited fields terminated by '\t';2.6. 分桶与分桶排序
分桶表是将一个表的数据分成多个文件进行管理,数据采用hash轮询分桶存储,分桶插入数据会生成多桶数据,分桶的性能体现在分桶字段的join上有优化
-- 创建分桶表 跑mr程序,不推荐使用本地文件,因为mr不支持跨节点获取本地磁盘数据,可以先上传hdfs,再加载数据
create table my_cluster_tb(id int,name string)
clustered by(id) -- 指定分桶字段
sorted by (id) -- 分桶排序
into 4 buckets; -- 一次插入分4桶数据;2.7. 设置表文件存储格式与压缩
Hive大部分业务场景使用列式存储 列式存储查询效率可比行式存储能高出一个数量级 推荐使用orc格式
主要存储格式
1).基于行式存储 textfile 、sequencefile
2).基于列式存储 orc、parquet
- TextFile 默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用，但使用Gzip这种方式，Hive不会对数据进行切分，从而无法对数据进行并行操作
- Orc 每个Orc文件由1个或多个stripe组成，每个stripe一般为HDFS的块大小，每一个stripe包含多条记录，这些记录相当于轻量级的索引与数据Orc格式块文件默认大小为256M,可修改(set hive.exec.orc.default.block.size=16777216)
- Parquet 以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据
orc文件格式建表示例:
create table orc_table(id int,name string)
stored as orc -- 指定orc文件存储格式
tblproperties('orc.compress'='NONE','orc.block.size'='1048576'); -- 默认会使用ZLIB压缩,可以通过orc.compress=NONE关闭压缩; 支持的压缩参数有 NONE/ZLIB/SNAPPY; orc.block.size 表示orc格式的块文件大小,这里配制的1Mparquet 文件格式建表示例:
create table parquet_table(id int,name string)
stored as parquet; -- parquet默认不压缩
tblproperties('parquet.compression'='GZIP'); -- 默认会使用ZLIB压缩,可以通过orc.compress=NONE关闭压缩; 支持的压缩参数有 NONE/GZIP/SNAPPY;压缩能力对比
| 文件格式 | 存储大小单位M | zib压缩 | snappy压缩 |
| textFile | 115.14 | 未测试 | 未测试 |
| orc格式 | 65.34 | 29.62 | 41.67 |
| parquet | 65.88 | 38.39 | 65.88 |
2.8. 获取表信息
--查询所有表的名称列表
show tables;
-- 查询指定表的结构
desc table_name;
-- 查询指定表属性
desc formatted table_name;
-- 查询表对应的分区列表
show partitions table_name;2.9. 修改表
- 重命名表
alter table old_table_name to new_table_name - 列操作
-- 增加列 alter table table_name add columns (new_field_name bigint comment '备注') -- 修改列 alter table table_name add columns (new_field1_name bigint comment '备注1',new_field2_name bigint comment '备注2') -- 替换列,按顺序从第一个字段开始修改元数据,对表内实际数据无影响,缺少的列查询不会再显示,增加的列查询显示是NULL alter table new_table_name replace columns(id bigint comment 'ID',name string comment '名称',age bigint comment '年龄'); - 修改设置内部表和外部表
-- 外部表转内部表 alter table table_name set tblproperties('EXTERNAL'='TRUE') -- 内部表转外部表 alter table table_name set tblproperties('EXTERNAL'='FALSE') -
修改分区
-- 添加分区 alert table table_name add partition(part='2023-01-01') partition(part='2023-01-02'); -- 删除分区,分区不支持修改名称 alert table table_name drop partition(part='2023-01-01'),partition(part='2023-01-02'); -- 更新分区,根据分区文件夹更新分区元数据 msck repair table table_name;
2.10. 删除表
drop table table_name2.11. 清空表
-- truncate只能清空内部表数据，不能清空外部表中数据
truncate table table_name;2.12. 创建临时表
with t1 as (select 123 as field1),t2 as (select 123 as field2) -- with创建临时表,只对当前sql有效
select * from t1 left join t2 on t1.field1 = t2.field2;3. 动态分区参数设置
关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区（Dynamic Partition），只不过，使用Hive的动态分区，需要进行相应的配置
3.1. 开启动态分区参数设置
hive.exec.dynamic.partition=true3.2. 设置为非严格模式
动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区
hive.exec.dynamic.partition.mode=nonstrict3.3. 在所有执行MR的节点上, 最大一共可以创建多少个动态分区. 默认1000
hive.exec.max.dynamic.partitions=10003.4. 在每个执行MR的节点上，最大可以创建多少个动态分区
该参数需要根据实际的数据来设定. 比如: 源数据中包含了一年的数据, 即day字段有365个值, 那么该参数就需要设置成大于365, 如果使用默认值100, 则会报错
hive.exec.max.dynamic.partitions.pernode=1003.5. 整个MR Job中，最大可以创建多少个HDFS文件, 默认100000
hive.exec.max.created.files=1000003.6. 当有空分区生成时, 是否抛出异常, 一般不需要设置,默认false
hive.error.on.empty.partition=false三. DML数据操作
1. 数据导入
1.1. 向表中加载数据
-- load data：加载数据
-- local：从本地加载;不写表示加载HDFS路径
-- inpath：加载路径
-- overwrite：覆盖表中数据,只能覆盖当前分区数据,不写表示追加
-- into table：表示载入到哪张表
-- student：具体的表
-- partition：指定分区
load data [local] inpath '数据的路径' [overwrite] into table student [partition (partcol1=val1,…)];
-- 示例,加载本地覆盖入表
load data local inpath '/home/myuser/data.txt' overwrite into table student
-- 示例,加载本地覆盖入表指定分区,part是创建表声明的分区字段,如果有二级分区,插入需要指定到子分区
load data local inpath '/home/myuser/data.txt' into table student_partition partition(part1='2023',part2='03-03')
-- 加载HDFS追加入表
load data inpath '/home/myuser/data.txt' into table student1.2. 插入数据
-- insert向表中插入数据,会跑mr程序,比较慢,每次执行都会生成至少一个文件,不推荐这种
insert into student values (1001,'赵1'),(1002,'赵2'),(1003,'赵3');
-- 插入查询的结果(最常用)
insert into student_temp(select id,name from student where id > 1000);
-- 覆盖插入
insert overwrite table student_temp(select id,name from student where id > 1000);
-- 插入分区表 将part分区当成字段插入,分区字段插入需要写在普通字段后面
insert overwrite table student_partition(select 1,"张三","2023-03-01");
-- 插入分区表 指定part分区
insert overwrite table student_partition partition(part="2023-02-01") (select 4,"王六");1.3. 根据查询结果建表
二.DDL数据定义 -> 2.表 -> 2.1.创建表 目录对应的内容里面有根据结果创建表的步骤,不推荐使用
1.4. location加载数据
-- 通过location指定数据存储目录,目录中已有的数据会被加载到表中,相当于补全元数据的操作
create table student(id int comment '编号',name string comment '姓名') comment '学生表'
row format delimited fields terminated by ','
location '/data';2. 数据导出
2.1. insert导出
-- 将查询的结果导出到本地,不会导出元数据
insert overwrite local directory '/home/data/student' -- 导出数据存储位置
row format delimited fields terminated by ',' -- 指定导出的分割符
(select id,name from student where id > 1000); -- 导出的数据,可以不加括号;
-- 将查询的结果导出到HDFS
insert overwrite directory '/hdfs/data/student'
row format delimited fields terminated by ','
(select id,name from student where id > 1000);2.2. import和export
-- 常用语数据迁移
-- 将表数据导出到HDFS,会导出元数据
export table student to '/hdfs/data/student';
-- 将export导出HDFS的数据再导入到Hive中
import table newstudent from '/hdfs/data/student';四. 查询
1. sql书写与执行顺序
| 书写次序 | 书写次序说明 | 执行 | 执行次序说明 |
| select | 查询 | from | 先执行表与表直接的关系 |
| from | 先执行表与表 直接的关系 | on | |
| join on | join | ||
| where | where | 过滤 | |
| group by | 分组 | group by | 分组 |
| having | 分组后过滤 | having | 分组后过滤 |
| distribute by cluster by | 4个by | select | 查询 |
| sory by | distinct | 去重 | |
| order by | distribute by cluster by | 4个by | |
| limit | 限制输出的行数 | sory by | |
| union/union all | 合并 | order by | |
| limit | 限制输出的行数 | ||
| union union all | 合并 |
2. 基本查询
2.1. 介于两者之间查询 between
-- 使用between的区间是闭区间,包含两边界值 可选not取反
select * from tbName where id [not] between 10 and 100;
2.2. 集合判断 in
-- 查询在集合中包含的数据 可选not取反
select * from tbName where id [not] in (1,2,3,4,5);2.3. 通配符与正则表达式
-- 通配符匹配 not取反
select * from tbName where name [not] like "张%";
-- 正则表达式匹配
select * from tbName where name [not] rlike "^张";3. 分组查询
过滤与分组过滤
-- 未分组的过滤使用where
select * from tbName where typeNumber > 10;
-- 分区后的过滤使用having,因为where执行顺序在分组前,having是在分组后执行
select typeNumber,count(1) as total from tbName group by typeNumber having typeNumber > 10;4. 连表查询
4.1. 内连接
-- 内连接使用 表1 join 表2 on 表1字段 = 表2字段
select * from tb_a join tb_b on tb_a .id = tb_b .id4.2. 左外连接
-- 左外连接使用 表1 left join 表2 on 表1字段 = 表2字段
select * from tb_a left join tb_b on tb_a .id = tb_b .id4.3. 右外连接
-- 右外连接使用 表1 right join 表2 on 表1字段 = 表2字段
select * from tb_a right join tb_b on tb_a .id = tb_b .id4.4. 满外连接
-- 满外连接使用 表1 full join 表2 on 表1字段 = 表2字段
select * from tb_a full join tb_b on tb_a .id = tb_b .id4.5. 各种连接之间的区别
| 名称 | 连接语句 | 显示结果 |
| 内连接 | 表1 join 表2 on 表1与表2的连接条件 | 连接条件中的null数据不被显示 |
| 左外连接 | 表1 left join 表2 on 表1与表2的连接条件 | 表1中,连接条件中null数据也会显示 |
| 右外连接 | 表1 right join 表2 on 表1与表2的连接条件 | 表2中,连接条件中null数据也会显示 |
| 满外连接 | 表1 full join 表2 on 表1与表2的连接条件 | 都会显示null数据 |
总结:
A 右连 B, B为主表,B全显示,A中连不上B的不被显示
A 左连 B, A为主表,A全显示,B中连不上A的不被显示
5. 排序
5.1. 简单全局排序
所有分区数据都需要整合排序
-- 排序默认ASC升序 DESC为降序,排序在select之后,可以使用别名排序,为了防止性能问题,需要加limit,通过局部limit最终计算全局limit,能大量提升性能
select * from tb_name order by field1 [DESC],field2 [DESC] limit 1000;
-- 示例按照年级,年龄,分数三个条件排序
select grade,age,achievement as score from student order by grade desc,age,score desc limit 1000;5.2. 分区与分区内部排序
大规模的数据集order by的效率非常低,在很多情况下,并不需要全局排序,此时可以使用sort by, 在分区内部进行排序,分区间的数据相互独立,互不影响
-- 分区排序建议先设置分区数量,分区数和业务相关,这里设置6个分区,因为对6个年级进行排序
set mapreduce.job.reduces=6
-- 通过distribute by指定分区字段,使用hash分区,分区内部排序,需要先分区,将相同年级的数据分在一个区,distribute by是hash分区,年级如果用中文,并非是数字,可能会出现两个不同年级的hash值相同,即数据全被分在同一个分区,导致存在空分区,但是这不会影响程序的结果
select * from student distribute by grade sort by age desc;
-- 特例: 如果distribute by与sort by用的同一个字段,而且为升序,可以替换为cluster by排序
select * from student distribute by grade sort by grade;
select * from student cluster by grade;6. 函数
6.1. 查看系统函数
-- 查看系统的所有函数列表
show functions;
-- 查看包含特定关键字的函数,这里比较特殊,通配不使用%,使用※
show functions like '*date*';
--查看系统函数的使用方法
desc function 'current_date';
-- 查看系统函数更详细的使用方法,推荐详细使用方法直接查询百度
desc function extended 'current_date';6.2. 空值替换
-- 空值替换 -----------------------
nvl(col ,default_value) -- 如果 col不为null,返回col,否则返回default_value
coalsece(col1,col2,col3,...) -- 从左到右找到第一个不为null的值返回6.3. 分支函数
-- 分支函数 -----------------------
if(boolean,result1,result2) -- 如果boolean为真,返回result1,否则返回result2
case col when value1 then result1 when value2 then result2 else result3 end -- 如果col值为value1,返回result1,如果是value2,返回result2,否则返回result3
case when boolean1 then result1 boolean2 then result2 else result3 end -- 如果boolean1为真返回,如果boolean2为真,返回result2,否则返回result36.4. 字符串拼接
-- 字符串拼接 ---------------------
concat(col1,col2,col3,...) -- 多个字符串拼在一起,可以传递数组,可以是字段值
concat_ws(separator,col1,col2,...) -- 指定分隔符拼接
repeat("value",count) -- 将相同的字符串重复重复拼接count次数6.5. 汇总
-- 多数据聚合汇总产生数组 -------------------
collect_set(col) -- 去重汇总
collect_list(col) -- 直接汇总,不去重6.6. 分割拆分
-- 分割拆分
split(str, separator) -- 将字符串按照后面的分隔符切割,转换成字符array
explode(col) -- 将Hive一列中复杂的array或者map结构拆分成多行
lateral view udtf(expression) tableAsName as columnAsName -- 首先为原始表的每行调用UDTF，UDTF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表6.7. 行列互转
-- 行转列,列转行 ------------------
select concat_ws('|',collect_list(name)) from table_name group by constellation; -- 行转列: 配合汇总函数collect_list将聚合的数据汇总,在通过字符串拼接函数将汇总的数据转化成一条数据
select movie,temp_field from tabe_name lateral view explode(split(col,',')) temp_tabe_name as temp_field; -- 列转行: 根据col将数据按照逗号切割转换成多行
-- 列转行和列转行示例:
select name,count(age),concat_ws('|',collect_list(age)) -- 行转列
from (
select name,age -- 列转行
from
(select "yangxp" as name, "18,19" as ages) temp1
lateral view explode(split(ages,",")) temp2 AS age
-- explode(array/map) tempTableName as [key,]value
-- explode(MapObject) temp2 AS key,value 可以使用map进行列转行,其中一行变成两列多行,两列是指key和value
-- explode(ArrayObject) temp2 AS unit 可以使用Array进行列转行,其中一行变成多行,每列是指Array里面的一个元素
) temp3
group by name; -- 行转列需要分组聚合,将每组转换成一列
-- 炸开函数,行转列 --
select posexplode(split("a,3,4,5,4,b",",")); -- posexplode携带编号/explode不带编号
6.8. 去重
-- 去重统计distinct
select substr(orderdate,0,7),count(distinct name) from business
group by substr(orderdate,0,7);6.9. 开窗函数
-- tableName business[name string,orderdate string comment '时间',cost int comment '指标']
-- 固定窗口 对查询的结果使用全开窗函数聚合 over()
select name,count(name) over()
from business
where substr(orderdate,0,7) = '2017-04';
-- 固定窗口 指定时间粒度跨度开窗
select name,sum(cost) over(partition by sunstr(orderdate,0,7))
from business
-- 跨度窗口 指定历史数据为窗口,每次开窗多一条历史数据, 查询每个用户截止到当前的日期的累计指标总和
sum(cost) over(
partition by name -- 开窗限制 组内开窗
order by orderdate -- 数据排序
rows between -- 开窗范围
unbounded preceding -- 窗口起始位置 本组的第一行
and -- 到
current row -- 窗口结束位置 当前行
)
-- 开窗特定聚合函数举例
select name,
orderdate,
lag(orderdate,2,'1970-01-01') over(partition by name order by orderdate) last_date,
first_value(if(cost>50,orderdate,null),true) over(partition by name order by orderdate rows between unbounded preceding and current row) -- 获取截止当前大于50的第一个日期
from business;
开窗表达式
| 开窗范围 | 备注 |
| current row | 当前行 |
| n preceding | 往前n行数据 |
| n following | 往后n行数据 |
| unbounded | 无边界 |
| unbounded preceding | 前无边界,表示从前面的起点 |
| unbounded following | 后无边界,表示到后面的终点 |
| 开窗特定聚合函数 | 备注 |
| lag(col,n,default_val) | 取往前第n行这一条数据 default_val表示默认值 |
| lead(col,n, default_val) | 取往后第n行这一条数据 |
| ntile(n) | 把有序窗口的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号 注意: n必须为int类型 |
| rank() dense_rank() row_number() | 编号函数,给有序数据编号 rank: 排序相同时会重复，总数不会变 例如[1,1,3,4] dense_rank: 排序相同时会重复，总数会减少 例如[1,1,2,3] row_number: 会根据顺序计算 例如[1,2,3,4] |
| first_value (col,true/false) | 当前窗口下的第一个值,第二个参数为true,跳过空值 |
| last_value (col,true/false) | 当前窗口下的最后一个值,第二个参数为true,跳过空值 |
6.10. 日期与时间函数
SELECT
current_date(), -- 返回当前日期,精确到日
current_timestamp(), -- 返回当前日期,精确到毫秒 可以转换为bigint格式 current_timestamp() as bigin
date_add("2023-04-01",2), -- 返回距离current_date之后2天的日期,精确到日
date_sub("2023-04-01",3), -- 返回距离current_date之前3天的日期,精确到日
months_between("2023-06-01","2023-05-02"), -- 返回两个日期之间间隔的月数,double
datediff("2023-04-01",current_date()), -- 返回日期差
year("2023-04-01"), -- 返回日期年
month("2023-04-01"), -- 返回日期月
day("2023-04-01"), -- 返回日期日
dayofweek("2023-03-30"), -- 返回星期几(星期日是1, 星期1是2)
weekofyear("2023-03-30 16:32:11"), -- 返回第几周
date_format("2023-03-30","yyyy-MM-dd"), -- 格式化日期 原始格式前缀支持yyyy-MM-dd HH:mm:ss.SSS 转化格式支持yMdHmsS
unix_timestamp("2021","yyyy"), -- 日期转时间戳 获取指定格式日期的时间戳 时间间隔可以用时间戳计算,对应到秒
from_unixtime(1628956800,"yyyy"), -- 时间戳转日期;
to_utc_timestamp(unix_timestamp(),"GMT+09:00"); -- 时间戳时区转换,时间戳传递毫秒 默认GMT+00:00;6.11. 取整函数
SELECT
ceil(1.1), -- 向上取整
floor(2.9), -- 向下取整
round(2.4); -- 四舍五入;6.12. 包装函数
select str_to_map(col_tmp,"\\|",":") from (SELECT "k1:1|k2:2|k3:3" as col_tmp) temp -- 将内容按照|分隔,每段以:隔开生成key value,最终返回map
select named_struct("nk1",k1,"nk2",k2,"nk3",k3) from (select 12 k1,13 k2,14 k3) temp -- 将内容封装为struct;6.13. 自定义函数UDF
官方文档 点击进入
| UDF | 一行输入一行输出 |
| UDAF | 多行输入一行输出,通常结合group by和开窗函数使用 |
| UDTF | 一行输入多行输出 例如 [lateral view explode / posexplode] 函数 |
1. 编码
a) 引入pom依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>b) UDF代码
package com.udf.my;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.util.Arrays;
/**
* 求字符串长度
*/
public class HiveUDF extends GenericUDF {
/**
* 用于验证输入参数个数个类型
*
* @param arguments
* @return
* @throws UDFArgumentException
*/
@Override
public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
//"string".equals(arguments[0].getTypeName())//获取输入参数类型
return PrimitiveObjectInspectorFactory.javaIntObjectInspector;//UDA最终返回数据类型为字符串长度 为int
}
/**
* 计算逻辑,根据输入的参数计算结果
*
* @param deferredObjects 支持输入多个参数
* @return
* @throws HiveException
*/
@Override
public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {//计算逻辑
int sum = 0;
for (DeferredObject deferredObject : deferredObjects) {
sum += deferredObject.get().toString().length();
}
return sum;
}
/**
* 显示错误代码
*
* @param strings
* @return
*/
@Override
public String getDisplayString(String[] strings) {
return Arrays.toString(strings);
}
}UDAF/UDTF百度自行查找
2. 打包
打包hiveudf上传到<hive_home>/lib/ 代码如果只依赖hive,只需要打最小包即可
3. 添加自定义函数到环境中
重启hiveserver2或者在客户端中热添加jar包任选一个
# 重启 先kill hiveserver2后再启动
hive --service hiveserver2# 热添加 在beeline中执行
add jar <hive_home>/lib/hiveudf.jar4. 在Hive中创建函数与jar关联
# 创建临时函数,只在当前会话中生效
create temporary function myudf as 'com.udf.my.HiveUDF';5. 使用函数
select myudf("abcdefg");6. 删除函数
-- 删除函数后再删除jar
drop function myudf;
delete jar /hive_home/lib/myudf.jar;7. 永久函数
# 创建永久函数使用hdfs地址 防止文件丢失
create function testAdd as 'AddTest' using jar 'hdfs://hadoop:9000/hive/udf/myTest.jar';
-- 删除注册的函数
drop function if exists testAdd;
7. 计算常见问题
2.1. 聚合数据内包含NULL导致结果不准确
常用聚合函数注意 UDAF函数在聚合的时候,NULL数据不会被计算进去
例如伪sql: select count(Array[1,2,3,4,NULL,6,7]); 结果为6,统计中NULL不参与计算
2.2. NULL查找
判断某个字段值是否为NULL不能用等号,
示例sql: select * from student where name = null
条件name = null结果永远都是false
null不能用等号判断,判断null的方式有两种
1.用is判断 name is null
2.用安全等号判断 name <=> null
五. 执行计划调优 explain
1. 执行计划Demo
1.1. sql
explain select 123;1.2. 响应试图
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: 5 |
| Processor Tree: |
| TableScan |
| alias: _dummy_table |
| Row Limit Per Split: 1 |
| Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats: COMPLETE |
| Select Operator |
| expressions: 123 (type: int) |
| outputColumnNames: _col0 |
| Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE Column stats: COMPLETE |
| Limit |
| Number of rows: 5 |
| Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE Column stats: COMPLETE |
| ListSink |
| |
+----------------------------------------------------+1.3. Demo参数解释
| STAGE DEPENDENCIES | 所有依赖的阶段 |
| STAGE PLANS | 所有阶段详细的执行计划 |
| Stage: Stage-0 | 当前记录的执行计划阶段名称 Stage-0 |
| Fetch Operator | 当前节点操作的操作类型为Fetch Operator 表示直接从文件读数据, 不需要走MapReduce |
| limit: 5 | 返回的查询的数据量 |
| Processor Tree | 执行计划当前阶段的处理流程 |
| TableScan | 扫描表 |
| Select Operator | 查询数据 |
| expressions | 查询的列数 |
| outputColumnNames | 输出的列数 |
| Statistics | 统计信息 |
| Limit | 分页 |
2. 执行计划连表sql示例
2.1. sql
explain
select ename,dname
from (select "deptno" deptno,"ename" ename) e join (select "deptno" deptno,"dname" dname) d
on e.deptno = d.deptno;2.2. 响应试图
3. 参数调优
3.1. Fetch Operator
Fetch Operator操作 直接从文件中读数据, 不需要执行mr程序
| 关注重点 | 解释 |
| limit | 取几行数据 |
3.2. Select Operator
查询数据
| 关注重点 | 解释 |
| expressions | 查询的列数 |
| Statistics | 统计信息(行数,文件大小) load data导入的数据不能用于统计 load data统计不准确,insert可以用于统计 |
3.3. Filter Operator
过滤数据
| 关注重点 | 解释 |
| predicate | 过滤条件 |
3.4. Map Join Operator
执行MapJoin
3.5. Join Operator
执行ReduceJoin 连接条件
3.6. File Output Operator
文件输出结果格式
| 关注重点 | 解释 |
| compressed | 压缩格式 |
| Statistics | 统计数据 |
| table | 最终输出数据格式 |
3.7. Group By Operator
分组操作
| 关注重点 | 解释 |
| aggregations | 执行的操作 |
| keys | 按照什么字段进行聚合 |
| mode | 分组方法 1. mergepartial 将局部聚合的数据再次汇总 2. hash 使用hash分组聚合 |
3.8. Reduce Output Operator
局部汇总,分区内部汇总
| 关注重点 | 解释 |
| key expressions | 分组的key |
| sort order | 升序或者降序 |
| Map-reduce partition columns | 分区key |
3.9. PTF Operator
开窗操作
| 关注重点 | 解释 |
| partition by | 开窗分组的列 |
| order by | 排序的列 |
| window functions | 开窗后执行的操作 |
六. 语法优化
1. 列裁剪与分区裁剪
列裁剪就是在查询时只读取需要的列，分区裁剪就是只读取需要的分区。当列很多或者数据量很大时，如果select * 或者不指定分区，全列扫描和全表扫描效率都很低。
Hive在读数据的时候，可以只读取查询中所需要用到的列，而忽略其他的列。这样做可以节省读取开销：中间表存储开销和数据整合开销
2. CBO优化
join的时候表的顺序的关系：前面的表都会被加载到内存中。后面的表进行磁盘扫描。CBO这个优化是默认开启的，它可以自动优化HQL中多个Join的顺序，并选择合适的Join算法。
set hive.cbo.enable=true; -- 是否开启CBO优化 默认true
set hive.compute.query.using.stats=true; -- 是否要根据表的元数据计算是否要CBO优化 默认 false
set hive.stats.fetch.column.stats=true; -- 是否根据列数来CBO优化3. 谓词下推
将SQL语句中的where谓词逻辑都尽可能提前执行，减少下游处理的数据量。对应逻辑优化器是PredicatePushDown，配置项为hive.optimize.ppd，默认为true
set hive.optimize.ppd = true;4. MapJoin
MapJoin是将Join双方比较小的表直接分发到各个Map进程的内存中，在Map进程中进行Join操作，这样就不用进行Reduce步骤，从而提高了速度。如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成Join。容易发生数据倾斜
4.1. 设置自动选择MapJoin #默认为true
set hive.auto.convert.join=true;4.2. 大表小表的阈值设置（默认25M以下认为是小表）
set hive.mapjoin.smalltable.filesize=25000000;5. 桶join优化
创建分桶表,使用分桶字段进行join看了一临时开启下列优化操作
set hive.optimize.bucketmapjoin = true; -- 默认 false
set hive.optimize.bucketmapjoin.sortedmerge = true; -- 默认 false
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; -- 默认 org.apache.hadoop.hive.ql.io.CombineHiveInputFormat6. Join数据倾斜优化
6.1. 使用配制参数优化
如果开启了，在Join过程中Hive会将计数超过阈值hive.skewjoin.key（默认100000）的倾斜key对应的行临时写进文件中，然后再启动另一个job做map join生成结果。通过 hive.skewjoin.mapjoin.map.tasks参数还可以控制第二个job的mapper数量，默认10000。
set hive.skewjoin.key=100000; -- join的键对应的记录条数超过这个值则会进行分拆，值根据具体数据量设置 默认 100000
set hive.optimize.skewjoin=false; -- 如果是join过程出现倾斜应该设置为true 默认false
set hive.skewjoin.mapjoin.map.tasks=10000; -- 控制第二个job的mapper数量 默认10000join的键对应的记录条数超过这个值则会进行分拆，值根据具体数据量设置
6.2. 大表数据倾斜使用hash散列方式拆分数据
7. Map优化
7.1. 复杂文件增加Map数
当input的文件都很大，任务逻辑复杂，Map执行非常慢的时候，可以考虑增加Map数，来使得每个Map处理的数据量减少，从而提高任务的执行效率
增加map的方法为：根据
computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M
公式，调整maxSize最大值。让maxSize最大值低于blocksize就可以增加map的个数。
set mapreduce.input.fileinputformat.split.maxsize=100000; -- 设置最大切片值为100000个字节,默认 256000000字节7.2. 小文件进行合并
在map执行前合并小文件，减少map数：CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能
set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; -- 默认为CombineHiveInputFormat
set hive.merge.mapfiles = true; -- 在map-only任务结束时合并小文件 默认true
set hive.merge.mapredfiles = true; -- 在map-reduce任务结束时合并小文件, 默认false
set hive.merge.size.per.task = 268435456; -- 合并文件的大小, 默认256M
set hive.merge.smallfiles.avgsize = 16777216; -- 当输出文件的平均大小小于该值时,启动一个独立的map-reduce任务进行文件合并7.3. Map端聚合
Map端聚合函数为count或者sum累计操作可以设置
set hive.map.aggr = true; -- 是否在Map端进行聚合，默认为True
set hive.groupby.mapaggr.checkinterval = 100000; -- 在Map端进行聚合操作的条目数目
set hive.groupby.skewindata = true; -- 有数据倾斜的时候进行负载均衡（默认是false）8. Reduce优化
Reduce个数并不是越多越好
(1) 过多的启动和初始化Reduce也会消耗时间和资源
(2) 另外，有多少个Reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题
在设置Reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的Reduce数；使单个Reduce任务处理数据量大小要合适
8.1. 合理设置Reduce数方式1
计算reducer数的公式
N=min(参数2，总输入数据量/参数1)(参数2 指的是上面的1009，参数1值得是256M)
set hive.exec.reducers.bytes.per.reducer = 256000000 -- 每个Reduce处理的数据量默认是256MB
set hive.exec.reducers.max = 1009 -- 每个任务最大的reduce数，默认为10098.2. 合理设置Reduce数方式2
在hadoop的mapred-default.xml文件中修改。
set mapreduce.job.reduces = 15; -- 设置每个job的Reduce个数。9. Hive 任务整体优化
9.1. Fetch抓取
Fetch抓取是指, Hive中对某些情况的查询可以不必使用MapReduce计算. 例如: select * from emp;在这种情况下, Hive可以简单地读取emp对应的存储目录下的文件, 然后输出查询结果到控制台
在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走MapReduce。
9.2. 本地模式
有时Hive的输入数据量是非常小的. 在这种情况下, 为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多. 对于大多数这种情况, Hive可以通过本地模式在单台机器上处理所有的任务. 对于小数据集, 执行时间可以明显被缩短.
set hive.exec.mode.local.auto=true; -- 开启本地mr 默认false
set hive.exec.mode.local.auto.inputbytes.max=50000000; -- 设置local mr的最大输入数据量，当输入数据量小于这个值时采用local mr的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.input.files.max=10; -- 设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为49.3. 并发执行
Hive一次只会执行一个阶段. 不过, 某个特定的job可能包含众多的阶段, 而这些阶段可能并非完全互相依赖的, 也就是说有些阶段是可以并行执行的, 这样可能使得整个job的执行时间缩短. 不过, 如果有更多的阶段可以并行执行, 那么job可能就越快完成
系统资源比较空闲的时候才有优势，否则，没资源，并行也起不来
建议在数据量大，sql很长的时候使用，数据量小，sql比较的小开启有可能还不如之前快
set hive.exec.parallel=true; -- 打开任务并行执行，默认为false
set hive.exec.parallel.thread.number=16; -- 同一个sql允许最大并行度，默认为89.4. 严格模式
Hive可以通过设置防止一些危险操作
set hive.strict.checks.no.partition.filter=true; -- 默认false 设置为true时，对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行,用户不允许扫描所有分区
set hive.strict.checks.orderby.no.limit=true; -- 默认false 设置为true时 使用了order by语句的查询，要求必须使用limit语句
set hive.strict.checks.cartesian.product=true; -- 默认false 设置为true时 会限制笛卡尔积的查询执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况
扫一扫
747
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
