Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Sqoop from Oracle to Hive table

SOLVED Go to solution
Highlighted

Sqoop from Oracle to Hive table

New Contributor

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?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Sqoop from Oracle to Hive table

New Contributor

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.

6 REPLIES 6

Re: Sqoop from Oracle to Hive table

New Contributor

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

New Contributor

@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

Mentor

@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

New Contributor

@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

Mentor

@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

New Contributor

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