hive 内置函數

參考連結： Hive常用字元串函數

                           Hive中複合資料結構簡介以及一些函數的用法說明

數學函數

下面表格中的函數為Hive的内置數學函數，大部分當參數為NULL時傳回NULL。

傳回類型	函數名	描述
bigint	round(double a)	傳回a經過四舍五入的 BIGINT  值
double	round(double a, int d)	傳回a經過四舍五入，保留d位小數的DOUBLE 值
bigint	floor(double a)	傳回小于等于a的最大BIGINT 值
bigint	ceil(double a), ceiling(double a)	傳回大于等于a的最小BIGINT 值
double	rand(), rand(int seed)	傳回一個均勻分布在0到1之間的随機數，指定seed将會確定産生的随機數序列是确定的
double	exp(double a), exp(decimal a)	傳回ea
double	ln(double a), ln(decimal a)	傳回a的自然對數值
double	log10(double a), log10(decimal a)	傳回a的以10為底的對數值
double	log2(double a), log2(decimal a)	傳回a的以2為底的對數值
double	log(double base, double a)	傳回a以base為底的對數值，除了DOUBLE 類型，還可以是DECIMAL 類型，并且兩種類型可以混合使用
double	pow(double a, double p), power(double a, double p)	傳回ap
double	sqrt(double a), sqrt(decimal a)	傳回a的平方根
string	bin(bigint a)	傳回a的二進制格式
string	hex(bigint a) hex(string a) hex(binary a)	如果參數為整數或者二進制，傳回十六進制的字元串，如果參數為字元串，該函數将每個字元轉換為十六進制表示，并傳回結果字元串
binary	unhex(string a)	将每對字元解釋為十六進制并轉換為該十六進制的位元組表示
string	conv(bigint num, int from_base, int to_base), conv(string num, int from_base, int to_base)	将數值從一種進制表示轉換為另一種進制，比如從二進制轉換為十進制
double	abs(double a)	傳回a的絕對值
int or double	pmod(int a, int b), pmod(double a, double b)	傳回a mod b的正數值
double	sin(double a), sin(decimal a)	傳回a的正弦值，a為弧度值
double	asin(double a), asin(decimal a)	如果1<=a<=-1，傳回a的反正弦值，否則傳回NULL
double	cos(double a), cos(decimal a)	傳回a的餘弦值，a為弧度值
double	acos(double a), acos(decimal a)	如果-1<=a<=1，傳回a的反餘弦值，否則傳回NULL
double	tan(double a), tan(decimal a)	傳回a的正切值，a為弧度值
double	atan(double a), atan(decimal a)	傳回a的反正切值
double	degrees(double a), degrees(decimal a)	将a從弧度值轉換為對應的角度值
double	radians(double a), radians(double a)	将a從角度值轉換為弧度值
int or double	positive(int a), positive(double a)	傳回a
int or double	negative(int a), negative(double a)	傳回-a
double or int	sign(double a), sign(decimal a)	如果a為正數傳回1.0，若為負數傳回-1.0，否則傳回0.0。參數為DECIMAL 時傳回值為INT類型
double	e()	傳回e的值
double	pi()	傳回π的值

集合函數

下表為Hive中的集合函數。

傳回類型	函數名	描述
int	size(Map<K.V>)	傳回map中的元素數量
int	size(Array<T>)	傳回數組中的元素數量
array<K>	map_keys(Map<K.V>)	傳回未經排序的包含map中keys的數組
array<V>	map_values(Map<K.V>)	傳回未經排序的包含map中values的數組
boolean	array_contains(Array<T>, value)	如果數組包含value，傳回true
array<t>	sort_array(Array<T>)	将數組按照升序排序并傳回

類型轉換函數

下表為Hive中的類型轉換函數。

傳回類型	函數名	描述
binary	binary(string|binary)	将輸入參數轉換為binary
<type>	cast(expr as <type>)	将表達式的結果expr轉換為<type>，如果轉換不成功則傳回NULL，例如cast(‘2014-08-09’ as date)

日期函數

下表中為Hive中的内置日期函數。

傳回類型	函數名	描述
string	from_unixtime(bigint unixtime[, string format])	将從unix時間（1970-01-01 00：00：00 UTC）開始的秒數轉換為表示目前系統時區的時間戳，格式為“1970-01-01 00：00：00”
bigint	unix_timestamp()	傳回目前Unix時間戳表示的秒數
bigint	unix_timestamp(string date)	将格式為yyyy-MM-dd HH：mm：ss的字元串轉換為Unix時間戳（秒），使用預設的時區和區域，如果失敗傳回0
bigint	unix_timestamp(string date, string pattern)	根據指定模式将時間字元串轉換為Unix時間戳（秒），失敗傳回0
string	to_date(string timestamp)	傳回時間戳字元串的日期部分，如：to_date("1970-01-01 00:00:00") = "1970-01-01"
int	year(string date)	傳回日期或時間戳字元串的年部分，如： year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970
int	month(string date)	傳回日期或時間戳字元串的月部分，如： month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11
int	day(string date) dayofmonth(date)	傳回日期或時間戳字元串的天部分，如：day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1
int	hour(string date)	傳回時間戳的小時部分： hour('2009-07-30 12:58:59') = 12, hour('12:58:59') = 12
int	minute(string date)	傳回時間戳的分鐘
int	second(string date)	傳回時間戳的秒
int	weekofyear(string date)	傳回時間戳的星期數，如： weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44
int	datediff(string enddate, string startdate)	傳回從startdate到enddate的天數，如： datediff('2009-03-01', '2009-02-27') = 2
string	date_add(string startdate, int days)	向startdate增加指定的天數，如： date_add('2008-12-31', 1) = '2009-01-01'
string	date_sub(string startdate, int days)	從startdate減去指定的天數，如： date_sub('2008-12-31', 1) = '2008-12-30'
timestamp	from_utc_timestamp(timestamp, string timezone)	假設給定時間戳為UTC時間，并将其轉換為給定時區的時間戳
timestamp	to_utc_timestamp(timestamp, string timezone)	假設給定時間戳為給定時區的時間，将其轉換為UTC時間戳

條件函數

下表為Hive支援的一些條件函數。

傳回類型	函數名	描述
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	如果testCondition為真，傳回valueTrue，否則傳回valueFalseOrNull
T	COALESCE(T v1, T v2, ...)	傳回第一個不是NULL的v，若全部為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=true傳回b，當c=true傳回d，其它傳回e

字元串函數

下表為Hive内置的字元串函數。

傳回類型	函數名	描述
int	ascii(string str)	傳回str第一個字元串的數值
string	base64(binary bin)	将二進制參數轉換為base64字元串
string	concat(string|binary A, string|binary B...)	傳回将A和B按順序連接配接在一起的字元串，如：concat('foo', 'bar') 傳回'foobar'
array<struct<string,double>>	context_ngrams(array<array<string>>, array<string>, int K, int pf)	從一組标記化的句子中傳回前k個文本
string	concat_ws(string SEP, string A, string B...)	類似concat() ，但使用自定義的分隔符SEP
string	concat_ws(string SEP, array<string>)	類似concat_ws() ，但參數為字元串數組
string	decode(binary bin, string charset)	使用指定的字元集将第一個參數解碼為字元串，如果任何一個參數為null，傳回null。可選字元集為： 'US_ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'
binary	encode(string src, string charset)	使用指定的字元集将第一個參數編碼為binary ，如果任一參數為null，傳回null
int	find_in_set(string str, string strList)	傳回str在strList中第一次出現的位置，strList為用逗号分隔的字元串，如果str包含逗号則傳回0，若任何參數為null，傳回null。如： find_in_set('ab', 'abc,b,ab,c,def') 傳回3
string	format_number(number x, int d)	将數字x格式化為'#,###,###.##'，四舍五入為d位小數位，将結果做為字元串傳回。如果d=0，結果不包含小數點或小數部分
string	get_json_object(string json_string, string path)	從基于json path的json字元串中提取json對象，傳回json對象的json字元串，如果輸入的json字元串無效傳回null。Json 路徑隻能有數字、字母和下劃線，不允許大寫和其它特殊字元
boolean	in_file(string str, string filename)	如果str在filename中以正行的方式出現，傳回true
int	instr(string str, string substr)	傳回substr在str中第一次出現的位置。若任何參數為null傳回null，若substr不在str中傳回0。Str中第一個字元的位置為1
int	length(string A)	傳回A的長度
int	locate(string substr, string str[, int pos])	傳回substr在str的位置pos後第一次出現的位置
string	lower(string A) lcase(string A)	傳回字元串的小寫形式
string	lpad(string str, int len, string pad)	将str左側用字元串pad填充，長度為len
string	ltrim(string A)	去掉字元串A左側的空格，如：ltrim(' foobar ')的結果為'foobar '
array<struct<string,double>>	ngrams(array<array<string>>, int N, int K, int pf)	從一組标記化的Returns the top-k 句子中傳回前K個N-grams
string	parse_url(string urlString, string partToExtract [, string keyToExtract])	傳回給定URL的指定部分，partToExtract的有效值包括HOST，PATH， QUERY， REF， PROTOCOL， AUTHORITY，FILE和USERINFO。例如：  parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') 傳回 'facebook.com'.。當第二個參數為QUERY時，可以使用第三個參數提取特定參數的值，例如： parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') 傳回'v1'
string	printf(String format, Obj... args)	将輸入參數進行格式化輸出
string	regexp_extract(string subject, string pattern, int index)	使用pattern從給定字元串中提取字元串。如： regexp_extract('foothebar', 'foo(.*?)(bar)', 2) 傳回'bar' 有時需要使用預定義的字元類：使用'\s' 做為第二個參數将比對s，'s'比對空格等。參數index是Java正則比對器方法group()方法中的索引
string	regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)	使用REPLACEMENT替換字元串INITIAL_STRING中比對PATTERN的子串，例如： regexp_replace("foobar", "oo|ar", "") 傳回'fb'
string	repeat(string str, int n)	将str重複n次
string	reverse(string A)	将字元串A翻轉
string	rpad(string str, int len, string pad)	在str的右側使用pad填充至長度len
string	rtrim(string A)	去掉字元串A右側的空格，如： rtrim(' foobar ') 傳回 ' foobar'
array<array<string>>	sentences(string str, string lang, string locale)	将自然語言文本處理為單詞和句子，每個句子在适當的邊界分割，傳回單詞的數組。參數lang和local為可選參數，例如： sentences('Hello there! How are you?') 傳回( ("Hello", "there"), ("How", "are", "you") )
string	space(int n)	傳回n個空格的字元串
array	split(string str, string pat)	用pat分割字元串str，pat為正規表達式
map<string,string>	str_to_map(text[, delimiter1, delimiter2])	使用兩個分隔符将文本分割為鍵值對。第一個分隔符将文本分割為K-V 對，第二個分隔符分隔每個K-V 對。預設第一個分隔符為“，“，第二個分隔符為=
string	substr(string|binary A, int start) substring(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開始，長度為len的子串，如： substr('foobar', 4, 1) 傳回 'b'
string	translate(string input, string from, string to)	将input中出現在from中的字元替換為to中的字元串，如果任何參數為null，結果為null
string	trim(string A)	去掉字元串A兩端的空格
binary	unbase64(string str)	将base64字元串轉換為二進制
string	upper(string A) ucase(string A)	傳回字元串A的大寫形式

聚合函數

下表為Hive内置的聚合函數。

傳回類型	函數名	描述
BIGINT	count(*) count(expr) count(DISTINCT expr[, expr_.])	count(*) – 傳回檢索到的行的總數，包括含有NULL值的行。count(expr) – 傳回expr表達式不是NULL的行的數量count(DISTINCT expr[, expr]) – 傳回expr是唯一的且非NULL的行的數量
DOUBLE	sum(col) sum(DISTINCT col)	對組内某列求和（包含重複值）或者對組内某列求和（不包含重複值）
DOUBLE	avg(col), avg(DISTINCT col)	對組内某列元素求平均值者（包含重複值或不包含重複值）
DOUBLE	min(col)	傳回組内某列的最小值
DOUBLE	max(col)	傳回組内某列的最大值
DOUBLE	variance(col), 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)	傳回組内某個列精确的第p位百分數，p必須在0和1之間
array<double>	percentile(BIGINT col, array(p1 [, p2]...))	傳回組内某個列精确的第p1，p2，……位百分數，p必須在0和1之間
DOUBLE	percentile_approx(DOUBLE col, p [, B])	傳回組内數字列近似的第p位百分數（包括浮點數），參數B控制近似的精确度，B值越大，近似度越高，預設值為10000。當列中非重複值的數量小于B時，傳回精确的百分數
array<double>	percentile_approx(DOUBLE col, array(p1 [, p2]...) [, B])	同上，但接受并傳回百分數數組
array<struct {'x','y'}>	histogram_numeric(col, b)	使用b個非均勻間隔的箱子計算組内數字列的柱狀圖（直方圖），輸出的數組大小為b，double類型的(x,y)表示直方圖的中心和高度
array	collect_set(col)	傳回消除了重複元素的數組
array	collect_list(col)	傳回允許重複元素的數組
INTEGER	ntile(INTEGER x)	該函數将已經排序的分區分到x個桶中，并為每行配置設定一個桶号。這可以容易的計算三分位，四分位，十分位，百分位和其它通用的概要統計

内置 Table-Generating函數（UDTF）

正常的使用者定義函數，如concat，輸入一個單行然後輸出一個單行，但table-generating函數将一個單輸入行轉換為多個輸出行。下表為Hive内置的table-generating函數。

傳回類型	函數名	描述
N rows	explode(ARRAY)	參數列為數組類型，将數組資料中的每個元素做為一行傳回
N rows	explode(MAP)	将輸入map中的每個鍵值對轉換為兩列，一列為key，另一列為value，然後傳回新行
inline(ARRAY<STRUCT[,STRUCT]>)	分解struct數組到表中
Array Type	explode(array<TYPE> a)	對于數組a中的每個元素，該函數産生包含該元素的行For
元組	json_tuple(jsonStr, k1, k2, ...)	參數為一組鍵k1，k2……和JSON字元串，傳回值的元組。該方法比  get_json_object  高效，因為可以在一次調用中輸入多個鍵
元組	parse_url_tuple(url, p1, p2, ...)	該方法同 parse_url()  相似，但可以一次性提取URL的多個部分，有效的參數名稱為： HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY>
N rows	posexplode(ARRAY)	行為與參數為數組的explode方法相似，但包含項在原始數組中的位置，傳回(pos,value)的二進制組
stack(INT n, v_1, v_2, ..., v_k)	将v_1, ..., v_k 分為n行，每行包含n/k列，n必須為常數

使用文法”SELECT udtf(col) AS colAlias...”有以下幾點限制：

•  在SELECT中不允許再有其他表達式：不支援SELECT pageid, explode(adid_list) AS myCol...
•  UDTF不能夠嵌套使用：不支援SELECT explode(explode(adid_list)) AS myCol...
• 不支援GROUP BY /CLUSTER BY / DISTRIBUTE BY / SORT BY：不支援SELECT explode(adid_list) AS myCol ... GROUP BY myCol