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.

Hi guys, How will I copy a table from oracle to hive containing xml data in one field?

Hi guys, How will I copy a table from oracle to hive containing xml data in one field?

New Contributor

the oracle table has 2 columns in 1st column has normal data and 2nd column contains xml data. which one is the best way to copy a table from oracle to hive with xml data? Please cooperate me

CREATE TABLE F_CATEGORY_XML (recid string, xmlrecord string)
ROW FORMAT SERDE 'oracle.hadoop.xquery.hive.OXMLSerDe'
WITH SERDEPROPERTIES (
"column.xpath.recid"="/row/c1/text()",
"column.xpath.xmlrecord"="/row/c2/text()"
)
STORED AS
INPUTFORMAT 'oracle.hadoop.xquery.hive.OXMLInputFormat'
OUTPUTFORMAT 'oracle.hadoop.xquery.hive.OXMLOutputFormat'
TBLPROPERTIES (
"xmlinput.start"="<row",
"xmlinput.end"="</row>"
);

Error:

FAILED: SemanticException Cannot find class 'oracle.hadoop.xquery.hive.OXMLInputFormat'

1 REPLY 1

Re: Hi guys, How will I copy a table from oracle to hive containing xml data in one field?

New Contributor

This error means you need the XQuery for Hadoop jars in your classpath. You can download them here:

https://www.oracle.com/technetwork/database/database-technologies/bdc/big-data-connectors/downloads/...

"Oracle XQuery for Hadoop 4.9.1"

Then sections 7.2 and 7.3 show how you can add the jars to your classpath:

https://docs.oracle.com/bigdata/bda412/BDCUG/oxh_hive.htm#BDCUG691


CREATE TABLE F_CATEGORY_XML (recid STRING, xmlrecord STRING)
ROW FORMAT
   SERDE 'oracle.hadoop.xquery.hive.OXMLSerDe'
STORED AS
   INPUTFORMAT 'oracle.hadoop.xquery.hive.OXMLInputFormat'
   OUTPUTFORMAT 'oracle.hadoop.xquery.hive.OXMLOutputFormat'
TBLPROPERTIES(
   "oxh-elements" = "row", 
   "oxh-column.recid" = "./c1",
   "oxh-column.xmlrecord" = "./c2"
)