AnsweredAssumed Answered

Sqoop Export from Hive to Oracle

Question asked by Santhoshkb on Sep 19, 2017
Latest reply on Oct 17, 2017 by maprcommunity

Doing POC on Sqoop Utility to export data from Hive to Oracle Database, where i am facing some issue on handling multi-line text column in hive.

 

Ex. Hive table Name "Hive_table" contains 3 columns (col1,col2,col3), data looks like as below

 

Col1   Col2                       Col3

1         Test something      Text 

                                          hello text

2         Test2                       Text2

                                           Hello text

                                           hello tex2

 

Col3 contains multi-line text, I thought of using regexp_replace to replace new line with some delimiter(like '$@$')

By using this my HDFS file creation is working fine but sqoop export is giving invalid content found exception 

 

Table contains 300 millions of records, let me know if there is any alternatives for this ...

 

My data resides in HBase as well can do sqoop export from hbase to oracle ? 

 

Below are the scripts used:

INSERT OVERWRITE DIRECTORY '/app/path/' ROW FORMAT DELIMITED FIELDS TERMINATED BY '#' SELECT col1, regexp_replace(col3,"\n","@&@") AS A FROM Hive_table


sqoop export --connect "jdbc:oracle:thin:@hostname*****" --hadoop-home "/opt/mapr/hadoop/hadoop-2.7.0" --password "*****" --username "****" --table "DEPARTMENTSCOPY" --input-fields-terminated-by '#' --input-lines-terminated-by '\n' --num-mappers 1 --columns "A,B" --export-dir "/app/path/" --input-null-string "\\\\N" --input-null-non-string "\\\\N" --verbose

 

Any solutions would be appreciated  

Outcomes