Sqoop import CLOB column from oracle database to hive string

Question

I am trying to run a sqoop process to get a table from an Oracle database into Hive. The table has a CLOB type column called VALUE, which is the one that is giving me the problems. To save this table in hive, in the query I convert the VALUE column to VARCHAR2 as follows:

DBMS_LOB.SUBSTR(VALUE, LENGTH(VALUE), 1) AS VALUE

but when LENGTH(VALUE) > 3800, I get the following error when executing the sqoop:

Error: java.io.IOException: SQLException in nextKeyValue ... Caused by: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

I have tried to import the data with this option: --map-column-java VALUE=String but it didn't work for me.

Does anyone know how to fix this?

Answer 1

Use below option to directly convert clob to string.

--map-column-java CLOB_column=String

Answer 2

I have solved my problem. As @Koushik_Roy has commented, the solution consisted in converting directly the CLOB column to String, adding in the sqoop the option:

--map-column-java CLOB_column=String 

(without the need to convert in the query anything previously).