json - 将 Json 转换为 HIVE 中的单独列

Question

我在 Hive 数据库表中有 4 列。前两列是字符串类型，第 3 和第 4 列是 JSON。类型。如何提取不同列中的json数据。Hive 中可用的 SERDE 似乎只处理 json 数据。我有普通 (STRING) 和 JSON 数据。我如何在此处的单独列中提取数据。

例子:

abc 2341    {max:2500e0,value:"20",Type:"1",ProviderType:"ABC"} {Name:"ABC",minA:1200e0,StartDate:1483900200000,EndDate:1483986600000,Flags:["flag4","flag3","flag2","flag1"]}

xyz 6789    {max:1300e0,value:"10",Type:"0",ProviderType:"foo"} {Name:"foo",minA:3.14159e0,StartDate:1225864800000,EndDate:1225864800000,Flags:["foo","foo"]}

Answer 1

给定一个固定的 JSON

create table mytable (str string,i int,jsn1 string, jsn2 string);

insert into mytable values
    ('abc',2341,'{"max":2500e0,"value":"20","Type":"1","ProviderType":"ABC"}','{"Name":"ABC","minA":1200e0,"StartDate":1483900200000,"EndDate":1483986600000,"Flags":["flag4","flag3","flag2","flag1"]}')
   ,('xyz',6789,'{"max":1300e0,"value":"10","Type":"0","ProviderType":"foo"}','{"Name":"foo","minA":3.14159e0,"StartDate":1225864800000,"EndDate":1225864800000,"Flags":["foo","foo"]}')
;

---

select  str,i
       ,jsn1_max,jsn1_value,jsn1_type,jsn1_ProviderType
       ,jsn2_Name,jsn2_minA,jsn2_StartDate,jsn2_EndDate
       ,jsn2_Flags

from    mytable

        lateral view json_tuple (jsn1,'max','value','Type','ProviderType') 
            j1 as jsn1_max,jsn1_value,jsn1_type,jsn1_ProviderType

        lateral view json_tuple (jsn2,'Name','minA','StartDate','EndDate','Flags') 
            j2 as jsn2_Name,jsn2_minA,jsn2_StartDate,jsn2_EndDate,jsn2_Flags
;            

---

+-----+------+----------+------------+-----------+-------------------+-----------+-----------+----------------+---------------+-----------------------------------+
| str |  i   | jsn1_max | jsn1_value | jsn1_type | jsn1_providertype | jsn2_name | jsn2_mina | jsn2_startdate | jsn2_enddate  |            jsn2_flags             |
+-----+------+----------+------------+-----------+-------------------+-----------+-----------+----------------+---------------+-----------------------------------+
| abc | 2341 | 2500.0   |         20 |         1 | ABC               | ABC       | 1200.0    |  1483900200000 | 1483986600000 | ["flag4","flag3","flag2","flag1"] |
| xyz | 6789 | 1300.0   |         10 |         0 | foo               | foo       | 3.14159   |  1225864800000 | 1225864800000 | ["foo","foo"]                     |
+-----+------+----------+------------+-----------+-------------------+-----------+-----------+----------------+---------------+-----------------------------------+