Hive常用函数

一、关系运算：

1. 等值比较: =

举例：Hive>select 1 from lxw_dual where 1=1;

2. 不等值比较: <>

举例：  hive>select1 from lxw_dual where 1 <> 2;

3.小于比较: <

举例：hive> select1 from lxw_dual where 1 < 2;

4. 小于等于比较: <=

举例：  hive>select1 from lxw_dual where 1 <= 1;

5. 大于比较: >

举例：hive> select1 from lxw_dual where 2 >1;

6. 大于等于比较: >=

举例： hive>select1 from lxw_dual where 1 >= 1;

7. 空值判断: IS NULL

举例： hive>select1 from lxw_dual where null is null;

8. 非空判断: IS NOT NULL

举例：hive> select1 from lxw_dual where 1 isnot null;

9. LIKE比较: LIKE

举例：hive> select1 from lxw_dual where 'football' like 'foot%';

10. JAVA的LIKE操作: RLIKE

举例：hive> select1 from lxw_dual where 'footbar’ rlike '^f.*r$’;

11. REGEXP操作: REGEXP

举例：hive> select1 from lxw_dual where 'footbar' REGEXP '^f.*r$';

二、数学运算：

1. 加法操作: +

举例：hive> select1 + 9 from lxw_dual;

2. 减法操作: -

举例：hive> select10 – 5 from lxw_dual;

3. 乘法操作: *

举例： hive>select40 * 5 from lxw_dual;

4. 除法操作: /

举例：hive> select40 / 5 from lxw_dual;

5. 取余操作: %

举例：hive> select 41 % 5 from lxw_dual;

6. 位与操作: &

举例：hive> select 4 & 8 from lxw_dual;

7. 位或操作: |

举例：hive> select 4 | 8 from lxw_dual;

8. 位异或操作: ^

举例：hive> select 4 ^ 8 from lxw_dual;

9．位取反操作: ~

举例：hive> select ~6 from lxw_dual;

三、逻辑运算：

1. 逻辑与操作: AND

举例：hive> select 1 from lxw_dual where 1=1and 2=2;

2. 逻辑或操作: OR

举例：hive> select 1 from lxw_dual where 1=2or 2=2;

3. 逻辑非操作: NOT

举例：hive> select 1 from lxw_dual where not1=2;

四、数值计算

1. 取整函数: round

举例：hive> select round(3.1415926) fromlxw_dual;

2. 指定精度取整函数: round

举例：hive> select round(3.1415926,4) fromlxw_dual;

3. 向下取整函数: floor

举例：hive> select floor(3.1415926) fromlxw_dual;

4. 向上取整函数: ceil

举例：hive> select ceil(3.1415926) fromlxw_dual;

5. 向上取整函数: ceiling

举例：hive> select ceiling(3.1415926) fromlxw_dual;

五、日期函数

1. UNIX时间戳转日期函数:from_unixtime

举例：hive> select from_unixtime(1323308943,'yyyyMMdd')fromlxw_dual;

2. 获取当前UNIX时间戳函数:unix_timestamp

举例：hive> select unix_timestamp() fromlxw_dual;

3. 日期转UNIX时间戳函数:unix_timestamp

举例：hive> select unix_timestamp('2011-12-0713:01:03') from lxw_dual;

4. 指定格式日期转UNIX时间戳函数:unix_timestamp

举例：hive> select unix_timestamp('2011120713:01:03','yyyyMMddHH:mm:ss') from lxw_dual;

5. 日期时间转日期函数:to_date

举例：hive> select to_date('2011-12-0810:03:01') from lxw_dual;

6. 日期转年函数: year

举例：hive> select year('2011-12-08 10:03:01')from lxw_dual;

六、条件函数

1. If函数: if

举例：select if(1=1,100,200) from lxw_dual;

2. 非空查找函数: COALESCE

举例：hive> select COALESCE(null,'100','50′) from lxw_dual;

3. 条件判断函数：CASE

举例： Select case 100 when 50 then 'tom' when 100then 'mary'else 'tim' end from lxw_dual;

4. 条件判断函数：CASE

举例：select case when 1=1 then 'tom' when 2=2then 'mary' else'tim' end from lxw_dual;

七、字符串函数

1. 字符串长度函数：length

举例：hive> select length('abcedfg') fromlxw_dual;

2. 字符串反转函数：reverse

举例：hive> select reverse(abcedfg’) from lxw_dual;

3. 字符串连接函数：concat

举例：hive> select concat(“abc”,”def”,”gh”)from lxw_dual;

4. 带分隔符字符串连接函数：concat_ws（可以聚合数组）

举例：hive> selectconcat_ws(',','abc','def','gh') from lxw_dual;

5. 字符串截取函数：substr,substring

举例：hive> select substr('abcde',3) fromlxw_dual;

6. 字符串截取函数：substr,substring

举例：hive> select substr('abcde',3,2) fromlxw_dual;

八、集合统计函数

1. 个数统计函数: count

举例：hive> select count(*) from lxw_dual;

2. 总和统计函数: sum

举例：hive> select sum(t) from lxw_dual;

3. 平均值统计函数: avg

举例：hive> select avg (distinct t) fromlxw_dual;

九、复合类型构建操作

1. Map类型构建: map

举例：hive> Create table lxw_test as selectmap('100','tom','200','mary')as t from lxw_dual;

2. Struct类型构建: struct

举例：hive> create table lxw_test as select struct('tom','mary','tim')ast from lxw_dual;

3. array类型构建: array

举例：hive> create table lxw_test asselectarray("tom","mary","tim") as t fromlxw_dual;

十、复杂类型访问操作

1. array类型访问: A[n]

举例：hive> create table lxw_test asselectarray("tom","mary","tim") as t fromlxw_dual;

2. map类型访问: M[key]

举例：hive> Create table lxw_test asselectmap('100','tom','200','mary') as t from lxw_dual;

3. struct类型访问: S.x

举例：hive> create table lxw_test as selectstruct('tom','mary','tim')as t from lxw_dual;

十一、复杂类型长度统计函数

1.  Map类型长度函数:size(Map)

举例：hive> select size(map('100','tom','101','mary'))

from lxw_dual;

2.  array类型长度函数:size(Array)

举例：hive> selectsize(array('100','101','102','103')) from lxw_dual;

3.   类型转换函数

举例：hive> select cast(1 as bigint) fromlxw_dual;