[toc]

關(guān)系運(yùn)算

1堡距、等值比較: =

語(yǔ)法：A=B
操作類型：所有基本類型
描述: 如果表達(dá)式A與表達(dá)式B相等，則為TRUE啦桌；否則為FALSE

hive> select 1 from iteblog where 1=1;
1

2准颓、不等值比較: <>

語(yǔ)法: A <> B
操作類型: 所有基本類型
描述: 如果表達(dá)式A為NULL，或者表達(dá)式B為NULL凉馆，返回NULL薪寓；如果表達(dá)式A與表達(dá)式B不相等，則為TRUE澜共；否則為FALSE

hive> select 1 from iteblog where 1 <> 2;
1

3向叉、小于比較: <

語(yǔ)法: A < B
操作類型：所有基本類型
描述: 如果表達(dá)式A為NULL，或者表達(dá)式B為NULL嗦董，返回NULL母谎；如果表達(dá)式A小于表達(dá)式B，則為TRUE京革；否則為FALSE

hive> select 1 from iteblog where 1 < 2;
1

4奇唤、小于等于比較: <=

語(yǔ)法: A <= B
操作類型: 所有基本類型
描述: 如果表達(dá)式A為NULL供璧，或者表達(dá)式B為NULL，返回NULL冻记；如果表達(dá)式A小于或者等于表達(dá)式B睡毒，則為TRUE；否則為FALSE

hive> select 1 from iteblog where 1 < = 1;
1

5冗栗、大于比較: >

語(yǔ)法: A > B
操作類型: 所有基本類型
描述: 如果表達(dá)式A為NULL演顾，或者表達(dá)式B為NULL，返回NULL隅居；如果表達(dá)式A大于表達(dá)式B钠至，則為TRUE；否則為FALSE

hive> select 1 from iteblog where 2 > 1;
1

6胎源、大于等于比較: >=

語(yǔ)法: A >= B
操作類型: 所有基本類型
描述: 如果表達(dá)式A為NULL棉钧，或者表達(dá)式B為NULL，返回NULL涕蚤；如果表達(dá)式A大于或者等于表達(dá)式B宪卿，則為TRUE；否則為FALSE

hive> select 1 from iteblog where 1 >= 1;
1

注意：String的比較要注意(常用的時(shí)間比較可以先 to_date 之后再比較)

hive> select * from iteblog;
OK
2011111209 00:00:00     2011111209
hive> select a, b, a<b, a>b, a=b from iteblog;
2011111209 00:00:00     2011111209 false   true    false

7万栅、空值判斷: IS NULL

語(yǔ)法: A IS NULL
操作類型: 所有類型
描述: 如果表達(dá)式A的值為NULL佑钾，則為TRUE；否則為FALSE

hive> select 1 from iteblog where null is null;
1

8烦粒、非空判斷: IS NOT NULL

語(yǔ)法: A IS NOT NULL
操作類型: 所有類型
描述: 如果表達(dá)式A的值為NULL休溶，則為FALSE；否則為TRUE

hive> select 1 from iteblog where 1 is not null;
1

9扰她、LIKE比較: LIKE

語(yǔ)法: A LIKE B
操作類型: strings
描述: 如果字符串A或者字符串B為NULL兽掰，則返回NULL；如果字符串A符合表達(dá)式B 的正則語(yǔ)法徒役，則為TRUE孽尽；否則為FALSE。B中字符”_”表示任意單個(gè)字符廉涕，而字符”%”表示任意數(shù)量的字符泻云。

hive> select 1 from iteblog where 'football' like 'foot%';
1
hive> select 1 from iteblog where 'football' like 'foot____';
1

注意：否定比較時(shí)候用NOT A LIKE B

hive> select 1 from iteblog where NOT 'football' like 'fff%';
1

10、JAVA的LIKE操作: RLIKE

語(yǔ)法: A RLIKE B
操作類型: strings
描述: 如果字符串A或者字符串B為NULL狐蜕，則返回NULL宠纯；如果字符串A符合JAVA正則表達(dá)式B的正則語(yǔ)法，則為TRUE层释；否則為FALSE婆瓜。

hive> select 1 from iteblog where 'footbar' rlike '^f.*r$';
1
注意：判斷一個(gè)字符串是否全為數(shù)字：
hive>select 1 from iteblog where '123456' rlike '^\\d+$';
1
hive> select 1 from iteblog where '123456aa' rlike '^\\d+$';

11、REGEXP操作: REGEXP

語(yǔ)法: A REGEXP B
操作類型: strings
描述: 功能與RLIKE相同

hive> select 1 from iteblog where 'footbar' REGEXP '^f.*r$';
1

數(shù)學(xué)運(yùn)算：

1、加法操作: +

語(yǔ)法: A + B
操作類型：所有數(shù)值類型
說(shuō)明：返回A與B相加的結(jié)果廉白。結(jié)果的數(shù)值類型等于A的類型和B的類型的最小父類型（詳見(jiàn)數(shù)據(jù)類型的繼承關(guān)系）个初。比如，int + int 一般結(jié)果為int類型猴蹂，而 int + double 一般結(jié)果為double類型

hive> select 1 + 9 from iteblog;
10
hive> create table iteblog as select 1 + 1.2 from iteblog;
hive> describe iteblog;
_c0 double

2院溺、減法操作: -

語(yǔ)法: A – B
操作類型：所有數(shù)值類型
說(shuō)明：返回A與B相減的結(jié)果。結(jié)果的數(shù)值類型等于A的類型和B的類型的最小父類型（詳見(jiàn)數(shù)據(jù)類型的繼承關(guān)系）磅轻。比如珍逸，int – int 一般結(jié)果為int類型，而 int – double 一般結(jié)果為double類型

hive> select 10 – 5 from iteblog;
5
hive> create table iteblog as select 5.6 – 4 from iteblog;
hive> describe iteblog;
_c0 double

3聋溜、乘法操作: *

語(yǔ)法: A * B
操作類型：所有數(shù)值類型
說(shuō)明：返回A與B相乘的結(jié)果谆膳。結(jié)果的數(shù)值類型等于A的類型和B的類型的最小父類型（詳見(jiàn)數(shù)據(jù)類型的繼承關(guān)系）。注意撮躁，如果A乘以B的結(jié)果超過(guò)默認(rèn)結(jié)果類型的數(shù)值范圍漱病，則需要通過(guò)cast將結(jié)果轉(zhuǎn)換成范圍更大的數(shù)值類型

hive> select 40 * 5 from iteblog;
200

4、除法操作: /

語(yǔ)法: A / B
操作類型：所有數(shù)值類型
說(shuō)明：返回A除以B的結(jié)果把曼。結(jié)果的數(shù)值類型為double

hive> select 40 / 5 from iteblog;
8.0

注意：hive中最高精度的數(shù)據(jù)類型是double,只精確到小數(shù)點(diǎn)后16位杨帽，在做除法運(yùn)算的時(shí)候要特別注意

hive>select ceil(28.0/6.999999999999999999999) from iteblog limit 1;   
結(jié)果為4
hive>select ceil(28.0/6.99999999999999) from iteblog limit 1;          
結(jié)果為5

5、取余操作: %

語(yǔ)法: A % B
操作類型：所有數(shù)值類型
說(shuō)明：返回A除以B的余數(shù)祝迂。結(jié)果的數(shù)值類型等于A的類型和B的類型的最小父類型（詳見(jiàn)數(shù)據(jù)類型的繼承關(guān)系）睦尽。

hive> select 41 % 5 from iteblog;
1
hive> select 8.4 % 4 from iteblog;
0.40000000000000036
--注意：精度在hive中是個(gè)很大的問(wèn)題，類似這樣的操作最好通過(guò)round指定精度
hive> select round(8.4 % 4 , 2) from iteblog;
0.4

6型雳、位與操作: &

語(yǔ)法: A & B
操作類型：所有數(shù)值類型
說(shuō)明：返回A和B按位進(jìn)行與操作的結(jié)果。結(jié)果的數(shù)值類型等于A的類型和B的類型的最小父類型（詳見(jiàn)數(shù)據(jù)類型的繼承關(guān)系）山害。

hive> select 4 & 8 from iteblog;
0
hive> select 6 & 4 from iteblog;
4

7纠俭、位或操作:```

語(yǔ)法: A | B
操作類型：所有數(shù)值類型
說(shuō)明：返回A和B按位進(jìn)行或操作的結(jié)果。結(jié)果的數(shù)值類型等于A的類型和B的類型的最小父類型（詳見(jiàn)數(shù)據(jù)類型的繼承關(guān)系）浪慌。

hive> select 4 | 8 from iteblog;
12
hive> select 6 | 8 from iteblog;
14

8冤荆、位異或操作: ^

語(yǔ)法: A ^ B
操作類型：所有數(shù)值類型
說(shuō)明：返回A和B按位進(jìn)行異或操作的結(jié)果。結(jié)果的數(shù)值類型等于A的類型和B的類型的最小父類型（詳見(jiàn)數(shù)據(jù)類型的繼承關(guān)系）权纤。

hive> select 4 ^ 8 from iteblog;
12
hive> select 6 ^ 4 from iteblog;
2

9．位取反操作: ~

語(yǔ)法: ~A
操作類型：所有數(shù)值類型
說(shuō)明：返回A按位取反操作的結(jié)果钓简。結(jié)果的數(shù)值類型等于A的類型。

hive> select ~6 from iteblog;
-7
hive> select ~4 from iteblog;
-5

邏輯運(yùn)算：

1汹想、邏輯與操作: AND

語(yǔ)法: A AND B
操作類型：boolean
說(shuō)明：如果A和B均為TRUE外邓，則為TRUE；否則為FALSE古掏。如果A為NULL或B為NULL损话，則為NULL

hive> select 1 from iteblog where 1=1 and 2=2;
1

2、邏輯或操作: OR

語(yǔ)法: A OR B
操作類型：boolean
說(shuō)明：如果A為TRUE，或者B為TRUE丧枪，或者A和B均為TRUE光涂，則為TRUE；否則為FALSE

hive> select 1 from iteblog where 1=2 or 2=2;
1

3拧烦、邏輯非操作: NOT

語(yǔ)法: NOT A
操作類型：boolean
說(shuō)明：如果A為FALSE忘闻，或者A為NULL，則為TRUE恋博；否則為FALSE

hive> select 1 from iteblog where not 1=2;
1

數(shù)值計(jì)算

1齐佳、取整函數(shù): round

語(yǔ)法: round(double a)
返回值: BIGINT
說(shuō)明: 返回double類型的整數(shù)值部分 （遵循四舍五入）

hive> select round(3.1415926) from iteblog;
3
hive> select round(3.5) from iteblog;
4
hive> create table iteblog as select round(9542.158) from iteblog;
hive> describe iteblog;
_c0 bigint

2、指定精度取整函數(shù): round

語(yǔ)法: round(double a, int d)
返回值: DOUBLE
說(shuō)明: 返回指定精度d的double類型

hive> select round(3.1415926,4) from iteblog;
3.1416

3交播、向下取整函數(shù): floor

語(yǔ)法: floor(double a)
返回值: BIGINT
說(shuō)明: 返回等于或者小于該double變量的最大的整數(shù)

hive> select floor(3.1415926) from iteblog;
3
hive> select floor(25) from iteblog;
25

4重虑、向上取整函數(shù): ceil

語(yǔ)法: ceil(double a)
返回值: BIGINT
說(shuō)明: 返回等于或者大于該double變量的最小的整數(shù)

hive> select ceil(3.1415926) from iteblog;
4
hive> select ceil(46) from iteblog;
46

5、向上取整函數(shù): ceiling

語(yǔ)法: ceiling(double a)
返回值: BIGINT
說(shuō)明: 與ceil功能相同

hive> select ceiling(3.1415926) from iteblog;
4
hive> select ceiling(46) from iteblog;
46

6秦士、取隨機(jī)數(shù)函數(shù): rand

語(yǔ)法: rand(),rand(int seed)
返回值: double
說(shuō)明: 返回一個(gè)0到1范圍內(nèi)的隨機(jī)數(shù)缺厉。如果指定種子seed，則會(huì)等到一個(gè)穩(wěn)定的隨機(jī)數(shù)序列

hive> select rand() from iteblog;
0.5577432776034763
hive> select rand() from iteblog;
0.6638336467363424
hive> select rand(100) from iteblog;
0.7220096548596434
hive> select rand(100) from iteblog;
0.7220096548596434

7隧土、自然指數(shù)函數(shù): exp

語(yǔ)法: exp(double a)
返回值: double
說(shuō)明: 返回自然對(duì)數(shù)e的a次方

hive> select exp(2) from iteblog;
7.38905609893065
-- 自然對(duì)數(shù)函數(shù): ln
-- 語(yǔ)法: ln(double a)
-- 返回值: double
-- 說(shuō)明: 返回a的自然對(duì)數(shù)
1
hive> select ln(7.38905609893065) from iteblog;
2.0

8提针、以10為底對(duì)數(shù)函數(shù): log10

語(yǔ)法: log10(double a)
返回值: double
說(shuō)明: 返回以10為底的a的對(duì)數(shù)

hive> select log10(100) from iteblog;
2.0

9、以2為底對(duì)數(shù)函數(shù): log2

語(yǔ)法: log2(double a)
返回值: double
說(shuō)明: 返回以2為底的a的對(duì)數(shù)

hive> select log2(8) from iteblog;
3.0

10曹傀、對(duì)數(shù)函數(shù): log

語(yǔ)法: log(double base, double a)
返回值: double
說(shuō)明: 返回以base為底的a的對(duì)數(shù)

hive> select log(4,256) from iteblog;
4.0

11辐脖、冪運(yùn)算函數(shù): pow

語(yǔ)法: pow(double a, double p)
返回值: double
說(shuō)明: 返回a的p次冪

hive> select pow(2,4) from iteblog;
16.0

12、冪運(yùn)算函數(shù): power

語(yǔ)法: power(double a, double p)
返回值: double
說(shuō)明: 返回a的p次冪,與pow功能相同

hive> select power(2,4) from iteblog;
16.0

13皆愉、開(kāi)平方函數(shù): sqrt

語(yǔ)法: sqrt(double a)
返回值: double
說(shuō)明: 返回a的平方根

hive> select sqrt(16) from iteblog;
4.0

14嗜价、二進(jìn)制函數(shù): bin

語(yǔ)法: bin(BIGINT a)
返回值: string
說(shuō)明: 返回a的二進(jìn)制代碼表示

hive> select bin(7) from iteblog;
111

15、十六進(jìn)制函數(shù): hex

語(yǔ)法: hex(BIGINT a)
返回值: string
說(shuō)明: 如果變量是int類型幕庐，那么返回a的十六進(jìn)制表示久锥；如果變量是string類型，則返回該字符串的十六進(jìn)制表示

hive> select hex(17) from iteblog;
11
hive> select hex('abc') from iteblog;
616263

16异剥、反轉(zhuǎn)十六進(jìn)制函數(shù): unhex

語(yǔ)法: unhex(string a)
返回值: string
說(shuō)明: 返回該十六進(jìn)制字符串所代碼的字符串

hive> select unhex('616263') from iteblog;
abc
hive> select unhex('11') from iteblog;
-
hive> select unhex(616263) from iteblog;
abc

17瑟由、進(jìn)制轉(zhuǎn)換函數(shù): conv

語(yǔ)法: conv(BIGINT num, int from_base, int to_base)
返回值: string
說(shuō)明: 將數(shù)值num從from_base進(jìn)制轉(zhuǎn)化到to_base進(jìn)制

hive> select conv(17,10,16) from iteblog;
11
hive> select conv(17,10,2) from iteblog;
10001

18、絕對(duì)值函數(shù): abs

語(yǔ)法: abs(double a) abs(int a)
返回值: double int
說(shuō)明: 返回?cái)?shù)值a的絕對(duì)值

hive> select abs(-3.9) from iteblog;
3.9
hive> select abs(10.9) from iteblog;
10.9

19冤寿、正取余函數(shù): pmod

語(yǔ)法: pmod(int a, int b),pmod(double a, double b)
返回值: int double
說(shuō)明: 返回正的a除以b的余數(shù)

hive> select pmod(9,4) from iteblog;
1
hive> select pmod(-9,4) from iteblog;
3

20歹苦、正弦函數(shù): sin

語(yǔ)法: sin(double a)
返回值: double
說(shuō)明: 返回a的正弦值

hive> select sin(0.8) from iteblog;
0.7173560908995228

21、反正弦函數(shù): asin

語(yǔ)法: asin(double a)
返回值: double
說(shuō)明: 返回a的反正弦值

hive> select asin(0.7173560908995228) from iteblog;
0.8

22督怜、余弦函數(shù): cos

語(yǔ)法: cos(double a)
返回值: double
說(shuō)明: 返回a的余弦值

hive> select cos(0.9) from iteblog;
0.6216099682706644

23殴瘦、反余弦函數(shù): acos

語(yǔ)法: acos(double a)
返回值: double
說(shuō)明: 返回a的反余弦值

hive> select acos(0.6216099682706644) from iteblog;
0.9

24、positive函數(shù): positive

語(yǔ)法: positive(int a), positive(double a)
返回值: int double
說(shuō)明: 返回a

hive> select positive(-10) from iteblog;
-10
hive> select positive(12) from iteblog;
12

25亮蛔、negative函數(shù): negative

語(yǔ)法: negative(int a), negative(double a)
返回值: int double
說(shuō)明: 返回-a

hive> select negative(-5) from iteblog;
5
hive> select negative(8) from iteblog;
-8

日期函數(shù)

1痴施、UNIX時(shí)間戳轉(zhuǎn)日期函數(shù): from_unixtime

語(yǔ)法: from_unixtime(bigint unixtime[, string format])
返回值: string
說(shuō)明: 轉(zhuǎn)化UNIX時(shí)間戳（從1970-01-01 00:00:00 UTC到指定時(shí)間的秒數(shù)）到當(dāng)前時(shí)區(qū)的時(shí)間格式

hive> select from_unixtime(1323308943,'yyyyMMdd') from iteblog;
20111208

2、獲取當(dāng)前UNIX時(shí)間戳函數(shù): unix_timestamp

語(yǔ)法: unix_timestamp()
返回值: bigint
說(shuō)明: 獲得當(dāng)前時(shí)區(qū)的UNIX時(shí)間戳

hive> select unix_timestamp() from iteblog;
1323309615

3、日期轉(zhuǎn)UNIX時(shí)間戳函數(shù): unix_timestamp

語(yǔ)法: unix_timestamp(string date)
返回值: bigint
說(shuō)明: 轉(zhuǎn)換格式為"yyyy-MM-dd HH:mm:ss"的日期到UNIX時(shí)間戳辣吃。如果轉(zhuǎn)化失敗动遭，則返回0。

hive> select unix_timestamp('2011-12-07 13:01:03') from iteblog;
1323234063

4神得、指定格式日期轉(zhuǎn)UNIX時(shí)間戳函數(shù): unix_timestamp

語(yǔ)法: unix_timestamp(string date, string pattern)
返回值: bigint
說(shuō)明: 轉(zhuǎn)換pattern格式的日期到UNIX時(shí)間戳厘惦。如果轉(zhuǎn)化失敗，則返回0哩簿。

hive> select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from iteblog;
1323234063

5宵蕉、日期時(shí)間轉(zhuǎn)日期函數(shù): to_date

語(yǔ)法: to_date(string timestamp)
返回值: string
說(shuō)明: 返回日期時(shí)間字段中的日期部分。

hive> select to_date('2011-12-08 10:03:01') from iteblog;
2011-12-08

6节榜、日期轉(zhuǎn)年函數(shù): year

語(yǔ)法: year(string date)
返回值: int
說(shuō)明: 返回日期中的年羡玛。

hive> select year('2011-12-08 10:03:01') from iteblog;
2011
hive> select year('2012-12-08') from iteblog;
2012

7、日期轉(zhuǎn)月函數(shù): month

語(yǔ)法: month (string date)
返回值: int
說(shuō)明: 返回日期中的月份宗苍。

hive> select month('2011-12-08 10:03:01') from iteblog;
12
hive> select month('2011-08-08') from iteblog;
8

8稼稿、日期轉(zhuǎn)天函數(shù): day

語(yǔ)法: day (string date)
返回值: int
說(shuō)明: 返回日期中的天。

hive> select day('2011-12-08 10:03:01') from iteblog;
8
hive> select day('2011-12-24') from iteblog;
24

9讳窟、日期轉(zhuǎn)小時(shí)函數(shù): hour

語(yǔ)法: hour (string date)
返回值: int
說(shuō)明: 返回日期中的小時(shí)让歼。

hive> select hour('2011-12-08 10:03:01') from iteblog;
10

10、日期轉(zhuǎn)分鐘函數(shù): minute

語(yǔ)法: minute (string date)
返回值: int
說(shuō)明: 返回日期中的分鐘丽啡。

hive> select minute('2011-12-08 10:03:01') from iteblog;
3

11谋右、日期轉(zhuǎn)秒函數(shù): second

語(yǔ)法: second (string date)
返回值: int
說(shuō)明: 返回日期中的秒。

hive> select second('2011-12-08 10:03:01') from iteblog;
1

12补箍、日期轉(zhuǎn)周函數(shù): weekofyear

語(yǔ)法: weekofyear (string date)
返回值: int
說(shuō)明: 返回日期在當(dāng)前的周數(shù)改执。

hive> select weekofyear('2011-12-08 10:03:01') from iteblog;
49

13、日期比較函數(shù): datediff

語(yǔ)法: datediff(string enddate, string startdate)
返回值: int
說(shuō)明: 返回結(jié)束日期減去開(kāi)始日期的天數(shù)坑雅。

hive> select datediff('2012-12-08','2012-05-09') from iteblog;
213

14天梧、日期增加函數(shù): date_add

語(yǔ)法: date_add(string startdate, int days)
返回值: string
說(shuō)明: 返回開(kāi)始日期startdate增加days天后的日期。

hive> select date_add('2012-12-08',10) from iteblog;
2012-12-18

15霞丧、日期減少函數(shù): date_sub

語(yǔ)法: date_sub (string startdate, int days)
返回值: string
說(shuō)明: 返回開(kāi)始日期startdate減少days天后的日期。

hive> select date_sub('2012-12-08',10) from iteblog;
2012-11-28

條件函數(shù)

1冕香、If函數(shù): if

語(yǔ)法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
說(shuō)明: 當(dāng)條件testCondition為TRUE時(shí)蛹尝，返回valueTrue；否則返回valueFalseOrNull

hive> select if(1=2,100,200) from iteblog;
200
hive> select if(1=1,100,200) from iteblog;
100

2悉尾、非空查找函數(shù): COALESCE

語(yǔ)法: COALESCE(T v1, T v2, …)
返回值: T
說(shuō)明: 返回參數(shù)中的第一個(gè)非空值突那；如果所有值都為NULL，那么返回NULL

hive> select COALESCE(null,'100','50′) from iteblog;
100

3构眯、條件判斷函數(shù)：CASE

語(yǔ)法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
返回值: T
說(shuō)明：如果a等于b愕难，那么返回c；如果a等于d，那么返回e猫缭；否則返回f

hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from iteblog;
mary
hive> Select case 200 when 50 then 'tom' when 100 then 'mary' else 'tim' end from iteblog;
tim

4葱弟、條件判斷函數(shù)：CASE

語(yǔ)法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
返回值: T
說(shuō)明：如果a為TRUE,則返回b；如果c為TRUE猜丹，則返回d芝加；否則返回e

hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from iteblog;
mary
hive> select case when 1=1 then 'tom' when 2=2 then 'mary' else 'tim' end from iteblog;
tom

字符串函數(shù)

1、字符串長(zhǎng)度函數(shù)：length

語(yǔ)法: length(string A)
返回值: int
說(shuō)明：返回字符串A的長(zhǎng)度

hive> select length('abcedfg') from iteblog;
7

2射窒、字符串反轉(zhuǎn)函數(shù)：reverse

語(yǔ)法: reverse(string A)
返回值: string
說(shuō)明：返回字符串A的反轉(zhuǎn)結(jié)果

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

3藏杖、字符串連接函數(shù)：concat

語(yǔ)法: concat(string A, string B…)
返回值: string
說(shuō)明：返回輸入字符串連接后的結(jié)果，支持任意個(gè)輸入字符串

hive> select concat('abc','def','gh') from iteblog;
abcdefgh

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

語(yǔ)法: concat_ws(string SEP, string A, string B…)
返回值: string
說(shuō)明：返回輸入字符串連接后的結(jié)果蝌麸，SEP表示各個(gè)字符串間的分隔符

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

5、字符串截取函數(shù)：substr,substring

語(yǔ)法: substr(string A, int start),substring(string A, int start)
返回值: string
說(shuō)明：返回字符串A從start位置到結(jié)尾的字符串

hive> select substr('abcde',3) from iteblog;
cde
hive> select substring('abcde',3) from iteblog;
cde
hive> select substr('abcde',-1) from iteblog;  （和ORACLE相同）
e

6艾疟、字符串截取函數(shù)：substr,substring

語(yǔ)法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
說(shuō)明：返回字符串A從start位置開(kāi)始收擦，長(zhǎng)度為len的字符串

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

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

語(yǔ)法: upper(string A) ucase(string A)
返回值: string
說(shuō)明：返回字符串A的大寫格式

hive> select upper('abSEd') from iteblog;
ABSED
hive> select ucase('abSEd') from iteblog;
ABSED

8此衅、字符串轉(zhuǎn)小寫函數(shù)：lower,lcase

語(yǔ)法: lower(string A) lcase(string A)
返回值: string
說(shuō)明：返回字符串A的小寫格式

hive> select lower('abSEd') from iteblog;
absed
hive> select lcase('abSEd') from iteblog;
absed

9春叫、去空格函數(shù)：trim

語(yǔ)法: trim(string A)
返回值: string
說(shuō)明：去除字符串兩邊的空格

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

10、左邊去空格函數(shù)：ltrim

語(yǔ)法: ltrim(string A)
返回值: string
說(shuō)明：去除字符串左邊的空格

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

11碾褂、右邊去空格函數(shù)：rtrim

語(yǔ)法: rtrim(string A)
返回值: string
說(shuō)明：去除字符串右邊的空格

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

12兽间、正則表達(dá)式替換函數(shù)：regexp_replace

語(yǔ)法: regexp_replace(string A, string B, string C)
返回值: string
說(shuō)明：將字符串A中的符合java正則表達(dá)式B的部分替換為C。注意正塌，在有些情況下要使用轉(zhuǎn)義字符,類似oracle中的regexp_replace函數(shù)嘀略。

hive> select regexp_replace('foobar', 'oo|ar', '') from iteblog;
fb

13、正則表達(dá)式解析函數(shù)：regexp_extract

語(yǔ)法: regexp_extract(string subject, string pattern, int index)
返回值: string
說(shuō)明：將字符串subject按照pattern正則表達(dá)式的規(guī)則拆分乓诽，返回index指定的字符帜羊。

hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from iteblog;
the
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) from iteblog;
bar
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from iteblog;
foothebar
-- 注意，在有些情況下要使用轉(zhuǎn)義字符鸠天，下面的等號(hào)要用雙豎線轉(zhuǎn)義讼育，這是java正則表達(dá)式的規(guī)則。
select data_field,
regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) as aaa,
regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1) as bbb,
regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1) as ccc
from pt_nginx_loginlog_st
where pt = '2012-03-26' limit 2;

14稠集、URL解析函數(shù)：parse_url

語(yǔ)法: parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
說(shuō)明：返回URL中指定的部分奶段。partToExtract的有效值為：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

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

15、json解析函數(shù)：get_json_object

語(yǔ)法: get_json_object(string json_string, string path)
返回值: string
說(shuō)明：解析json的字符串json_string,返回path指定的內(nèi)容剥纷。如果輸入的json字符串無(wú)效痹籍，那么返回NULL。

hive> select  get_json_object('{"store":
>   {"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
>    "bicycle":{"price":19.95,"color":"red"}
>   },
>  "email":"amy@only_for_json_udf_test.net",
>  "owner":"amy"
> }
> ','$.owner') from iteblog;
amy

16晦鞋、空格字符串函數(shù)：space

語(yǔ)法: space(int n)
返回值: string
說(shuō)明：返回長(zhǎng)度為n的字符串

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

17蹲缠、重復(fù)字符串函數(shù)：repeat

語(yǔ)法: repeat(string str, int n)
返回值: string
說(shuō)明：返回重復(fù)n次后的str字符串

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

18棺克、首字符ascii函數(shù)：ascii

語(yǔ)法: ascii(string str)
返回值: int
說(shuō)明：返回字符串str第一個(gè)字符的ascii碼

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

19、左補(bǔ)足函數(shù)：lpad

語(yǔ)法: lpad(string str, int len, string pad)
返回值: string
說(shuō)明：將str進(jìn)行用pad進(jìn)行左補(bǔ)足到len位

hive> select lpad('abc',10,'td') from iteblog;
tdtdtdtabc
--注意：與GP线定，ORACLE不同娜谊，pad 不能默認(rèn)

20、右補(bǔ)足函數(shù)：rpad

語(yǔ)法: rpad(string str, int len, string pad)
返回值: string
說(shuō)明：將str進(jìn)行用pad進(jìn)行右補(bǔ)足到len位

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

21渔肩、分割字符串函數(shù): split

語(yǔ)法: split(string str, string pat)
返回值: array
說(shuō)明: 按照pat字符串分割str因俐，會(huì)返回分割后的字符串?dāng)?shù)組

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

22、集合查找函數(shù): find_in_set

語(yǔ)法: find_in_set(string str, string strList)
返回值: int
說(shuō)明: 返回str在strlist第一次出現(xiàn)的位置周偎，strlist是用逗號(hào)分割的字符串抹剩。如果沒(méi)有找該str字符，則返回0

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

集合統(tǒng)計(jì)函數(shù)

1蓉坎、個(gè)數(shù)統(tǒng)計(jì)函數(shù): count

語(yǔ)法: count(), count(expr), count(DISTINCT expr[, expr_.])
返回值: int
說(shuō)明: count()統(tǒng)計(jì)檢索出的行的個(gè)數(shù)澳眷，包括NULL值的行；count(expr)返回指定字段的非空值的個(gè)數(shù)蛉艾；count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的個(gè)數(shù)

hive> select count(*) from iteblog;
20
hive> select count(distinct t) from iteblog;
10

2钳踊、總和統(tǒng)計(jì)函數(shù): sum

語(yǔ)法: sum(col), sum(DISTINCT col)
返回值: double
說(shuō)明: sum(col)統(tǒng)計(jì)結(jié)果集中col的相加的結(jié)果；sum(DISTINCT col)統(tǒng)計(jì)結(jié)果中col不同值相加的結(jié)果

hive> select sum(t) from iteblog;
100
hive> select sum(distinct t) from iteblog;
70

3勿侯、平均值統(tǒng)計(jì)函數(shù): avg

語(yǔ)法: avg(col), avg(DISTINCT col)
返回值: double
說(shuō)明: avg(col)統(tǒng)計(jì)結(jié)果集中col的平均值拓瞪；avg(DISTINCT col)統(tǒng)計(jì)結(jié)果中col不同值相加的平均值

hive> select avg(t) from iteblog;
50
hive> select avg (distinct t) from iteblog;
30

4、最小值統(tǒng)計(jì)函數(shù): min

語(yǔ)法: min(col)
返回值: double
說(shuō)明: 統(tǒng)計(jì)結(jié)果集中col字段的最小值

hive> select min(t) from iteblog;
20

5助琐、最大值統(tǒng)計(jì)函數(shù): max

語(yǔ)法: maxcol)
返回值: double
說(shuō)明: 統(tǒng)計(jì)結(jié)果集中col字段的最大值

hive> select max(t) from iteblog;
120

6祭埂、非空集合總體變量函數(shù): var_pop

語(yǔ)法: var_pop(col)
返回值: double
說(shuō)明: 統(tǒng)計(jì)結(jié)果集中col非空集合的總體變量（忽略null）

7、非空集合樣本變量函數(shù): var_samp

語(yǔ)法: var_samp(col)
返回值: double
說(shuō)明: 統(tǒng)計(jì)結(jié)果集中col非空集合的樣本變量（忽略null）

8兵钮、總體標(biāo)準(zhǔn)偏離函數(shù): stddev_pop

語(yǔ)法: stddev_pop(col)
返回值: double
說(shuō)明: 該函數(shù)計(jì)算總體標(biāo)準(zhǔn)偏離蛆橡，并返回總體變量的平方根，其返回值與VAR_POP函數(shù)的平方根相同

9掘譬、樣本標(biāo)準(zhǔn)偏離函數(shù): stddev_samp

語(yǔ)法: stddev_samp(col)
返回值: double
說(shuō)明: 該函數(shù)計(jì)算樣本標(biāo)準(zhǔn)偏離

10．中位數(shù)函數(shù): percentile

語(yǔ)法: percentile(BIGINT col, p)
返回值: double
說(shuō)明: 求準(zhǔn)確的第pth個(gè)百分位數(shù)泰演，p必須介于0和1之間，但是col字段目前只支持整數(shù)葱轩，不支持浮點(diǎn)數(shù)類型

11睦焕、中位數(shù)函數(shù): percentile

語(yǔ)法: percentile(BIGINT col, array(p1 [, p2]…))
返回值: array<double>
說(shuō)明: 功能和上述類似，之后后面可以輸入多個(gè)百分位數(shù)靴拱，返回類型也為array<double>复亏，其中為對(duì)應(yīng)的百分位數(shù)。

select percentile(score,<0.2,0.4>) from iteblog缭嫡； --取0.2，0.4位置的數(shù)據(jù)

12抬闷、近似中位數(shù)函數(shù): percentile_approx

語(yǔ)法: percentile_approx(DOUBLE col, p [, B])
返回值: double
說(shuō)明: 求近似的第pth個(gè)百分位數(shù)妇蛀，p必須介于0和1之間耕突，返回類型為double，但是col字段支持浮點(diǎn)類型评架。參數(shù)B控制內(nèi)存消耗的近似精度眷茁，B越大，結(jié)果的準(zhǔn)確度越高纵诞。默認(rèn)為10,000上祈。當(dāng)col字段中的distinct值的個(gè)數(shù)小于B時(shí)，結(jié)果為準(zhǔn)確的百分位數(shù)

13浙芙、近似中位數(shù)函數(shù): percentile_approx

語(yǔ)法: percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B])
返回值: array<double>
說(shuō)明: 功能和上述類似登刺，之后后面可以輸入多個(gè)百分位數(shù)，返回類型也為array<double>嗡呼，其中為對(duì)應(yīng)的百分位數(shù)纸俭。

14、直方圖: histogram_numeric

語(yǔ)法: histogram_numeric(col, b)
返回值: array<struct {'x','y'}>
說(shuō)明: 以b為基準(zhǔn)計(jì)算col的直方圖信息南窗。

hive> select histogram_numeric(100,5) from iteblog;
[{"x":100.0,"y":1.0}]

復(fù)合類型構(gòu)建操作

1揍很、Map類型構(gòu)建: map

語(yǔ)法: map (key1, value1, key2, value2, …)
說(shuō)明：根據(jù)輸入的key和value對(duì)構(gòu)建map類型

hive> Create table iteblog as select map('100','tom','200','mary') as t from iteblog;
hive> describe iteblog;
t       map<string ,string>
hive> select t from iteblog;
{"100":"tom","200":"mary"}

2、Struct類型構(gòu)建: struct

語(yǔ)法: struct(val1, val2, val3, …)
說(shuō)明：根據(jù)輸入的參數(shù)構(gòu)建結(jié)構(gòu)體struct類型

hive> create table iteblog as select struct('tom','mary','tim') as t from iteblog;
hive> describe iteblog;
t       struct<col1:string ,col2:string,col3:string>
hive> select t from iteblog;
{"col1":"tom","col2":"mary","col3":"tim"}

3万伤、array類型構(gòu)建: array

語(yǔ)法: array(val1, val2, …)
說(shuō)明：根據(jù)輸入的參數(shù)構(gòu)建數(shù)組array類型

hive> create table iteblog as select array("tom","mary","tim") as t from iteblog;
hive> describe iteblog;
t       array<string>
hive> select t from iteblog;
["tom","mary","tim"]

復(fù)雜類型訪問(wèn)操作

1窒悔、array類型訪問(wèn): A[n]

語(yǔ)法: A[n]
操作類型: A為array類型，n為int類型
說(shuō)明：返回?cái)?shù)組A中的第n個(gè)變量值敌买。數(shù)組的起始下標(biāo)為0简珠。比如，A是個(gè)值為['foo', 'bar']的數(shù)組類型放妈，那么A[0]將返回'foo',而A[1]將返回'bar'

hive> create table iteblog as select array("tom","mary","tim") as t from iteblog;
hive> select t[0],t[1],t[2] from iteblog;
tom     mary    tim

2北救、map類型訪問(wèn): M[key]

語(yǔ)法: M[key]
操作類型: M為map類型，key為map中的key值
說(shuō)明：返回map類型M中芜抒，key值為指定值的value值珍策。比如，M是值為{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}的map類型宅倒，那么M['all']將會(huì)返回'foobar'

hive> Create table iteblog as select map('100','tom','200','mary') as t from iteblog;
hive> select t['200'],t['100'] from iteblog;
mary    tom

3攘宙、struct類型訪問(wèn): S.x

語(yǔ)法: S.x
操作類型: S為struct類型
說(shuō)明：返回結(jié)構(gòu)體S中的x字段。比如拐迁，對(duì)于結(jié)構(gòu)體struct foobar {int foo, int bar}蹭劈，foobar.foo返回結(jié)構(gòu)體中的foo字段

hive> create table iteblog as select struct('tom','mary','tim') as t from iteblog;
hive> describe iteblog;
t       struct<col1:string ,col2:string,col3:string>
hive> select t.col1,t.col3 from iteblog;
tom     tim

復(fù)雜類型長(zhǎng)度統(tǒng)計(jì)函數(shù)

1.Map類型長(zhǎng)度函數(shù): size(Map<k .V>)

語(yǔ)法: size(Map<k .V>)
返回值: int
說(shuō)明: 返回map類型的長(zhǎng)度

hive> select size(map('100','tom','101','mary')) from iteblog;
2

2.array類型長(zhǎng)度函數(shù): size(Array<T>)

語(yǔ)法: size(Array<T>)
返回值: int
說(shuō)明: 返回array類型的長(zhǎng)度

hive> select size(array('100','101','102','103')) from iteblog;
4

3.類型轉(zhuǎn)換函數(shù)

語(yǔ)法: cast(expr as <type>)
返回值: Expected "=" to follow "type"
說(shuō)明: 返回轉(zhuǎn)換后的數(shù)據(jù)類型

hive> select cast(1 as bigint) from iteblog;
1

4.collect_set

語(yǔ)法: collect_set(col)
返回值： array

5.collect_list

語(yǔ)法: collect_list(col)
返回值: array
說(shuō)明: Returns a list of objects with duplicates. (As of Hive 0.13.0.)

內(nèi)置表生成函數(shù)（UDTF）

explode

語(yǔ)法: explode(ARRAY<T> a)
返回值: T
說(shuō)明: 將數(shù)組爆炸為多行。返回一個(gè)具有單個(gè)列(col)的行集线召，數(shù)組中的每個(gè)元素對(duì)應(yīng)一行铺韧。

hive (ods)> select explode(array('A','B','C')) as col;
OK
col
A
B
C

hive (ods)> select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf;
OK
tf.col
A
B
C

explode

語(yǔ)法: explode(MAP<Tkey,Tvalue> m)
返回值: Tkey,Tvalue
說(shuō)明: 返回具有兩列(鍵、值)的行集缓淹，即輸入映射中的每個(gè)鍵-值對(duì)對(duì)應(yīng)一行哈打。(從Hive 0.8.0開(kāi)始)塔逃。

hive (ods)> select explode(map('A',10,'B',20,'C',30)) as (key,value);
OK
key     value
A       10
B       20
C       30

hive (ods)> select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf as key,value;
OK
tf.key  tf.value
A       10
B       20
C       30

posexplode

語(yǔ)法: posexplode(ARRAY<T> a)
返回值: int,T
說(shuō)明: 附加一個(gè)int類型的位置列(初始數(shù)組中項(xiàng)目的位置，從0開(kāi)始)料仗。

hive (ods)> select posexplode(array('A','B','C')) as (pos,val);
OK
pos     val
0       A
1       B
2       C

hive (ods)> select tf.* from (select 0) t lateral view posexplode(array('A','B','C')) tf as pos,val;
OK
tf.pos  tf.val
0       A
1       B
2       C

inline(ARRAY<STRUCT<f1:T1,...,fn:Tn>> a)

語(yǔ)法: inline(ARRAY<STRUCT<f1:T1,...,fn:Tn>> a)
返回值: T1,...,Tn
說(shuō)明: 將結(jié)構(gòu)數(shù)組爆炸為多行湾盗。返回一個(gè)有N列的行集(N =結(jié)構(gòu)中頂層元素的數(shù)量)，每個(gè)結(jié)構(gòu)從數(shù)組中取出一行立轧。(從Hive0.10開(kāi)始)

hive (ods)> select inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) as (col1,col2,col3);
OK
col1    col2    col3
A       10      2015-01-01
B       20      2016-02-02

hive (ods)> select tf.* from (select 0) t lateral view inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) tf as col1,col2,col3;
OK
tf.col1 tf.col2 tf.col3
A       10      2015-01-01
B       20      2016-02-02

stack

語(yǔ)法: stack(int r,T1 V1,...,Tn/r Vn)
返回值: T1,...,Tn/r
說(shuō)明: 分解n個(gè)V1格粪，…，Vn變成r行氛改。每一行都有n/r列帐萎。r必須是常數(shù)。

select stack(2,'A',10 ,'B',20 ,'C',30) as (col0,col1);
hive (ods)> select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') as (col0,col1,col2);
OK
col0    col1    col2
A       10      2015-01-01
B       20      2016-01-01

hive (ods)> select tf.* from (select 0) t lateral view stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') tf as col0,col1,col2;
OK
tf.col0 tf.col1 tf.col2
A       10      2015-01-01
B       20      2016-01-01

json_tuple

語(yǔ)法: json_tuple(string jsonStr,string k1,...,string kn)
返回值: string1,...,stringn
說(shuō)明: 獲取JSON字符串和一組n個(gè)鍵平窘，并返回一個(gè)包含n個(gè)值的元組吓肋。這是get_json_object UDF的一個(gè)更有效的版本，因?yàn)樗梢酝ㄟ^(guò)一個(gè)調(diào)用獲得多個(gè)鍵瑰艘。

json_tuple

語(yǔ)法: parse_url_tuple(string urlStr,string p1,...,string pn)
返回值: string1,...,stringn
說(shuō)明: 獲取URL字符串和一組n個(gè)URL部分是鬼，并返回一個(gè)包含n個(gè)值的元組。這類似于parse_url() UDF紫新，但可以同時(shí)從URL提取多個(gè)部分均蜜。有效的部分名稱有:主機(jī)、路徑芒率、查詢囤耳、引用、協(xié)議偶芍、權(quán)限充择、文件、用戶信息匪蟀、查詢:<KEY>椎麦。

使用SELECT udtf(col) AS colAlias…語(yǔ)法有幾個(gè)限制:

• SELECT中不允許有其他表達(dá)式
  不支持 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

請(qǐng)參閱LanguageManual LateralView，了解沒(méi)有這些限制的替代語(yǔ)法材彪。
如果您想創(chuàng)建自定義UDTF观挎，請(qǐng)參閱編寫UDTFs。

轉(zhuǎn)載自過(guò)往記憶（https://www.iteblog.com/）