NiFi passing date from ExecuteSQL to PutDatabaseRecord

julglad 0
Viewed 41

I need to insert the result of an SQL query into a Postgres table. For that I use ExecuteSQL("Use Avro Logical Types" true) and PutDatabaseRecord("StatementType" INSERT, "Record Reader" AvroReader) processors. The insert doesn’t work because Nifi converts the date to this number: '1322683200000' and the column in the destination table is of type date.

I suppose I should either add the "UpdateRecord" processor between "ExecuteSQL" and "PutDatabaseRecord" processors or use "Data Record Path" property in the "PutDatabaseRecord" processor. But I can't find an example of configuring the UpdateRecord processor or filling the "Data Record Path" property

apache-nifi date postgresql
2 Answers

I tried to do the same process on my side with a dummy select current_date as value; in the sql execute processor and then passing the same over to insert the data into PostgresDB.

The flow was able to insert the date value into the sample table that I created for testing. Can you try the same and see if you have any issues or provide some samples of data and how you are building the data flow.

Data Flow

Execute Sql Processor

PutDatabaseRecord processor

Joby0

Insert started to work when I put columns in sql query in the same order as in destination table.

I thought that Nifi should match columns by names

julglad0
Related