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

[#1]

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'

[#2]

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"
)