question Re: Unable to insert data in MySQL using ConvertJSONToSQL processor through NiFi in Archives of Support Questions (Read Only)

Unable to insert data in MySQL using ConvertJSONToSQL processor through NiFi

java_etl_bigdat — Mon, 19 Aug 2019 10:36:22 GMT

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.

Re: Unable to insert data in MySQL using ConvertJSONToSQL processor through NiFi

jgeorge — Fri, 08 Jul 2016 23:29:00 GMT

Hi @Prasanta Sahoo,

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

Thanks,

Jobin

Re: Unable to insert data in MySQL using ConvertJSONToSQL processor through NiFi

jpercivall — Sat, 09 Jul 2016 00:52:15 GMT

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.ConvertJSONToSQL/index.html

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

Re: Unable to insert data in MySQL using ConvertJSONToSQL processor through NiFi

java_etl_bigdat — Mon, 11 Jul 2016 13:33:53 GMT

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.

Re: Unable to insert data in MySQL using ConvertJSONToSQL processor through NiFi

jgeorge — Mon, 19 Aug 2019 10:35:57 GMT

Hi @Prasanta Sahoo,

Can you try "ConvertAvroToJSON" processor with below configuration:

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

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

Thanks,

Jobin