Sqoop DB2 Import with BLOB Datatype (Has XML Data) to Hive Table

[#1]

I am trying to import a DB2 Table into Hive which is having a field defined as BLOB datatype which will have XML stored in it.

But getting the below error. Is there any workaround?

Import Script: sqoop import --connect jdbc:db2://smatest.os390.allmerica.com:4104/SMATEST --username ******** --password ******** --table HNDBD034.T3T02T_RATE_TXN_XML_LOG --split-by ID -m 1 --hive-import;

Logs: 16/08/23 14:08:39 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.4.2.0-258 16/08/23 14:08:39 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 16/08/23 14:08:39 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override 16/08/23 14:08:39 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc. 16/08/23 14:08:39 INFO manager.SqlManager: Using default fetchSize of 1000 16/08/23 14:08:39 INFO tool.CodeGenTool: Beginning code generation 16/08/23 14:08:40 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM HNDBD034.T3T02T_RATE_TXN_XML_LOG AS t WHERE 1=0 16/08/23 14:08:40 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM HNDBD034.T3T02T_RATE_TXN_XML_LOG AS t WHERE 1=0 16/08/23 14:08:40 ERROR orm.ClassWriter: Cannot resolve SQL type 2009 16/08/23 14:08:40 ERROR orm.ClassWriter: Cannot resolve SQL type 2009 16/08/23 14:08:40 ERROR orm.ClassWriter: Cannot resolve SQL type 2009 16/08/23 14:08:40 ERROR orm.ClassWriter: Cannot resolve SQL type 2009 16/08/23 14:08:40 ERROR orm.ClassWriter: Cannot resolve SQL type 2009 16/08/23 14:08:40 ERROR orm.ClassWriter: Cannot resolve SQL type 2009 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RQ_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RS_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RQ_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RS_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RQ_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RS_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RQ_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RS_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RQ_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RS_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RQ_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RS_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RQ_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RS_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RQ_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RS_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RQ_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RS_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RQ_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RS_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RQ_STRING_TXT 16/08/23 14:08:40 ERROR orm.ClassWriter: No Java type for SQL type 2009 for column XML_RS_STRING_TXT 16/08/23 14:08:40 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException java.lang.NullPointerException at org.apache.sqoop.orm.ClassWriter.parseNullVal(ClassWriter.java:1377) at org.apache.sqoop.orm.ClassWriter.parseColumn(ClassWriter.java:1402) at org.apache.sqoop.orm.ClassWriter.myGenerateParser(ClassWriter.java:1528) at org.apache.sqoop.orm.ClassWriter.generateParser(ClassWriter.java:1491) at org.apache.sqoop.orm.ClassWriter.generateClassForColumns(ClassWriter.java:1932) at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1736) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605) at org.apache.sqoop.Sqoop.run(Sqoop.java:148) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235) at org.apache.sqoop.Sqoop.main(Sqoop.java:244)

[#2 · Accepted solution]

try including this option

–map-column-java blob_column_name=String,clob_column_name=String

[#3]

@AravinthRaj Soundararajan

As part of the import, you need to determine data type mappings. There are a number of data types that will not get implicitly converted to Hive. As @Satish Bomma suggested, you need to explicitly help sqoop to use a target data type.

[#4]

Hello,

I am importing a DB2 database with Sqoop to Hive. Is there any way to permanently store mappings for specific types? I am importing several tables that contain "Character" columns which all have to be mapped manually. I want to permanently store that "Character" is stored as "Varchar".
Can this be done?

regards

[#5]

@Muffex, As this is an older post, you would have a better chance of receiving a resolution by starting a new thread. 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. 

[#6]

Hello @VidyaSargur,

thanks for your answer. You are totally right. I only realized that this is an older thread after I had already posted. 

Therefore I already created a new thread (https://community.cloudera.com/t5/Support-Questions/Permanently-store-sqoop-map-column-hive-mapping-...).

regards