Hive一行拆多行脸侥、多行拼一行

一行拆多行結(jié)合使用了lateral view和explode焕窝。
多行拼一行結(jié)合使用了concat_ws與collect_set棒呛，某些hive版本支持group_concat可以代替前者。

舉例說明：
有兩張表sen_tbl(敏感字段定義表)告唆、msg_tbl(消息表)莫秆。其中消息表的消息是一個json字符串，最多有兩層json嵌套悔详。而json消息中的某些value是含有敏感信息的，key-value是否敏感的定義存放在sen_tbl中惹挟。

現(xiàn)在要做的是將json消息中的敏感value加密茄螃，然后分成不含敏感的json，和只含敏感的json连锯。所以需要分為兩步：

拆分json归苍，并加密敏感value
分別合并敏感和非敏感鍵值對用狱，合并成2個json，放在不同的列

1. 建表拼弃，插入數(shù)據(jù)：

create table sen_tbl (
    col_nam string,  --字段（key）名稱
    is_sen string  --是否敏感：1敏感夏伊，2不敏感
);

insert into sen_tbl values('CRDACCPTNMELCT','1');  --敏感
insert into sen_tbl values('TXNMERCHNO','1');  --敏感
insert into sen_tbl values('ACCTNBR','0');
insert into sen_tbl values('CRSERVICECODE','0');
insert into sen_tbl values('modelFileId','0');
insert into sen_tbl values('trs_ist','0');


drop table if exists msg_tbl;
create table msg_tbl (
    msg_id string,  --消息id
    json_msg string  --消息主體
);


insert into msg_tbl values(
'1',
'{
    "sceneParams": {
        "ACCTNBR": "0100020100001001",
        "CRDACCPTNMELCT": "如家和美酒店管理有限公司如家快捷天壇店",
        "CRSERVICECODE": "340",
        "TXNMERCHNO": "303605280000102"
    },
    "modelFileId": 2,
    "modelFileThreshold": {
        "trs_ist": 0.4
    }
}'
);

insert into msg_tbl values(
'2',
'{
    "sceneParams": {
        "ACCTNBR": "0100020100001222",
        "CRDACCPTNMELCT": "招商銀行",
        "CRSERVICECODE": "340",
        "TXNMERCHNO": "303605285555555"
    }
}'
);

2. 將json消息的第一層拆分成多行數(shù)據(jù)：

create table explode_msg as
select msg_id,   --消息id
       '1' as json_lv,   --key在json中的層級
       tbl1.key, 
       tbl1.value
  from msg_tbl
  lateral view explode(default.json_to_map(json_msg)) tbl1 as key,value
;

SQL執(zhí)行結(jié)果數(shù)據(jù)在簡書中沒有對齊，在其他編輯器入notepad++中是可以對齊的

+---------------------+----------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| explode_msg.msg_id  | explode_msg.json_lv  |   explode_msg.key   |                                                               explode_msg.value                                                                |
+---------------------+----------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| 1                   | 1                    | modelFileId         | 2                                                                                                                                              |
| 1                   | 1                    | sceneParams         | {"CRDACCPTNMELCT":"如家和美酒店管理有限公司如家快捷天壇店","CRSERVICECODE":"340","ACCTNBR":"0100020100001001","TXNMERCHNO":"303605280000102"}  |
| 1                   | 1                    | modelFileThreshold  | {"trs_ist":0.4}                                                                                                                                |
| 2                   | 1                    | sceneParams         | {"CRDACCPTNMELCT":"招商銀行","CRSERVICECODE":"340","ACCTNBR":"0100020100001222","TXNMERCHNO":"303605285555555"}                                |
+---------------------+----------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------+

3. 將json消息第二層拆分成多行數(shù)據(jù)：

drop table if exists explode_msg_2;
create table explode_msg_2 as
select msg_id, 
       '2' as json_lv, 
       key, 
       tbl1.key2, 
       tbl1.value2
  from explode_msg
  lateral view explode(default.json_to_map(value)) tbl1 as key2,value2
;

+-----------------------+------------------------+---------------------+---------------------+-----------------------------------------+
| explode_msg_2.msg_id  | explode_msg_2.json_lv  |  explode_msg_2.key  | explode_msg_2.key2  |          explode_msg_2.value2           |
+-----------------------+------------------------+---------------------+---------------------+-----------------------------------------+
| 1                     | 2                      | sceneParams         | CRDACCPTNMELCT      | 如家和美酒店管理有限公司如家快捷天壇店  |
| 1                     | 2                      | sceneParams         | CRSERVICECODE       | 340                                     |
| 1                     | 2                      | sceneParams         | ACCTNBR             | 0100020100001001                        |
| 1                     | 2                      | sceneParams         | TXNMERCHNO          | 303605280000102                         |
| 1                     | 2                      | modelFileThreshold  | trs_ist             | 0.4                                     |
| 2                     | 2                      | sceneParams         | CRDACCPTNMELCT      | 招商銀行                                |
| 2                     | 2                      | sceneParams         | CRSERVICECODE       | 340                                     |
| 2                     | 2                      | sceneParams         | ACCTNBR             | 0100020100001222                        |
| 2                     | 2                      | sceneParams         | TXNMERCHNO          | 303605285555555                         |
+-----------------------+------------------------+---------------------+---------------------+-----------------------------------------+

4. 合并以上兩步的數(shù)據(jù)吻氧，并對敏感value加密：

drop table if exists explode_msg_fnl;
create table explode_msg_fnl as
select msg_id,
       json_lv,
       key as key1,
       key2,
       value2 as value,
       case when b.is_sen = '1' then md5(a.value2) else a.value2 end as value_sen
  from explode_msg_2 a
  join sen_tbl b
    on a.key2 = b.col_nam
union all
select msg_id,
       json_lv,
       key as key1,
       null,
       value,
       case when b.is_sen = '1' then md5(a.value) else a.value end as value_sen
  from explode_msg a
  join sen_tbl b
    on a.key = b.col_nam
 where value not like '{"%'
;

+-------------------------+--------------------------+-----------------------+-----------------------+-----------------------------------------+-----------------------------------+
| explode_msg_fnl.msg_id  | explode_msg_fnl.json_lv  | explode_msg_fnl.key1  | explode_msg_fnl.key2  |          explode_msg_fnl.value          |     explode_msg_fnl.value_sen     |
+-------------------------+--------------------------+-----------------------+-----------------------+-----------------------------------------+-----------------------------------+
| 1                       | 1                        | modelFileId           | NULL                  | 2                                       | 2                                 |
| 1                       | 2                        | sceneParams           | ACCTNBR               | 0100020100001001                        | 0100020100001001                  |
| 1                       | 2                        | modelFileThreshold    | trs_ist               | 0.4                                     | 0.4                               |
| 1                       | 2                        | sceneParams           | CRSERVICECODE         | 340                                     | 340                               |
| 1                       | 2                        | sceneParams           | TXNMERCHNO            | 303605280000102                         | ecb0166601a8264180164810a2df4ee9  |
| 1                       | 2                        | sceneParams           | CRDACCPTNMELCT        | 如家和美酒店管理有限公司如家快捷天壇店  | d531cfc939890cfbb3127f59bc30060a  |
| 2                       | 2                        | sceneParams           | ACCTNBR               | 0100020100001222                        | 0100020100001222                  |
| 2                       | 2                        | sceneParams           | CRSERVICECODE         | 340                                     | 340                               |
| 2                       | 2                        | sceneParams           | TXNMERCHNO            | 303605285555555                         | 0ab5cdd213a8313d69b3d8e1b5e1eadb  |
| 2                       | 2                        | sceneParams           | CRDACCPTNMELCT        | 招商銀行                                | e0f88f4dbec781d1ab8402e53f0e25c3  |
+-------------------------+--------------------------+-----------------------+-----------------------+-----------------------------------------+-----------------------------------+

5. 重新合并json的第二層的key溺忧、value，多行合并為一行：

drop table if exists result_msg;
create table result_msg as
select msg_id, key1, is_sen, concat('"',value,'"') as key1_val
  from explode_msg_fnl
 where json_lv = 1

union all
select msg_id, 
       key1,
       is_sen,
       concat('{', concat_ws(',', collect_set(map_val)), '}') as key1_val
  from (select msg_id, 
               key1, 
               concat('"',key2,'":"',value,'"') as map_val,
               is_sen
          from explode_msg_fnl
         where json_lv = 2
       ) t
 group by msg_id, key1, is_sen
;

+--------------------+---------------------+--------------------+--------------------------------------------------------------------------------------------------------+
| result_msg.msg_id  |   result_msg.key1   | result_msg.is_sen  |                                          result_msg.key1_val                                           |
+--------------------+---------------------+--------------------+--------------------------------------------------------------------------------------------------------+
| 1                  | modelFileThreshold  | 0                  | {"trs_ist":"0.4"}                                                                                      |
| 1                  | sceneParams         | 0                  | {"CRSERVICECODE":"340","ACCTNBR":"0100020100001001"}                                                   |
| 1                  | sceneParams         | 1                  | {"CRDACCPTNMELCT":"d531cfc939890cfbb3127f59bc30060a","TXNMERCHNO":"ecb0166601a8264180164810a2df4ee9"}  |
| 2                  | sceneParams         | 0                  | {"CRSERVICECODE":"340","ACCTNBR":"0100020100001222"}                                                   |
| 2                  | sceneParams         | 1                  | {"CRDACCPTNMELCT":"e0f88f4dbec781d1ab8402e53f0e25c3","TXNMERCHNO":"0ab5cdd213a8313d69b3d8e1b5e1eadb"}  |
| 1                  | modelFileId         | 0                  | "2"                                                                                                    |
+--------------------+---------------------+--------------------+--------------------------------------------------------------------------------------------------------+

6. 合并json第一層的key盯孙、value：

drop table if exists result_msg_2;
create table result_msg_2 as
select msg_id, 
       is_sen,
       concat('{', concat_ws(',', collect_set(json_val)), '}') as json_val
  from (select msg_id,
               is_sen,
               concat('"',key1,'": ',key1_val) as json_val
          from result_msg
       ) t
 group by msg_id, is_sen
;

+----------------------+----------------------+-----------------------------------------------------------------------------------------------------------------------------------+
| result_msg_2.msg_id  | result_msg_2.is_sen  |                                                       result_msg_2.json_val                                                       |
+----------------------+----------------------+-----------------------------------------------------------------------------------------------------------------------------------+
| 1                    | 0                    | {"modelFileThreshold": {"trs_ist":"0.4"},"sceneParams": {"CRSERVICECODE":"340","ACCTNBR":"0100020100001001"},"modelFileId": "2"}  |
| 1                    | 1                    | {"sceneParams": {"CRDACCPTNMELCT":"d531cfc939890cfbb3127f59bc30060a","TXNMERCHNO":"ecb0166601a8264180164810a2df4ee9"}}            |
| 2                    | 0                    | {"sceneParams": {"CRSERVICECODE":"340","ACCTNBR":"0100020100001222"}}                                                             |
| 2                    | 1                    | {"sceneParams": {"CRDACCPTNMELCT":"e0f88f4dbec781d1ab8402e53f0e25c3","TXNMERCHNO":"0ab5cdd213a8313d69b3d8e1b5e1eadb"}}            |
+----------------------+----------------------+-----------------------------------------------------------------------------------------------------------------------------------+

7. 行轉(zhuǎn)列鲁森，一列為不包含敏感的json，另一列為包含敏感的json：

create table result_msg_fnl as
select msg_id,
       max(case when is_sen = 0 then json_val end) as json_val,
       max(case when is_sen = 1 then json_val end) as json_val_sen
  from result_msg_2
 group by msg_id
;

+------------------------+-----------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+
| result_msg_fnl.msg_id  |                                                      result_msg_fnl.json_val                                                      |                                               result_msg_fnl.json_val_sen                                               |
+------------------------+-----------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+
| 1                      | {"modelFileThreshold": {"trs_ist":"0.4"},"sceneParams": {"CRSERVICECODE":"340","ACCTNBR":"0100020100001001"},"modelFileId": "2"}  | {"sceneParams": {"CRDACCPTNMELCT":"d531cfc939890cfbb3127f59bc30060a","TXNMERCHNO":"ecb0166601a8264180164810a2df4ee9"}}  |
| 2                      | {"sceneParams": {"CRSERVICECODE":"340","ACCTNBR":"0100020100001222"}}                                                             | {"sceneParams": {"CRDACCPTNMELCT":"e0f88f4dbec781d1ab8402e53f0e25c3","TXNMERCHNO":"0ab5cdd213a8313d69b3d8e1b5e1eadb"}}  |
+------------------------+-----------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+

Hive一行拆多行何缓、多行拼一行

Hive一行拆多行、多行拼一行