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.

ExecuteSQL to HTML Result

Highlighted

ExecuteSQL to HTML Result

Hi,

I need to create an HTML file based on an "ExecuteSQL" result. My flow is like

- Receive request and data

- ExecuteSQL

- ConvertAvtoToJSON

- SplitJSON (JSONPath Expression=$.*)

- EvaluateJsonPath (To get the values as attributes like CODE = $.CODE)

- UpdateAttribute (HTML_ROW=<trow>${CODE}</trow><trow>${OID}</trow>)

- FetchFile (To get the template.html file contents which has an {{JSONRESULT}} in an appriate location)

- ReplaceText (Search Value={{JSONRESULT}}, Replacement Value=${HTML_ROW})

- Respond with enriched data

Problem is I get just one row of data.

Somehow I need to merge the results I think.

How can I get all the rows in a single HTML file?

Is this approach suitable?

Thanks.

3 REPLIES 3

Re: ExecuteSQL to HTML Result

I will also try these approaches:

    - I will create a unique file for each request. Then I will append each SQL result row into that file. Lastly, I will inject file contents into the HTML template file.
    - Another approach could be using a Python script with ExecuteScript processor which will build HTML string based on SQL result rows.
    - We can also use a global variable which we can append each SQL result row as HTML tags into that variable.

Re: ExecuteSQL to HTML Result

Super Guru
@Turker TUNALI

You can use MergeContent (or) MergeRecord processors and define min/max group size (or) entries then processor will wait until these values are reached atleast lower bounds.

-> Use max bin age property to force merge the bin after specified time (like 1hr,30min..etc).

Refer to this and this links for more details regards to Merge Processors in NiFi.

Re: ExecuteSQL to HTML Result

Thanks @Shu . I've finally merged the json flows in 1 flow file. If I use PutFile after the MergeContent I see this.

{"HTML_ROW":"<trow>1asd</trow><trow>1</trow>"}{"HTML_ROW":"<trow>CODE</trow><trow>9</trow>"}

But I need something like

<trow>1asd</trow><trow>1</trow><trow>CODE</trow><trow>9</trow>

My current flow is like this one.

99422-2019-01-25-135015-localhost-d221a2c0d3ea.png

I've 2 questions.

- How can I use flow contents in the ReplaceText processor? I am using Literal Replace and my search value is {{JSONRESULT}} . I've tried $1 and ${'1'} but I can't get the flow content on the ReplaceText processor.

- How can I get my desired output with my approach?

Regards,

Turker TUNALI


2019-01-25-135015-localhost-d221a2c0d3ea.png
Don't have an account?
Coming from Hortonworks? Activate your account here