How to aggregate rows in Apache Hop
What this shows
Memory Group By aggregates rows into one row per group - counts, sums, averages and so on. Unlike the ordinary Group By, it does not need its input sorted, because it keeps the groups in memory as it goes. That makes it simpler to use and unsuitable for data larger than your memory.
Reference documentationThe complete list of Memory Group By options is documented in the Apache Hop manual, which is the authoritative reference.hop.apache.org →Sample pipeline
The pipeline in the video is Apache Hop's own sample, memory-group-by-basic.hpl, which ships in the Hop samples project under transforms/.
Transcript
Full transcript
Memory Group By aggregates rows into one row per group - counts, sums, averages and so on. Unlike the ordinary Group By, it does not need its input sorted, because it keeps the groups in memory as it goes. That makes it simpler to use and unsuitable for data larger than your memory.
Seven book titles, each with a genre and a number of copies sold, are aggregated into one row per genre.
The top table names the fields that define a group. Here it is genre alone, so every row sharing a genre collapses into one. List more than one field and the group is the combination of them.
The lower table says what to work out for each group. Every line has a name for the new field, the field it reads, and the operation. Number of Values counts the titles, Sum and Average work on copies sold, and Maximum picks out the best seller.
Running the pipeline collapses seven rows into four.
One row per genre, carrying the four aggregates and nothing else. That is the thing to remember: fields that are neither grouped on nor aggregated do not survive, because there is no single value for them to take.