2020.9.22课堂笔记(Hive函数及性能优化)

一.Hive函数分类

从输入输出角度分类

• 标准函数：一行数据中的一列或多列为输入，结果为单一值
• 聚合函数：多行的零列到多列为输入，结果为单一值
• 表生成函数：零个或多个输入，结果为多列或多行

从实现方式分类

• 内置函数
• 自定义函数
  UDF：自定义标准函数（一行数据中的一列或多列为输入，结果为单一值）
  UDAF：自定义聚合函数（多行的零列到多列为输入，结果为单一值）
  UDTF：自定义表生成函数（零个或多个输入，结果为多列或多行）

内置函数
Hive提供大量内置函数供开发者使用
标准函数：
(1)字符函数

返回值	函数	描述
string	concat(string|binary A, string|binary B…)	对二进制字节码或字符串按次序进行拼接
int	instr(string str, string substr)	查找字符串str中子字符串substr出现的位置
int	length(string A)	返回字符串的长度
int	locate(string substr, string str[, int pos])	查找字符串str中的pos位置后字符串substr第一次出现的位置
string	lower(string A) /upper(string A)	将字符串A的所有字母转换成小写/大写字母
string	regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)	按正则表达式PATTERN将字符串中符合条件的部分替换成REPLACEMENT所指定的字符串
array	split(string str, string pat)	按照正则表达式pat来分割字符串str
string	substr(string|binary A, int start, int len) substring(string|binary A, int start, int len)	对字符串A,从start位置开始截取长度为len的字符串并返回
string	trim(string A)	将字符串A前后出现的空格去掉
map	str_to_map(text[, delimiter1, delimiter2])	将字符串str按照指定分隔符转换成Map
binary	encode(string src, string charset)	用指定字符集charset将字符串编码成二进制值

(2)类型转换函数

返回值	类型转换函数	描述
“type”	cast(expr as <type>)	将expr转换成type类型 如：cast(“1” as BIGINT) 将字符串1转换成了BIGINT类型
binary	binary(string|binary)	将输入的值转换成二进制

(3)数学函数

返回值	数学函数	描述
DOUBLE	round(DOUBLE a)	返回对a四舍五入的BIGINT值
binary	round(DOUBLE a, INT d)	返回对a四舍五入并保留d位小数位的值
BIGINT	floor(DOUBLE a)	向下取整，如：6.10->6 -3.4->-4
DOUBLE	rand(INT seed)	返回一个DOUBLE型随机数，seed是随机因子
DOUBLE	power(DOUBLE a, DOUBLE p)	计算a的p次幂
DOUBLE	abs(DOUBLE a)	计算a的绝对值

(4)日期函数

返回值	函数	描述
string	from_unixtime(bigint unixtime[, string format])	将时间戳转换成format格式
int	unix_timestamp()	获取本地时区下的时间戳
bigint	unix_timestamp(string date)	将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳
string	to_date(string timestamp)	返回时间字符串的日期部分
int	year(string date)，类似的有： month/day/hour/minute/second/weekofyear	返回时间字符串的年份部分 返回月/天/时/分/秒/第几周
int	datediff(string enddate, string startdate)	计算开始时间到结束时间相差的天数
string	date_add(string startdate, int days)	从开始时间startdate加上days
string	date_sub(string startdate, int days)	从开始时间startdate减去days
date	current_date	返回当前时间的日期
timestamp	current_timestamp	返回当前时间戳
string	date_format(date/timestamp/string ts, string fmt)	按指定格式返回时间date 如：date_format(“2016-06-22”,“MM-dd”)=06-22

(5)集合函数

返回值	函数	描述
int	size(Map<K.V>)	返回map中键值对个数
int	size(Array)	返回数组的元素数量
array	map_keys(Map<K.V>)	返回map中的所有key
array	map_values(Map<K.V>)	返回map中的所有value
boolean	array_contains(Array, value)	如该数组Array包含value返回true。，否则返回false
array	sort_array(Array)	对数组进行排序

(6)条件函数

返回值	函数	描述
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull
T	nvl(T value, T default_value)	value为NULL返回default_value,否则返回value
T	COALESCE(T v1, T v2, …)	返回第一非null的值，如果全部都为NULL就返回NULL
T	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	如果a=b就返回c,a=d就返回e，否则返回f
T	CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END	如果a=ture就返回b,c= ture就返回d,否则返回e
boolean	isnull( a )	如果a为null就返回true，否则返回false
boolean	isnotnull ( a )	如果a为非null就返回true，否则返回false

聚合函数
count、sum、max、min、avg等
表生成函数：输出可以作为表使用

返回值	函数	描述
N rows	explode(array)	对于array中的每个元素生成一行且包含该元素
N rows	explode(MAP)	每行对应每个map键值对 其中一个字段是map的键，另一个字段是map的值
N rows	posexplode(ARRAY)	与explode类似，不同的是还返回各元素在数组中的位置
N rows	stack(INT n, v_1, v_2, …, v_k)	把k列转换成n行，每行有k/n个字段，其中n必须是常数
tuple	json_tuple(jsonStr, k1, k2, …)	从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值

查看函数用法的命令:

desc function 函数名;

二.Hive UDF开发流程

1)继承UDF类或GenericUDF类 2)重写evaluate()方法并实现函数逻辑 3)编译打包为jar文件 4)上传到正确的HDFS路径或linux本地路径 5)使用jar创建临时/永久函数 6)调用函数 临时函数:临时创建,退出终端后函数消失,在函数的有效期内,所有的数据库都可以使用; 永久函数:永久性使用,需要将jar包放到hdfs上,切换到其他数据库不可用; 练习请看如下链接:

三.Hive事务

事务（Transaction ）指一组单元化操作，这些操作要么都执行，要么都不执行 ACID特性

• Atomicity：原子性- Consistency：一致性- Isolation：隔离性- Durability：持久性
  Hive事务的特点和局限 V0.14版本开始支持行级事务
• 支持INSERT、DELETE、UPDATE(v2.2.0开始支持Merge)- 文件格式只支持ORC
  局限
• 表必须是bucketed表- 需要消耗额外的时间、资源和空间- 不支持开始、提交、回滚、桶或分区列上的更新- 锁可以为共享锁或排它锁(串联的而不是并发)- 不允许从一个非ACID连接读写ACID表- 使用较少
  Hive事务的开启和设置
• 通过Hive命令行方式设置,当前session有效- 通过配置文件设置，全局有效- 通过UI工具（如Ambari)设置

-- 通过命令行方式开启事务
set hive.support.concurrency = true;
set hive.enforce.bucketing = true;
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.compactor.initiator.on = true;
set hive.compactor.worker.threads = 1; 

-- 通过配置文件hive-site.xml
<property>
<name>hive.support.concurrency</name>
<value>true</value
 </property>
 <property>
<name>hive.txn.manager</name><value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>

Hive PLSQL：Hive存储过程（v2.0之后）

• 支持SparkSQL和Impala- 兼容Oracle、DB2、MySQL、TSQL标准- 使将现有的过程迁移到Hive变得简单和高效- 使编写UDF不需要Java技能- 它的性能比Java UDF稍微慢一些- 功能较新
  在Hive2 bin目录下运行./hplsql

./hplsql -f plsql_demo.pl
RETURNS STRING 
BEGIN RETURN 'Hello, ' || text || '!'; 
END;
Print hello(' word') 

CREATE PROCEDURE getCount()
BEGIN DECLARE cnt INT = 0;	
SELECT COUNT(*) INTO cnt FROM employee;
PRINT 'Users cnt: ' || cnt;
END;
call getCount();

四. Hive性能调优

1.Hive性能调优工具 - EXPLAIN

EXPLAIN：显示查询语句的执行计划，但不运行 语法:

EXPLAIN [EXTENDED|DEPENDENCY|AUTHORIZATION] hive_query

• EXTENDED:提供执行计划关于操作的额外信息，比如文件路径- DEPENDENCY:提供JSON格式输出，包括查询所依赖的表和分区列表- AUTHORIZATION:列出所有需要授权的实体，包括查询的输入输出和授权失败 通过工具生成可视化执行计划

2.Hive性能调优工具 - ANALYZE

ANALYZE：分析表数据，用于执行计划选择的参考

• 收集表的统计信息，如行数、最大值等- 使用时调用该信息加速查询 语法:

ANALYZE TABLE employee COMPUTE STATISTICS; 

ANALYZE TABLE employee_partitioned 
PARTITION(year=2014, month=12) COMPUTE STATISTICS;

ANALYZE TABLE employee_id COMPUTE STATISTICS 
FOR COLUMNS employee_id;

3.Hive优化设计

• 使用分区表、桶表- 使用适当的文件格式，如orc, avro, parquet- 使用适当的压缩格式，如snappy- 考虑数据本地化 - 增加一些副本- 避免小文件- 使用Tez引擎代替MapReduce- 使用Hive LLAP(在内存中读取缓存)- 考虑在不需要时关闭并发

4.Job优化 - 本地模式运行

Hive支持将作业自动转换为本地模式运行

• 当要处理的数据很小时，完全分布式模式的启动时间比作业处理时间要长

-- 通过以下设置开启本地模式
SET hive.exec.mode.local.auto=true; --default false 
SET hive.exec.mode.local.auto.inputbytes.max=50000000; 
SET hive.exec.mode.local.auto.input.files.max=5; --default 4

Job必须满足以下条件才能在本地模式下运行

• Job总输入大小小于 hive.exec.mode.local.auto. inputbytes.max- map任务总数小于 hive.exec.mode.local.auto. input.files.max- 所需的Reduce任务总数为1或0

5.Job优化 - JVM重用（JVM Reuse）

通过JVM重用减少JVM启动的消耗

• 默认每个Map或Reduce启动一个新的JVM- Map或Reduce运行时间很短时，JVM启动过程占很大开销- 通过共享JVM来重用JVM，以串行方式运行MapReduce Job- 适用于同一个Job中的Map或Reduce任务- 对于不同Job的任务，总是在独立的JVM中运行

-- 通过以下设置开启JVM重用
set mapred.job.reuse.jvm.num.tasks = 5;  -- 默认值为1

6.Job优化 - 并行执行

并行执行可提高集群利用率

• Hive查询通常被转换成许多按默认顺序执行的阶段- 这些阶段并不总是相互依赖的- 它们可以并行运行以节省总体作业运行时间- 如果集群的利用率已经很高，并行执行帮助不大

-- 通过以下设置开启并行执行
SET hive.exec.parallel=true;  -- default false 
SET hive.exec.parallel.thread.number=16;  -- default 8,定义并行运行的最大数量

7.查询优化

自动启动Map端Join 防止数据倾斜set hive.optimize.skewjoin=true; 启用CBO(Cost based Optimizer)

set hive.cbo.enable=true; 
set hive.compute.query.using.stats=true; 
set hive.stats.fetch.column.stats=true; 
set hive.stats.fetch.partition.stats=true;	

启动Vectorization（矢量化）

set hive.vectorized.execution.enabled = true; 
set hive.vectorized.execution.reduce.enabled = true;

使用CTE、临时表、窗口函数等

8.数据倾斜

某些节点计算的能力较差或者由于此节点需要计算的数据比较多，导致数据倾斜 在hive中产生数据倾斜的场景：

• 1.group by产生数据倾斜–>开启Map端聚合参数设置- 2.大表和小表进行join操作–>使用mapjoin 将小表加载到内存- 3.空值产生的数据倾斜–>1)id为空的不参与关联2)给空值分配随机的key值- 4.小文件过多或文件过于复杂–>合理设置map和reduce数

9.压缩算法

减少传输数据量，会极大提升MapReduce性能:采用数据压缩是减少数据量的很好的方式 常用压缩方法对比如下表:

压缩方式	可分割	压缩后大小	压缩解压速度
gzip	否	中	中
lzo	是	大	快
snappy	否	大	快
bzip2	是	小	慢

hive中的日期函数：

日期函数UNIX时间戳转日期函数: from_unixtime

语法:from_unixtime(bigint unixtime[, string format])
返回值: string
说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
举例：

hive> select from_unixtime(1323308943,‘yyyyMMdd’) from dual;
20111208

获取当前UNIX时间戳函数:unix_timestamp

语法: unix_timestamp()
返回值: bigint
说明: 获得当前时区的UNIX时间戳
举例：

hive> select unix_timestamp() from dual;
1323309615

日期转UNIX时间戳函数:unix_timestamp

语法:unix_timestamp(string date)
返回值: bigint
说明: 转换格式为“yyyy-MM-dd HH:mm:ss“的日期到UNIX时间戳。如果转化失败，则返回0。
举例：

hive> select unix_timestamp(‘2011-12-07 13:01:03’) from dual;
1323234063

指定格式日期转UNIX时间戳函数:unix_timestamp

语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
举例：

hive> select unix_timestamp(‘20111207 13:01:03’,‘yyyyMMdd HH:mm:ss’) from dual;
1323234063

日期时间转日期函数:to_date

语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。
举例：

hive> select to_date(‘2011-12-08 10:03:01’) from dual;
2011-12-08

日期转年函数:year

语法: year(string date)
返回值: int
说明: 返回日期中的年。
举例：

hive> select year(‘2011-12-08 10:03:01’) from dual;
2011
hive> select year(‘2012-12-08’) from dual;
2012

日期转月函数:month

语法: month (string date)
返回值: int
说明: 返回日期中的月份。
举例：

hive> select month(‘2011-12-08 10:03:01’) from dual;
12
hive> select month(‘2011-08-08’) from dual;
8

日期转天函数:day

语法: day (string date)
返回值: int
说明: 返回日期中的天。
举例：

hive> select day(‘2011-12-08 10:03:01’) from dual;
8
hive> select day(‘2011-12-24’) from dual;
24

日期转小时函数:hour

语法: hour (string date)
返回值: int
说明: 返回日期中的小时。
举例：

hive> select hour(‘2011-12-08 10:03:01’) from dual;
10

日期转分钟函数:minute

语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。
举例：

hive> select minute(‘2011-12-08 10:03:01’) from dual;
3

日期转秒函数:second

语法: second (string date)
返回值: int
说明: 返回日期中的秒。
举例：

hive> select second(‘2011-12-08 10:03:01’) from dual;
1

日期转周函数:weekofyear

语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。
举例：

hive> select weekofyear(‘2011-12-08 10:03:01’) from dual;
49

日期比较函数:datediff

语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
举例：

hive> select datediff(‘2012-12-08’,‘2012-05-09’) from dual;
213

日期增加函数:date_add

语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
举例：

hive> select date_add(‘2012-12-08’,10) from dual;
2012-12-18

日期减少函数:date_sub

语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
举例：

hive> select date_sub(‘2012-12-08’,10) from dual;
2012-11-28