Hive常用運算(關系運算)、邏輯運算與數學運算、數值運算、日期函數、條件函數、字元串函數

hive 常用運算

第一部分：關系運算

hive支援的關系運算符

•常見的關系運算符

•等值比較: =

•不等值比較: <>

•小于比較: <

•小于等于比較: <=

•大于比較: >

•大于等于比較: >=

•空值判斷: is null

•非空判斷: is not null

•like比較: like

•java的like操作: rlike

•regexp操作: regexp

   文法：a=b

操作類型：所有基本類型

描述: 如果表達式a與表達式b相等，則為true；否則為false

舉例：

hive> select 1 from dual where 1=1;

文法: a <> b

操作類型: 所有基本類型

描述: 如果表達式a為null，或者表達式b為null，傳回null；如果表達式a與表達式b不相等，則為true；否則為false

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

文法: a < b

描述: 如果表達式a為null，或者表達式b為null，傳回null；如果表達式a小于表達式b，則為true；否則為false

hive> select 1 from dual where 1 < 2;

文法: a <= b

描述: 如果表達式a為null，或者表達式b為null，傳回null；如果表達式a小于或者等于表達式b，則為true；否則為false

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

文法: a >= b

描述: 如果表達式a為null，或者表達式b為null，傳回null；如果表達式a大于或者等于表達式b，則為true；否則為false

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

文法: a is null

操作類型: 所有類型

描述: 如果表達式a的值為null，則為true；否則為false

hive> select 1 from dual where null isnull;

文法: a is not null

描述: 如果表達式a的值為null，則為false；否則為true

hive> select 1 from dual where 1 is notnull;

文法: a like b

操作類型: strings

描述: 如果字元串a或者字元串b為null，則傳回null；如果字元串a符合表達式b   的正則文法，則為true；否則為false。b中字元”_”表示任意單個字元，而字元”%”表示任意數量的字元。

hive> select 1 from dual where ‘key' like 'foot%';

1

hive> select 1 from dual where ‘key ' like 'foot____';

注意：否定比較時候用 not a like b

hive> select 1 from dual where not ‘key ' like 'fff%';

文法: a rlike b

描述: 如果字元串a或者字元串b為null，則傳回null；如果字元串a符合java正規表達式b的正則文法，則為true；否則為false。

hive> select 1 from dual where 'footbar’ rlike '^f.*r$’;

注意：判斷一個字元串是否全為數字：

hive>select 1 from dual where '123456'rlike '^\\d+$';

hive> select 1 from dual where'123456aa' rlike '^\\d+$';

文法: a regexp b

描述: 功能與rlike相同

hive> select 1 from dual where ‘key' regexp '^f.*r$';

第二部分：邏輯運算與數學運算

hive數學運算

•加法操作: +

•減法操作: -

•乘法操作: *

•除法操作: /

•取餘操作: %

•位與操作: &

•位或操作: |

•位異或操作: ^

•位取反操作: ~

文法: a + b

操作類型：所有數值類型

說明：傳回a與b相加的結果。結果的數值類型等于a的類型和b的類型的最小父類型（詳見資料類型的繼承關系）。比如，int + int 一般結果為int類型，而int + double 一般結果為double類型

hive> select 1 + 9 from dual;

10

文法: a – b

說明：傳回a與b相減的結果。結果的數值類型等于a的類型和b的類型的最小父類型（詳見資料類型的繼承關系）。比如，int – int 一般結果為int類型，而int – double 一般結果為double類型

hive> select 10 – 5 from dual;

5

• 乘法操作 : *

文法: a * b

說明：傳回a與b相乘的結果。結果的數值類型等于a的類型和b的類型的最小父類型（詳見資料類型的繼承關系）。注意，如果a乘以b的結果超過預設結果類型的數值範圍，則需要通過cast将結果轉換成範圍更大的數值類型

hive> select 40 * 5 from dual;

200

• 除法操作 : /

文法: a / b

說明：傳回a除以b的結果。結果的數值類型為double

hive> select 40 / 5 from dual;

8.0

注意： hive 中最高精度的資料類型是 double, 隻精确到小數點後 16 位，在做除法運算的時候要特别注意

hive>selectceil(28.0/6.999999999999999999999) from dual limit 1;  

結果為4

hive>select ceil(28.0/6.99999999999999)from dual limit 1;         

結果為5

• 取餘操作 : %

文法: a % b

說明：傳回a除以b的餘數。結果的數值類型等于a的類型和b的類型的最小父類型（詳見資料類型的繼承關系）。

hive> select 41 % 5 from dual;

hive> select 8.4 % 4 from dual;

0.40000000000000036

注意：精度在 hive 中是個很大的問題，類似這樣的操作最好通過 round 指定精度

hive> select round(8.4 % 4 , 2) fromdual;

0.4

• 位與操作 : &

文法: a & b

說明：傳回a和b按位進行與操作的結果。結果的數值類型等于a的類型和b的類型的最小父類型（詳見資料類型的繼承關系）。

hive> select 4 & 8 from dual;

hive> select 6 & 4 from dual;

4

• 位或操作 : |

文法: a | b

說明：傳回a和b按位進行或操作的結果。結果的數值類型等于a的類型和b的類型的最小父類型（詳見資料類型的繼承關系）。

hive> select 4 | 8 from dual;

12

hive> select 6 | 8 from dual;

14

• 位異或操作 : ^

文法: a ^ b

說明：傳回a和b按位進行異或操作的結果。結果的數值類型等于a的類型和b的類型的最小父類型（詳見資料類型的繼承關系）。

hive> select 4 ^ 8 from dual;

hive> select 6 ^ 4 from dual;

2

• 位取反操作 : ~

文法: ~a

說明：傳回a按位取反操作的結果。結果的數值類型等于a的類型。

hive> select ~6 from dual;

-7

hive> select ~4 from dual;

-5

hive邏輯運算

•邏輯與操作: and

•邏輯或操作: or

•邏輯非操作: not

• 邏輯與操作 : and

文法: a and b

操作類型：boolean

說明：如果a和b均為true，則為true；否則為false。如果a為null或b為null，則為null

hive> select 1 from dual where 1=1 and2=2;

• 邏輯或操作 : or

文法: a or b

說明：如果a為true，或者b為true，或者a和b均為true，則為true；否則為false

hive> select 1 from dual where 1=2 or2=2;

• 邏輯非操作 : not

文法: not a

說明：如果a為false，或者a為null，則為true；否則為false

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

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

第三部分：數值運算

•取整函數: round

•指定精度取整函數: round

•向下取整函數: floor

•向上取整函數: ceil

•向上取整函數: ceiling

•取随機數函數: rand

•自然指數函數: exp

•以10為底對數函數: log10

•以2為底對數函數: log2

• 對數函數: log

•幂運算函數: pow

•幂運算函數: power

•開平方函數: sqrt

•二進制函數: bin

•十六進制函數: hex

•反轉十六進制函數: unhex

•進制轉換函數: conv

•絕對值函數: abs

•正取餘函數: pmod

•正弦函數: sin

•反正弦函數: asin

•餘弦函數: cos

•反餘弦函數: acos

•positive函數: positive

•negative函數: negative

• 取整函數 : round

文法: round(double a)

傳回值: bigint

說明: 傳回double類型的整數值部分 （遵循四舍五入）

hive> select round(3.1415926) from dual;

3

hive> select round(3.5) from dual;

hive> create table dual as selectround(9542.158) from dual;

hive> describe dual;

_c0    bigint

• 指定精度取整函數 : round

文法: round(double a, intd)

傳回值: double

說明: 傳回指定精度d的double類型

hive> select round(3.1415926,4) fromdual;

3.1416

• 向下取整函數 : floor

文法: floor(double a)

說明: 傳回等于或者小于該double變量的最大的整數

hive> select floor(3.1415926) from dual;

hive> select floor(25) from dual;

25

• 向上取整函數 : ceil

文法: ceil(double a)

說明: 傳回等于或者大于該double變量的最小的整數

hive> select ceil(3.1415926) from dual;

hive> select ceil(46) from dual;

46

• 向上取整函數 : ceiling

文法: ceiling(double a)

說明: 與ceil功能相同

hive> select ceiling(3.1415926) fromdual;

hive> select ceiling(46) from dual;

• 取随機數函數 : rand

文法: rand(),rand(intseed)

說明: 傳回一個0到1範圍内的随機數。如果指定種子seed，則會等到一個穩定的随機數序列

hive> select rand() from dual;

0.5577432776034763

• 自然指數函數 : exp

文法: exp(double a)

說明: 傳回自然對數e的a次方

hive> select exp(2) from dual;

7.38905609893065

自然對數函數: ln

文法: ln(double a)

說明: 傳回a的自然對數

• 以 10 為底對數函數 : log10

文法: log10(double a)

說明: 傳回以10為底的a的對數

hive> select log10(100) from dual;

• 以 2 為底對數函數 : log2

文法: log2(double a)

說明: 傳回以2為底的a的對數

hive> select log2(8) from dual;

• 對數函數 : log

文法: log(double base,double a)

說明: 傳回以base為底的a的對數

hive> select log(4,256) from dual;

• 幂運算函數 : pow

文法: pow(double a, doublep)

說明: 傳回a的p次幂

hive> select pow(2,4) from dual;

16.0

• 幂運算函數 : power

文法: power(double a,double p)

說明: 傳回a的p次幂,與pow功能相同

hive> select power(2,4) from dual;

• 開平方函數 : sqrt

文法: sqrt(double a)

說明: 傳回a的平方根

hive> select sqrt(16) from dual;

• 二進制函數 : bin

文法: bin(bigint a)

傳回值: string

說明: 傳回a的二進制代碼表示

hive> select bin(7) from dual;

111

• 十六進制函數 : hex

文法: hex(bigint a)

說明: 如果變量是int類型，那麼傳回a的十六進制表示；如果變量是string類型，則傳回該字元串的十六進制表示

hive> select hex(17) from dual;

11

hive> select hex(‘abc’) from dual;

616263

• 反轉十六進制函數 : unhex

文法: unhex(string a)

說明: 傳回該十六進制字元串所代碼的字元串

hive> select unhex(‘616263’) from dual;

abc

hive> select unhex(‘11’) from dual;

-

hive> select unhex(616263) from dual;

• 進制轉換函數 : conv

文法: conv(bigint num, intfrom_base, int to_base)

說明: 将數值num從from_base進制轉化到to_base進制

hive> select conv(17,10,16) from dual;

hive> select conv(17,10,2) from dual;

10001

• 絕對值函數 : abs

文法: abs(double a)   abs(int a)

傳回值: double        int

說明: 傳回數值a的絕對值

hive> select abs(-3.9) from dual;

3.9

hive> select abs(10.9) from dual;

10.9

• 正取餘函數 : pmod

文法: pmod(int a, intb),pmod(double a, double b)

傳回值: int double

說明: 傳回正的a除以b的餘數

hive> select pmod(9,4) from dual;

hive> select pmod(-9,4) from dual;

• 正弦函數 : sin

文法: sin(double a)

說明: 傳回a的正弦值

hive> select sin(0.8) from dual;

0.7173560908995228

• 反正弦函數 : asin

文法: asin(double a)

說明: 傳回a的反正弦值

hive> select asin(0.7173560908995228)from dual;

0.8

• 餘弦函數 : cos

文法: cos(double a)

說明: 傳回a的餘弦值

hive> select cos(0.9) from dual;

0.6216099682706644

• 反餘弦函數 : acos

文法: acos(double a)

說明: 傳回a的反餘弦值

hive> select acos(0.6216099682706644)from dual;

0.9

• positive 函數 : positive

文法: positive(int a),positive(double a)

說明: 傳回a

hive> select positive(-10) from dual;

-10

hive> select positive(12) from dual;

• negative 函數 : negative

文法: negative(int a),negative(double a)

說明: 傳回-a

hive> select negative(-5) from dual;

hive> select negative(8) from dual;

-8

第四部分：日期函數

•unix時間戳轉日期函數: from_unixtime

• 擷取目前unix時間戳函數: unix_timestamp

•日期轉unix時間戳函數: unix_timestamp

• 指定格式日期轉unix時間戳函數: unix_timestamp

•日期時間轉日期函數: to_date

•日期轉年函數: year

• 日期轉月函數: month

• 日期轉天函數: day

• 日期轉小時函數: hour

• 日期轉分鐘函數: minute

• 日期轉秒函數: second

• 日期轉周函數:weekofyear

• 日期比較函數: datediff

• 日期增加函數: date_add

• 日期減少函數: date_sub

• unix 時間戳轉日期函數 : from_unixtime

文法: from_unixtime(bigintunixtime[, string format])

說明: 轉化unix時間戳（從1970-01-01 00:00:00utc到指定時間的秒數）到目前時區的時間格式

hive> selectfrom_unixtime(1323308943,'yyyymmdd') from dual;

20111208

• 擷取目前 unix 時間戳函數 : unix_timestamp

文法: unix_timestamp()

說明: 獲得目前時區的unix時間戳

hive> select unix_timestamp() from dual;

1323309615

• 日期轉 unix 時間戳函數 : unix_timestamp

文法:unix_timestamp(string date)

說明: 轉換格式為"yyyy-mm-dd hh:mm:ss"的日期到unix時間戳。如果轉化失敗，則傳回0。

hive> select unix_timestamp('2011-12-0713:01:03') from dual;

1323234063

• 指定格式日期轉 unix 時間戳函數 : unix_timestamp

文法:unix_timestamp(string date, string pattern)

說明: 轉換pattern格式的日期到unix時間戳。如果轉化失敗，則傳回0。

hive> select unix_timestamp('2011120713:01:03','yyyymmdd hh:mm:ss') from dual;

• 日期時間轉日期函數 :to_date

文法: to_date(stringtimestamp)

說明: 傳回日期時間字段中的日期部分。

hive> select to_date('2011-12-0810:03:01') from dual;

2011-12-08

• 日期轉年函數 : year

文法: year(string date)

傳回值: int

說明: 傳回日期中的年。

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

2011

hive> select year('2012-12-08') fromdual;

2012

• 日期轉月函數 : month

文法: month (string date)

說明: 傳回日期中的月份。

hive> select month('2011-12-0810:03:01') from dual;

hive> select month('2011-08-08') fromdual;

• 日期轉天函數 : day

文法: day (string date)

說明: 傳回日期中的天。

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

hive> select day('2011-12-24') fromdual;

24

• 日期轉小時函數 : hour

文法: hour (string date)

說明: 傳回日期中的小時。

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

• 日期轉分鐘函數 : minute

文法: minute (string date)

說明: 傳回日期中的分鐘。

hive> select minute('2011-12-0810:03:01') from dual;

• 日期轉秒函數 : second

文法: second (string date)

說明: 傳回日期中的秒。

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

• 日期轉周函數 :weekofyear

文法: weekofyear (stringdate)

說明: 傳回日期在目前的周數。

hive> select weekofyear('2011-12-0810:03:01') from dual;

49

• 日期比較函數 :datediff

文法: datediff(stringenddate, string startdate)

說明: 傳回結束日期減去開始日期的天數。

hive> selectdatediff('2012-12-08','2012-05-09') from dual;

213

• 日期增加函數 :date_add

文法: date_add(stringstartdate, int days)

說明: 傳回開始日期startdate增加days天後的日期。

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

2012-12-18

• 日期減少函數 :date_sub

文法: date_sub (stringstartdate, int days)

說明: 傳回開始日期startdate減少days天後的日期。

hive> select date_sub('2012-12-08',10)from dual;

2012-11-28

第五部分：條件函數

•if函數: if

•非空查找函數: coalesce

•條件判斷函數：case

• if 函數 : if

文法: if(booleantestcondition, t valuetrue, t valuefalseornull)

傳回值: t

說明:  當條件testcondition為true時，傳回valuetrue；否則傳回valuefalseornull

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

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

100

• 非空查找函數 :coalesce

文法: coalesce(t v1, t v2,…)

說明:  傳回參數中的第一個非空值；如果所有值都為null，那麼傳回null

hive> select coalesce(null,'100','50′) from dual;

條件判斷函數： case

文法 : case a when b thenc [when d then e]* [else f] end

傳回值 : t

說明：如果 a 等于 b ，那麼傳回 c ；如果 a 等于 d ，那麼傳回 e ；否則傳回 f

hive> select case 100 when 50 then 'tom'when 100 then 'mary' else 'tim' end from dual;

mary

第六部分：字元串函數

•字元串長度函數：length

•字元串反轉函數：reverse

•字元串連接配接函數：concat

• 帶分隔符字元串連接配接函數：concat_ws

• 字元串截取函數：substr,substring

• 字元串轉大寫函數：upper,ucase

• 字元串轉小寫函數：lower,lcase

• 去空格函數：trim

• 左邊去空格函數：ltrim

• 右邊去空格函數：rtrim

•正規表達式替換函數：regexp_replace

•正規表達式解析函數：regexp_extract

•url解析函數：parse_url

•json解析函數：get_json_object

•空格字元串函數：space

•重複字元串函數：repeat

•首字元ascii函數：ascii

•左補足函數：lpad

•右補足函數：rpad

•分割字元串函數: split

•集合查找函數: find_in_set

• 字元串長度函數： length

文法: length(string a)

說明：傳回字元串a的長度

hive> select length('abcedfg') fromdual;

7

• 字元串反轉函數： reverse

文法: reverse(string a)

說明：傳回字元串a的反轉結果999999舉例：

hive> select reverse(abcedfg’) from dual;

gfdecba