HIVE常用內置函數

1，json轉換為結構化對象

select get_json_object({"store":{"fruit":["aa, "bb, "cc"]} ,"owner":"amy"},$.store.fruit[0]);

select find_in_set(ab,ef,ab,de);
select repeat(abc,5);

2，解析URL

parse_url(url, partToExtract[, key]) -- extracts a part from a URL -- 解析URL字元串， partToExtract的選項包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]。

3，正則查找與替換

regexp_extract(string subject, string pattern, int index) substr(string A, int start, int len),substring(string A, int start, int len) regexp_replace(string A, string B, string C)

select concat_ws(,abc,def,gh)
select reverse(abcedfg)

4，是否包含子字元串

//測試字元串是否包含子字元串 select if(split(父串,子串)[1] is not NULL,1,NULL)

5，數字IP轉換成常規IP

//將數字IP轉換成常規IP 字元串是如何轉換而來的呢?其實並不複雜，以「218.22.123.26」這個IP地址為例， IP地址轉換成數字串方法如下:先將「218.22.123.26」轉換為十六進位「DA.16.7B.1A」，然後去掉小數點後，變為「DA167B1A」，最後將這個十六進位數轉換為十進位「3658906394」，那麼「218.22.123.26」就變為「3658906394」了。其他IP地址轉換為數字串也是使用同樣的方法。 SELECT concat_ws(.,CONV(substr(hex(3658906394),1,2),16,10), CONV(substr(hex(3658906394),3,2),16,10), CONV(substr(hex(3658906394),5,2),16,10), CONV(substr(hex(3658906394),7,2),16,10));

select CONV(substr(hex(3658906394),3,4),16,10);
substr(string A, int start, int len)
substring(string A, intstart, int len)

6，集合查找函數

集合查找函數:find_in_set find_in_set(string str, string strList) 返回str在strlist第一次出現的位置，strlist是用逗號分割的字元串。如果沒有找該str字元，則返回0

cast(<expr> as <type>)
size(Array<T>)
from_unixtime(int unixtime)
to_date(string timestamp)

7，正則表達式的基礎學習

. 一個單一字元 * 零次或多次＋一次或多次？零次或一次 {n,m} 至少匹配n次，最多匹配m次

[a-z] //匹配所有的小寫字母
[A-Z] //匹配所有的大寫字母
[a-zA-Z] //匹配所有的字母
[0-9] //匹配所有的數字
[0-9.-] //匹配所有的數字，句號和減號
[ f

] //匹配所有的白字元
[^a-z] //除了小寫字母以外的所有字元
[^\/^] //除了()(/)(^)之外的所有字元
[^"] //除了雙引號(")和單引號()之外的所有字元

8，位運算

select 2&2－－－－－－2 select 3&2－－－－－－2 select 1|2－－－－－－3 select 2|2－－－－－－2 select ~2－－－－－－ -3

bin(BIGINT a)
hex(BIGINT a) hex(string a)
unhex(string a)
conv(BIGINT num, int from_base, int to_base)－－－
select conv(150354d103e2a6cfb,16,10)
pmod(double a, double b) 返回餘數絕對值
cast(expr as <type>) 類型轉換

9，日期運算

date_add(string startdate, int days） date_sub(string startdate, int days) unix_timestamp(string date, string pattern) 將指定格式的時間轉換成秒 from_unixtime(bigint unixtime[, string format])

select from_unixtime(1463042015872,yyyy-MM-dd HH:mm:ss)
select sentences(Hello there! How are you?)

10，語句分析

SELECT ngrams(sentences(Hello there! How are you?), 2, 100 ,1000) 結果:[{"ngram":["Hello, "there"],"estfrequency":1.0},{"ngram":["How, "are"],"estfrequency":1.0},{"ngram":["are, "you"],"estfrequency":1.0}] SELECT context_ngrams(sentences(lower(tweet)), array(null,null), 100, [, 1000]) FROM twitter;

11，集合

collect_set(col) 無重複記錄 explode(array<TYPE> a) 拆分成列

12，分析函數（很重要）

分析函數:LAG--排序向上去第n,LEAD--排序向下去第n, FIRST_VALUE--取分組排序當前行的第一個, LAST_VALUE--取分組排序當前行的最後一個就是自己；

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2
FROM lxw1234
ORDER BY cookieid,createtime;