question Re: Apache Nifi - Error on ExecuteSQL in a table with spatial data on OracleDB in Support Questions

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

GCaixeta — Wed, 15 Sep 2021 14:03:09 GMT

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?

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

GCaixeta — Thu, 16 Sep 2021 12:19:01 GMT

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.

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

GCaixeta — Mon, 20 Sep 2021 08:25:13 GMT

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.