Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution
Highlighted

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

Explorer

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

Accepted Solutions
Highlighted

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

Super 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
Highlighted

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

Explorer

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;
Highlighted

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

Super 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

Highlighted

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

Super 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

Highlighted

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

Explorer

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

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

Explorer

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

Don't have an account?
Coming from Hortonworks? Activate your account here