[NIFI-5246] ExecuteSQL Fails When Querying From PostgreSQL Geometric Types

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Implemented
• Affects Version/s: 1.5.0
• Fix Version/s: 1.9.0
• Component/s: Extensions
• Labels: Avro ExecuteSQL postgresql
• Assignee: Unassigned
• Reporter: Scott Wilburn
• Created: 28/May/18 19:39
• Updated: 06/Apr/20 18:38
• Resolved: 06/Apr/20 18:38

Description

ExecuteSQL processor errors when querying PostgreSQL database tables that contain geometric types, such as polygons, lines, and points. If Avro types are incompatible with any source types, then it needs to have a way cast to text/string so that queries do not fail.

The following is the error from NiFi:

ExecuteSQL[id=016366bb-22ff-1f1e-8061-a95b6f956fda] ExecuteSQL[id=0145610bb-22ff-1f1e-8061-a95b6f956fda] failed to process session due to java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / geometry (table: buildings, column: spatialobject) cannot be converted to Avro type: createSchema: Unknown SQL type 1111 / geometry (table: buildings, column: spatialobject) cannot be converted to Avro type