Oracle + NiFi => all fields converted to String

[#1]

Hi,

I'm using NiFi to transfer Oracle (11c) database tables to HDFS (Avro format).

Something goes wrong with the typing of the AVRO columns: they are all defined as String, even when the Oracle tablecolumn is of another type like Numeric, Timestamp of Datetime. Clearly, this is annoying 🙂

After some googling, I found suggestions that the problem exists in the combination of certain Oracle JDBC drivers and the converter to Avro. However, I could not find a proper solution; does anyone know what JDBC (or other?) driver to use in order to have correct typing in the Avro output?

[#2 · Accepted solution]

Hi @Bart Maes

I was able to see the same behavior, with Oracle Amazon RDS and Oracle jdbc-drivers-12c.

As for Numeric columns, I think there're few problems ATM:

1. Oracle uses NUMBER even if the column is defined with INT [1] (This is not a driver side issue, I guess.)

2. JDBC driver maps Number to BigDecimal

3. NiFi implementation maps BigDecimal to String, since at that time, Avro didn't support BigDecimal mapping until Avro 1.8.1. Current NiFi uses Avro 1.7.7. With Avro 1.8.1, we can utilize LogicalType [2] to map BigDecimal or Date more user friendly way.

4. There's an ongoing effort in NiFi project [3] to use LogicalType mechanism, so that those data types can be mapped with more appropriate data types.

Excuse me for not being able to share a workaround for current situation, but once that change has been merged to NiFi, your experience with those data types will be better. Or other folks may know other solution.

1. http://stackoverflow.com/questions/1419109/resultsetmetadata-returning-bigdecimal-insted-of-int

2. http://avro.apache.org/docs/1.8.1/spec.html#Logical+Types

3. https://issues.apache.org/jira/browse/NIFI-2624

Hope this helps,

Koji

[#3]

u can set ,

Use Avro Logical Types = true

in executeSQL or other processor.

this will solve the problem