1. 建表语句结构 

create table if not exists employees ( name         string, salary       float, subordinates array
    
     , deductions   map
     
      , address      struct
      
        ) row format delimited fields terminated by '\001'  collection items terminated by '\002'  map keys terminated by '\003' lines terminated by '\n' stored as textfile;
      
     
    

2. 表里 name 和 subordinates 的数据结构

3. 使用 lateral view 和 explode 查询

select name,subordinate from employees lateral view explode(subordinates) subordinates_table as subordinate;

总结: explode就是将hive一行中复杂的 array 或者 map 结构拆分成多行。

下面就做个小例子, 创建 hive 表 doc, 表里只有一列 text 类型为 string, 将 hadoop 目录下的 README.txt 导入该表, 并写出 sql 求出 wordcount

create table if not exists doc(text string) row format delimited lines terminated by '\n';load data local inpath '/opt/hadoop-2.7.4/README.txt' overwrite into table doc;select word, count(*) from doc lateral view explode(split(text,' ')) ITable as word group by word;