Skip to main content
GCaixeta
GCaixeta
Gold ⭐️
September 15, 2021
Solved

Apache Nifi - Error on ExecuteSQL in a table with spatial data on OracleDB

I've been trying to run an SQL that comes from a GenerateTableFetch processor, so when the SQL will be executed the following error is thrown:

ExecuteSQL[id=ba6f39d7-4ea4-19bd-984a-1b3176886957] Unable to execute SQL select query SELECT * FROM MY_SPATIAL_TABLE WHERE 1=1 for StandardFlowFileRecord[uuid=fafa1a55-5345-4588-b0be-9e216fc86099,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1631702321511-1, container=default, section=1], offset=688879, length=46],offset=0,name=0e20eaaa-f1a7-4ce8-8cf2-e4a7858c6adc,size=46] due to java.lang.IllegalArgumentException: createSchema: Unknown SQL type 2002 / MDSYS.SDO_GEOMETRY (table: NiFi_ExecuteSQL_Record, column: GEOM) cannot be converted to Avro type; routing to failure: java.lang.IllegalArgumentException: createSchema: Unknown SQL type 2002 / MDSYS.SDO_GEOMETRY (table: NiFi_ExecuteSQL_Record, column: GEOM) cannot be converted to Avro type
↳ causes: org.apache.nifi.processor.exception.ProcessException: java.lang.IllegalArgumentException: createSchema: Unknown SQL type 2002 / MDSYS.SDO_GEOMETRY (table: NiFi_ExecuteSQL_Record, column: GEOM) cannot be converted to Avro type

 

Does someone know some processor that I could use on this case (I've been trying to understand if the Geomesa could help me on that) or any other suggestion?

GCaixeta
GCaixetaAnswer
Gold ⭐️
September 20, 2021

After looks better to the process, I understood what is need to persist Spatial data from a Oracle Database into a PostgreSQL.

 

First I can`t automate all the process due the fact the NIFI (could be a feature request!?) cannot handle GeoSpatial data, due this I had to use specific functions in my Oracle query to return the data in WKT format (which is a text format). Making possible the data serialization from NIFI, and after that I just apply some modifications over the data (to make the data complaint with the table) and everything works.

View in thread

2 replies

GCaixeta
GCaixetaAuthor
Gold ⭐️
September 16, 2021

Well after some tries and research, one way that I found to fix the select is using some functions from the Oracle DB offer( read more here and here). Although this remove the automation from the process due the fact that NIFI is not able to handle with Spatial Data (Geometry type, more specifically), nevertheless I'm still looking for some other way to solve this.

Translate
GCaixeta
GCaixetaAuthorAnswer
Gold ⭐️
September 20, 2021

After looks better to the process, I understood what is need to persist Spatial data from a Oracle Database into a PostgreSQL.

 

First I can`t automate all the process due the fact the NIFI (could be a feature request!?) cannot handle GeoSpatial data, due this I had to use specific functions in my Oracle query to return the data in WKT format (which is a text format). Making possible the data serialization from NIFI, and after that I just apply some modifications over the data (to make the data complaint with the table) and everything works.

Translate
Terms & ConditionsAccessibility statement