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 export from Hive table stored in Parquet format to Oracle CLOB column results in (null) value

Solved Go to solution

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

New Contributor

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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

Mentor

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

[Note>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
4 REPLIES 4

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

Mentor

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

[Note>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

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

New Contributor

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.

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

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

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

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