Skip to main content
CaioFalco
CaioFalco
Gold ⭐️
September 3, 2020
Solved

How to split the output flow file from ExecuteSQL (query perform joins)?

Hello.
I'm using an ExecuteSQL Processor to extract data from Oracle DB. The query has multiple joins and returns a large number of fields.

The problem is that ExecuteSQL Processor returns a single flow file (avro format) that is huge. 

I want to split the flow file (based in a number of rows, for example) and then merge them at the proper moment.

I have read about QueryDatabaseTable and GenerateFetchTable which are processors that can split the output flow files but looks like these processors aren't able to perform joins..

 

Does anyone knows a workaround?

CaioFalco
CaioFalcoAnswer
Gold ⭐️
September 10, 2020

Maybe I have found a solution..

I'm gonna use the ExecuteSQL to do a "select insert" query.. The query will perform the joins and load the data into a table. Then the QueryDatabaseTable will read from the new table.. That way I'll be able to use the "Max Rows Per Floe File" property.

View in thread

9 replies

Faerballert
Faerballert
Gold ⭐️
September 3, 2020

At ExecuteSQL Processor is a property named by "Max Rows Per Flow File".

There you can set how much rows each Flow File should be contain and later you can merge them like you wanted cause the flow files get an fragment attribute.

Translate
    CaioFalco
    CaioFalcoAuthor
    Gold ⭐️
    September 3, 2020

    There is no such a option at ExecuteSQL..

    CaioFalco_0-1599164411538.png

     

    Perhaps you're using a newer version of NiFi: Mine is 1.5.0.3.1.2.0-7

    Translate
    SagarKanani
    SagarKanani
    Gold ⭐️
    September 4, 2020

    You should use ListDatabaseTable and generatetablefetch to perform an incremental load. If you are joining the tables, you can do a replacetext after generatetablefetch to add the join query and then feed the flowfile to execute sql. You can split the amount of data in generatetablefetch. 

    OR 

    You can use splitrecord / splitcontent to split the single avro to multiple smaller files and then use mergecontent to merge them back if required.

    Hope this helps. If the comment helps you to find a solution or move forward, please accept it as a solution for other community members

    Translate
    Terms & ConditionsAccessibility statement