Search code examples
oraclehadoopsqoop

Sqoop Imported Failed: Cannot convert SQL type 2005 when trying to import Oracle table

I get the following error when trying to import a table from an Oracle database as a parquet file.

 ERROR tool.ImportTool: Imported Failed: Cannot convert SQL type 2005

This question has already been raised here, but the proposed solution does not help me.

I am trying to import a table from command line using the following command with parameters in <> filled in with their corresponding value:

sqoop import --connect jdbc:oracle:thin:@<host>:<port>/<service> --username <user> --password <password> --hive-import --query 'SELECT * FROM <DB>.<table> WHERE $CONDITIONS' --split-by <ID> --hive-database <HIVE_DB> --hive-table <HIVE_TABLE> --incremental append --check-column <ID> --map-column-hive <ID>=integer --compression-codec=snappy --target-dir=/user/hive/<FOLDER> --as-parquetfile --last-value 0 -m 1

Does anyone know how to solve this? I am not an expert on the sqooped Oracle database, but it seems to be due to the presence of CLOB data types. I am running this command on CDH 5.8 with sqoop 1.4.6

Running the job without --as-parquetfile results in a sqoop job that seems to get stuck at map 0% reduce 0%.

Solution

  • Use --map-column-java to map clob datatype to Java String.

    For example, you have a column C1. Use:

    --map-column-java C1=String

    Check docs for more details.