Error when selecting table data that is stored in the sequence file format

You might encounter SQL5105N while querying table data that is stored in sequence files.

Symptoms

A query against a table whose data is stored in the sequence file format might return the following error:


SQL5105N  The statement failed because a Big SQL component encountered an
error. Component receiving the error: "BigSQL IO". Component returning the
error: "UNKNOWN". Log entry identifier: "[BSL-97-29a9f5ca6]".  SQLSTATE=58040

The following exception is written to the bigsql.log file:


Exception Label: UNMAPPED(java.io.IOException: wrong key class: org.apache.hadoop.io.NullWritable is not class org.apache.hadoop.io.BytesWritable)                                                
java.io.IOException: wrong key class: org.apache.hadoop.io.NullWritable is not class org.apache.hadoop.io.BytesWritable                                                                             
    at org.apache.hadoop.io.SequenceFile$Reader.next(SequenceFile.java:2564)                                                                                                                         
    at org.apache.hadoop.mapred.SequenceFileRecordReader.next(SequenceFileRecordReader.java:82)                                                                                                      
    at com.ibm.biginsights.bigsql.dfsrw.reader.DfsHiveSplit2Batch.split2Batch(DfsHiveSplit2Batch.java:110)                                                                                           
    at com.ibm.biginsights.bigsql.dfsrw.jaro.DfsSplitManager$SplitRunnable.run(DfsSplitManager.java:126)                                                                                             
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1153)                                                                                                               
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)                                                                                                               
    at java.lang.Thread.run(Thread.java:785)                                                                                                                                                         
    at com.ibm.biginsights.bigsql.dfsrw.jaro.DfsSplit2BatchThread.run(DfsSplit2BatchThread.java:58)

Causes

This error occurs only if you are using different data ingestion methods and one method uses a different sequence key than another method.

Resolving the problem

The recommended workaround is to create a new table and use a Hive INSERT...SELECT operation to copy the data from the table whose data is stored in sequence file format to the new table.