Hive+Xml Processing

                                  Hive+Xml Processing

First to understand XPATH() : by using this to parse XML data into String array.

Example: small xml data

   <rec><name>Babu</name><age>25</age><sex>male</sex></rec>

 <rec><name>Radha</name><age>23</age><sex>female</sex></rec>

NOTE:  xml data converted into hive table in two steps process

1.      Convert the xml data into array format  

2.      Array data can be converted into hive table format.

Process :

Step1: create the hive table

      Ex: Hive>create table hivexml(str string);

Step2: load the xmldata into hive table

     EX:    Hive>load data local inpath ‘xmlfile’ into table hivexml;

Ø  By this step load all local xml data into your hive table astise, so we can convert that data into STRING ARRAY format by using XPATH(), And then we can convert the array data into normal hive table data,

Step3:  convert the xml data into array format

     EX:   Hive>select xpath(str,’rec/*/text()’) from xmlhive;

Ø  OutPut:  ["Babu”,”25”,”male”]

                [“Radha”,”23”,”female”]

Explanation of ‘rec/*/text()’

                     rec: its define Node of xml same as XML DATA  (Check the xml data)

                       *: its define all the fields of xml data.

If you want specific fields simply mansion it  like below

Ex: Hive>select xpath(str,’rec/name/text()’)from xmlhive;

Ø  OutPut:  [“Babu”]

                [“Radha”]

Step4:  crate the HIVE table required columns

   EX: Hive> create  table newhivexml(name string,age  int,sex  string);

Ø  After creating the table to load the xml array format data into newhivexml table like below

Step5: 

     Hive> insert overwrite table newhivexml select xpath_string(str,'rec/name'),xpath_string(str,'rec/age'),xpath_string(str,'rec/sex')from hivexml;

Hive>select * from  newhivwxml ;

To get the data in table format like below.

name    age    sex

Babu    25      male

Radha   23      female

Thank you.

*  This note only for to get some basic idea purpose give me your feedback