hive explode和LateralView的使用，创建包含数组类型字段的表

LateralView

点击查看用法

lateralview 与用户定义的表生成功能（例如）结合使用explode()。如，UDTF为每个输入行生成零个或多个输出行。lateralview 首先将UDTF应用于基础表的每一行，然后将结果输出行与输入行连接起来以形成具有提供的表别名的虚拟表。

Lateral View 用法

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

explode (array)

select explode(array('A','B','C'));select explode(array('A','B','C')) as col;select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf;select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf as col;

col
A
B
C

explode (map)

select explode(map('A',10,'B',20,'C',30));select explode(map('A',10,'B',20,'C',30)) as (key,value);select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf;select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf as key,value;

key	value
A	10
B	20
C	30

创造测试数据测试Lateral View和explode

创建一个包含数组类型字段的表，且格式为textfile

字段间分隔符为空格，数组元素间分隔符为逗号

create table xtable(name string,age string,subject array
   
    ) row format delimited fields terminated by ' ' collection items terminated by ',' stored as textfile;

查看表所在位置

0: jdbc:hive2://hadoop91:10000> desc formatted xtable;OK| Location:                     | hdfs://hadoop90:9000/user/hive/warehouse/xtable             | NULL                  |

创造数据

vi xtable.txt# 存入以下数据xhx 15 math,english,historybjx 20 physical,biological

将数据加载到表中

[root@hadoop91 ~]# hdfs dfs  -put /root/xtable.txt hdfs://hadoop90:9000/user/hive/warehouse/xtable/

查看表

0: jdbc:hive2://hadoop91:10000> select * from xtable;+--------------+-------------+-------------------------------+--+| xtable.name  | xtable.age  |        xtable.subject         |+--------------+-------------+-------------------------------+--+| xhx          | 15          | ["math","english","history"]  || bjx          | 20          | ["physical","biological"]     |+--------------+-------------+-------------------------------+--+

查询中添加一个explode

0: jdbc:hive2://hadoop91:10000> select explode(subject) from xtable;+-------------+--+|     col     |+-------------+--+| math        || english     || history     || physical    || biological  |+-------------+--+5 rows selected (0.402 seconds)

如果想要把name和id也查出来，则结果如下，报错了

0: jdbc:hive2://hadoop91:10000> select name,age,explode(subject) from xtable;Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

所以这个时候就需要用到Lateral View了

0: jdbc:hive2://hadoop91:10000> select name,age,subcol from xtable lateral view explode(subject) subtable as subcol;+-------+------+-------------+--+| name  | age  |   subcol    |+-------+------+-------------+--+| xhx   | 15   | math        || xhx   | 15   | english     || xhx   | 15   | history     || bjx   | 20   | physical    || bjx   | 20   | biological  |+-------+------+-------------+--+5 rows selected (0.302 seconds)

转载地址：http://pduzi.baihongyu.com/

你可能感兴趣的文章