Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
New Contributor

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)

1 ACCEPTED SOLUTION

avatar
Super Collaborator

try including this option

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

View solution in original post

5 REPLIES 5

avatar
Super Collaborator

try including this option

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

avatar
Super Guru

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

avatar
Explorer

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

avatar
Community Manager

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



Regards,

Vidya Sargur,
Community Manager


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community:

avatar
Explorer

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