question Re: nifi convertavrotoorc decimal datatype issue in Support Questions

nifi convertavrotoorc decimal datatype issue

hskundhal — Tue, 19 Jun 2018 23:01:21 GMT

Hi, I am using nifi to load data to hive from sqlserver. I am using convertavrotoOrc to load data in orc format via puthdfs and puthiveql. Sqlserver datatypes of decimal are converted into binary datatype by processor and data is not readable. How I can correct this.

Re: nifi convertavrotoorc decimal datatype issue

mburgess — Tue, 19 Jun 2018 23:47:11 GMT

ConvertAvroToORC is in the Hive bundle which uses Avro 1.7.7, which does not support logical types such as decimal. This is discussed in NIFI-5079, where it was decided to add support via a PutORC processor in the upcoming Hive 3 bundle (slated for NiFi 1.7.0 and HDF 3.2).

If you are using a version of NiFi prior to 1.6.0, then upgrading may help solve the original conversion issue when fetching from SQLServer (via NIFI-4846). However if you're converting to ORC you will still run into the issue above. A workaround might be to store the Avro directly to HDFS and put the Hive table atop the Avro data vs ORC data.

Re: nifi convertavrotoorc decimal datatype issue

saikrishna_tara — Wed, 20 Jun 2018 00:04:46 GMT

@Matt Burgess,

if we follow your approach , we can then use INSERT INTO SELECT to load from Avro table to ORC table.? reason being in some cases like ACID in Hive only works in ORC format.

Re: nifi convertavrotoorc decimal datatype issue

mburgess — Wed, 20 Jun 2018 01:17:08 GMT

Yes I think that will work. Also if you convert from decimal to a different type then you should be able to use PutHiveStreaming, although that isn't always as performant as it could be. In the upcoming Hive 3 bundle, there is a new Streaming API and PutHive3Streaming should be much faster (and Avro logical types are supported).

Re: nifi convertavrotoorc decimal datatype issue

hskundhal — Wed, 20 Jun 2018 05:07:56 GMT

Thanks @Matt Burgess and @Saikrishna Tarapareddy. I converted upstream decimal to string to get it correctly in incremental ACID mode into hive table.