Support Questions

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

Unable to insert data in MySQL using ConvertJSONToSQL processor through NiFi

avatar
Contributor

Hi, I want to load data from hive table to MySql table using HDF (nifi). I have created the following dataflow.

After execution of dataflow, it is showing 151 bytes data has written the destination table. However there is no data in the destination table.

Please help me if there is any configuration issue.

5637-capture5.png

5639-capture6.png

5640-capture7.png

5651-capture8.png

1 ACCEPTED SOLUTION

avatar

Hi @Prasanta Sahoo,

Please add PutSQL processor (with DBCPConnectionPool for mysql) after ConvertJSONToSQL processor and try.

Thanks,

Jobin

View solution in original post

4 REPLIES 4

avatar

Hi @Prasanta Sahoo,

Please add PutSQL processor (with DBCPConnectionPool for mysql) after ConvertJSONToSQL processor and try.

Thanks,

Jobin

avatar

The ConvertJSONToSQL processor[1] merely converts a JSON object into a SQL query that can be executed. After it is converted, you haven't sent the command anywhere yet. You need another processor after that in order to actually send the command to be executed by a system. The PutSQL processor[2] (as suggested by @Jobin George) executes the SQL UPDATE or INSERT command that is in the contents of the incoming FlowFile.

[1] https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi.processors.standard.ConvertJSONToS...

[2] https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi.processors.standard.PutSQL/index.h...

avatar
Contributor

As suggested by @Jobin George , I have added PutSQL processor and it inserted only one record. However i have 100 records are coming from ExecuteSql processor( contains select query: select * from table_name) and want to insert 100 records. Please help me if any configuration required for this requirement.

avatar

Hi @Prasanta Sahoo,

Can you try "ConvertAvroToJSON" processor with below configuration:

For "JSON container options" Instead of "none" try "array"

5674-screen-shot-2016-07-10-at-115507-pm.png

That determines how the stream flows as an array or single object.

Thanks,

Jobin