fpapon opened a new issue, #8907: URL: https://github.com/apache/hop/issues/8907
### What would you like to happen? ## Problem Statement The current Apache Hop **Formula** transform relies on Apache POI to evaluate Excel-like formulas. While this implementation provides extensive formula support, it introduces significant performance overhead when processing large datasets. Apache POI is primarily designed for spreadsheet processing rather than high-throughput, row-by-row expression evaluation in ETL pipelines. This can lead to several performance concerns: - High CPU and memory overhead during formula evaluation. - Unnecessary spreadsheet-related object allocations. - Increased garbage collection pressure. - Reduced throughput when processing millions of rows. - Performance degradation when multiple formulas are evaluated for each row. In production ETL workloads, the Formula transform can become a significant performance bottleneck. ## Proposed Solution Introduce a new **OpenFormula transform** based on a lightweight expression evaluation engine that does not rely on Apache POI. The new transform should implement the OpenFormula standard, providing formula evaluation capabilities specifically optimized for Apache Hop's row-based processing model. The objective is to offer a high-performance alternative to the existing Formula transform while preserving a familiar formula syntax and configuration experience. ### Functional Requirements - Support OpenFormula expressions and commonly used functions. - Allow formulas to reference incoming pipeline fields. - Support numeric, string, boolean, date, and time operations. - Support conditional expressions and nested functions. - Support the creation of new fields and replacement of existing field values. - Provide proper handling of null values, data types, and evaluation errors. - Integrate with the existing Apache Hop GUI and metadata model. - Document compatibility differences with the existing Formula transform. ### Performance Requirements The implementation should: - Avoid Apache POI dependencies in the formula evaluation path. - Parse and compile expressions once during transform initialization whenever possible. - Reuse compiled expressions across incoming rows. - Minimize per-row memory allocations. - Avoid unnecessary intermediate objects. - Support efficient execution with multiple transform copies. - Provide predictable memory consumption for large datasets. ## Backward Compatibility The existing Formula transform should remain available to preserve compatibility with existing pipelines. The new OpenFormula transform should be introduced as an alternative implementation, allowing users to migrate progressively. Ideally, a migration mechanism could help convert existing Formula transform configurations when their expressions are compatible with the new engine. ## Testing and Benchmarking Performance improvements should be validated through reproducible benchmarks comparing the existing Formula transform and the proposed OpenFormula transform. Functional tests should also verify the accuracy of the evaluation results and document differences between OpenFormula and Apache POI semantics. ## Related Work There are already ongoing discussions and optimization efforts around the current Formula transform: - https://github.com/apache/hop/pull/8304 — Formula optimization. These initiatives could provide a starting point for this feature. The main distinction of this proposal is to provide a **dedicated, fully Apache POI-independent OpenFormula transform**, rather than relying on optimization paths that still fall back to Apache POI. Any reused library or implementation should also be compatible with Apache Hop's licensing requirements. ## Expected Benefits - Significantly improved performance for formula-intensive ETL pipelines. - Reduced CPU and memory consumption. - Better scalability for large datasets. - An expression evaluation engine better suited to Apache Hop's streaming architecture. - A clear separation between Excel-compatible formula evaluation and OpenFormula-based processing. The ultimate goal is to make formula evaluation efficient enough to be used extensively in production ETL pipelines without becoming a major performance bottleneck. ### Issue Priority Priority: 3 ### Issue Component Component: Transforms -- 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]
