question Re: sqoop export from Hive table stored in Parquet format to Oracle CLOB column results in (null) value in Archives of Support Questions (Read Only)

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

ramprasad_sivap — Fri, 10 Feb 2017 15:02:55 GMT

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.

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

aervits — Fri, 10 Feb 2017 21:13:01 GMT

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

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

ramprasad_sivap — Mon, 13 Feb 2017 11:59:03 GMT

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

aervits — Mon, 13 Feb 2017 22:10:22 GMT

@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

ramprasad_sivap — Thu, 16 Feb 2017 09:06:12 GMT

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

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

abenesova — Mon, 08 Mar 2021 10:34:09 GMT

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

Thx

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

VidyaSargur — Mon, 08 Mar 2021 15:06:45 GMT

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.