Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Create hive table from xml data file

Create hive table from xml data file

New Contributor

I have a xml file containing data that I want to import into a table that I can query with Hive.

What is the simplest way to do this?

I tried the steps here: http://completehadooptraining.blogspot.ca/2013/02/hivexml-processing.html

But I get an error at step 2 about permission denied when trying to copy the input file.

3 REPLIES 3

Re: Create hive table from xml data file

Master Guru
Please post your exact error, in full, to allow us to troubleshoot it.

Re: Create hive table from xml data file

Contributor

hi all, while I was creating hive table for XML file it is giving below errors.

hive> select * from xmltb;
OK
<seller_info>
<seller_name>jenzen12</seller_name>
<seller_rating>new</seller_rating>
</seller_info>
Time taken: 1.905 seconds
hive> select xpath_string(line, '/seller_info/seller_name/text()') as seller_name from xmltb;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201705101713_0054, Tracking URL = http://localhost.localdomain:50030/jobdetails.jsp?jobid=job_201705101713_0054
Kill Command = /usr/lib/hadoop/bin/hadoop job -kill job_201705101713_0054
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2017-05-18 18:02:09,138 Stage-1 map = 0%, reduce = 0%
2017-05-18 18:02:53,579 Stage-1 map = 100%, reduce = 100%
Ended Job = job_201705101713_0054 with errors
Error during job, obtaining debugging information...
Job Tracking URL: http://localhost.localdomain:50030/jobdetails.jsp?jobid=job_201705101713_0054
Examining task ID: task_201705101713_0054_m_000002 (and more) from job job_201705101713_0054

Task with the most failures(4):
-----
Task ID:
task_201705101713_0054_m_000000

URL:
http://localhost.localdomain:50030/taskdetails.jsp?jobid=job_201705101713_0054&tipid=task_2017051017...
-----
Diagnostic Messages for this Task:
java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row {"line":"<seller_info>"}
at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:159)
at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:50)
at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:417)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:332)
at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
at org.apache.hadoop.mapred.Child.main(Child.java:262)
Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row {"line":"<seller_info>"}
at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:673)
at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:141)
... 8 more
Caused by: org.

FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask
MapReduce Jobs Launched:
Job 0: Map: 1 HDFS Read: 0 HDFS Write: 0 FAIL
Total MapReduce CPU Time Spent: 0 msec
hive>

 

kindly help me out from this.

Re: Create hive table from xml data file

New Contributor

For this XML file,

select xpath(line, '/seller_info/seller_name/')  from xmltb; should work. Similar kind of thing worked for me.