hive中函数的具体运用

一、URL解析函数

1、url的组成

PROTOCOL：协议类型
通信协议类型，一般也叫作Schema，常见的有http、https等；

HOST：域名
一般为服务器的域名主机名或ip地址

PATH：访问路径
访问路径目录，由“/”隔开的字符串，表示的是主机上的目录或文件地址

QUERY：参数数据
查询参数，此项为可选项，可以给动态网页传递参数，用“&”隔开，每个参数的名和值用“=”隔开

Hive中为了实现对URL的解析，专门提供了解析URL的函数parse_url和parse_url_tuple，在show functions中可以看到对应函数

2、parse_url和parse_url_tuple

parse_url函数是Hive中提供的最基本的url解析函数，可以根据指定的参数，从URL解析出对应的参数值进行返回，函数为普通的一对一函数类型。

语法
parse_url(url, partToExtract[, key]) - extracts a part from a URL
  Parts: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO key 
parse_url在使用时需要指定两个参数
第一个参数：url：指定要解析的URL
第二个参数：key：指定要解析的内容

parse_url_tuple
parse_url_tuple函数是Hive中提供的基于parse_url的url解析函数，可以通过一次指定多个参数，从URL解析出多个参数的值进行返回多列，函数为特殊的一对多函数类型，即通常所说的UDTF函数类型。

语法
parse_url_tuple(url, partname1, partname2, ..., partnameN) - extracts N (N>=1) parts from a URL.
It takes a URL and one or multiple partnames, and returns a tuple. All the input parameters and output column types are string.
Partname: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY_NAME>

parse_url在使用时可以指定多个参数
第一个参数：url：指定要解析的URL
第二个参数：key1：指定要解析的内容1
……
第N个参数：keyN：指定要解析的内容N

例如

查询tb_url中每个url的HOST、PATH
select parse_url_tuple(url,"HOST","PATH") as (host,path) from tb_url;

查询tb_url中每个url的PROTOCOL、HOST、QUERY
select parse_url_tuple(url,"PROTOCOL","HOST","PATH") as (protocol,host,path) from tb_url;

3、 Lateral View侧视图
Lateral View是一种特殊的语法，主要用于搭配UDTF类型功能的函数一起使用，用于解决UDTF函数的一些查询限制的问题。
侧视图的原理是将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by 、limit等语句中，不需要再单独嵌套一层子查询。
一般只要使用UDTF，就会固定搭配lateral view使用。

基本语法如下
select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3……

二、行列转换应用与实现

1、case when判断
功能
用于实现对数据的判断，根据条件，不同的情况返回不同的结果，类似于Java中的switch case 功能
语法

语法一
CASE 
WHEN 条件1 THEN VALUE1
WHEN 条件2 THEN VALUE2
……
WHEN 条件N THEN VALUEN
ELSE 默认值
END

语法二
CASE 列
WHEN V1 THEN VALUE1
WHEN V2 THEN VALUE2
……
WHEN VN THEN VALUEN
ELSE 默认值
END

语法测试

select
  id,
  case
  when id < 2 then 'a'
  when id = 2 then 'b'
  else 'c'
  end as caseName
from tb_url;

语法二测试

select
  id,
  case id
  when 1 then 'a'
  when 2 then 'b'
  else 'c'
  end as caseName
from tb_url;

select
  col1 as col1,
  max(case col2 when 'c' then col3 else 0 end) as c,
  max(case col2 when 'd' then col3 else 0 end) as d,
  max(case col2 when 'e' then col3 else 0 end) as e
from
  row2col1
group by
  col1;

2、多行转单列
concat
功能：用于实现字符串拼接，不可指定分隔符
语法

concat(element1,element2,element3……)

测试
select concat(“法外”,“狂徒”,“张三”);
±----------------+
| 法外狂徒张三 |
±----------------+

concat_ws
功能：用于实现字符串拼接，可以指定分隔符
语法

concat_ws(SplitChar，element1，element2……)

测试
select concat_ws(“-”,“法外”,“狂徒”,“张三”);
±------------------+
| 法外-狂徒-张三 |
±------------------+**

collect_list
功能：用于将一列中的多行合并为一行，不进行去重
语法

collect_list（colName）

测试
select collect_list(col1) from row2col1;
+----------------------------+
| ["a","a","a","b","b","b"]  |
+----------------------------+

concat_set
功能：用于将一列中的多行合并为一行，并进行去重
语法

collect_set（colName）

测试
select collect_set(col1) from row2col1;
+------------+
| ["b","a"]  |
+------------+

3、多列转多行
union
功能：将多个select语句结果合并为一个，且结果去重且排序
语法

select_statement 
UNION [DISTINCT] 
select_statement 
UNION [DISTINCT] 
select_statement ...

union all
功能：将多个select语句结果合并为一个，且结果不去重不排序
语法
select_statement UNION ALL select_statement UNION ALL select_statement …

三、连续登录用户hive实现方式

连续两天登录的问题
窗口函数lead
功能：用于从当前数据中基于当前行的数据向后偏移取值
语法：lead(colName，N，defautValue)
colName：取哪一列的值
N：向后偏移N行
defaultValue：如果取不到返回的默认值
分析
当前数据中记录了每个用户每一次登陆的日期，一个用户在一天只有1条信息，我们可以基于用户的登陆信息，找到如下规律：
连续两天登陆 ： 用户下次登陆时间 = 本次登陆以后的第二天
连续三天登陆 ： 用户下下次登陆时间 = 本次登陆以后的第三天
……依次类推。
我们可以对用户ID进行分区，按照登陆时间进行排序，通过lead函数计算出用户下次登陆时间，通过日期函数计算出登陆以后第二天的日期，如果相等即为连续两天登录。

with t1 as (
  select
    userid,
    logintime,
    --本次登陆日期的第二天
      date_add(logintime,1) as nextday,
    --按照用户id分区，按照登陆日期排序，取下一次登陆时间，取不到就为0
     lead(logintime,1,0) over (partition by userid order by logintime) as nextlogin
from tb_login )
select distinct userid from t1 where nextday = nextlogin;

连续N天登录的实现

with t1 as (
select
  userid,
  logintime,
  --本次登陆日期的第N天
  date_add(logintime,N-1) as nextday,
  --按照用户id分区，按照登陆日期排序，取下下一次登陆时间，取不到就为0
  lead(logintime,N-1,0) over (partition by userid order by logintime) as nextlogin
from tb_login)
select distinct userid from t1 where nextday = nextlogin;

四、分组TopN问题实现

TopN函数：row_number、rank、dense_rank
row_number：对每个分区的数据进行编号，如果值相同，继续编号
rank：对每个分区的数据进行编号，如果值相同，编号相同，但留下空位
dense_rank：对每个分区的数据进行编号，如果值相同，编号相同，不留下空位

基于row_number实现，按照部门分区，每个部门内部按照薪水降序排序

select
       empno,
       ename,
       salary,
       deptno,
       row_number() over (partition by deptno order by salary desc) as rn
from tb_emp;

五、拉链表实现

拉链表使用场景
拉链表专门用于解决在数据仓库中数据发生变化如何实现数据存储的问题，如果直接覆盖历史状态，会导致无法查询历史状态，如果将所有数据单独切片存储，会导致存储大量非更新数据的问题。拉链表的设计是将更新的数据进行状态记录，没有发生更新的数据不进行状态存储，用于存储所有数据在不同时间上的所有状态，通过时间进行标记每个状态的生命周期，查询时，根据需求可以获取指定时间范围状态的数据，默认用9999-12-31等最大值来表示最新状态。

整体实现过程一般分为三步，
第一步先增量采集所有新增数据【增加的数据和发生变化的数据】放入一张增量表。
第二步创建一张临时表，用于将老的拉链表与增量表进行合并。
第三步，最后将临时表的数据覆盖写入拉链表中。

合并拉链表与增量表

insert overwrite table tmp_zipper
select
  userid,
  phone,
  nick,
  gender,
  addr,
  starttime,
  endtime
from ods_zipper_update
union all
--查询原来拉链表的所有数据，并将这次需要更新的数据的endTime更改为更新值的startTime
select
  a.userid,
  a.phone,
  a.nick,
  a.gender,
  a.addr,
  a.starttime,
  --如果这条数据没有更新或者这条数据不是要更改的数据，就保留原来的值，否则就改为新数据的开始时间-1
  if(b.userid is null or a.endtime < '9999-12-31', a.endtime ,date_sub(b.starttime,1)) as endtime
from dw_zipper a  left join ods_zipper_update b
on a.userid = b.userid ;

覆盖拉链表

insert overwrite table dw_zipper
select * from tmp_zipper;