QueryDatabaseTable - importing from postgresql table with Numeric and decimal data type columns

[#1]

I am trying to import data from a postgresql table to Hive using Apache Nifi using a simple flow QueryDatabaseTable -> PutHiveStreamig and getting following error.

WARN [put-hive-streaming-0] org.apache.hive.hcatalog.data.JsonSerDe Error [org.codehaus.jackson.JsonParseException: Current token (VALUE_STRING) not numeric, can not use numeric value accessors

Source table is having some columns of type Numeric which I think is not handled well in the Nifi code causing error while interpreting data types at Hive side. In JSON record, numeric values are appearing as strings with quotes like "33.750". Is there a work around for the problem ?

[#2]

In JdbcCommon.java, see lines 318 - 322

// Did not find direct suitable type, need to be clarified!!!!
case DECIMAL:
case NUMERIC: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion().noDefault();
              break;

[#3]

can you post the DDL of both tables?

Check out my example: https://community.hortonworks.com/articles/64122/incrementally-streaming-rdbms-data-to-your-hadoop.h...

Are there a lot of nulls?

Can you post an example row?

What JDK and NIFI versions are you using?

What version of Hive and Hadoop are you running?

It may be an issue like this when some data is a number and some is an number in quotes

https://issues.apache.org/jira/browse/PIG-3407

Maybe this fix is relevant https://community.hortonworks.com/questions/17996/nifi-applying-an-avro-schema-in-convertcsvtoavro.h...

Do you have the latest HDF 2.01 version?

http://docs.hortonworks.com/HDPDocuments/HDF2/HDF-2.0.1/index.html

[#4 · Accepted solution]

QueryDatabaseTable does indeed treat DECIMAL and NUMERIC types as strings in the outgoing Avro, there is a Jira (NIFI-2624) to improve the handling of these types. In the meantime, you might be able to use ConvertAvroSchema, but you won't be able to support BigDecimal values there either; it only supports conversion to/from int, long, double, and float. If your values fit in a double, that might work for now.

[#5]

Thanks for the response. Tried with ConvertAvroSchema and is working fine.

[#6]

I am using HDF-2.0.1.0-12 with HDP 2.4 (Hive 1.2.1.2.4. HDFS 2.7.1.2.4) . jdk1.8.0_71