Hive常用函数

IF函数：if

语法: if(testCondition,valuTrue,valueFalseOrNull)
说明:当条件 testCondition 为True时，返回valueTrue, 否则返回valueFalseOrNull

条件判断函数：CASE

语法： Case A when b THEN c [WHEN d THEN e] * [ELSE f] END
说明：如果a 等于 b，那么返回c,如果a等于d，那么返回e，否则返回f

eg：
 -- 销售类型增加一级部门的prt_distribution_flag_name：直销为 -1，分销为-2
select
case t1.prt_distribution_flag_name When '直销' then '-1' when '分销' then '-2' else '未知' end as parent_dept_id,
t2.distribution_flag_key as dept_id,
t1.*
from
(select
     distinct 
     lvl,
     full_name,     
     prt_distribution_flag_name,
     distribution_flag_name
from dw.ol_power_distribution_type_dim 
where lvl =2 and dt ='20180813') t1 
left join 
dw.kn1_dim_distribution t2 on t1.distribution_flag_name =t2.distribution_flag_name 
and t1.prt_distribution_flag_name = t2.prt_distribution_flag_name

踩坑：Query 20180815_101245_23113_5iy2v failed: line 2:77: All CASE results must be the same type: bigint类型要一致

NVL

语法：nvl（value，default_value）
说明：如果value是null，返回default_value）

去空格函数：trim，ltrim,rtrim

语法：
trim(A)：去除字符串两边的空格
ltrim(A):去除字符串左边的空格
rtrim(A):去除字符串右边的空格

正则表达式替换函数：regexp_replace

语法：regexp_replace（A,B,C） 说明：将字符串A中的符合java正则表达式B的部分替换成C。
注意：在有些情况下要使用转义字符，类似oracle中的regexp_replace函数

字符串连接函数：concat

语法： concat（str1,str2,str3,…strN）
说明：返回输入字符串连接后的结果，支持任意个输入字符串。

日期时间转日期函数： to_date

语法：to_date(expr)
说明:返回日期时间段内的日期部分。`

左补足函数：lpad

语法：lpad(str,len,pad)
说明:将str用pad进行左补足到len位

右补足函数： rpad

语法：rpad(str,len,pad)
说明:将str用pad进行右补足到len位

排序函数：row_number() Over()

语法：
row_number() over (partition by xxx order by xxx) rank（其中rank为分组的别名，你也可以换个名字比方说换成hahahah）
说明：从 1开始，按照顺序 生成分组内记录的开始。

使用事例

数据提取目标：从tmp_test表中根据col1字段去重，选取clo2最大的那条记录，导入tmp_test_c表。

创建数据表：
    create table tmp_test(col1 string,clo2 string);

添加测试数据：
    insert into table tmp_test
    select 1,'str1' from dual
    union all
    select 2,'str2' from dual
    union all
    select 3,'str3' from dual
    union all
    select 3,'str31' from dual
    union all
    select 3,'str33' from dual
    union all
    select 4,'str41' from dual
    union all
    select 4,'str42' from dual;

查看数据：

使用row_number（）函数查询数据：

hive (default)> select t.*,row_number() over(partition by col1,clo2 sort by clo2 desc) rn
              > from tmp_test t;

按照数据提取目标提取数据：

hive (default)> select *
              > from
              > (
              > select t.*,row_number() over(distribute by col1 sort by clo2 desc) rn
              > from tmp_test t
              > )tt
              > where tt.rn=1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eB31FtPQ-1600857848135)(img/row_number3.png)]

获取当前UNIX时间戳函数：unix_timestamp

语法：unix_timestamp()说明：获取当前时区的UNIX时间戳

Unix时间戳转日期函数：from_unixtime()

语法：from_unixtime(bigint unixtime[,string format])
说明：转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式。

带分隔符字符串连接函数：concat_ws

语法：concat_ws（SEP,A,B…）
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符。