sqoop export from Hive table stored in Parquet format to Oracle CLOB column results in (null) value

[#1]

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. Can anyone help me out here?? Thanks.

[#2 · Accepted solution]

from sqoop 1.4.6 user guide https://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_supported_data_types

	Note
Data types RAW, LONG and LOB (BLOB, CLOB and NCLOB) are supported for Data Connector for Oracle and Hadoop imports. They are not supported for Data Connector for Oracle and Hadoop exports

[#3]

Hi Artem,

Thanks for highlighting this. Could you suggest any alternative tool or idea or framework that can extract a CLOB column from Hive to Oracle.

Thanks.

[#4]

@Ramprasad S

I don't know off the top of my head, do you mind opening this as a new question and accept this. It will be easier to find an answer that way.

[#5]

@Artem Ervits sure I will raise this feature as a new request. Thank you.

[#6]

Hello, where is the new topic? I am not able to export blob/clob from hive to oracle.

Thx

[#7]

HI @abenesova,  I would suggest you open a new thread, as this is an older post. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post.