question Re: QueryDatabaseTable - importing from postgresql table with Numeric and decimal data type columns in Archives of Support Questions (Read Only)

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

vmaroli — Wed, 02 Nov 2016 18:56:33 GMT

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 ?

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

vmaroli — Wed, 02 Nov 2016 19:04:58 GMT

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;

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

TimothySpann — Wed, 02 Nov 2016 20:48:23 GMT

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.html

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.html

Do you have the latest HDF 2.01 version?

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

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

mburgess — Wed, 02 Nov 2016 23:39:09 GMT

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.

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

vmaroli — Thu, 03 Nov 2016 16:04:28 GMT

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

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

vmaroli — Thu, 03 Nov 2016 16:07:48 GMT

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