Troubleshooting import and export of Hadoop formats

Following is a list of limitations and possible errors when using Fast Data Movement with Hadoop formats. These limitations were found when testing the data movement feature on the listed environments at the time of the release. Note that these issues might be fixed on the side of database service provider at any time.

• Import to Hadoop formats is implemented as a Hive statement which inserts text data transferred from Db2® Warehouse into a Hive table that is stored in one of the Hadoop formats. The same applies to export in the opposite direction. Therefore, data transfer strongly depends on Hive and on the implementation of formats on a particular Hadoop distribution. This means that any limitations and defects that exist on a particular Hadoop distribution also affect data movement.
• The enable.doAs=true setting in Hive is required in order to import to Hadoop format. With this setting operations executed in Hive are executed with a connected user, rather than the user who started HiveServer.
• Nonprinting control characters cannot be processed when using Hadoop formats. If your data contains these characters, do not import to Hadoop formats.
• Do not use Avro format when your data contains NUMERIC fields in Db2 Warehouse, which are then mapped to DECIMAL on Hadoop. Converted data might be inconsistent.
• Moving a table containing timestamp data type that is stored in ORC format might lead to data inconsistencies. This problem depends on JDBC driver and Hive version. You should always double-check that the data is consistent after movement. You can use checksum calculation for that purpose.
• IBM BigInsights limitations:
  • On BigInsights 3, importing to Avro and Parquet is not supported and it results in the following error:

    com.ibm.fq.common.exception.hive.HiveException: Unable to execute Hive sql. 
    Error while processing statement: FAILED: SemanticException Unrecognized file 
    format in STORED AS clause: avr

  • On BigInsights 3, do not import tables to ORC format if they contain bigint and int8 data types.
  • On BigInsights 4.0, using Parquet is not supported and it results in one of the following errors:

    Incompatible Long vector column and primitive category VOID

    Unimplemented vector assigner for writable type class 
    org.apache.hadoop.hive.serde2.io.HiveDecimalWritable

  • On BigInsights 4.0, importing timestamp data type to Avro format is not supported.
  • Import to BigSQL is not supported. You can import a table to Hive and then synchronize it manually to BigSQL. However, querying files imported in Avro format from BigSQL is not supported even after manual synchronization.
• Cloudera limitations:
  • Import to Hadoop formats is not supported for Cloudera versions prior to 5.3.
  • Import of tables with new line characters within rows is not possible for Parquet. The error symptom is extra rows with unaligned data on the record with new line character. You can import such data using other compression formats.
  • Import of timestamp data type to Avro format is not supported on Cloudera.
  • Impala does not support ORC format.
  • Querying imported Avro tables in Impala is not supported on Cloudera versions prior to 5.5.
• Hortonworks limitations:
  • On Hortonworks 2.2, it is not possible to specify compression type in fq.output.compressed for Avro. You can leave the parameter empty and then default Hadoop compression is used.
  • On Hortonworks 2.2 import to Parquet is not supported.
  • When using Hortonworks 2.3, the add jar command for Hive JDBC connection might result in NullPointerException

<property>
        <name>fq.data.format.setting</name>
        <value>hive.exec.compress.output=true,avro.output.codec=snappy</value>
    </property>

As a result, a list of the following commands is sent to Hive:

SET hive.exec.compress.output=true;
SET avro.output.codec=snappy;