列转行经典实现，细谈hive中的爆炸函数

1.前言

相信很多同学在处理需求的时候，少不了要进行行列的转化，当然面试的时候更少不了这个经典问题，那么什么是行列转化？如下图，不同商品在不同月份的销量数据，有时候我们希望数据和左侧一样的排列，但原始数据却像右侧一样排列，此时我们需要把右侧的列排列转换成左侧的行排列，反之亦然。

当然它们有着各种的实现方式，今天向大家介绍其中Hive中两个函数来实现其中的列转行的场景。

2. explode和posexplode

explode和posexplode是Hive中两个常用且强大的函数，它们用于处理复杂数据类型的时候有着不可替代的作用。Explode函数是Hive中一个有着魔术般的函数，它可以将array或者map复杂数据类型的列进行展开。实际上，explode函数一般会配合着Lateral view使用，这个我们后面再介绍。

为我们构建案例，假设我们有一个学生成绩信息表，含有数组类型的表：

-----------建表
create table ds_hive.stu_score(
  stu_id string,
  sub_ids array < string >,
  scores array < string >
) stored as orc;-----插入数据
insert overwrite table ds_hive.stu_score
select
  1001,
  array('语文', '数学', '英语'),
  array('90', '88', '79')
union all
select
  1002,
  array('语文', '地理'),
  array('54', '97');

基于上面的数据，我们用explode函数对学生科目列进行展开,具体sql：

select explode(sub_ids) as sub_id from ds_hive.stu_score;

结果如下：

这里需要注意，explode函数只能直接查询，不能增加其他列，如果想要增加其他的列内容，需要配合lateral view [outer] 使用。

如果直接添加列会报如下错误：

Posexplode函数除了和explode函数一样能够展开array或map类型的列，还能同时返回展开元素的位置（即索引）。我们再用array_table表作为例子，这次用posexplode：

你会发现，posexplode 函数会把数组的索引和值一并返回，这对于需要同时关心元素值和在数组中的位置的场景非常有帮助。

通过上述例子，相信你已经对explode和posexplode有了一定的理解。在实际的数据处理中，这两个函数的重要性更是不言而喻。希望这个指南能帮助你在遇到复杂数据类型时能更加得心应手。 

3. Lateral View及Lateral View Outer

上述我们已经提到了Lateral View，Lateral View和Lateral View Outer的用法，这两者是在处理数据时经常遇到的操作。那么它们到底是什么呢？它们一般会配合着表生成函数（如explode）一起使用，对array或者map类型的列进行展开。Hive的lateral view是用来连接生成虚拟表的。explode函数只能直接查询，不能增加其他列，所以这里满足我们上述例子讲的例子，结合Lateral View一起使用：

select
  stu_id,
  tmp_table.sub_id
from
  ds_hive.stu_score lateral view explode(sub_ids) tmp_table as sub_id;

查询结果：

我们观察一下，Lateral View有一个限制——如果在explode的列中有空值，那么它会将包含空值的行完全去掉。不参与计算，这时，我们就可以使用Lateral View Outer。只需要在lateral view后加上outer，它就会保留那些包含空值的行。如果数组为空，那么explode的结果就会是NULL。具体使用lateral view outer：

-------插入数据
insert overwrite table ds_hive.stu_score
select
  1001,
  array('语文', '数学', '英语'),
  array('90', '88', '79')
union all
select
  1002,
  array('语文', '地理'),
  array('54', '97')
union all
select
  10023,
  null,
  null -------查询
select
  stu_id,
  tmp_table.sub_id
from
  ds_hive.stu_score lateral view outer explode(sub_ids) tmp_table as sub_id;

你可看到，id为3的行得以保留，不再被忽视。

4.综合实战

在上述的数据表里，我们交代了三个字段，学生id、科目和成绩，需求：求出每一个学生的每一科成绩，按行展现。

我们使用两次lateral view explode，可以计算出两列的笛卡尔积，SQL如下：

select
  stu_id,
  sub_id,
  score
from
  ds_hive.stu_score lateral view outer explode(sub_ids) tmp_sub as sub_id lateral view outer explode(scores) tmp_sc as score

结果会是：

根据上面我们可以观察到：sub_id列和score两列进行了笛卡尔积计算，如果是我们需要笛卡尔积的时候，可以这样完成。但是，这个并不是我们想要的结果，我们需要的是一一对应的关系，如果我们的sub_ids和scores是有对应关系的，即 sub_ids中的语文对应scores中的0，数学对应8，英语对应79，我们就需要有对应的关系，这个时候我们就有思路了，我们前面介绍过posexplode，可以拿出他们各自的序列，那我们久拿出各自的序列，限定sub_idx和sc_idx 相等，得到我们想要的结果，具体代码实现：

select
  stu_id,
  sub_id,
  score
from
  ds_hive.stu_score lateral view posexplode(sub_ids) tmp_sub as sub_idx,
  sub_id lateral view posexplode(scores) tmp_sc as sc_idx,
  score
where
  sub_idx = sc_idx

查询结果如下：

可以看到只留下了对应关系的数据，这个时候我们就可以得到我们想要的结果了。这个应用不仅在企业中使用比较多，面试的时候，也经常会被面试官拿来提问。

涤生大数据往期精彩推荐