如何将字符串转换为蜂巢中爆炸的结构数组？ [英] How to Convert string to array of struct in hive and explode?

问题描述

我在蜂巢中有以下格式的数据。 table test（seq string，result string）;

  | seq |结果| 
 ---------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------ 
 | 0001 | [{ OFFERID： Default_XYZ， BUSINESSNAME： 苹果， businessGroup： 默认， businessIssue： 默认， interactionId：  -  4930126168287369915， CAMPAIGNID：P- 1\" ， 等级： 1}，{ OFFERID： Default_NAV， BUSINESSNAME： 橙， businessGroup： 默认， businessIssue： 默认， interactionId： -7830126168223452134，campaignID：P-1，rank：2}] 
  

输出应该像



  | seq | offerId | businessName | businsesGroup | businessIssue | interactionId | campaignId |等级| 
 ---------------------------------------------- -------------------------------------------------- ---------------- 
 | 0001 | Default_XYZ | Apple |默认|默认| -4930126168287369915 | P-1 | 1 | 
 | 0001 | Default_NAV |橙色|默认|默认| -7830126168223452134 | P-1 | 2 | 
  

我尝试将字符串转换为结构数组，但它不适用于直接CAST。

有任何帮助吗？

  select orderNumber，offerId，businessName，rank from（
 
 select sequenceNumber，
 collect_list（oid ['offerId']）as offerid_list 
，collect_list（oid ['businessName']）as businessName_list 
，collect_list（oid ['rank']）as rank_list 
 from（
 select sequenceNumber，
 str_to_map（translate （）（）（）（）（）（）（）（）（）（） \\\}，））oid as offer_id 
）x 
 by sequenceNumber 
 
）y横向视图爆炸（offerid_list）olist as offerId 
横向视图爆炸（businessName_list）olist作为businessName 
横向视图explode（rank_list）rlis t为等级
  

解决方案

/ p>

  select 
 seq，
 split（split（results，，）[0]，'： '）[1] as offerId，
 split（split（results，，）[1]，'：'）[1] as businessName，
 split（split（results，，） [2]，'：'）[1]作为businessGroup，
 split（split（results，，）[3]，'：'）[1]作为businessIssue，
 split（split（split结果，，）[4]，'：'）[1] as interactiveId，
 split（split（results，，）[5]，'：'）[1] as campignId 
 regexp_replace（split（split（results，，）[6]，：）[1]，[\\ | |]]，）作为等级
 
从
（
 select seq，
 split（translate（result），''\\ [|] | \'，''），}，）as r 
 from test 
）t1 
 LATERAL VIEW爆炸（r）rr AS结果
  

I have data in below format in hive. table test(seq string, result string);

|seq  | result                                                                                                                                                                                                                                                                                                                                                 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|0001 | [{"offerId":"Default_XYZ","businessName":"Apple","businessGroup":"Default","businessIssue":"Default","interactionId":"-4930126168287369915","campaignID":"P-1","rank":"1"},{"offerId":"Default_NAV","businessName":"Orange","businessGroup":"Default","businessIssue":"Default","interactionId":"-7830126168223452134","campaignID":"P-1","rank":"2"}] |

Output should be like

|seq  | offerId     | businessName   | businsesGroup| businessIssue | interactionId        | campaignId | rank |
----------------------------------------------------------------------------------------------------------------
|0001 | Default_XYZ | Apple          | Default      | Default       | -4930126168287369915 | P-1        | 1    |
|0001 | Default_NAV | Orange         | Default      | Default       | -7830126168223452134 | P-1        | 2    |

I tried to convert string to Array of struct, but it didnt work with direct CAST.

Any help please?

[EDIT - Tried below query]

 select sequenceNumber, offerId, businessName, rank from (

 select sequenceNumber,
        collect_list(oid['offerId']) as offerid_list
       , collect_list(oid['businessName']) as businessName_list
        ,collect_list(oid['rank']) as rank_list
  from (
 select sequenceNumber,
        str_to_map(translate(offer_Id,'{}','')) as oid

        from test
        lateral view explode (split(translate(result, '[]"',''),"\\},")) oid as offer_id
    ) x
    group by sequenceNumber

      ) y lateral view explode(offerid_list) olist as offerId
      lateral view explode(businessName_list) olist as businessName
      lateral view explode(rank_list) rlist as rank

解决方案

Found one solution to my question:

select                                                   
seq, 
split(split(results,",")[0],':')[1] as offerId,
split(split(results,",")[1],':')[1] as businessName,
split(split(results,",")[2],':')[1] as businessGroup,
split(split(results,",")[3],':')[1] as businessIssue,
split(split(results,",")[4],':')[1] as interactionId,
split(split(results,",")[5],':')[1] as campignId
regexp_replace(split(split(results,",")[6],":")[1], "[\\]|}]", "") as  rank

from
(
  select seq,
     split(translate(result), '"\\[|]|\""',''), "},") as r
      from test  
) t1
LATERAL VIEW explode(r) rr AS results

这篇关于如何将字符串转换为蜂巢中爆炸的结构数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！