json - 使用查找表的 Hive 查询

Question

我有 2 个表，

• 带有 JSON Serde 的客户表只包含一列
• other是查找表，定位客户层级(1,2,3,4)

客户表

{ 
  "Name": "John Doe",
  "Info" : {
      "Address": "111 Main Street",
      "ID": 2222
  }
}

查找表有 2 列，客户 ID 和级别。例如

ID     Level
1111    1
1123    4
2234    1

我如何编写 Hive 查询来识别客户表中级别为 1 的所有客户？

谢谢

Answer 1

使用 get_json_object() 从 JSON 中提取 ID 并加入查找表，添加过滤。

演示:

 select s.cust_name, s.Address, s.ID, lkp.level
    from
        (select
               get_json_object(json_col,'$.Info.ID') as ID,
               get_json_object(json_col,'$.Name') as cust_name,
               get_json_object(json_col,'$.Info.Address') as Address
   from        
               ( --replace this subquery with your table
                select '{"Name": "John Doe","Info" : {"Address": "111 Main Street","ID": 2222}}' as json_col)s 
        ) s
   left join    
            ( --replace this subquery with your table
            select stack(4,
                         1111,    1,
                         1123,    4,
                         2234,    1,
                         2222,    3
                        )  as (ID, Level) 
            )lkp on s.ID=lkp.ID
where lkp.Level !=1     --filter out level 1 
   or lkp.level is null --this is to allow records without corresponding level in lkp 
;

结果:

OK
cust_name       address id      level
John Doe        111 Main Street 2222    3
Time taken: 31.469 seconds, Fetched: 1 row(s)