我们将 XML 数据作为名为 XML 的单个字符串列加载到 Hadoop 中。我们正在尝试检索数据级别,并将其标准化或分解为单行进行处理(你知道,就像表格一样!)已经尝试过分解功能,但没有得到我们想要的。
示例 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>
QUERY 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;
QUERY 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;
RESULTS
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"
似乎是一件很常见的事情,但找不到任何例子。任何帮助是极大的赞赏。