Support Questions

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

How to Extract table values row by row, send the column value dynamically to ExecuteSQL processor and update the another table with query start time and end time?

avatar
Contributor

I have two table in mysql
1)query

mysql> desc query;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| query_id | int(11) | YES | | NULL | |
| query_execute | varchar(500) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+

2)queryexecute

mysql> desc queryExecutionInfo;
+-----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| query_id | int(11) | YES | | NULL | |
| query_startTime | time | YES | | NULL | |
| query_endTime | time | YES | | NULL | |
+-----------------+---------+------+-----+---------+-------+

1) I read query table rows 1 by 1
record like -(1,'select * from sample')
(2,'select * from demo')
2) then send every row query_execute column value dyanamicaly to ExecuteSql processor.
3) that time update queryExecutionInfo table column query_startTime.
4) run Executesql processor query.
5) after completion of query execution update the query_endTime column value of queryExecutionInfo table.

this process continue till all the rows completed in query table.

Can someone point me at an example to get me going

3 REPLIES 3

avatar
Contributor

1 - ExecuteSQL -> 2 - ConvertAvroToJSON -> 3 - splitJSon -> 4 - EvaluateJsonPath -> 5 - UpdateAttribute -> 6 ExecuteSQL -> 7 - replaceText -> 8- putSQL.

1 - 2 - 3 : query your table and split by row. You will have one flowfile by row. Use $ for the JsonPathExpression in the split processor.

4 - Retrieve the query_id and the query_execute in two attributes of each flowfile (values of your properties are $.query_id and $.query_execute)

5 - Create a new attribute query_startTime with the value ${now():format('HH:mm:ss')

6 - Execute the query ${query_execute}

7 - Write your insert query : INSERT INTO queryExecutionInfo VALUES (${query_id}, ${query_startTime},{${now():format('HH:mm:ss'));

8 - Execute your insert

I think this flow works, maybe someone will have something efficient.

avatar
Contributor

Thanks Quentin for reply,

I designed the flow as you said,after the replace text the query_id,query_startTime,query_endTime is not inserting into

queryExecutionInfo table .

I attached the screen shot of replaceText and ExecuteSql processor.

31417-replacetext.png

31415-putsql.png

Please kindly guide me for the same.


properties.png

avatar
Contributor

Do you have an error ?