Hive - XML- Serde - Key Value Pair - Create table

Question

Key Value pair combination. We are trying to explode the ID as a column name and VALUE as the corresponding data for each column.

`<CT> <items> <item> <field> <id>Column1</id> <value>25672</value> </field> 
 <field> <id>Column2</id> <value>FGE</value> </field> <field> 
 <id>Column3</id> <value>Florence to Venice</value> </field> </item> 
 </items> 
 </CT>`

We are expecting to create a table as below, Expected Output:

Column1 Column2 Column3
25672   FGE     Florence to Venice

We tried using Map to extract the key value pair but we are not getting the desired result.

'CREATE EXTERNAL TABLE dev.reference_test(
PM_SubCollection array<map<string,string>>
)
ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES(
"column.xpath.PM_SubCollection"="/CT/items/item/field",
"xml.map.specification.id"="#id->#value"
)
STORED AS
INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION '/dev/reference_test'
TBLPROPERTIES (
"xmlinput.start"="",
"xmlinput.end"=""
);'


Output:
'[{"field":"Column125672"},{"field":"Column2FGE"},{"field":"Column3Florence to Venice"}]'

Any suggestions would be helpful

Not going to happen. The columns should be known in advance. — David דודו Markovitz, Jul 07 '17 at 17:19
Thank you for the response. If we know the columns and create the table with the expected columns, can we map the value part of it to the respective columns? — Paciferous, Jul 07 '17 at 17:52

score 1 · Answer 1 · answered Jul 09 '17 at 15:14

See https://github.com/dvasilen/Hive-XML-SerDe/issues/42

If you have to capture the message id as well as id and value from the following XML

<?xml version="1.0" encoding="UTF-8"?>
<CT>
   <messageID>11736</messageID>
   <items>
      <item>
         <field>
            <id>Column1</id>
            <value>25672</value>
         </field>
         <field>
            <id>Column2</id>
            <value>FGE</value>
         </field>
         <field>
            <id>Column3</id>
            <value>Florence to Venice</value>
         </field>
      </item>
   </items>
</CT>

Then I would go with the DDL which looks like this:

DROP TABLE IF EXISTS xml_42a;

CREATE  TABLE xml_42a(
message_id string,
fields array<struct<field:struct<id:string,value:string>>>
)
ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES(
  "column.xpath.message_id"="/CT/messageID/text()",
  "column.xpath.fields"="/CT/items/item/field"
  )
STORED AS 
INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat' 
TBLPROPERTIES (
"xmlinput.start"="<CT>",
"xmlinput.end"="</CT>"
);

load data local inpath '/Users/dvasilen/Misc/XML/42a.xml' OVERWRITE into table xml_42a;

select * from xml_42a;

and here is the output:

hive> 
    > select * from xml_42a; 
OK
11736   [{"field":{"id":"Column1","value":"25672"}},{"field":{"id":"Column2","value":"FGE"}},{"field":{"id":"Column3","value":"Florence to Venice"}}]
Time taken: 0.08 seconds, Fetched: 1 row(s)

Hive - XML- Serde - Key Value Pair - Create table

1 Answers1