question Sqoop from Oracle to Hive table in Support Questions

Sqoop from Oracle to Hive table

hariprasanthmad — Thu, 24 Jan 2019 17:21:27 GMT

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?

Re: Sqoop from Oracle to Hive table

sadusumilli — Fri, 25 Jan 2019 07:41:59 GMT

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.

Re: Sqoop from Oracle to Hive table

hariprasanthmad — Fri, 25 Jan 2019 12:02:30 GMT

@Siva A Thankyou

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

Re: Sqoop from Oracle to Hive table

Shelton — Sat, 26 Jan 2019 09:27:55 GMT

@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

Re: Sqoop from Oracle to Hive table

hariprasanthmad — Tue, 29 Jan 2019 15:55:42 GMT

@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?

Re: Sqoop from Oracle to Hive table

Shelton — Sat, 02 Feb 2019 06:10:19 GMT

@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

Re: Sqoop from Oracle to Hive table

hariprasanthmad — Wed, 06 Feb 2019 12:33:09 GMT

@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-during.html