Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

to insert content of json file into Postgresql

Highlighted

to insert content of json file into Postgresql

New Contributor

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 REPLIES 2

Re: to insert content of json file into Postgresql

Super Mentor

@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

Re: to insert content of json file into Postgresql

New Contributor

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.