Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

PutDatabaseRecord and ARRAY type

avatar
New Contributor

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

6 REPLIES 6

avatar
New Contributor

Hi experts,

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

Thanks.

avatar
Super Guru

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

avatar
New Contributor

@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!

avatar
New Contributor

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

avatar
New Contributor

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!

avatar
Master Guru

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