爆炸XML数据的列蜂房(Exploding a row of XML data in Hive)

我们必须加载到Hadoop的一个名为XML的String列XML数据。 我们正试图获取到数据的规范化水平，或者它爆炸成单行进行处理（你知道，像表​​！）尝试过爆炸的功能，但没有得到我们想要的东西。

示例XML

<Reports>
<Report ID="1">
<Locations>
  <Location ID="20001">
    <LocationName>Irvine Animal Shelter</LocationName>
  </Location>   
  <Location ID="20002">
    <LocationName>Irvine City Hall</LocationName>
  </Location>   
  </Locations>
</Report>
<Report ID="2">
<Locations>
  <Location ID="10001">
    <LocationName>California Fish Grill</LocationName>
  </Location>   
  <Location ID="10002">
    <LocationName>Fukada</LocationName>
  </Location>   
  </Locations>
</Report>
</Reports>

查询1

我们正在查询从孩子（地址/位置）更高级别的Report.Id然后ID和名称。 下面让我们基本上所有可能的组合的笛卡尔积（在这个例子中，8行，而不是我们希望的4）。

SELECT xpath_int(xml, '/Reports/Report/@ID') AS id, location_id, location_name 
FROM   xmlreports 
LATERAL VIEW explode(xpath(xml, '/Reports/Report/Locations/Location/@ID')) myTable1 AS location_id 
LATERAL VIEW explode(xpath(xml, '/Reports/Report/Locations/Location/LocationName/text()')) myTable2 AS location_name;

查询2

试图组成结构，然后爆炸，但是这将返回两行和两列。

SELECT id, loc.col1, loc.col2
FROM (
SELECT xpath_int(xml, '/Reports/Report/@ID') AS id, 
       array(struct(xpath(xml, '/Reports/Report/Locations/Location/@ID'), xpath(xml,     '/Reports/Report/Locations/Location/LocationName/text()'))) As foo
FROM   xmlreports) x
LATERAL VIEW explode(foo) exploded_table as loc;

结果

1   ["20001","20002"]       ["Irvine Animal Shelter","Irvine City Hall"]
2   ["10001","10002"]       ["California Fish Grill","Irvine Spectrum"]

我们要的是

1   "20001" "Irvine Animal Shelter"
1   "20002" "Irvine City Hall"
2   "10001" "California Fish Grill"
2   "10002" "Irvine Spectrum"

似乎是一个常见的事想做，但找不到任何的例子。 任何帮助是极大的赞赏。

Answer 1:

我看到两个办法来解决这个问题。

1. 创建自定义的UDF将解析包含一个XML元素，并返回你需要数组。 此后爆炸阵列。

2. 使用子查询。

我使用子查询的实施的解决方案2。 使用子查询即使蜂巢是“聪明”，创建一个只为这个地图，减少工作，所以我想你不会有性能问题。

SELECT 
 l2.key,
 l2.rid,
 l2.location_id,
 location_name
FROM (
 SELECT 
  l1.key as key,  
  l1.rid as rid, 
  location_id as location_id,
  l1.xml as xml
 FROM (
   SELECT key, xml, rid
   FROM xmlreports
   LATERAL VIEW explode(xpath(xml, '/Reports/Report/@ID')) rids as rid
 ) l1
 LATERAL VIEW explode(xpath(l1.xml, concat('/Reports/Report[@ID = ',l1.rid, ']/Locations/Location/@ID'))) locids as location_id
) l2
LATERAL VIEW explode(xpath(l2.xml, concat('/Reports/Report[@ID = ',l2.rid, ']/Locations/Location[@ID = ', l2.location_id ,' ]/LocationName/text()'))) locnames as location_name;

上运行XML文件，这个查询后，你提供我得到的结果，你正在寻找

1   1   20001   Irvine Animal Shelter
1   1   20002   Irvine City Hall
1   2   10001   California Fish Grill
1   2   10002   Fukada

希望这能解决你的问题。

问候，迪诺

文章来源: Exploding a row of XML data in Hive