Joaopolo opened a new issue, #8033:
URL: https://github.com/apache/hop/issues/8033
### What would you like to happen?
Apache Hop version?
2.19.0
Java version?
21
Operating system
Linux
What do I need?
I would like the Database Join transform to support loading its SQL query
from a standard external SQL file, similarly to the functionality available in
the Table Input transform.
Currently, in my ETL process, I generate batches of 250 records and use the
Database Join transform to perform lookups. However, the SQL query needs to be
configured directly inside each transform.
This creates unnecessary duplication because the same lookup logic may need
to be repeated across multiple transformations. If the SQL query needs to be
changed, I must manually update every instance where that query is used.
The requested enhancement would allow the Database Join transform to
reference and load a SQL query from an external .sql file. This would provide:
Reuse of the same SQL query across multiple transformations.
Easier maintenance and updates to lookup logic.
Reduced duplication of SQL code.
Better consistency between transformations.
Similar behavior and flexibility to the existing Table Input transform.
Expected behavior
The Database Join transform should provide an option to load or reference an
external SQL file containing the query to be executed.
The query should still support parameter substitution using the incoming
fields from the transformation, preserving the current behavior of the Database
Join transform.
This would allow a single SQL file to be reused across multiple
transformations while still executing the query with different input values for
each batch or row.
Use case
For example, I process records in batches of 250 and perform database
lookups using the Database Join transform. The same SQL lookup may be required
in multiple transformations.
Instead of duplicating the SQL query in every Database Join configuration, I
should be able to define it once:
${PROJECT_HOME}/sql/lookup_records.sql
And reference this file from multiple Database Join transforms.
This would make maintaining complex ETL pipelines significantly easier,
especially when the same lookup query is reused across several transformations.
<img width="836" height="236" alt="Image"
src="https://github.com/user-attachments/assets/8e816c40-c24b-4f4b-a59a-93d6f9ad795c";
/>
Resulting in the following:
<img width="859" height="251" alt="Image"
src="https://github.com/user-attachments/assets/668f93ef-88f3-4fe1-86ac-9f0c64d9e8c0";
/>
### Issue Priority
Priority: 3
### Issue Component
Component: Pipelines
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
