Big Data Hadoop & Spark

Converting XML into CSV Using Pig

This blog focuses on converting the XML format of data into CSV format using pig commands.
Now we will take a sample XML data. After installing hadoop we get many configuration files in xml format and in this case we are taking hdfs-site.xml as our input data.
Our hdfs-site.xml file looks like this.

<configuration>
<property>
<name>dfs.replication</name>
<value>1</value>
</property>
<property>
<name>dfs.namenode.name.dir</name>
<value>/home/kiran/hadoop/namenode</value>
</property>
<property>
<name>dfs.datanode.data.dir</name>
<value>/home/kiran/hadoop/datanode</value>
</property>
<property>
<name>dfs.block.size</name>
<value>67108864</value>
</property>
</configuration>

Now we will convert the data inside this file to CSV format using pig.

A = load '/hdfs-site.xml' using org.apache.pig.piggybank.storage.XMLLoader('property') as (x:chararray);

Here we will load the xml file using the default XML loader available in pig, inside the XML loader we are specifying that our root element is property and we are storing the whole thing with an alias name x as chararray.

B = foreach A generate REPLACE(x,'[\\n]','') as x;

Hadoop
Here we are bringing the contents between the property tag in one line. This looks like as mentioned below
<property><name>dfs.replication</name><value>1</value></property>

C = foreach B generate REGEX_EXTRACT_ALL(x,'.*(?:<name>)([^<]*).*(?:<value>)([^<]*).*');

Now we are removing the brackets by using the above mentioned regular expression.

Before flatten statement the output looks like this.

D =FOREACH C GENERATE FLATTEN (($0));


Here by using flatten it will remove the remaining brackets.Now the Final result looks like this.

 The above output will be stored in a file using CSV loader available in pig by using the below command:

STORE D INTO '/pig_conversions/xml_to_csv' USING org.apache.pig.piggybank.storage.CSVExcelStorage();

This output is stored in the location /pig_conversions/xml_to_csv with name part-m-00000 of HDFS. We can download and see the contents of the file.
This is the final output which is in CSV format. We can now easily perform analysis on this data.
Hope this blog helped you in learning how to convert XML data into CSV.
Keep visiting our website Acadgild for more updates on Big Data and other technologies. Click here to learn Big Data Hadoop Development.
Hadoop

4 Comments

  1. Hi,
    What is “org.apache.pig.piggybank.storage.XMLLoader”. Is this pre-defined in pig or we have to register piggybank first.
    Thanks.

    1. Hi Nitin,
      org.apache.pig.piggybank.storage.XMLLoader is a default XML loader available in the latest versions of pig. Here we have used pig-0.15.0. You need not to register the piggybank, piggybank will be present in the pig library itself.

  2. Pingback: Accessing xml files using Hadoop pig, Hive | HadoopMinds

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Articles

Close