Support Questions
Find answers, ask questions, and share your expertise

PutDatabaseRecord and ARRAY type

Highlighted

PutDatabaseRecord and ARRAY type

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
Highlighted

Re: PutDatabaseRecord and ARRAY type

New Contributor

Hi experts,

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

Thanks.

Highlighted

Re: PutDatabaseRecord and ARRAY type

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

Highlighted

Re: PutDatabaseRecord and ARRAY type

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!

Highlighted

Re: PutDatabaseRecord and ARRAY type

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

Highlighted

Re: PutDatabaseRecord and ARRAY type

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!

Highlighted

Re: PutDatabaseRecord and ARRAY type

Super Guru

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