How to read from a database in Apache Hop
What this shows
Table Input runs a query and turns its result into rows. That much is obvious. The part worth thinking about is how much of the work you leave to the database, because the choice is yours and the two options perform very differently.
Reference documentationThe complete list of Table Input options is documented in the Apache Hop manual, which is the authoritative reference.hop.apache.org →Sample pipeline
The pipeline in the video is transforms/table-input-basic.hpl, in the Putki tutorial samples project.
Transcript
Full transcript
Table Input runs a query and turns its result into rows. That much is obvious. The part worth thinking about is how much of the work you leave to the database, because the choice is yours and the two options perform very differently.
One transform reads, one stands in for whatever comes next. Everything interesting is inside the first one.
Connection names a database connection defined elsewhere, as metadata, rather than here. That is what lets the same pipeline run against a development database and a production one without being edited: the name stays, what it points at changes.
The query joins two tables, filters on a sales figure and sorts the result. All three happen in the database, which is the point: it has indexes and a query planner, and moving eight rows out of it beats moving every row out and then throwing most of them away. Replace variables in script lets a value come from the environment rather than being typed in.
These three turn one query into many. Insert data from transform feeds values from an incoming row into the query, and Execute for each row runs it once per row that arrives. Powerful and easy to regret: a thousand incoming rows means a thousand round trips, where a join would have been one.
Running the pipeline sends the query and reads what comes back.
Six books, already in order, with the author's name resolved from the other table. Nothing in the pipeline sorted or joined anything. The rows arrived like this because the query said so, and that is the habit worth forming.