PutDatabaseRecord and ARRAY type

[#1]

I have spent a few days with NiFi trying to use ExecuteSQLRecord and PutDatabaseRecord (configured with AvroRecordsetWriter) to transfer data from one PostgreSQL table to another table. Everything works find until I include an array of float column. The error shown in PutDatabaseRecord was "Cannot cast an instance of Ljavalang.Object to type Types.ARRAY"

Can anyone show me an example of how to make PutDatabaseRecord work with an array column in Postgresql database?

Thanks,

Andy

[#2]

Hi experts,

Could anyone shed some light on this? Or this is not yet supported?

Thanks.

[#3]

@AndyL Can you update original post or reply with screenshots of the Processor Configuration and Record Reader Configuration (including avro schemas)?   

[#4]

@stevenmatison Think I've managed to hit this as well. 

Some relevant screenshots (processor config, schema): https://imgur.com/a/Z4EH5wG

Schema for good measure: 

{
     "type": "record",
     "namespace": "com.asdf",
     "name": "sdfg",
     "fields": [
       { "name": "doc_id", "type": {"type": "array", "items": "long"} },
       { "name": "start_id", "type": {"type": "array", "items": "int"} },
       { "name": "end_id", "type": {"type": "array", "items": "int"} },
       { "name": "passage_date", "type": {"type": "int", "logicalType": "date"} },
       { "name": "passage_time", "type": {"type": "int", "logicalType": "time-millis"} }
     ]
} 

Thanks- any insight would be much appreciated!

[#5]

Hi @jumble!

Did you find the answer for this issue? I've met the same one and I don't know what to do.

Thanks, Ana

[#6]

Hi Ana,

Unfortunately I didn't find a "clean" solution for this at all- ended up converting my Avro records to JSON (using ConvertAvroToJSON), then ran into some similar issues with the ConvertJSONToSQL processor (it wasn't able to correctly generate SQL for all relevant data types- I seem to recall datetimes being an issue?), so used a Python ExecuteScript processor to convert JSON to SQL statements before executing these with PutSQL.

TL;DR: Avro records -> ConvertAvroToJSON -> ExecuteScript ("homemade" JSON to SQL conversion) -> PutSQL

Hope this helps!

[#7]

ARRAYs are a bit tricky.   But JSONReader and Writer may work better.