arrays - 如何将字符串转换为复杂的结构数组并在蜂巢中爆炸

Question

我有下面的蜂巢表

id     string 
code   string
config string  

价值观：

dummyID|codeA|[{"pmc":"111","scc":"aa1","pgtp":"a22","pgn":"a33","pgrc":"a44"},{"pmc":"222","scc":"bb1","pgtp":"b22","pgn":"b33","pgrc":"b44","sen":"b77"},{"pmc":"333","scc":"cc1","pgtp":"c22","pgn":"c33","pgrc":"c44","pscc":[],"mapb":"c88"},{"pmc":"444","scc":"dd1","pgtp":"d22","pgn":"d33","pgrc":"d44","pscc":["ghgh"],"mapb":"d88"},{"pmc":"555","scc":"ee1","pgtp":"e22","pgn":"e33","pgrc":"e44","mapb":"e88"}]

我需要像下面的输出一样分解数组：（结构下的任何元素都可以是可选的）

dummyID|codeA|{"pmc":"111","scc":"aa1","pgtp":"a22","pgn":"a33","pgrc":"a44"}
dummyID|codeA|{"pmc":"222","scc":"bb1","pgtp":"b22","pgn":"b33","pgrc":"b44","sen":"b77"}
dummyID|codeA|{"pmc":"333","scc":"cc1","pgtp":"c22","pgn":"c33","pgrc":"c44","pscc":[{"qtgm":"tt1","swrt":"rr2"}],"mapb":"c88"}
dummyID|codeA|{"pmc":"444","scc":"dd1","pgtp":"d22","pgn":"d33","pgrc":"d44","pscc":["ghgh"],"mapb":"d88"}
dummyID|codeA|{"pmc":"555","scc":"ee1","pgtp":"e22","pgn":"e33","pgrc":"e44","mapb":"e88"}

我试过了：

select 
id,
code,
exp_val   
FROM   temp 
LATERAL VIEW explode(array(config)) temp AS exp_val ;

上面的查询没有给出任何错误，但没有爆炸并获得单行，横向视图内联也不起作用

我尝试使用以下架构创建表并尝试从上面的字符串配置字段插入记录，但由于数据类型不匹配错误而失败

id    string,
code  string,
config  array<struct<pmc:String,scc:String,pgtp:string,pgn:string,pgrc:string,pscc:Array<String>,sen:Array<String>,mapb:Array<String>>> 

当我尝试为配置运行选择查询时，我得到以下结果

|dummyID|codeA|{"pmc":"[{\"pmc\":\"111\",\"scc\":\"aa1\",\"pgtp\":\"a22\",\"pgn\":\"a33\",\"pgrc\":\"a44\"},{\"pmc\":\"222\",\"scc\":\"bb1\",\"pgtp\":\"b22\",\"pgn\":\"b33\",\"pgrc\":\"b44\",\"sen\":\"b77\"},{\"pmc\":\"333\",\"scc\":\"cc1\",\"pgtp\":\"c22\",\"pgn\":\"c33\",\"pgrc\":\"c44\",\"pscc\":[],\"mapb\":\"c88\"},{\"pmc\":\"444\",\"scc\":\"dd1\",\"pgtp\":\"d22\",\"pgn\":\"d33\",\"pgrc\":\"d44\",\"pscc\":[\"ghgh\"],\"mapb\":\"d88\"},{\"pmc\":\"555\",\"scc\":\"ee1\",\"pgtp\":\"e22\",\"pgn\":\"e33\",\"pgrc\":\"e44\",\"mapb\":\"e88\"}]","scc":null,"pgtp":null,"pgn":null,"pgrc":null,"pscc":null,"sen":null,"mapb":null} 

Explode 也不适用于此数据集

有什么我想念的吗？

标签： arrayshadoopstructhiveexplode

Answer 1

删除array内部爆炸功能并尝试以下操作

select 
 id,
 code,
 exp_val   
FROM temp 
LATERAL VIEW explode(config) temp AS exp_val ;

第二种选择：

select 
 t.id,
 t.code,
 e.*   
FROM temp t
LATERAL VIEW outer inline(t.config) e ;