如何在xml数据格式的配置单元中使用横向视图分解？

zmeyuzjn 于 2021-06-01 发布在 Hadoop

关注(0)|答案(2)|浏览(364)

我正在尝试将xml格式的销售数据加载到配置单元表中。下面是一个小样本的数据。
我知道，如果我将下面的数据分离到几个表中，然后根据需要将它们连接起来，就可以将其加载到配置单元中。但是我只是想知道我是否可以将它们加载到一个表中，并且预期的输出应该像所附的屏幕截图一样。
请帮助我的表结构，我应该使用，我如何才能有效地使用横向视图分解选项来实现这一点。
样本数据：

<Store>
    <Version>1.1</Version>
    <StoreId>16695</StoreId>    
    <Bskt>
      <TillNo>4</TillNo>
      <BsktNo>1753</BsktNo>
      <DateTime>2017-10-31T11:19:34.000+11:00</DateTime>
      <OpID>50056</OpID>
      <Itm>
        <ItmSeq>1</ItmSeq>
        <GTIN>29559</GTIN>
        <ItmDsc>CHOCALATE</ItmDsc>
      <ItmProm>
          <PromCD>CM</PromCD>
        </ItmProm>
      </Itm>
      <Itm>
        <ItmSeq>2</ItmSeq>
        <GTIN>59653</GTIN>
        <ItmDsc>CORN FLAKES</ItmDsc>
      </Itm>
        <Itm>
        <ItmSeq>3</ItmSeq>
        <GTIN>42260</GTIN>
        <ItmDsc> MILK CHOCOLATE 162GM</ItmDsc>
        <ItmProm>
          <PromCD>MTSRO</PromCD>
          <OfferID>11766</OfferID>
        </ItmProm>
      </Itm>
    </Bskt>
    <Bskt>
      <TillNo>5</TillNo>
      <BsktNo>1947</BsktNo>
      <DateTime>2017-10-31T16:24:59.000+11:00</DateTime>
      <OpID>50063</OpID>
      <Itm>
        <ItmSeq>1</ItmSeq>
        <GTIN>24064</GTIN>
        <ItmDsc>TOMATOES 2KG</ItmDsc>
        <ItmProm>
          <PromCD>INSTORE</PromCD>
        </ItmProm>
      </Itm>
      <Itm>
        <ItmSeq>2</ItmSeq>
        <GTIN>81287</GTIN>
        <ItmDsc>ROTHMANS BLUE</ItmDsc>
        <ItmProm>
          <PromCD>TF</PromCD>
        </ItmProm>
      </Itm>
    </Bskt>
  </Store>

期望输出
在此处输入图像描述
表结构：

CREATE EXTERNAL TABLE IF NOT EXISTS POC_BASKET_ITEM_PROMO (
`Version` string,
`StoreId` string,
`DateTime` array<string>,
`BsktNo` array<double>,
`TillNo` array<int>,
`Item_Seq_num` array<int>,
`GTIN` array<string>,
`ItmDsc` array<string>,
`Promo_CD` array<string>,
`Offer_ID` array<int>
)

ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES (

"column.xpath.Version"="/Store/Version/text()",
"column.xpath.StoreId"="/Store/StoreId/text()",
"column.xpath.DateTime"="/Store/Bskt/DateTime/text()",
"column.xpath.BsktNo"="/Store/Bskt/BsktNo/text()",
"column.xpath.TillNo"="/Store/Bskt/TillNo/text()",
"column.xpath.Item_Seq_num"="/Store/Bskt/Itm/ItmSeq/text()",
"column.xpath.GTIN"="/Store/Bskt/Itm/GTIN/text()",
"column.xpath.ItmDsc"="/Store/Bskt/Itm/ItmDsc/text()",
"column.xpath.Promo_CD"="/Store/Bskt/Itm/ItmProm/PromCD/text()",
"column.xpath.Offer_ID"="/Store/Bskt/Itm/ItmProm/OfferID/text()"
)

STORED AS INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
    OUTPUTFORMAT 
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' 
    LOCATION 'hdfs://namenode:8020/DEV/TEST/nanda_test'
    TBLPROPERTIES (
    "xmlinput.start"="<Store","xmlinput.end"="</Store>"
);

输出：在此处输入图像描述
尝试在下面的查询中读取数据，结果并没有以我想要的方式显示。

select Version,StoreId,basket_dtm,basket_number,till_number from POC_BASKET_ITEM_PROMO
    LATERAL VIEW explode(DateTime) table1 as basket_dtm 
    LATERAL VIEW explode(BsktNo) table2 as basket_number
    LATERAL VIEW explode(TillNo) table3 as till_number;

结果：
在此处输入图像描述

hadoop Hive xml parsing Explode

来源：https://stackoverflow.com/questions/53384728/how-to-use-lateral-view-explode-in-hive-for-xml-data-format

2条答案

按热度按时间

izj3ouym1#

感谢您的详细解决方案。我测试了一下，效果非常好。我尝试了一种类似的方法，用xmlserde直接从xml读取数据。
我的挑战：

1)XML to JSON conversion takes additional development efforts and we don't have Apache Nifi installation parcels in Cloudera by default, we need to install it with custom parcels.
2) My data will definitely have spaces/tab spaces in it, especially in 'Item description' field.We need to load the data with the same names as we receive. So converting to JSON and use the 'org.openx.data.jsonserde.JsonSerDe' didn't help. Queries failed with errors as suggested by you.

下面是配置单元表结构和我用来读取数据的查询。我能够在没有任何问题的情况下成功地引爆一级阵列（bskt）。
但是当我尝试分解第二级数组（itm）时，它会为“itm”中的所有字段返回空结果。
我的查询或表结构本身有问题吗？

create external table nanda_scan_xml  (
  Version string,
  StoreId string,
  Bskt array<struct<
                    Bskt:struct<
                                DateTime:string,
                                TillNo:string,
                                BsktNo:string,
                                Itm:array<struct<
                                                Itm:struct<
                                                    ItmSeq:string,      
                                                    GTIN:string,        
                                                    ItmDsc:string,      
                                                    DeptCD:string,      
                                                    ItmCD:string,       
                                                    SalesQTY:string,        
                                                    SalesExGST:string,      
                                                    Points:string,      
                                                    CostExGST:string,       
                                                    GSTRate:string,     
                                                    DiscAmtExGST:string,        
                                                    ItmProm:struct<     
                                                                    PromCD:string,      
                                                                    OfferID:string      
                                                                  >
                                                              >
                                                     >
                                            >
                                >
                    >
            >
)
row format serde 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
with serdeproperties 
(
    "column.xpath.Version"       = "/Store/Version/text()",
    "column.xpath.StoreId"       = "/Store/StoreId/text()",
    "column.xpath.Bskt"  = "/Store/Bskt"

)
stored as 
inputformat     'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
outputformat    'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' 
LOCATION 'hdfs://namenode/LandingArea/Sources/SCANP/IGA_SCAN/STAGING/'
tblproperties 
(
    "xmlinput.start"    = "<Store>",
    "xmlinput.end"      = "</Store>"
);

查询：
1）对于工作正常的bskt：

SELECT  Version,
        StoreId,
        basket.Bskt.DateTime,
        basket.Bskt.bsktno,
        basket.Bskt.tillno
FROM eim_stg.nanda_scan_xml
LATERAL VIEW EXPLODE(Bskt) b AS basket;

结果：
在此处输入图像描述2）尝试在单个查询中分解两个横向视图时：

SELECT  Version,
        StoreId,
        basket.Bskt.DateTime,
        basket.Bskt.bsktno,
        basket.Bskt.tillno,
        item.Itm.ItmSeq,
        item.Itm.ItmDsc,
        item.Itm.GTIN,
        item.Itm.itmprom.OfferID,
        item.Itm.itmprom.PromCD 
FROM eim_stg.nanda_scan_xml
LATERAL VIEW EXPLODE(Bskt) b AS basket
LATERAL VIEW EXPLODE(basket.Bskt.Itm) i AS item limit 100;

结果：
在此处输入图像描述
3）查询：

SELECT  Version,
        StoreId,
        basket.Bskt.DateTime,
        basket.Bskt.bsktno,
        basket.Bskt.tillno,
        item.Itm.ItmSeq,
        item.Itm.ItmDsc,
        item.Itm.GTIN,
        item.Itm.itmprom.OfferID,
        item.Itm.itmprom.PromCD 
FROM eim_stg.nanda_scan_xml
LATERAL VIEW EXPLODE(Bskt) b AS basket
LATERAL VIEW EXPLODE(basket.Itm) i AS item limit 100;

错误：
在此处输入图像描述

赞(0）回复(0）举报 2021-06-02

a5g8bdjr2#

分解数组对象的工作方式类似于交叉连接。因此，如果您有3列，每列包含2个元素的数组，那么对所有列应用explode将得到8行。
不能将一个对象从数组Map到另一个。
实际上你可以用 posexplode 这给了你 index 对于每个元素。你可以根据条件加入。但是，当您有多个列并且每列的数组大小不同时，这就很棘手了。
解决方案
使用 posexplode 如果要分解的列较少且数组大小相同。对你来说这是行不通的。所以呢
将xml存储为复杂数据类型：将整个xml存储为复杂数据类型（不仅仅是数组），我指的是创建一个 struct 基于您的xml。如果没有太多复杂的xml，就可以实现这一点。然而 xmlSerde 不如 JSONserde 当涉及到将文件转换为复杂数据类型时。
所以在你的情况下最好的解决办法是。
将xml转换为json。你可以用 NiFi 或者其他技术。
使用创建配置单元表 JSONserde 加载这个文件。
根据需要创建视图。
xml的json

{"Version":"1.1","StoreId":"16695","Bskt":[{"TillNo":"4","BsktNo":"1753","DateTime":"2017-10-31T11:19:34.000+11:00","OpID":"50056","Itm":[{"ItmSeq":"1","GTIN":"29559","ItmDsc":"CHOCALATE","ItmProm":{"PromCD":"CM"}},{"ItmSeq":"2","GTIN":"59653","ItmDsc":"CORNFLAKES"},{"ItmSeq":"3","GTIN":"42260","ItmDsc":"MILKCHOCOLATE162GM","ItmProm":{"PromCD":"MTSRO","OfferID":"11766"}}]},{"TillNo":"5","BsktNo":"1947","DateTime":"2017-10-31T16:24:59.000+11:00","OpID":"50063","Itm":[{"ItmSeq":"1","GTIN":"24064","ItmDsc":"TOMATOES2KG","ItmProm":{"PromCD":"INSTORE"}},{"ItmSeq":"2","GTIN":"81287","ItmDsc":"ROTHMANSBLUE","ItmProm":{"PromCD":"TF"}}]}]}
``` `JsonSerde` 如果文件中有制表符或其他空格，则可能会显示错误。所以最好把它们去掉。
Hive表

create external table temp.test_json
(
Version string,
StoreId string,
Bskt array<struct<
BsktNo:string,
DateTime:string,
OpID:string,
TillNo:string,
Itm:array<struct<
GTIN:string,
ItmDsc:string,
ItmSeq:string,
ItmProm:struct<
OfferID:string,
PromCD:string
>

)
row format serde 'org.openx.data.jsonserde.JsonSerDe'
location '/tmp/test_json/table/';

![](https://i.stack.imgur.com/DHTHV.png)
创建视图

SELECT Version,
StoreId,
basket.bsktno,
basket.tillno,
basket.datetime,
item.itmseq,
item.itmdsc,
item.gtin,
item.itmprom.offerid,
item.itmprom.promcd
FROM temp.test_json
lateral view explode(bskt) b AS basket
lateral view explode(basket.itm) i AS item

![](https://i.stack.imgur.com/cXoUG.png)

赞(0）回复(0）举报 2021-06-01

我来回答

如何在xml数据格式的配置单元中使用横向视图分解？

2条答案

相关问题

热门标签

最新问答