Hive-XML-SerDe - Key/Value Pair - Map

Question

<Search>

<Country>USA</Country>
<Region>West</Region>
<Address>
    <Home>
        <Item>
                <id>Number</id>
                <value>135</value>
            </Item>
        <Item>
                <id>Street</id>
                <value>Pacific</value>
            </Item>
        <Item>
                <id>City</id>
                <value>Irvine</value>
            </Item>
        </Home>
     <Home>
        <Item>
                <id>Number</id>
                <value>1672</value>
            </Item>
        <Item>
                <id>Street</id>
                <value>Madison</value>
            </Item>
        <Item>
                <id>City</id>
                <value>Denver</value>
            </Item>
         </Home>
     </Address>

I am trying to create the below table structure but I am not getting the desired result

I am trying to create the below table structure but I am not getting the desired result I am trying to create the below table structure but I am not getting the desired result I am trying to create the below table structure but I am not getting the desired result

Country    Region                      Map
USA        West             {Number:135,Street:Pacific,City:Irvine}
USA        West             {Number:1672,Street:Madison,City:Denver}

`CREATE EXTERNAL TABLE search(
country string,
region string,
search array<struct<item:map<string,string>>>
)
PARTITIONED BY(date STRING)
ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES(
"column.xpath.country" = "/Search/country/text()",
"column.xpath.region" = "/Search/region/text()",
"column.xpath.item"="/Search/Address/Home/Item"
)
STORED AS
INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION '/search'
TBLPROPERTIES (
"xmlinput.start"="",
"xmlinput.end"=""
);

Is this possible or any other suggestions on how to get this data in the above format. Any help would be great. Thank you. `

Answer 1

Given the XML, the best you can do is probably something like this:

DROP TABLE IF EXISTS xml_47;

CREATE  TABLE xml_47(
  country string,
  region string,
  address array<struct<Home:array<struct<Item:struct<id:string,value:string>>>>>
)
ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES(
"column.xpath.country" = "/Search/Country/text()",
"column.xpath.region" = "/Search/Region/text()",
"column.xpath.address"="/Search/Address/Home"
)
STORED AS
INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
TBLPROPERTIES (
"xmlinput.start"="<Search>",
"xmlinput.end"="</Search>"
);

load data local inpath '/Users/dvasilen/Misc/XML/47.xml' OVERWRITE into table xml_47;

select * from xml_47;

Here is the output:

USA West    [{"home":[{"item":{"id":"Number","value":"135"}},{"item":{"id":"Street","value":"Pacific"}},{"item":{"id":"City","value":"Irvine"}}]},{"home":[{"item":{"id":"Number","value":"1672"}},{"item":{"id":"Street","value":"Madison"}},{"item":{"id":"City","value":"Denver"}}]}]
Time taken: 0.067 seconds, Fetched: 1 row(s)

To get to the elements:

select address[0].home[0] from xml_47;
OK
{"item":{"id":"Number","value":"135"}}
Time taken: 0.076 seconds, Fetched: 1 row(s)

To get the desired output:

USA West {Number:135,Street:Pacific,City:Irvine}
USA West {Number:1672,Street:Madison,City:Denver}

you will have to flatten the address array using LATERAL VIEW https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView