Sqoop from Oracle to Hive table

[#1]

Dear All,

I am using this sqoop command for importing Oracle Table into Hive Table

sqoop import --connect jdbc:oracle:thin:@123.123.123.112:1500:COL --username admin -P --table db.table1 --hive-import --hive-overwrite --create-hive-table --hive-database hive_db --hive-table table1 --split-by primary_Key

After Importing from oracle to Hive

Some rows and columns are entirely Showing 'NULL' .

ORACLE TABLE	HIVE TABLE -ISSUE
1. PRIMARY_KEY (NOT NULL COLUMN) 2. There is 0 'NULL' Value	1. SOME ROWS AND COLUMNS ARE NULL 2. 35 NULL (ROWS and COLUMS)

How to clear it?

[#2 · Accepted solution]

I would suggest you to define field delimiter (--fields-terminated-by <char>). Looks like your data has values with comma (which is the default delimiter) and that is creating issues.

[#3]

@Siva A Thankyou

field delimiter (--fields-terminated-by | ) and field delimiter (--fields-terminated-by ~) used '\020' Solved my issue

[#4]

@Siva A

Try using the import control options, you can find them here - https://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html e.g The --null-string and --null-non-string arguments are optional.\ If not specified, then the string "null" will be used.

HTH

[#5]

@Geoffrey Shelton Okot tried your answers

--null-string and --null-non-string

Still Facing Same Issues.

we are using oracle11g type DB and tables as source to hive as destination .Is there any Datatype kinds of issue is there?

[#6]

@Siva A

Sqoop will by default import NULL values as string null. Hive is, however, using string \N to denote NULL values and therefore predicates dealing with NULL (like IS NULL) will not work correctly. You should append parameters --null-string and --null-non-string in case of import job or --input-null-string and --input-null-non-string in case of an export job if you wish to properly preserve NULL values.

Because sqoop is using those parameters in generated code, you need to properly escape value \N to \N:

HTH

[#7]

@Siva A

@Geoffrey Shelton Okot

--fields-terminated-by "\020" is solved this issue and i shared this details under idea.find the link below!

https://community.hortonworks.com/content/idea/236981/how-to-avoid-null-values-and-extra-columns-dur...