Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution
Highlighted

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

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

Accepted Solutions
Highlighted

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

Expert Contributor

try including this option

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

View solution in original post

5 REPLIES 5
Highlighted

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

Expert Contributor

try including this option

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

View solution in original post

Highlighted

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

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

Highlighted

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

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

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

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. 


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:

Highlighted

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

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 

Don't have an account?
Coming from Hortonworks? Activate your account here