Temos dados XML carregados no Hadoop como uma única coluna de string chamada XML. Estamos tentando recuperar para níveis de dados uma normalizar ou explodi-la em linhas únicas para processamento (você sabe, como uma tabela!) Já tentei explodir a função, mas não conseguindo exatamente o que queremos.

SAMPLE 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

Estamos consultando o Report.Id de nível mais alto e, em seguida, o ID e o nome do filho (Locations / Location). O que se segue dá-nos essencialmente um produto cartesiano de todas as combinações possíveis (neste exemplo, 8 linhas em vez das 4 que esperamos).

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

Tentei agrupar em uma estrutura e depois explodir, mas isso retorna duas linhas e duas matrizes.

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;

RESULTADOS

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

O QUE QUEREMOS É

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

Parece uma coisa comum querer fazer, mas não consegue encontrar nenhum exemplo. Qualquer ajuda é muito apreciada.