hive 补0函数 hive空值处理函数

转载

mob6454cc6d5f87 2023-07-14 21:33:59

文章标签 hive 补0函数字符串 hive Hive 文章分类 代码人生

最近有重新回看了《Hadoop权威指南》，又想起来总结一些博文了，所以针对Hive常用函数和性能调优又仔细查了很多资料，写了这篇文章，还是一样，感谢各位在网络上的分享！！！

一.Hive常用函数

1.时间处理函数

函数名称	返回值	作用	备注
current_date()	string	返回当天时间	返回时间格式‘yyyy-MM-dd’
unix_timestamp()	bigint	返回当前时区的时间戳
from_unixtime(bigint unixtime[, string format])	string	返回Unix时间戳转日期格式	可以通过format定义返回结果样式
unix_timestamp(string date,string pattern)	bigint	返回制定格式转化至时间戳	转化失败返回0
to_date(string date)	string	返回时间字段中的日期部分	格式错误返回null
year(string date)	int	返回时间字段中的‘年’部分	month,day,hour,minute,second使用方法相同
datediff(string enddate,string startdate)	int	返回（结束日期-开始日期）的数值	仅返回时间上的天数差距

2.类型转换函数

隐式类型转换：所有整数类型都可以隐式转换为一个范围更广的类型。所有整数类型 + float + String都可以转换为Double类型。时间戳和日期可以隐式地转换成文本类型。BOOLEAN不能做任何的类型转换。

显式类型转换：

cast( 表达式a as 数据类型b )：可以将表达式a的数据类型显式的转化为数据类型b，并且转化失败会返回null。

3.条件判断函数

函数名称	返回值	作用	备注
if(boolean condition, T valueTrue, T valueFalse)	T	判断condition的正误正确则返回valueTrue，错误则返回valueFalse
coalesce(T value1,T value2,...)	T	返回参数列表中第一个非空数值	如果所有数值均为null，则返回null
case when conditionA then answerB else answerC end	T	如果conditionA成立，则返回answerB否则返回answerC	可以连续 [when a then b] 对多种情况进行判断和结果返回

4.空值判断函数

hive表中默认将null存储为\N。可以使用is null和is not null来判断某值是否是空值。可以结合条件判断函数使用（ if( valueA is null ,'true' , 'false' ) ）。

也可以直接使用nvl()函数对数值列进行处理，同理还可以使用条件判断函数中的coalesce()函数。

nvl( 表达式a , 表达式b )：判断表达式a是否为null，若是则返回表达b，若不是则返回表达式a。等价于 if( 表达式a is null ,表达式b , 表达式a ) 。

if函数和nvl函数的区别：两者的区别在于if函数的参数值类型可以不同，但是nvl函数的参数类型必须为同类型。

在处理空字符串 '' 时，可以使用 a = '' 或者 a <> ''进行判断。

5.字符串处理函数

函数名称	返回值	作用	备注
length(string a)	int	返回字符串a的长度
reverse(string a)	string	返回字符串a的字符串结果翻转
concat(string a, string b,…)	string	返回字符串a和字符串b和...的字符串拼接结果
concat_ws(string sepString, string a, string b,…)	string	返回字符串a和字符串b和...的字符串拼接结果，且字符串之间使用sepString连接
substr(string a,int start[,int end])	string	返回字符串a从start位置开始到末尾【到end】截取出来的字符串
upper(string a)/ucase(string a)	string	返回字符串a的大写格式	lower(string a)和lcase(string a)返回字符串a的小写格式
trim(string a)	string	返回字符串a去除前后两端空格的字符串	ltrim(string a)和rtrim(string a)单独去除左端/右端空格
regexp_replace(string a, string pattern, string b)	string	将字符串a中符合正则表达式字符串pattern的内容替换为字符串b的内容	正则表达式pattern字符串需要注意转义
regexp_extract(string a, string pattern, int index)	string	将字符串a按照正则表达式字符串pattern的内容拆分，并且返回下标为index的内容	类似于replace()函数中的小括号和$取值的方式，下标0为原始字符串
split(string a, string b)	array	将字符串a按照字符串b进行分割，返回字符串数组	注意转义
instr(string a, string b)	int	若字符串b在字符串a中存在则返回具体位置(int)，不存在则返回0
parse_url(string a, string partUrl[, string key])	string	解析url字符串a，并且根据具体所需partUrl返回对应位置数据信息。也可通过使用‘QUERY’和具体的key返回指定的参数数值	partUrl可使用的参数包括： [HOST(域名),PATH(路径),QUERY(参数),REF(定位), PROTOCOL(协议)FILE(路径+参数),AUTHORITY(域名),USERINFO]
parse_url_tuple(string a, string partUrl[, string key])	string	解析url字符串a，用法与parse_url类似，可以同时返回多个key的value值
get_json_object(string a string path)	string	解析json字符串a，并且根据path获取json字符串中对应key的value值	字符串a必须符合json格式，若字符串无效则返回null
json_tuple(string a, string keyValue,...)	string	解析json字符串a，并且根据keyValue取得对应key的value值	get_json_object可以处理更加复杂的json，json_tuple可以处理一次想取出多个key的场景
lpad(string a, int len, string b)	string	将字符串a左侧用字符串b补全至长度为len	rpad(string a, int len, string b)作用于右侧
find_in_set(string a, string strList)	int	返回字符串a在strList中出现的位置（strList必须是用逗号分隔的字符串）	如果没有找到对应字符，则返回0

6.其他类型处理函数

函数名称	返回值	作用	备注
explode()		用于array类型数据时，array中每个元素生成一行用于map类型数据时，map中每个键值对一行，key/value各自一列	不能关联原有的表中的其他字段。不能与group by、cluster by、distribute by、sort by联用。不能进行UDTF嵌套。不允许选择其他表达式。
array_contains(array, T b)	T/F	用于判断array类型数据中是否存在T类型的b	可用于where条件查询语句
lateral view explode(array)		用于将单行array类型数据拆分为分开的多行数据结果集 array位置为想拆分的数组字段，单独拆分一列，相当于是该列产生的结果集与原表进行笛卡尔积，也可以同时拆分多列
collect_list(arrayName)		将分组中的某列转为一个数组返回，不去重
collect_set(arrayName)		将分组中的某列转为一个数组返回，去重

7.分析窗口函数

用于分区排序（ over( partition by ) ），动态group by，计算topN（ row_number() ）等场景。

定义window子句（eg. : rows between current row and 2 preceding），如果不指定ROWS BETWEEN，默认是从起点到当前行：

2 PRECEDING：前两行

2 FOLLOWING：后两行

CURRENT ROW：当前行

UNBOUNDED：起点

UNBOUNDED PRECEDING：从前面的起点

UNBOUNDED FOLLOWING：从后边的终点

函数名称	返回值	作用	备注
rank()	序列函数	并列跳跃排名：在返回排名结果字段中并列名次的下一名将为并列数量+并列名次（如并列第一两名，则排行为1，1，3）不支持window子句
dense_rank()	序列函数	并列不跳跃排名：在返回排名结果字段中并列名次的下一名将为正常名次（如并列第一两名，则排行为1，1，2）不支持window子句
row_number()	序列函数	顺序排序：在返回排名结果字段中无视并列名次（如并列第一两名，则排行为1，2，3）不支持window子句
first_value()	取值函数	返回当前排序分组内第一个数据的值（也可通过改变排序次序获取最大值或最小值）
last_value()	取值函数	返回当前排序分组内最后一个数据的值（也可通过改变排序次序获取最大值或最小值）
ntile(int a)	序列函数	将返回结果分为a份，可用于取百分比数量数据
lag(string lineName[, int a])	上提函数	用于统计窗口内往上第n行值第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
lead(string lineName[, int a])	下沉函数	作用同上，往下第n行值，其余相同
cume_dist()	序列函数	用于统计指定值占总数的百分比

二.Hive性能调优

1.Hive配置调优（https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties）

所有黑体+下划线一般都不会选择默认值，用于调优，而所有正常字体一般可选择默认值。

配置语句	默认值	配置作用	备注
hive.auto.convert.join	false	是否启用Hive基于输入文件大小的，将普通join转换为mapjoin的优化 MapJoin是常用的优化操作，适用于小表JOIN大表的场景，由于表的JOIN操作是在Map端且在内存进行的，所以其并不需要启动Reduce任务也就不需要经过shuffle阶段，从而能在一定程度上节省资源提高JOIN效率	该参数为true时，Hive自动对左边的表统计量，如果是小表就加入内存，即对小表使用mapjoin
hive.mapjoin.smalltable.filesize	25000000	小表的输入文件大小的阈值（单位：字节）；如果文件大小小于此阈值，它将尝试将普通join转换为mapjoin	该参数作为大表，小表判断的阈值，如果表的大小小于该值则会被加载到内存中运行
hive.auto.convert.join.noconditionaltask	true	是否启用Hive基于输入文件大小的，将普通join转换为mapjoin的优化	将普通join转换为mapjoin时，是否将多个mapjoin转换为一个mapjoin
hive.auto.convert.join.noconditionaltask.size	10000000	将普通join转换为mapjoin时，最多可以将最大值为多少的小表转换为一个mapjoin	多个mapjoin转换为1个时，所有小表的文件大小总和的最大值
hive.exec.parallel	false	是否并行执行jobs，适用于可以并行执行的MapReduce job
hive.exec.parallel.thread.number	8	设定最多可以并行执行多少个job	代表一次 SQL 计算中允许并行执行的 job 个数的最大值
hive.execution.engine	mr（2.0.0后弃用）	选择执行引擎。可选项有MapReduce，Tez和Spark	当执行引擎发生改变时，可以通过对对应引擎进行调优达到对hive语句执行的优化
mapred.reduce.tasks	-1	默认每一个job对应的reducer的数量，通常设定为可用主机的近似值
hive.exec.reducers.bytes.per.reducer	256000000	每一个reducer处理的数据量大小
hive.exec.reducers.max	1009	每一个job最大可以使用的reducer数量
hive.map.aggr	true	在使用group by查询语句时，是否使用map端聚合
hive.groupby.mapaggr.checkinterval	100000	在map端group by聚合时处理的数据行数
hive.map.aggr.hash.min.reduction=0.5;	0.5	进行聚合的最小比例，如果聚合后数据量和处理的数据行数的比值大于该数字，则将关闭聚合。设置为1可以确保从未关闭聚合
hive.map.aggr.hash.percentmemory	0.3	当使用map端group by聚合时，map端聚合可使用的内存上限
hive.groupby.skewindata	false	在使用group by时是否进行数据倾斜优化