to insert content of json file into Postgresql

[#1]

Hi,

I want to insert a simple nifi flow where i'm fetching a json file(test.json) from s3 and want to insert the contents of the test.json file in my postgreql table.

Can you please guide me with the flow.

Currently i have:

lists3object -> fetchs3object -> PUTSQL

First 2 steps works for me, i dunno how to inser the content of the test.json on my postgresql table.

[#2]

@Jayashree S

For inserting JSON data to Database you can refer to the ConvertJSONToSQL processor. This processor converts simple JSON into an SQL INSERT statement that will allow a relational database to be populated with the JSON data.

It Converts a JSON-formatted FlowFile into an UPDATE, INSERT, or DELETE SQL statement. The incoming FlowFile is expected to be "flat" JSON message, meaning that it consists of a single JSON element and each field maps to a simple type.

A good explanation of it can be found in the following HCC thread: https://community.hortonworks.com/questions/139219/convert-json-to-sql-format.html

.

Other references of ConvertJSONToSQL with MSSQL Database. https://rapidminernotes.blogspot.com/2018/01/populating-sql-server-from-apache-nifi.html

[#3]

My nifi flow is not progressing nor showing any error:

I'm not able to paste screenshots here.

Though all processors got started, none of them are doing any transactions (in & out ) both are 0 bytes and i dont see any errors also.

Last message logged in nifi-app.log is:

2019-06-14 07:26:48,864 INFO [NiFi Web Server-92] o.a.n.controller.StandardProcessorNode Stopping processor: class org.apache.nifi.processors.aws.s3.ListS3

2019-06-14 07:26:48,864 INFO [Timer-Driven Process Thread-4] o.a.n.c.s.TimerDrivenSchedulingAgent Stopped scheduling ListS3[id=016b1032-cd19-10b1-6d77-5a63389cd903] to run

2019-06-14 07:26:49,166 INFO [Flow Service Tasks Thread-1] o.a.nifi.controller.StandardFlowService Saved flow controller org.apache.nifi.controller.FlowController@5aaa6545 // Another save pending = false

2019-06-14 07:26:51,166 INFO [NiFi Web Server-20] o.a.n.c.s.StandardProcessScheduler Stopping FetchS3Object[id=016b102b-cd19-10b1-385a-bb202d1cebc4]

2019-06-14 07:26:51,166 INFO [NiFi Web Server-20] o.a.n.controller.StandardProcessorNode Stopping processor: class org.apache.nifi.processors.aws.s3.FetchS3Object

2019-06-14 07:26:51,179 INFO [Timer-Driven Process Thread-4] o.a.n.c.s.TimerDrivenSchedulingAgent Stopped scheduling FetchS3Object[id=016b102b-cd19-10b1-385a-bb202d1cebc4] to run

2019-06-14 07:26:51,185 INFO [Flow Service Tasks Thread-1] o.a.nifi.controller.StandardFlowService Saved flow controller org.apache.nifi.controller.FlowController@5aaa6545 // Another save pending = false

2019-06-14 07:26:56,833 INFO [NiFi Web Server-92] o.a.n.c.s.StandardProcessScheduler Starting ListS3[id=016b1032-cd19-10b1-6d77-5a63389cd903]

2019-06-14 07:26:56,836 INFO [Timer-Driven Process Thread-8] o.a.n.c.s.TimerDrivenSchedulingAgent Scheduled ListS3[id=016b1032-cd19-10b1-6d77-5a63389cd903] to run with 1 threads

2019-06-14 07:26:57,202 INFO [Flow Service Tasks Thread-1] o.a.nifi.controller.StandardFlowService Saved flow controller org.apache.nifi.controller.FlowController@5aaa6545 // Another save pending = false

The flow was working partially earlier with some error message at Splitjson processor. But now i'm not able to proceed as i don understand where it is stuck. I tried restarting nifi, but no luck.

Can you please suggest where could be the issue.