hiveql函數筆記（二）

1、數據查詢

//提高聚合的性能

SET hive.map.aggr=true;

SELECT count(*),avg(salary) FROM employees;

//木匾不允許在一個查詢語句中使用多于一個的函數（DISTINCT。。。）表達式

SELECT count(DISTINCT symbol) FROM stocks;

?

表生成函數：

explode(APPAY array)　　返回0到多行結果，每行都對應輸入的array數組中的一個元素

explode(ARRAY<TYPE> a)　　對于a中的每個元素，explode()會生成一個記錄包含這個元素

explode(ARRAY<TYPE> a)　　對于a中的每個元素，explode()會生成一行記錄包含這個元素

inline(ARRAY<STRUCT[,STRUCT]>)　　將結構體數組提取出來并插入到表中

json_tuple(STRING jsonStr,p1,p2,..,pn)　　對輸入的JSON字符串進行處理，合格get_json_object這個UDF類似，不過更高效，其通過一次調用就可以獲取多個鍵值。

parse_url_tuple(url,partname1,partname2,..,partNameN)　　其中N>=1，從URL中解析出N個部分信息。其輸入參數是：URL，以及多個要抽取的部分的名稱。部分名稱是大小寫敏感的，而且不應該包含有空格：HOST,PATH,QUERY,REF,PROTOCOL,AUTHORITY,FILE,USERINFO,QUERY:<KEY_NAME>

?

其他內置函數：

ascii(STRING s)　　返回字符串s中首個ASCII字符的整數值

base64(BINARY bin)　　將二進制值bin轉換成基于64位的字符串

cast(<expr> as <type>)　　將expr轉換成type類型的。例如:cast('1' as BIGINT)將會將字符串'1'轉換成BIGINT數值類型。如果轉換失敗，則返回NULL

concat(BINARY s1,BINARY s2,..)　　將二進制字節碼按次序拼接成一個字符串

concat(STRING s1,STRING s2,..)　　將字符串s1,s2等拼接成一個字符串。例如：concat('ab','cd')的結果是'abcd'

concat)ws(STRING separator,STRING s1,STRING s2,...)　　和concat類似，不過是使用指定的分隔符進行拼接.

decode(BINARY bin,STRING charset)　　使用指定的字符集charset將二進制值bin解碼成字符串（支持的字符集有：'UTF-16'）。如果任一輸入參數為NULL，則結果返回NULL

encode(STRING src,STRING charset)　　使用指定的字符集charset將字符串src編碼成二進制值（支持的字符集有:'US-ASCII','ISO-8859-1','UTF-8',UTF-16BE','UTF-16LE','UTF-16')）,如果任一輸入參數為NULL，則結果為NULL

find_in_set(STRING s,STRING commaSeparatedString)　　返回在以逗號分隔的字符串中s出現的位置，如果沒有找到則返回NULL

format_number(NUMBER X,INT d)　　將數值x轉換成'#,###,###,##'格式字符串，并博保留d位小數。如果d為0，那么輸出值就沒有小數點后面的值。

get_json_object(STRING json_string,STRING path)　　從給定路徑上的JSON字符串中抽取JSON對象，并返回這個對象的JSON字符串形式。如果輸入的JSON字符串是非法的，則返回NULL

in_file(STRING s,STRING filename)　　如果文件名為filename的文件中有完整一行數據和字符串s完全匹配的話，則返回true

instr(STRING str,STRING substr)　　查找字符串str中子字符串substr第一次出現的位置

length(STRING s)　　計算字符串s的長度

locate(STRING substr,STRING ?[,INT pos])　　查找在字符串str中的pos位置后字符串substr第一次出現的位置

lpad(STRING s,INT len,STRING pad)　　從左邊開始對字符串s使用字符串pad進行填充，最終達到len長度為止。如果字符串s本身長度比len大的話，那么多余的部分被去除掉。

rpad(STRING s,INT len,STRING pad)　　從右邊開始對字符串s......

ltrim(STRING s)　　將字符串s前面出現的空格全部去除掉。例如trim(' hive ')的結果是'hive '

regexp_replace(STRING s,STRING regex,STRING replacement) 按照JAVA正則表達式regex將字符串s中符合條件的部分替換成replacement所指定的字符串a.如果replacement部分是空的話，那么符合正則的部門就會背去除掉。例如regexp_replace('hive','[ie]','z')的 結果是'hzvz'

repeat(STRING s,INT n)　　重復輸出n次字符串s

reverse(STRING s)　　反轉字符串

rtrim(STRING s)　　將字符串s后面出現的空格全部去除掉。例如trim('hive')的結果是'hive'

split(STRING s,STRING pattern)　　按照正則表達式pattern分隔字符串s,并將分割后的部分以字符串數組的方式返回。

str_to_map(STRING s,STRING delim1,STRING delim2)　　將字符串s按照按指定分隔符轉換成map,第一個參數是輸入的字符串，第二個參數是鍵值對之間的分隔符，第三個分隔符是鍵和值之間的分隔符

substr(STRING s,STRING start_index)　　對于字符串s,從start位置開始截取length長度的字符串，作為字符串。例如:substr('abcdefgh',3,2)的結果是'cd'

unix_timestamp(STRING date,STRING pattern)　　將指定時間字符串格式字符串轉換成Unix時間戳，如果格式不對則返回0，例如unix_timestamp('2009-03-20','yyyy-MM-dd')=1237532400

to_date(STRING timestamp)　　返回時間字符串的日期部分，例如：to_date("1970-01-01 00:00:00")="1970-01-01"

year(STRING date)　　返回時間字符串中的年份并使用INT類型表示。例如:year("1970-01-01") = 1970

month....day...hour...minute...second...weekofyear同理

datediff(STRING enddate,STRING startdate)　　計算開始時間startdata到結束時間enddata相差的天數。例如:datediff('2009-03-01','2009-02-27')=2

date_add(STRING startdate,INT days)　　為開始時間startdata增加days天，例如data_sub('2008-12-31',1)='2008-12-30'

?

謂詞操作符

A<=>B?　　如果A和B都為NULL則返回TRUE，其他的和等號(=)操作符的結果一致，如果任一為NULL則結果為NULL

A<>B,A!=B　　A或者B為NULL則返回NULL，如果A不等于B則返回TRUE,反之返回FALSE

A[NOT] BETWEEN B AND C　　如果A的值大于或等于B而且小于或等于C，則結果為TRUE

A[NOT] like B　　B是一個SQL下的簡單正則表達式

A RLIKE B,A REGEXP B　　B是一個正則表達式，如果A與其相匹配，則返回TRUE。

?

參考文獻：《Hive編程指南》