[ 知识点 ] Hive函数名及释义！

最新推荐文章于 2023-05-10 14:41:46 发布

景明。

最新推荐文章于 2023-05-10 14:41:46 发布

阅读量446

点赞数 1

分类专栏： Hive 文章标签： hive sql 数据库大数据

本文链接：https://blog.csdn.net/weixin_51184877/article/details/109702234

版权

Hive 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

函数类型

show functions	显示Hive下内置所有函数
desc function extended 函数名	查看函数的详细使用方法

Hive 数学函数

函数名	释义	示例
log(double x , double y)	x为底的y的对数	select log(2,8); ⇨ 3.0
pow(x,y)	返回x的y次方的值	select pow(4,0.5); ⇨ 2 select pow(4,2); ⇨ 16
conv(bigint/string x,int a,int b)	返回数值x从a进制转换为b进制的字符串	select conv(‘100101’,2,10); ⇨ 37 select conv(‘100101’,2,16); ⇨ 25
pmod(int/double x,int/double y)	返回x余y后的结果（int/double）	select pmod(5,2); ⇨ 1
hex(string x) unhex(string x)	返回字符串x的正反16进制转化	select hex(‘abc’); ⇨ 616263 select unhex(‘616263’); ⇨ abc
positive(int/double x)	返回x	select positive(1); ⇨ 1
negative(int/double x)	返回x的相反数	select positive(1); ⇨ -1
radians(int/double x)	返回x度对应的弧度值	select radians(30); ⇨ 0.5235987755982988
degrees(int/double x)	返回x弧度对应的角度值	select degrees(0.5); ⇨ 28.64788975654116
sign(x)	返回x的符号，正数则返回1.0，负数返回-1.0，否则返回0.0	select sign(-15); ⇨ -1.0 select sign(15); ⇨ 1.0 select sign(0); ⇨ 0
pi()	返回圆周率	select pi(); ⇨ 3.141592653589793
e()	返回e的值	select e(); ⇨ 2.718281828459045
greatest(T…vs)	横向求最大值，不支持数组	select greatest(1,2,5,3,1); ⇨ 5
least(T…vs)	横向求最小值，不支持数组	select greatest(1,2,5,3,1); ⇨ 1

Hive 集合函数

函数名	释义	示例
size(Map<K,V>/Array)	返回键值、数组的大小，其中Map<K,V>要为双数	select size(map(‘a’,‘1’,‘b’,‘2’,‘c’,‘3’)); ⇨ 3 select size(array(1,2,3)); ⇨ 3
map_keys(Map<K,V>)	返回map中的所有key	select map_keys(map(‘a’,‘1’,‘b’,‘2’,‘c’,‘3’)); ⇨ [“a”,“b”,“c”]
map_values(Map<K,V>)	返回map中所有的value	select map_values(map(‘a’,‘1’,‘b’,‘2’,‘c’,‘3’)); ⇨ [“1”,“2”,“3”]
array_contains(Arrat,T)	如果数组包含值，则返回TRUE	select array_contains(array(1,2,3),2); ⇨ true
sort_array(Array)	根据数组元素的自然顺序按升序对输入数组进行排序	select sort_array(array(‘d’,‘b’,‘c’,‘a’)); ⇨ [“a”,“b”,“c”,“d”]

Hive 类型转换

Hive类型转换	释义	示例
cast(expr as)	将一个类型的数据转换成另一个数据类型	select cast(‘abc123’ as binary); ⇨ abc123 select cast(“1” as bigint); ⇨ 1

注：

如果将浮点型的数据转换成int类型的，内部操作是通过round()或者floor()函数来实现的，而不是通过cast实现。
对于BINARY类型的数据，只能将BINARY类型的数据转换成STRING类型。如果你确信BINARY类型数据是一个数字类型(a number)，这时候你可以利用嵌套的cast操作。
对于Date类型的数据，只能在Date、Timestamp以及String之间进行转换。

Hive 日期函数

函数名	释义	示例
from_unixtime(bigint unixtime, string format)	将时间的秒值转成format格式	select from_unixtime(1564648646,“yyyy-MM-dd”); ⇨ 2019-08-01
date_format(date/timestamp/string date,string format)	提取所需部分的值	select date_format(‘2009-03-12’,‘MM’); ⇨ 03
current_date()	返回当前日期	select current_date(); ⇨ 2020-12-15
to_date(string timestamp)	返回时间字符串的日期部分	select to_date(‘2020-03-21’); ⇨ 2020-03-21
current_timestamp()	返回当前的日期到秒位	select current_timestamp(); ⇨ 2020-12-15 12:09:05.733
unix_timestamp()	获取本地时区下的时间戳	select unix_timestamp(); ⇨ 1608005566
unix_timestamp(string date)	将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳	select unix_timestamp(‘2009-03-20 11:30:01’); ⇨ 1237519801
unix_timestamp(string date, string format_pattern)	将指定时间字符串格式字符串转换成Unix时间戳，如果格式不对返回0	select unix_timestamp(‘2009-03-20’, ‘yyyy-MM-dd’); ⇨ 1237532400
date_add(string date/timestamp,int days)	在日期后面增加天数	select date_add(‘2020-02-03’,1); ⇨ 2020-02-04
add_months(string date/timestamp,±2)	在日期后面增加月数	select add_months(‘2020-02-03’,3); ⇨ 2020-05-03
next_day(string date/timestamp,‘Mo/Tu/WE/Th/Fr/Sa/Su’)	返回日期之后的下一个Mo/Tu/WE/Th/Fr/Sa/Su	select next_day(current_date(),‘Mo’); ⇨ 2020-12-21
last_day(string date/timestamp)	返回日期该月的最后一天	select last_day(‘2020-03-01’); ⇨ 2020-03-31
trunc(string date/timestamp,‘YY/MM’)	返回日期的本年/本月的第一天	select trunc(‘2020-03-15’,‘MM’); ⇨ 2020-03-01
datediff(string enddate, string startdate)	计算开始时间startdate到结束时间enddate相差的天数	select datediff(‘2020-03-01’, ‘2020-02-01’); ⇨ 29
months_between(string bigdate,string smalldate)	两日期的月数之差	select months_between(‘2020-03-01’, ‘2020-02-01’); ⇨ 1.0

日期函数例题

日期函数例题	答案
返回日期的本季度第一天	select concat_ws(’-’,cast(year(current_date()) as string),cast(ceil(month(current_date())/3)*3-2 as string),‘01’);
返回日期的本周的第一天	select date_add(next_day(current_date(),‘SU’),-7);

Hive 条件函数

函数名	释义
if(boolean Condition, T vTrue, T vFalse)	如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull
nvl(T value, T default_value)	如果value值为NULL就返回default_value,否则返回value
coalesce(T v1, T v2, …)	返回第一非null的值，如果全部都为NULL就返回NULL
case A when B then C [when D then E]* [else F] end	如果A=B就返回C,A=D就返回E，否则返回f
case when A then B [when C then D]* [else E] end	如果A=ture就返回B,C= ture就返回D，否则返回E
isnull( a )	如果a为null就返回true，否则返回false
isnotnull ( a )	如果a为非null就返回true，否则返回false

Hive 字符串函数

函数名	释义	示例
ascii(string x)	返回字符串首字母的ASCII码	select ascii(‘abc’); ⇨ 97
concat(string/binary A, string/binary B…)	对二进制字节码或字符串按次序进行拼接	select concat(1,‘aa’)； ⇨ 1aa
concat_ws(string SEP, string A, string B…)	与concat()类似，但使用指定的分隔符进行分隔	select concat_ws(’-,‘1,‘aa’); ⇨ 报错 select concat_ws(’-’, ‘aa’,‘bb’); ⇨ aa-bb
concat_ws(string SEP, array)	拼接Array中的元素并用指定分隔符进行分隔	select concat_ws(’#’,array(‘aa’,‘bb’,‘cc’)); ⇨ aa#bb#cc
collect_list()	将多行某些列的多行进行不去重合并，并通过某符号进行连接，多与concat_ws并用
collect_set()	将多行某些列的多行进行去重合并，并通过某符号进行连接，多与concat_ws并用
sentences(string sentence)	字符串sentence将被转换成单词数组，不同符号结果不一样[[“hello”,“world”],[“how”,“are”,“you”]]	select sentences(‘hello,how are u’); ⇨ [[“hello”,“how”,“are”,“u”]] select sentences(‘hello!how are u’); ⇨ [[“hello”],[“how”,“are”,“u”]]
ngrams(array<array>, int N, int K, int pf)	按N个单词出现频次，倒序取TOP K	词频，连续1个单词倒序以后前2位 select ngrams(sentences(‘hello world?how are you’),1,2); ⇨ [{“ngram”:[“how”],“estfrequency”:1.0},{“ngram”:[“you”],“estfrequency”:1.0}]
context_ngrams(array<array>, array, int K, int pf)	与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列与array中指定单词之后配合出现的频次，倒序取TOP K	select context_ngrams(sentences(‘hello kb10?how are youhello world,are you ok?’),array(‘you’,null),2); ⇨ [{“ngram”:[“ok”],“estfrequency”:1.0}]
encode(string src, string charset)	使用指定的字符集charset将字符串编码成二进制值	select encode(‘我爱中华001abc你们’,‘UTF-16BE’); ⇨ br1N-SN001abcO`N�
decode(binary bin, string charset)	使用指定的字符集charset将二进制值bin解码成字符串	select decode(encode(‘我爱中华001abc你们’,‘UTF-16BE’),‘UTF-16BE’); ⇨ 我爱中华001abc你们
format_number(number x, int d)	将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数	select format_number(478493145631.2546,5); ⇨ 478,493,145,631.25460
get_json_object(string json_string, string path)	从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式	select get_json_object(’{“info”:{“city”:“nj”}}’,’$.info.city’); ⇨ nj select get_json_object(’{“info”:[“city”,“nj”]}’,’$.info[0]’); ⇨ city
in_file(string line, string path)	path中指向的文件是否包含line	select in_file(‘a’,’/root/kkk.txt’); ⇨ false
parse_url(string urlString, string partToExtract [, string keyToExtract])	返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分	select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’); ⇨ ‘facebook.com’
printf(String format, T…t)	按照printf风格格式输出字符串,格式化字符串	select printf(’%s,%d,%.2f’,‘henry’,18,23456.789); ⇨ henry,18,23456.79
like rlike	模糊匹配	select a.* from srcpart a where a.hr like ‘%2’ limit 1; select ‘fb’ rlike ‘.*’ from src limit 1;
regexp_replace(string src, string regex, string replacement)	按照Java正则表达式PATTERN将字符串src中符合条件的部分成replacement所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉	select regexp_replace(‘you me he y_ou young yuuuuuug’,‘y\\w{2,3}’,‘YOU’); ⇨ YOU me he YOU YOUg YOUuuug
regexp_extract(string src, string regex, int index)	抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串	select regexp_extract(‘namehenryokdarlingduck’,‘name(.?)ok(.?)duck’,2); ⇨ darling
split(string str, string regex)	按照正则表达式pat来分割字符串str,并将分割后的数组字符串的形式返回	select split(regexp_replace(’[“henry”,“pola”,“ariel”]’,’\\[\|\]\|"’,’’),’,’); ⇨ [“henry”,“pola”,“ariel”] select split(‘henry.chen@hotmail.com’,’\.\|@’); ⇨ [“henry”,“chen”,“hotmail”,“com”]
str_to_map(string str[,string spel1,string spel2])	将字符串str按照指定分隔符转换成Map	select str_to_map(‘name:henry,age:22,gender:male’); ⇨ {“name”:“henry”,“gender”:“male”,“age”:“22”}
initcap(string A)	将字符串A转换第一个字母大写其余字母的字符串	select initcap(‘abc’); ⇨ Abc
translate(string\|char\|varchar input, string\|char\|varchar from, string\|char\|varchar to)	将input出现在from中的字符串替换成to中的字符串	select translate(‘abcabcabaac’,‘ab’,’#’); ⇨ #c#c#**c
substr(string\|binary A, int start)	对于字符串A,从start位置开始截取字符串并返回	string
substr(string\|binary A, int start, int len) substring(string\|binary A, int start, int len)	对于二进制/字符串A,从start位置开始截取长度为length的字符串并返回	select substr(‘henry’,2); ⇨ enry
locate(string substr, string str[, int pos])	查找字符串str中的pos位置后字符串substr第一次出现的位置，pos从1开始	select locate(‘en’,‘henry’,1); ⇨ 2
instr(string str, string substr)	查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的	select instr(‘henry’,‘en’); ⇨ 2

md5单向加密

函数名	释义	示例
base64 unbase64	利用base64方法将64位的字符串转换二进制值	select base64(cast(‘henry’ as binary)); ⇨ aGVucnk= select unbase64(‘aGVucnk=’); ⇨ henry
hex unhex	返回字符串a正反16进制后的值（对称加密）	select hex(‘abc123’); ⇨ 616263313233 select unhex(‘616263313233’); ⇨ abc123
soundex(string A)	将普通字符串转换成soundex字符串	select soundex(‘bc’); ⇨ B200
leventhein(string a, string b)	计算两个字符串之间的差异大小	select levenshtein(‘kitten’, ‘sitting’); ⇨ 3

Hive 聚合函数

函数名	释义	类型
count(*)	统计总行数，包括含有NULL值的行	bigint
count(expr)	统计提供非NULL的expr表达式值的行数	int
count(DISTINCT expr[, expr…])	统计提供非NULL且去重后的expr表达式值的行数	bigint
sum(col)	表示求指定列的和	double
sum(DISTINCT col)	表示求去重后的列的和	double
avg(col)	表示求指定列的平均值	double
avg(DISTINCT col)	表示求去重后的列的平均值	double
min(col)	求指定列的最小值	double
max(col)	求指定列的最大值	double
var_pop(col)	求指定列数值的方差:离散程度	double
var_samp(col)	求指定列数值的样本方差：变异程度	double
stddev_pop(col)	求指定列数值的标准偏差	double
stddev_samp(col)	求指定列数值的样本标准偏差	double
covar_pop(col1, col2)	求指定列数值的协方差	double
covar_samp(col1, col2)	求指定列数值的样本协方差	double
corr(col1, col2)	返回两列数值的相关系数	double
percentile(BIGINT col, p)	返回col的p(0~1)%分位数	double
collect_list(col)	行转列
collect_set(col)	列转行

Hive 表生成函数

函数名	释义	示例
explode(array a)	对于a中的每个元素，将生成一行且包含该元素
explode(ARRAY)	每行对应数组中的一个元素	select name,city from employee_id lateral view explode(cities) ct as city;
explode(MAP<K,V>)	每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值	select name,pos,score from employee_id lateral view explode(scores) st as pos,score;
posexplode(ARRAY)	与explode类似，不同的是还返回各元素在数组中的位置	select posexplode(array(‘aa’,‘bb’,‘cc’));
stack(INT n, v_1, v_2, …, v_k)	把M列转换成N行，每行有M/N个字段，其中n必须是个常数	select stack(2,‘aa’,‘bb’,‘cc’,‘dd’); ⇨ ‘aa’,‘bb’ ⇨ ‘cc’,‘dd’
json_tuple(jsonStr, k1, k2, …)	从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值
parse_url_tuple(url, p1, p2, …)	返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,…是要抽取的部分	这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:
inline(ARRAY<STRUCT[,STRUCT]>)	将结构体数组提取出来并插入到表中	select inline(array(struct(‘aa’,‘bb’,‘cc’),struct(‘ee’,‘dd’,‘ff’)));

Hive 窗口函数

#如需控制范围需要指定 ... over(... rows between ??? and ???)
	
		FIRST_VALUE(col)：取分组内排序后，截止到当前行，第一个值
		LAST_VALUE(col)： 取分组内排序后，截止到当前行，最后一个值
		lead(col,n,DEFAULT) ：用于统计窗口内当前行往后第n行的值。
			第一个参数为列名，
			第二个参数为往下第n行（可选，默认为1），
			第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
		lag(col,n,DEFAULT) ：用于统计窗口内当前行0往前第n行值。
			第一个参数为列名，
			第二个参数为往上第n行（可选，默认为1），
			第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
		# OVER从句
		 over(partition by ??? order by ??? rows|range between ??? and ???)
		aggr/analyze/window over(partition by f1 order by f2/distribute by f1 sort by f2
							rows between ? and ?) alias
		#-----mapred优化distribute by 
		set mapreduce.job.reduce=partition_number;
		set hive.map.aggr=true
		
		#----group by优化
		#----不可以和over从句配合
		#----group by必须聚合
		set hive.groupby.skewindata=true
		
		#---分区表
		partition by  + order by
		
		distribute by + sort by
		
		cluster by=>distribute by + sort by
		
		
		
		
		partition by 分组(区)
		order by 排序
			有partition by：分组内排序，否则全局排序
		rows|range between ??? and ???
			有partition by：
				unbounded preceding:区内第一行
				unbounded following:区内最后一行
			无partition by：
				unbounded preceding:表中第一行
				unbounded following:表中最后一行
		1、使用标准的聚合函数COUNT、SUM、MIN、MAX、AVG
		2、使用partition by语句，使用一个或者多个原始数据类型的列
		3、使用partition by与order by语句，使用一个或者多个数据类型的分区或者排序列
		4、使用窗口规范，窗口规范支持以下格式：

		(rows | range) between (unbounded | [num]) preceding and ([num] preceding | current row | (unbounded | [num]) following)
		(rows | range) between current row and (current row | (unbounded | [num]) following)
		(rows | range) between [num] following and (unbounded | [num]) following
		5、当order by后面缺少窗口从句条件，窗口规范默认是 range between unbounded preceding and current row.

		6、当order by和窗口从句都缺失, 窗口规范默认是 row between unbounded preceding and unbounded following.
		-----------------------窗口函数的数据表查询例子----------------------------------------
		select orderdate,name,cost,
		sum(cost) over() s1,全表相加
		sum(cost) over(partition by name)s2,全区相加
		sum(cost) over(partition by name order by cost) s3,区内第一行到当前行相加
		sum(cost) over(partition by name order by cost rows between 2 preceding and current row) s4,区内当前行前一行到当前行相加
		sum(cost) over(partition by name order by cost rows between 1 preceding and 1 following) s5,区内当前行前一行到当前行后一行相加
		sum(cost) over(partition by name order by cost rows between current row and 2 following) s6,区内当前行到当前行后2行相加
		sum(cost) over(partition by name order by cost rows between 3 preceding and 1 preceding) s7区内当前行前三行到当前行前1行相加
		from platorder

		7、over从句支持以下函数， 但是并不支持和窗口一起使用它们。

		8、Ranking函数: Rank, NTile, DenseRank, CumeDist, PercentRank.
		Lead 和 Lag 函数.

Hive 分析函数

函数名	释义	示例
row_number()	从1开始，按照顺序，生成分组内记录的序列	按照pv降序排列，生成分组内每天的pv名次
rank()	生成数据项在分组中的排名，排名相等会在名次中留下空位	1,2,2,4
dense_rank()	生成数据项在分组中的排名，排名相等会在名次中不会留下空位	1,2,2,3
CUME_DIST(col)	小于等于当前值的行数	统计小于等于当前薪水的人数，所占总人数的比例
PERCENT_RANK(col)	分组内当前行的RANK值-1/分组内总行数-1
NTILE(n)	用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布,NTILE不支持ROWS between	NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS between 3 preceding AND CURRENT ROW)

调优

set hive.map.aggr=true
set hive.groupby.skewindata = true
#如何保证分布式
partition by	#决定numberReduceTasks,最佳配备为分区表
#静态分区
#动态分区
		#建原始教据源表I
		#建表分区方式相同
		#是否开启动态分区，默认是false，所以必须要设置成true
		SET hive.exec.dynamic.partition=true ;
		#动态分区模式，默认为strict，表示表中分区字段必须有一个是静态的分区值，nostrict表示允许所有字段都可以作为动态分区
		SET hive.exec.dynamic.partition.mode=nonstrict;
		#动态新增数据
		insert into PARTITTON_YTABLE partition (PARTITIONM_FIELD...)
		select f1....,PARTITTON_FIELD... from DATA_TABLE;

order by			#只作用于一个reducer端，全局排序,数据量大效率低
distribute by		#决定map端如何分发数据给reducer端 Partitioner:哪一行进哪个reducer
sort by				#每个reducer单独排序
group by			#功能类似于distribute by类似，但之后必须进行聚合操作
cluster by			#当distribute by和sort by为相同字段，且排序只能升序