Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Rising Star

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 ?

1 ACCEPTED SOLUTION

avatar
Master Guru

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.

View solution in original post

5 REPLIES 5

avatar
Rising Star

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;

avatar
Master Guru

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

avatar
Master Guru

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.

avatar
Rising Star

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

avatar
Rising Star

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