[SQOOP-3132] sqoop export from Hive table stored in Parquet format to Oracle CLOB column results in (null)

• Type: Bug
• Status: Patch Available
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 1.4.6
• Component/s: connectors/oracle, hive-integration
• Labels: beginner

Environment

sandbox

• External issue URL: http://stackoverflow.com/questions/42153171/sqoop-export-from-hive-table-stored-in-parquet-format-to-oracle-clob-column-resu
• Assignee: Sandish Kumar HN
• Reporter: Ramprasad
• Created: 10/Feb/17 09:16
• Updated: 22/Aug/17 13:53

Description

I am trying to export a String column from Hive table (stored in Parquet format) to Oracle CLOB data type column using sqoop export. Below are the commands I run for creation of tables in Oracle & Hive and, the sqoop command I use to to export the data.

Table creation & insert into Hive:
create table default.sqoop_oracle_clob_test (sample_id int, verylargestring String) stored as PARQUET;
[SUCCESS]

insert into default.sqoop_oracle_clob_test (sample_id, verylargestring) values (123, "Really a very large String");
insert into default.sqoop_oracle_clob_test (sample_id, verylargestring) values (456, "Another very large String");
[SUCCESS]

Table creation in Oracle
create table sqoop_exported_oracle (sample_id NUMBER, verylargestring CLOB);
[success]

Sqoop export command:
sqoop \
export \
--connect jdbc:oracle:thin:@//host:port/database_name \
--username ****** \
--password ****** \
--table sqoop_exported_oracle \
--columns SAMPLE_ID,VERYLARGESTRING \
--map-column-java "VERYLARGESTRING=String" \
--hcatalog-table "sqoop_oracle_clob_test" \
--hcatalog-database "default"

sqoop job executes fine without any error messages and displays the message Exported 2 records.

The result in Oracle table is as below,

select * from sqoop_exported_oracle;

sample_id | verylargestring
123 | (null)
456 | (null)

I tried using --staging-table as well but, resulted in same. I suspect this is a bug while exporting to oracle CLOB columns when retrieving from Hive which is stored in parquet format.