Support Questions

Find answers, ask questions, and share your expertise

Sqoop from Oracle to Hive table

avatar

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

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

View solution in original post

6 REPLIES 6

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

avatar

@Siva A Thankyou

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

avatar
Master 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

avatar

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

avatar
Master 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

avatar

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