Hive行转列[一行拆分成多行/一列拆分成多列]

场景：

hive有张表armmttxn_tmp，其中有一个字段lot_number，该字段以逗号分隔开多个值，每个值又以冒号来分割料号和数量，如：A3220089:-40,A3220090:-40,A3220091:-40,A3220083:-40,A3220087:-40,A3220086:-40,A3220088:-40,A3220084:-40,A3220081:-40,A3220082:-40,A3220092:-40,A3220093:-40,A3220085:-40,A3220094:-40。

要求：

把该字段拆分开来，并且把料号和数量单独列出，也就是分成两列。

原数据：

select 
key_id,
lot_number 
from armmttxn_tmp
where key_id = '48641906';

Hive行转列[一行拆分成多行/一列拆分成多列]

用到的函数：split()、explode()

步骤：

step1：以逗号拆分开，如下：

[“A3220089:-40″,”A3220090:-40″,”A3220091:-40″,”A3220083:-40″,”A3220087:-40″,”A3220086:-40″,”A3220088:-40″,”A3220084:-40″,”A3220081:-40″,”A3220082:-40″,”A3220092:-40″,”A3220093:-40″,”A3220085:-40″,”A3220094:-40”]

使用split函数，把数据拆分开

select 
key_id ,
split(lot_number, ',') lot_number
from armmttxn_tmp
where key_id = '48641906';

Hive行转列[一行拆分成多行/一列拆分成多列]

step2：一行变成多行

select 
explode(split(lot_number, ',')) lot_number
from armmttxn_tmp
where key_id = '48641906';

Hive行转列[一行拆分成多行/一列拆分成多列]

这里如果加上key_id字段，会怎样呢？

Hive行转列[一行拆分成多行/一列拆分成多列]

SQL 错误 [10081] [42000]: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF’s are not supported outside the SELECT clause, nor nested in expressions

原因：当使用UDTF函数的时候,hive只允许对拆分字段进行访问。

所以，可以这样使用：select explode(split(lot_number, ‘,’)) lot_number from armmttxn_tmp where key_id = ‘48641906’;

但不可以这样使用：select key_id ,explode(split(lot_number, ‘,’)) lot_number from armmttxn_tmp where key_id = ‘48641906’;

如果想访问除了拆分字段以外的字段，怎么办呢?

用lateral view侧视图！

lateral view为侧视图,是为了配合UDTF来使用,把某一行数据拆分成多行数据.不加lateral view的UDTF只能提取单个字段拆分,并不能塞会原来数据表中.加上lateral view就可以将拆分的单个字段数据与原始表数据关联上.

注意：在使用lateral view的时候需要指定视图别名

–表名 lateral view UDTF(xxx) 视图别名（虚拟表名） as a,b,c（列别名）

–lateral view explode 相当于一个拆分lot_number字段的虚表,然后与原表进行关联.

step3：拆分的字段与原始表数据关联上.

select 
key_id ,
split(view.*,':') lot_number
from armmttxn_tmp lateral view explode(split(lot_number, ',')) view
where key_id = '48641906';

Hive行转列[一行拆分成多行/一列拆分成多列]

但还不是我们想要的最终结果，还需要把lot_number拆分成两列

step4：拆分成两列

select 
key_id ,
split(view.*,':')[size(split(view.*, ':'))-2] as lot_number,
split(view.*,':')[size(split(view.*, ':'))-1] as quantity
from armmttxn_tmp lateral view explode(split(lot_number, ',')) view
where key_id = '48641906';

Hive行转列[一行拆分成多行/一列拆分成多列]