https://community.cloudera.com/t5/Support-Questions/Apache-NiFi-to-split-incoming-data-from-a-file-based-on/m-p/220284#M182169 <P><A rel="user" href="https://community.cloudera.com/users/75261/hksinghdec.html" nodeid="75261" target="_blank">@Hemu Singh</A></P><P>For this use case you need to use <STRONG>Query Record </STRONG>processor and Based on the Record Reader controller services configured this processor will execute<STRONG> sql queries on the Flowfile </STRONG>Contents and The <STRONG>result of the SQL query </STRONG>then<STRONG> becomes</STRONG> the content of the <STRONG>output FlowFile</STRONG><STRONG> </STRONG>in the <STRONG>format as specified</STRONG> in the <STRONG>Record Writer</STRONG> controller service. </P><P><STRONG><U>Flow:-</U></STRONG></P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="68518-flow.png" style="width: 1867px;"><img src="https://community.cloudera.com/t5/image/serverpage/image-id/15901i4CF2592551BC98E3/image-size/medium?v=v2&amp;px=400" role="button" title="68518-flow.png" alt="68518-flow.png" /></span></P><P><STRONG><U>Flow Explanation:-</U></STRONG></P><P>1.Generate Flowfile //added some test data</P><P>2.UpdateAttribute //added schema to the flowfile</P><P>3.Filter Column QueryRecord //</P><P style="margin-left: 20px;">3.1.need to configure/enable <STRONG>Record Reader/Writer</STRONG> controller services. </P><P style="margin-left: 20px;">3.2.added new property that will run sql where query on the flowfile</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="68519-filtercolumn.png" style="width: 1800px;"><img src="https://community.cloudera.com/t5/image/serverpage/image-id/15902i94211833D8617D84/image-size/medium?v=v2&amp;px=400" role="button" title="68519-filtercolumn.png" alt="68519-filtercolumn.png" /></span></P><P style="margin-left: 20px;">3.3.Use the original relation to store the file as is i.e having 100 records in it.<BR />4.QueryRecord //</P><P style="margin-left: 20px;">4.1.add two new properties that can run row_number window function(i'm having <STRONG>id column</STRONG> in the flowfile) and get first 75 records in one relation</P><DIV><DIV><DIV><DIV><DIV><DIV><DIV><DIV><STRONG>first 75 records</STRONG></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV><DIV><DIV><PRE>select * from (select *,Row_Number() over(order by id asc) as rn from FLOWFILE) r where r.rn &lt;= 75</PRE></DIV></DIV><P><STRONG>76 to 100 record </STRONG></P><PRE>select * from (select *,Row_Number() over(order by id asc) as rn from FLOWFILE) r where r.rn &gt; 75 and r.rn &lt;= 100 </PRE><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="68520-queryrecord.png" style="width: 2520px;"><img src="https://community.cloudera.com/t5/image/serverpage/image-id/15903i7397D231B08728C0/image-size/medium?v=v2&amp;px=400" role="button" title="68520-queryrecord.png" alt="68520-queryrecord.png" /></span></P><P>Use the above two relations first75records and 76to100 record relationships for further processing.</P><P>In addition Query Record supports Limit offset..etc also so you can use either row_number/limit offset ..etc to get only the desired 75 records from the flowfile.</P><P>Please refer to <A href="https://community.hortonworks.com/articles/121794/running-sql-on-flowfiles-using-queryrecord-process.html" target="_blank" rel="nofollow noopener noreferrer">this</A> and <A href="https://community.hortonworks.com/questions/178446/splitting-single-file-in-to-two-file-based-on-colu.html?childToView=178482#answer-178482" target="_blank" rel="nofollow noopener noreferrer">this</A> for QueryRecord processor configs and usage.</P> Sun, 18 Aug 2019 02:30:30 GMT Shu_ashu 2019-08-18T02:30:30Z