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.

Unable to insert data in MySQL using ConvertJSONToSQL processor through NiFi

Solved Go to solution
Highlighted

Unable to insert data in MySQL using ConvertJSONToSQL processor through NiFi

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

Accepted Solutions
Highlighted

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

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
Highlighted

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

Hi @Prasanta Sahoo,

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

Thanks,

Jobin

View solution in original post

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

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...

Highlighted

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

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.

Highlighted

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

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

Don't have an account?
Coming from Hortonworks? Activate your account here