hive函數使用學習筆記

1.内置運算符

1.1關系運算符

運算符	類型	說明
A = B	所有原始類型	如果A與B相等,傳回TRUE,否則傳回FALSE
A == B	無	失敗，因為無效的文法。 SQL使用”=”，不使用”==”。
A <> B	所有原始類型	如果A不等于B傳回TRUE,否則傳回FALSE。如果A或B值為”NULL”，結果傳回”NULL”。
A < B	所有原始類型	如果A小于B傳回TRUE,否則傳回FALSE。如果A或B值為”NULL”，結果傳回”NULL”。
A <= B	所有原始類型	如果A小于等于B傳回TRUE,否則傳回FALSE。如果A或B值為”NULL”，結果傳回”NULL”。
A > B	所有原始類型	如果A大于B傳回TRUE,否則傳回FALSE。如果A或B值為”NULL”，結果傳回”NULL”。
A >= B	所有原始類型	如果A大于等于B傳回TRUE,否則傳回FALSE。如果A或B值為”NULL”，結果傳回”NULL”。
A IS NULL	所有類型	如果A值為”NULL”，傳回TRUE,否則傳回FALSE
A IS NOT NULL	所有類型	如果A值不為”NULL”，傳回TRUE,否則傳回FALSE
A LIKE B	字元串	如 果A或B值為”NULL”，結果傳回”NULL”。字元串A與B通過sql進行比對，如果相符傳回TRUE，不符傳回FALSE。B字元串中 的”_”代表任一字元，”%”則代表多個任意字元。例如： (‘foobar’ like ‘foo’)傳回FALSE，（ ‘foobar’ like ‘foo_ _ _’或者 ‘foobar’ like ‘foo%’)則傳回TURE
A RLIKE B	字元串	如 果A或B值為”NULL”，結果傳回”NULL”。字元串A與B通過java進行比對，如果相符傳回TRUE，不符傳回FALSE。例如：（ ‘foobar’ rlike ‘foo’）傳回FALSE，（’foobar’ rlike ‘^f.*r$’ ）傳回TRUE。
A REGEXP B	字元串	與RLIKE相同。

1.2算術運算符

運算符	類型	說明
A + B	所有數字類型	A和B相加。結果的與操作數值有共同類型。例如每一個整數是一個浮點數，浮點數包含整數。是以，一個浮點數和一個整數相加結果也是一個浮點數。
A – B	所有數字類型	A和B相減。結果的與操作數值有共同類型。
A * B	所有數字類型	A和B相乘，結果的與操作數值有共同類型。需要說明的是，如果乘法造成溢出，将選擇更高的類型。
A / B	所有數字類型	A和B相除，結果是一個double（雙精度）類型的結果。
A % B	所有數字類型	A除以B餘數與操作數值有共同類型。
A & B	所有數字類型	運算符檢視兩個參數的二進制表示法的值，并執行按位”與”操作。兩個表達式的一位均為1時，則結果的該位為 1。否則，結果的該位為 0。
A|B	所有數字類型	運算符檢視兩個參數的二進制表示法的值，并執行按位”或”操作。隻要任一表達式的一位為 1，則結果的該位為 1。否則，結果的該位為 0。
A ^ B	所有數字類型	運算符檢視兩個參數的二進制表示法的值，并執行按位”異或”操作。當且僅當隻有一個表達式的某位上為 1 時，結果的該位才為 1。否則結果的該位為 0。
~A	所有數字類型	對一個表達式執行按位”非”（取反）。

1.3邏輯運算符

運算符	類型	說明
A AND B	布爾值	A和B同時正确時,傳回TRUE,否則FALSE。如果A或B值為NULL，傳回NULL。
A && B	布爾值	與”A AND B”相同
A OR B	布爾值	A或B正确,或兩者同時正确返傳回TRUE,否則FALSE。如果A和B值同時為NULL，傳回NULL。
A | B	布爾值	與”A OR B”相同
NOT A	布爾值	如果A為NULL或錯誤的時候傳回TURE，否則傳回FALSE。
! A	布爾值	與”NOT A”相同

1.4複雜類型函數

函數	類型	說明
map	(key1, value1, key2, value2, …)	通過指定的鍵/值對，建立一個map。
struct	(val1, val2, val3, …)	通過指定的字段值，建立一個結構。結構字段名稱将COL1，COL2，…
array	(val1, val2, …)	通過指定的元素，建立一個數組。

1.5對複雜類型函數操作

函數	類型	說明
A[n]	A是一個數組，n為int型	傳回數組A的第n個元素，第一個元素的索引為0。如果A數組為['foo','bar']，則A[0]傳回’foo’和A[1]傳回”bar”。
M[key]	M是Map<K, V>，關鍵K型	傳回關鍵值對應的值，例如mapM為 \{‘f’ -> ‘foo’, ‘b’ -> ‘bar’, ‘all’ -> ‘foobar’\}，則M['all'] 傳回’foobar’。
S.x	S為struct	傳回結構x字元串在結構S中的存儲位置。如 foobar \{int foo, int bar\} foobar.foo的領域中存儲的整數。

2.内置函數

2.1數學函數

傳回類型	函數	說明
BIGINT	round(double a)	四舍五入
DOUBLE	round(double a, int d)	小數部分d位之後數字四舍五入，例如round(21.263,2),傳回21.26
BIGINT	floor(double a)	對給定資料進行向下舍入最接近的整數。例如floor(21.2),傳回21。
BIGINT	ceil(double a), ceiling(double a)	将參數向上舍入為最接近的整數。例如ceil(21.2),傳回23.
double	rand(), rand(int seed)	傳回大于或等于0且小于1的平均分布随機數（依重新計算而變）
double	exp(double a)	傳回e的n次方
double	ln(double a)	傳回給定數值的自然對數
double	log10(double a)	傳回給定數值的以10為底自然對數
double	log2(double a)	傳回給定數值的以2為底自然對數
double	log(double base, double a)	傳回給定底數及指數傳回自然對數
double	pow(double a, double p) power(double a, double p)	傳回某數的乘幂
double	sqrt(double a)	傳回數值的平方根
string	bin(BIGINT a)	傳回二進制格式，參考：http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex
string	hex(BIGINT a) hex(string a)	将整數或字元轉換為十六進制格式。參考：http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex
string	unhex(string a)	十六進制字元轉換由數字表示的字元。
string	conv(BIGINT num, int from_base, int to_base)	将 指定數值，由原來的度量體系轉換為指定的試題體系。例如CONV(‘a’,16,2),傳回。參考：’1010′ http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv
double	abs(double a)	取絕對值
int double	pmod(int a, int b) pmod(double a, double b)	傳回a除b的餘數的絕對值
double	sin(double a)	傳回給定角度的正弦值
double	asin(double a)	傳回x的反正弦，即是X。如果X是在-1到1的正弦值，傳回NULL。
double	cos(double a)	傳回餘弦
double	acos(double a)	傳回X的反餘弦，即餘弦是X，，如果-1<= A <= 1，否則傳回null.
int double	positive(int a) positive(double a)	傳回A的值，例如positive(2)，傳回2。
int double	negative(int a) negative(double a)	傳回A的相反數，例如negative(2),傳回-2。

2.2收集函數

傳回類型	函數	說明
int	size(Map<K.V>)	傳回的map類型的元素的數量
int	size(Array<T>)	傳回數組類型的元素數量

2.3類型轉換函數

傳回類型	函數	說明
指定 “type”	cast(expr as <type>)	類型轉換。例如将字元”1″轉換為整數:cast(’1′ as bigint)，如果轉換失敗傳回NULL。

2.4日期函數

傳回類型	函數	說明
string	from_unixtime(bigint unixtime[, string format])	UNIX_TIMESTAMP參數表示傳回一個值’YYYY- MM – DD HH：MM：SS’或YYYYMMDDHHMMSS.uuuuuu格式，這取決于是否是在一個字元串或數字語境中使用的功能。該值表示在目前的時區。
bigint	unix_timestamp()	如果不帶參數的調用，傳回一個Unix時間戳（從’1970- 01 – 0100:00:00′到現在的UTC秒數）為無符号整數。
bigint	unix_timestamp(string date)	指定日期參數調用UNIX_TIMESTAMP（），它傳回參數值’1970- 01 – 0100:00:00′到指定日期的秒數。
bigint	unix_timestamp(string date, string pattern)	指定時間輸入格式，傳回到1970年秒數：unix_timestamp(’2009-03-20′, ‘yyyy-MM-dd’) = 1237532400 參考：http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html
string	to_date(string timestamp)	傳回時間中的年月日： to_date(“1970-01-01 00:00:00″) = “1970-01-01″
string	to_dates(string date)	給定一個日期date，傳回一個天數（0年以來的天數）
int	year(string date)	傳回指定時間的年份，範圍在1000到9999，或為”零”日期的0。
int	month(string date)	傳回指定時間的月份，範圍為1至12月，或0一個月的一部分，如’0000-00-00′或’2008-00-00′的日期。
int	day(string date) dayofmonth(date)	傳回指定時間的日期
int	hour(string date)	傳回指定時間的小時，範圍為0到23。
int	minute(string date)	傳回指定時間的分鐘，範圍為0到59。
int	second(string date)	傳回指定時間的秒，範圍為0到59。
int	weekofyear(string date)	傳回指定日期所在一年中的星期号，範圍為0到53。
int	datediff(string enddate, string startdate)	兩個時間參數的日期之差。
int	date_add(string startdate, int days)	給定時間，在此基礎上加上指定的時間段。
int	date_sub(string startdate, int days)	給定時間，在此基礎上減去指定的時間段。

2.5條件函數

傳回類型	函數	說明
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	判斷是否滿足條件，如果滿足傳回一個值，如果不滿足則傳回另一個值。
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時傳回b,當值為c時傳回d。否則傳回e。

2.6字元函數

傳回類型	函數	說明
int	length(string A)	傳回字元串的長度
string	reverse(string A)	傳回倒序字元串
string	concat(string A, string B…)	連接配接多個字元串，合并為一個字元串，可以接受任意數量的輸入字元串
string	concat_ws(string SEP, string A, string B…)	連結多個字元串，字元串之間以指定的分隔符分開。
string	substr(string A, int start) substring(string A, int start)	從文本字元串中指定的起始位置後的字元。
string	substr(string A, int start, int len) substring(string A, int start, int len)	從文本字元串中指定的位置指定長度的字元。
string	upper(string A) ucase(string A)	将文本字元串轉換成字母全部大寫形式
string	lower(string A) lcase(string A)	将文本字元串轉換成字母全部小寫形式
string	trim(string A)	删除字元串兩端的空格，字元之間的空格保留
string	ltrim(string A)	删除字元串左邊的空格，其他的空格保留
string	rtrim(string A)	删除字元串右邊的空格，其他的空格保留
string	regexp_replace(string A, string B, string C)	字元串A中的B字元被C字元替代
string	regexp_extract(string subject, string pattern, int index)	通過下标傳回正規表達式指定的部分。regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) returns ‘bar.’
string	parse_url(string urlString, string partToExtract [, string keyToExtract])	傳回URL指定的部分。parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1′, ‘HOST’) 傳回：’facebook.com’
string	get_json_object(string json_string, string path)	select a.timestamp, get_json_object(a.appevents, ‘$.eventid’), get_json_object(a.appenvets, ‘$.eventname’) from log a;
string	space(int n)	傳回指定數量的空格
string	repeat(string str, int n)	重複N次字元串
int	ascii(string str)	傳回字元串中首字元的數字值
string	lpad(string str, int len, string pad)	傳回指定長度的字元串，給定字元串長度小于指定長度時，由指定字元從左側填補。
string	rpad(string str, int len, string pad)	傳回指定長度的字元串，給定字元串長度小于指定長度時，由指定字元從右側填補。
array	split(string str, string pat)	将字元串轉換為數組。
int	find_in_set(string str, string strList)	傳回字元串str第一次在strlist出現的位置。如果任一參數為NULL,傳回NULL；如果第一個參數包含逗号，傳回0。
array<array<string>>	sentences(string str, string lang, string locale)	将字元串中内容按語句分組，每個單詞間以逗号分隔，最後傳回數組。 例如sentences(‘Hello there! How are you?’) 傳回：( (“Hello”, “there”), (“How”, “are”, “you”) )
array<struct<string,double>>	ngrams(array<array<string>>, int N, int K, int pf)	SELECT ngrams(sentences(lower(tweet)), 2, 100 [, 1000]) FROM twitter;
array<struct<string,double>>	context_ngrams(array<array<string>>, array<string>, int K, int pf)	SELECT context_ngrams(sentences(lower(tweet)), array(null,null), 100, [, 1000]) FROM twitter;

3.内置的聚合函數（UDAF）

傳回類型	函數	說明
bigint	count(*) , count(expr), count(DISTINCT expr[, expr_., expr_.])	傳回記錄條數。
double	sum(col), sum(DISTINCT col)	求和
double	avg(col), 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(col, p)	傳回數值區域的百分比數值點。0<=P<=1,否則傳回NULL,不支援浮點型數值。
array<double>	percentile(col, array(p~1,,\ [, p,,2,,]…))	傳回數值區域的一組百分比值分别對應的數值點。0<=P<=1,否則傳回NULL,不支援浮點型數值。
double	percentile_approx(col, p[, B])	Returns an approximate p^th^ percentile of a numeric column (including floating point types) in the group. The B parameter controls approximation accuracy at the cost of memory. Higher values yield better approximations, and the default is 10,000. When the number of distinct values in col is smaller than B, this gives an exact percentile value.
array<double>	percentile_approx(col, array(p~1,, [, p,,2_]…) [, B])	Same as above, but accepts and returns an array of percentile values instead of a single one.
array<struct\{‘x’,'y’\}>	histogram_numeric(col, b)	Computes a histogram of a numeric column in the group using b non-uniformly spaced bins. The output is an array of size b of double-valued (x,y) coordinates that represent the bin centers and heights
array	collect_set(col)	傳回無重複記錄

4.内置表生成函數（UDTF）

傳回類型	函數	說明
數組	explode(array<TYPE> a)	數組一條記錄中有多個參數，将參數拆分，每個參數生成一列。
json_tuple	get_json_object 語句：select a.timestamp, get_json_object(a.appevents, ‘$.eventid’), get_json_object(a.appenvets, ‘$.eventname’) from log a; json_tuple語句: select a.timestamp, b.* from log a lateral view json_tuple(a.appevent, ‘eventid’, ‘eventname’) b as f1, f2

explode示例：

數組	SQL	傳回
myCol  [1,2]  [3,4]	SELECT explode(myCol) AS myNewCol FROM myTable	myNewCol 1  2  3  4