question Re: ExecuteSQL dynamic query.. in Archives of Support Questions (Read Only)

ExecuteSQL dynamic query..

yahya_najjar — Fri, 09 Dec 2016 00:31:00 GMT

Beloved forum,

is it possible to have dynamic query using ExecuteSQL processor ?

e.x.

FileX.txt content (comma separated)
XXXXX, BBBBB, CCCCC, CCCDD
XXXXX, EEEEE, CCCCC, DDDDD

outputstream
XXXXX, BBBBB, CCCCC, CCCDD, 'select customer_name from table where id=CCCDD limit 1'
XXXXX, EEEEE, CCCCC, DDDDD, 'select customer_name from table where id=DDDDD limit 1'

I want to amend the select statement result to the original file ?

Thankssss

Re: ExecuteSQL dynamic query..

mburgess — Fri, 09 Dec 2016 01:39:15 GMT

So if the customer_name value for id=CCCDD was "Matt" then you'd like the first output row to read:

XXXXX, BBBBB, CCCCC, CCCDD, Matt

Is that correct? If so, you could do the following:

Use SplitText to split the incoming CSV into one flow file per line
ExtractText to store the four column values as attributes (example template called Working_With_CSV here), let's assume the attribute for the fourth column is called "column.4"
ReplaceText to set the content of the flow file to a SQL statement "select customer_name from table where id=${column.4} limit 1"
ExecuteSQL to execute the statement
ConvertAvroToJson to get the record into JSON (for further processing)
EvaluateJsonPath to get the value of customer_name into an attribute (named "customer.name" with a JSON Path of $[0].customer_name or something like that)
ReplaceText to set the row back to the original columns plus the new one, with something like "${column.1},${column.2},${column.3},${column.4}, ${customer.name}"
(optional) MergeContent to join the rows back together (if you need them as one file)

Re: ExecuteSQL dynamic query..

yahya_najjar — Fri, 09 Dec 2016 07:21:41 GMT

its working, many thanks..

is it possible to have the same through executescript processor ?

Re: ExecuteSQL dynamic query..

yahya_najjar — Tue, 13 Dec 2016 09:50:23 GMT

is it possible to route files based on file content,

route to processor A if field2 = BBBBB, and to processor B if field2 = AAAA ? @Matt Burgess

Re: ExecuteSQL dynamic query..

mburgess — Tue, 13 Dec 2016 22:44:53 GMT

Yes, you can use something like the regex from step 2 above in a RouteOnContent processor, or after the ExtractText (step 2 above) you can use RouteOnAttribute looking for values of column.2.

Re: ExecuteSQL dynamic query..

yahya_najjar — Wed, 14 Dec 2016 00:00:50 GMT

Thanks @Matt Burgess

shall I put the regex as property ? can you advise further how to define it ?

Thanks a million

Re: ExecuteSQL dynamic query..

mburgess — Wed, 14 Dec 2016 22:47:41 GMT

Yes you can add a dynamic property whose value is a regular expression (see the documentation for more details).