hive常用函数

1.Hive

1.nvl函数

nvl函数：空值转换函数
函数形式：nvl（expr1，expr2），类似于mysql-nullif（expr1，expr2）
作用：将查询为Null值转换为指定值。
若expr1为Null，则返回expr2，否则返回expr1。
适用于数字型、字符型和日期型，但是expr1和expr2的数据类型必须为相同类型。
转载自
NVL函数
NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

2.NVL2函数

NVL2函数的格式如下：NVL2(expr1,expr2, expr3)
含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

3.NULLIF函数

NULLIF函数的格式如下：NULLIF(exp1,expr2)
含义是：如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

4.Coalesce函数

格式如下：Coalesce(expr1, expr2, expr3…… exprn)
Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。返回表达式中第一个非空表达式
1-4转载自：添加链接描述
COALESCE是一个函数， (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

select coalesce(success_cnt,period,1) from tableA

当success_cnt不为null，那么无论period是否为null，都将返回success_cnt的真实值（因为success_cnt是第一个参数），当success_cnt为null，而period不为null的时候，返回period的真实值。只有当success_cnt和period均为null的时候，将返回1。
转载自

5.str_to_map(字符串参数, 分隔符1, 分隔符2)

使用两个分隔符将文本拆分为键值对。
分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是 ‘,’，对于分隔符2默认分隔符是 ‘=’。
例子：

1. 创建map字段

DROP TABLE IF EXISTS tmp.tmp_str_to_map;
CREATE TABLE IF NOT EXISTS tmp.tmp_str_to_map
(
ocolumn string comment '原始字段',
rcolumn map<string,string> comment 'map字段'
);

2. concat + str_to_map函数
   用concat + & 取表字段拼接成map类型

insert overwrite table tmp.tmp_str_to_map
SELECT
concat('&crowd:', m0.id,'&clicker:',m0.dui_leader,'&sen:',m0.application_type) ocolumn,
str_to_map(concat('column1:', m0.id,'&column2:',m0.dui_leader,'&column3:',m0.application_type), '&', ':') rcolumn
FROM tmp.tmp_adhoc_detail_20180927 m0
limit 1
;

3. 取用map里的字段，用[""]即可

select
rcolumn,
rcolumn["column1"] column1
from tmp.tmp_str_to_map;

4. 也可以直接转换取用，而不需要存储字段

SELECT
  m0.id column1,
  str_to_map(concat('column1:', m0.id,'&column2:',m0.dui_leader,'&column3:',m0.application_type), '&', ':')["column1"] column1_1
from tmp.tmp_adhoc_detail_20180927 m0 <br>limit 1

5. 结果:
   
   转载自

6.Hive中rlike,like,not like，regexp区别与使用详解

参考链接
关于like与rlike,not like,like not的使用对比总结
1.Rlike功能和like功能大致一样，like是后面只支持简单表达式匹配（_%）,而rlike则支持标准正则表达式语法。所以如果正则表达式使用熟练的话，建议使用rlike，功能更加强大。所有的like匹配都可以被替换成rlike。反之，则不行。但是注意：like是从头逐一字符匹配的，是全部匹配，但是rlike则不是,可以从任意部位匹配，而且不是全部匹配。
2. NOT A LIKE B是LIKE的结果否定，如果like匹配结果时true，则not…like的匹配结果时false，反之也是结果也是相对。实际中也可以使用 A NOT LIKE B，也是LIKE的否定，与 NOT A LIKE B一样。当然前提要排除出现null问题**，null值这个奇葩除外，null的结果都是null值。**
3.同理NOT RLIKE 的使用，也是NOT A RLIKE B是对RLIKE的否定。当然前提要排除出现null问题，null值这个奇葩除外，null的结果都是null值。

7.max_pt

select
    cart_id,
    cart_start_time,
    cart_end_time
from
    dm_motor_business_mct.dim_business_ad
where
    date = max_pt('dm_motor_business_mct.dim_business_ad')
    and '${DATE}' between cart_start_time
    and cart_end_time --取当前进行中的合同
group by
    cart_id,
    cart_start_time,
    cart_end_time --去重

MAX_PT 解释如下：
命令格式

max_pt(<table_full_name>)

命令说明
返回分区表的一级分区中有数据的分区的最大值，按字母排序，且读取该分区下对应的数据。
参数说明
table_full_name：必填。STRING类型。指定表名。必须对表有读权限。
返回值说明
返回最大的一级分区的值。
说明
如果只是用alter table的方式新加了一个分区，但是此分区中并无任何数据，则此分区不会做为返回值。
示例
例如tbl是分区表，该表对应的分区为20120901和20120902，且都有数据。则以下语句中max_pt返回值为‘20120902’。MaxCompute SQL语句会读出pt=‘20120902’分区下的数据。
select * from tbl where pt=max_pt(‘myproject.tbl’);
转载自

8.删除分区数据

--先清非标分区spot_nonstandard_ad_content，再导入
--方法一
alter table dm_motor_business_test.dwd_user_touch_spot_detail_daily_letian drop partition(date='${date}', label='spot_nonstandard_ad_content');

--并检查是否数据已删除
select * 
from dm_motor_business_test.dwd_user_touch_spot_detail_daily_letian
where date='${date}'
and  label='spot_nonstandard_ad_content'

9.时间格式转换

--时间戳--》20210301
from_unixtime(project_start_time, 'yyyyMMdd') as project_start_time,
--20210301--》2021-03-01
from_unixtime(unix_timestamp(c.project_start_time,'yyyymmdd'),'yyyy-mm-dd') as cart_start_time,