1. Hive總結(jié)-常用函數(shù)

學習書目：https://zhuanlan.zhihu.com/p/82601425

1.1. 基礎無腦型：

1.1.1. 字符串長度函數(shù)：length

hive> select length('abced') from dual;          5

1.1.2. 字符串反轉(zhuǎn)函數(shù)：reverse

hive> select reverse('abcedfg') from dual;        gfdecba

1.1.3. 字符串轉(zhuǎn)大寫函數(shù)：upper,ucase

hive> select ucase('abCd') from dual;    ABCD

1.1.4. 字符串連接函數(shù)：concat。

hive> select concat('abc','def') from dual;        abcdef

支持任意個輸入字符串

1.1.5. 字符串轉(zhuǎn)小寫函數(shù)：lower,lcase

hive> select lcase('abCd') from dual;        abcd

1.1.6. 去空格函數(shù)：trim；

hive> select trim(' abc ') from dual;        abc

說明：去除字符串兩邊的空格

hive> select ltrim(' abc ') from dual;        abc

1.1.6.1. 左邊去空格函數(shù)：ltrim；

hive> select rtrim(' abc ') from dual;        abc

1.1.6.2. 右邊去空格函數(shù)：rtrim

帶分隔符字符串連接函數(shù)：concat_ws

hive> select concat_ws(',','abc','def','gh') from dual;        abc,def,gh

1.1.7. 空格字符串函數(shù)：space

說明：返回長度為n的空字符串

hive> select space(10) from dual;hive> select length(space(10)) from dual;        10

1.1.8. 重復字符串函數(shù)：repeat

hive> select repeat('abc',5) from dual;        abcabcabcabcabc

1.2. 查找替換截取

1.2.1. 字符串截取函數(shù)：substr,substring

hive> select substr('abcde',3) from dual;        cdehive> select substring('abcde',3) from dual;        cdehive>  select substr('abcde',-1) from dual;         e

說明：返回字符串A從start位置到結(jié)尾的字符串

1.2.2. 字符串截取函數(shù)：substr,substring

hive> select substr('abcde',3,2) from dual;        cdhive> select substring('abcde',3,2) from dual;        cdhive>select substring('abcde',-2,2) from dual;        de

說明：返回字符串A從start位置開始，長度為len的字符串

1.2.3. 正則表達式替換函數(shù)：regexp_replace

hive> select regexp_replace('foobar', 'oo|ar', '') from dual;        fbhive> select regexp_replace(split(labels,'\\.')[0], '\\.|\\{|\\}|\\"', '') as labels;

(此處使用了轉(zhuǎn)移字符：雙,)

說明：將字符串A中的符合java正則表達式B的部分替換為C。注意，在有些情況下要使用轉(zhuǎn)義字符\,類似oracle中的regexp_replace函數(shù)。

1.2.4. 正則表達式解析函數(shù)：regexp_extract

hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from dual;          thehive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) from dual;         barhive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from dual;        foothebarhive> select regexp_extract('中國abc123!','[\\u4e00-\\u9fa5]+',0) from dual; //實用：只匹配中文hive> select regexp_replace('中國abc123','[\\u4e00-\\u9fa5]+','') from dual; //實用：去掉中文

語法: regexp_extract(string subject, string pattern, int index)

返回值: string

說明：將字符串subject按照pattern正則表達式的規(guī)則拆分，返回index指定的字符。

第三個參數(shù):

0 是顯示與之匹配的整個字符串

1 是顯示第一個括號里面的

2 是顯示第二個括號里面的字段

注意，在有些情況下要使用轉(zhuǎn)義字符，等號要用雙豎線轉(zhuǎn)義，這是java正則表達式的規(guī)則。

1.3. 字符串內(nèi)容解析

1.3.1. URL解析函數(shù)：parse_url

語法: parse_url(string urlString, string partToExtract [, string keyToExtract])

返回值: string

說明：返回URL中指定的部分。partToExtract的有效值為：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

hive> select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from dual;        facebook.comhive> select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') from dual;        v1

1.3.2. json解析函數(shù)：get_json_object

語法: get_json_object(string json_string, string path)

返回值: string

說明：解析json的字符串json_string,返回path指定的內(nèi)容。如果輸入的json字符串無效，那么返回NULL。

hive>select  get_json_object('{"nation":"china"}','$.nation') from dual;china

1.3.3. 首字符ascii函數(shù)：ascii ；返回值: int

hive> select ascii('abcde') from dual;        97

說明：返回字符串str第一個字符的ascii碼

1.3.4. 左補足函數(shù)：lpad（常用來不足長度不足的字符串，然后進行截取）

hive> select lpad('abc',10,'td') from dual;tdtdtdtabc

1.3.4.1. 右補足函數(shù)：rpad

hive> select rpad('abc',10,'td') from dual;        abctdtdtdt

1.3.5. 分割字符串函數(shù): split，跟底層MR中的split方法功能一樣。返回值: array

hive> select split('abtcdtef','t') from dual;        ["ab","cd","ef"]

1.3.6. 集合查找函數(shù): find_in_set ；返回值: int

說明: 返回str在strlist第一次出現(xiàn)的位置，strlist是用逗號分割的字符串。如果沒有找該str字符，則返回0

hive> select find_in_set('ab','ef,ab,de') from dual;        2hive> select find_in_set('at','ef,ab,de') from dual;        0

1.3.7. 在一個字符串中搜索指定的字符,返回發(fā)現(xiàn)指定的字符的位置: INSTR(string C1,string C2,int I,int J)；

sql hive> select instr("abcde",'b') from dual; 2

1.3.8. 使用兩個分隔符將文本拆分為鍵值對：str_to_map(text[, delimiter1, delimiter2]) 返回：map

Delimiter1將文本分成K-V對，Delimiter2分割每個K-V對。對于delimiter1默認分隔符是','，對于delimiter2默認分隔符是'='

sql hive> select str_to_map('aaa:123&bbb:456', '&', ':') from dual; {"bbb":"456","aaa":"123"}

1.4. 時間函數(shù)

1.4.1. unix_timestamp() 返回當前時間戳。另外，current_timestamp() 也有同樣作用。

hive> select unix_timestamp();1568552090hive> select unix_timestamp('2020-01-01 01:01:01');1577811661hive> select unix_timestamp('2020-01-01','yyyy-MM-dd');1577808000

1.4.2. from_unixtime(int/bigint timestamp)

返回 timestamp 時間戳對應的日期，格式為 yyyy-MM-dd HH:mm:ss。

hive> select from_unixtime(1577811661);2020-01-01 01:01:01hive> select from_unixtime(1577811661,'yyyy/MM/dd HH');2020/01/01 01

1.4.3.current_date() ，當前日期時間

hive> select current_date();2021-04-25hive> select current_timestamp();2021-04-25 16:56:49.274hive> select unix_timestamp();1619341037hive> select from_unixtime(unix_timestamp(),'yyyy-MM-dd');2021-04-25hive> select from_unixtime(unix_timestamp(),'yyyyMMdd');20210425hive> select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:dd:ss');2021-04-25 17:25:51

1.4.4. date_add()

hive> select date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1);2021-04-24hive> select date_add(current_date,-1);2021-04-24

1.4.5. date_format()

時間戳<互轉(zhuǎn)>日期：from_unixtime(), to_unix_timestamp

hive> select from_unixtime(1517725479,'yyyy-MM-dd HH:dd:ss');2018-02-04 14:04:39hive> select to_unix_timestamp('2017-01-01 12:12:12','yyyy-MM-dd HH:dd:ss');1484193612

date_format 輸出標準時間格式：

select from_unixtime(unix_timestamp());hive> select to_date('2017-01-01 12:12:12');2017-01-01hive> select date_format(current_timestamp(),'yyyy-MM-dd HH:mm:ss');2021-04-25 17:21:26hive> select date_format(current_date(),'yyyyMMdd');20210425

utc時間轉(zhuǎn)換:

hive> select to_utc_timestamp(current_timestamp(),8);2021-04-25 17:22:52.29hive> select from_utc_timestamp(current_timestamp(),8);