HiveSQL如何生成连续日期剖析

HiveSQL如何生成连续日期剖析

情景假设：
有一结果表，表中有start_dt和end_dt两个字段，，想要根据开始和结束时间生成连续日期的多条数据，应该怎么做？直接上结果sql。（为了便于演示和测试这里通过SELECT '2024-03-01' AS start_dt,'2024-03-06' AS end_dt模拟一个结果表数据）

SELECT  t1.start_dt
       ,t1.end_dt
       ,t2.pos
       ,date_add(t1.start_dt,t2.pos) AS conn_dt
FROM
(
    SELECT  '2024-03-01' AS start_dt
           ,'2024-03-06' AS end_dt
) t1 lateral view posexplode(split(repeat(',', DATEDIFF(end_dt, start_dt)), ',')) t2 AS pos, val;
+--------------+-------------+---------+-------------+
| t1.start_dt  |  t1.end_dt  | t2.pos  |   conn_dt   |
+--------------+-------------+---------+-------------+
| 2024-03-01   | 2024-03-06  | 0       | 2024-03-01  |
| 2024-03-01   | 2024-03-06  | 1       | 2024-03-02  |
| 2024-03-01   | 2024-03-06  | 2       | 2024-03-03  |
| 2024-03-01   | 2024-03-06  | 3       | 2024-03-04  |
| 2024-03-01   | 2024-03-06  | 4       | 2024-03-05  |
| 2024-03-01   | 2024-03-06  | 5       | 2024-03-06  |
+--------------+-------------+---------+-------------+

如果对涉及到的函数和语法不是特别了解，直接看到上述结果可能有点懵，接下来换个形式理解下，即如下sql

SELECT  t1.start_dt
       ,t1.end_dt
       ,t2.pos
       ,date_add(t1.start_dt,t2.pos) AS conn_dt
FROM
(
    SELECT  '2024-03-01' AS start_dt
           ,'2024-03-06' AS end_dt
) t1
CROSS JOIN
(
    SELECT  posexplode(split(repeat(',',DATEDIFF('2024-03-06','2024-03-01')),',')) AS(pos,val)
) t2;
-- 执行结果同上

如上sql结构比较简单，即t1表和t2表进行笛卡尔集，t1是原始表只有1行数据，但是结果是6行数据，因此关键点是t2的结果。
t2本质上其实就是先生成一组从0开始编号的6行结果，笛卡尔积之后，从而将数据从原先的一行变成6行，然后再根据生成的序号，每一行数据使用开始日期+序号得到一个新日期。最终得到的结果中新日期是连续日期。
可以看出生成连续序号结果的关键语句是lateral view posexplode(split(repeat(',', DATEDIFF(end_dt, start_dt)), ',')) t2 AS pos, val，这条语句包可以拆解为以下两部分

• lateral view，是hive支持的语法
• posexplode(split(repeat(',', DATEDIFF(end_dt, start_dt)), ','))，是hive提供的函数。

先看下posexplode函数以及嵌套的内部函数都干了什么，对函数依次拆解执行，直接从结果来了解每个函数作用。

select datediff('2024-03-06','2024-03-01'); -- 5
select repeat(',',datediff('2024-03-06','2024-03-01')); -- ,,,,,
select split(repeat(',',datediff('2024-03-06','2024-03-01')),','); -- ["","","","","",""]

select posexplode(split(repeat(',',datediff('2024-03-06','2024-03-01')),','));
+------+------+
| pos  | val  |
+------+------+
| 0    |      |
| 1    |      |
| 2    |      |
| 3    |      |
| 4    |      |
| 5    |      |
+------+------+

看到这里就明白posexlode函数及其嵌套函数干了什么，其他几个函数可能比较熟悉，不做过多介绍，在这里仅介绍下posexplode函数和lateral view语法。

在说明posexplode函数之前，有必要先学习下explode函数。从字面意思来看posexplode其实是pos+explode。
explode和posexplode是udtf函数。

1. explode 函数

explode函数有两种入参形式，分别支持数组和map。依次看下传入数组和map的处理结果。

格式：explode(ARRAY<T> a)
将数组分解为多行。返回一个包含单列 （col） 的行集，数组中的每个元素对应一行。

1.1. 数组作为入参

简而言之，对数组进行行转列，示例如下

select explode(array('a','b','c'));
+------+
| col  |
+------+
| a    |
| b    |
| c    |
+------+

-- 通过as对生成的结果列命名
select explode(array('a','b','c')) as c1;
+-----+
| c1  |
+-----+
| a   |
| b   |
| c   |
+-----+

1.2. map作为入参

格式：explode(MAP<Tkey,Tvalue> m)
将map分解为多行。返回具有两列（key，value）的行集，输入map中的每个键值对变成输出中的一行。从 Hive 0.8.0 开始。

在此插入根据字符串生成map的方法，方便explode方法进行测试。
格式：str_to_map(text[, delimiter1, delimiter2])
使用两个分隔符将文本拆分为键值对。Delimiter1 将文本分隔为 K-V 对，Delimiter2 拆分每个 K-V 对。
delimiter1的默认值为,
delimiter2的默认值为:

select str_to_map('a:1,b:2,c:3');
+----------------------------+
|            _c0             |
+----------------------------+
| {"a":"1","b":"2","c":"3"}  |
+----------------------------+
select explode(str_to_map('a:1,b:2,c:3'));
+------+--------+
| key  | value  |
+------+--------+
| a    | 1      |
| b    | 2      |
| c    | 3      |
+------+--------+

-- 通过as与对生成的结果列命名
select explode(str_to_map('a:1,b:2,c:3')) as (c1,c2);
+-----+-----+
| c1  | c2  |
+-----+-----+
| a   | 1   |
| b   | 2   |
| c   | 3   |
+-----+-----+

因此explode函数的作用是将数组或map中的每一个元素作为结果中的一行，如果是map从将key和value分别作为结果中的两列值。

接下来再看posexlpode函数就很好理解了。

2. posexplode 函数

格式：posexplode(ARRAY<T> a)
将数组分解为多行，并附加 int 类型的位置列（原始数组中项的位置，从 0 开始）。返回一个包含两列 （pos，val） 的行集，数组中的每个元素对应一行。

简而言之，posexplode函数就是在explode函数的结果上增加一列序号值，序号从0开始，从函数名称可以看出posexplode函数就是pos+explode.

select posexplode(array('a','b','c'));
+------+------+
| pos  | val  |
+------+------+
| 0    | a    |
| 1    | b    |
| 2    | c    |
+------+------+

-- 同样可以通过as对结果进行命名
select posexplode(array('a','b','c')) as(index,value);
+--------+--------+
| index  | value  |
+--------+--------+
| 0      | a      |
| 1      | b      |
| 2      | c      |
+--------+--------+

• posexplode函数仅支持数组作为入参。
• 单独使用posexplode函数时（区别于和lateral view一起使用）通过as对结果进行重命名时，必须带有括号，否则sql执行报错。

到此posexplode函数的作用已经搞清楚了，接下来学习下lateral view语法。

3. lateral view语法

语法格式如下

lateralView: LATERAL VIEW (OUTER) udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*

定义描述
简单说，lateral view是用来和udtf函数结合使用的，udtf函数为每个输入行生成零个或多个输出行，通过上面explode函数结果也可以说明这点。
lateral view将udtf应用于基表的每一行，然后将生成的输出行join到输入行上，以形成具有所提供表别名的虚拟表。

从 hive 0.12.0版本开始，可以省略列别名。默认使用udft函数返回的字段名。

详细示例参考官网文档。

根据描述再理解语法格式中每一部分的含义

现在重新来看开头的结果sql就比较清晰了，t1是basicTabel的别名，t2是udtf输出结果的虚拟表别名，as pos,val则是posexplode函数生成的两列结果的别名，然后在select中分别使用t1和t2来获取两个表的字段。又因为lateral view将基表的每一行都作为udtf函数的输入，因此可以在datediff函数中直接使用end_dt和start_dt列。

SELECT  t1.start_dt
       ,t1.end_dt
       ,t2.pos
       ,t2.val
       ,date_add(t1.start_dt,t2.pos) AS conn_dt
FROM
(
    SELECT  '2024-03-01' AS start_dt
           ,'2024-03-06' AS end_dt
) t1 lateral view posexplode(split(repeat(',', DATEDIFF(end_dt, start_dt)), ',')) t2 AS pos, val;

3.1. outer 关键字

作用：当udft的结果不会生成任何行时，如果不使用outer关键字，则最终结果也不会生成任何行，可以这样理解，左表inner join右表(udtf结果)，当右表是空表时，则最终结果也一定是空的，指定outer后inner join就会变成left join。示例如下

select posexplode(array());
+------+------+
| pos  | val  |
+------+------+
+------+------+

SELECT  t1.start_dt
       ,t1.end_dt
       ,t2.pos
       ,t2.val
       ,date_add(t1.start_dt,t2.pos) AS conn_dt
FROM
(
    SELECT  '2024-03-01' AS start_dt
           ,'2024-03-06' AS end_dt
) t1 lateral view posexplode(array()) t2 AS pos, val;
-- 由于posexplode函数的结果为空，最终结果为空。
+--------------+------------+---------+---------+----------+
| t1.start_dt  | t1.end_dt  | t2.pos  | t2.val  | conn_dt  |
+--------------+------------+---------+---------+----------+
+--------------+------------+---------+---------+----------+

SELECT  t1.start_dt
       ,t1.end_dt
       ,t2.pos
       ,t2.val
       ,date_add(t1.start_dt,t2.pos) AS conn_dt
FROM
(
    SELECT  '2024-03-01' AS start_dt
           ,'2024-03-06' AS end_dt
) t1 lateral view outer posexplode(array()) t2 AS pos, val;
-- 使用outer关键字后，基表数据会输出，而右表字段为null
+--------------+-------------+---------+---------+----------+
| t1.start_dt  |  t1.end_dt  | t2.pos  | t2.val  | conn_dt  |
+--------------+-------------+---------+---------+----------+
| 2024-03-01   | 2024-03-06  | NULL    | NULL    | NULL     |
+--------------+-------------+---------+---------+----------+