4

sqoop not import datatype varchar2 to hadoop I have a table in oracle Database and I want import the data to hdfs. I am trying to do it with sqoop, but varchar2 columns are not imported. I mean that these data isn't arriving to hdfs file. my sqoop command 

sqoop import -D mapred.job.name='default oraoop'  --driver oracle.jdbc.driver.OracleDriver --connect "jdbc:oracle:thin:MyIp:MyServiceName" --username "XXXX" --password "XX" --target-dir "My_dir" --query 'select * from MyTable where $CONDITIONS' --split-by "coulmn"  --boundary-query "SELECT min(splitColumn),max(SplitCoulmn)  FROM DUAL" --num-mappers 30
Ram Ghadiyaram
  • 28,239
  • 13
  • 95
  • 121
Mohamed Emad
  • 104
  • 1
  • 8

2 Answers2

1

you can try to downgrade the ojdbc instead of using higher ojdbc "ojdbc6 or ojdbc7" use "ojdbc14" this solved the problem for me but in order not to face an exception with some encoding classes not being found remove or rename the "ori18n.jar" while importing data from the orale9i.

you can find the paths to these jar files in "$HADOOP_CLASSPATH" and "$SQOOP_HOME"

melbadry
  • 26
  • 5
0

May be sqoop couldn't identify the matching java type of VARCHAR2, so try with --map-column-java.

let's say column A is the VARCHAR2 type then your sqoop command would be,

sqoop import -D mapred.job.name='default oraoop' --driver oracle.jdbc.driver.OracleDriver --connect "jdbc:oracle:thin:MyIp:MyServiceName" --username "XXXX" --password "XX" --target-dir "My_dir" --query 'select * from MyTable where $CONDITIONS' --map-column-java a=String --split-by "coulmn" --boundary-query "SELECT min(splitColumn),max(SplitCoulmn) FROM DUAL" --num-mappers 30

let me know if this works.

Sathiyan S
  • 1,013
  • 6
  • 13
  • I tried it and i got this error 16/11/09 04:20:12 ERROR tool.ImportTool: Imported Failed: No ResultSet method for Java type string – Mohamed Emad Nov 09 '16 at 09:21
  • 'S' should be capital in `String`.. I have edited the command can you try now? – Sathiyan S Nov 09 '16 at 09:24
  • I tried it but still varchar2 columns are not imported – Mohamed Emad Nov 09 '16 at 09:41
  • So you are getting Null values? – Sathiyan S Nov 09 '16 at 09:47
  • can you also try dumping into hive and specify `--map-column-hive` and let me know. – Sathiyan S Nov 09 '16 at 09:54
  • I tried --map-column-hive but still varchar2 columns are not imported – Mohamed Emad Nov 09 '16 at 10:08
  • Mohamed, instead of giving `--query` try with `--columns c1,c2,...`. – Sathiyan S Nov 09 '16 at 10:14
  • I got this Error when I used --columns Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter – Mohamed Emad Nov 09 '16 at 10:48
  • sqoop import -D mapred.job.name='default oraoop' --driver oracle.jdbc.driver.OracleDriver --connect "jdbc:oracle:thin:@MyIp:MyServiceName" --username "XXX" --password "XXX" --target-dir "MyDir" --map-column-hive A=String --table 'MyTable' --split-by "x" --boundary-query "SELECT Min(x),Max(x) FROM DUAL" --num-mappers 30 --hive-import --hive-table xxx --columns AA,BB – Mohamed Emad Nov 09 '16 at 11:06
  • this is my command I just removed some data like IP, username ,Password , table names etc.. – Mohamed Emad Nov 09 '16 at 11:40
  • so you masked column names also? at least if you could provide me the columns from source table and its data type will be help full to analyze – Sathiyan S Nov 09 '16 at 11:49