函数类型

  • 相关用法
  • Hive 数学函数
  • Hive 集合函数
  • Hive 类型转换
  • Hive 日期函数
  • 日期函数例题
  • Hive 条件函数
  • Hive 字符串函数
  • md5单向加密
  • Hive 聚合函数
  • Hive 表生成函数
  • Hive 窗口函数
  • Hive 分析函数
  • 调优


相关用法

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

注：

  1. 如果将浮点型的数据转换成int类型的，内部操作是通过round()或者floor()函数来实现的，而不是通过cast实现。
  2. 对于BINARY类型的数据，只能将BINARY类型的数据转换成STRING类型。如果你确信BINARY类型数据是一个数字类型(a number)，这时候你可以利用嵌套的cast操作。
  3. 对于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为相同字段，且排序只能升序