I propose adding DuckDB as the underlying database engine in the Inline Querier transformer for the following reasons:
1. Performance Benefits:
- Columnar Storage: DuckDB’s columnar storage model allows for much faster query execution, particularly on large datasets, as it optimises for analytical queries. This would result in quicker workflows, especially for FME users dealing with substantial data volumes.
- Vectorised Execution: DuckDB uses a vectorised execution model that can process chunks of data more efficiently than the row-wise approach used in SQLite. This would significantly enhance performance in complex querying scenarios.
2. Better Handling of Analytical Workloads:
- DuckDB is designed with analytical workloads in mind, making it more suitable for data processing tasks typically performed in FME. This switch would provide users with more efficient query processing when handling large geospatial datasets.
3. Increased Flexibility with Data Types:
- DuckDB offers more advanced data types and functions, which would extend the flexibility of the Inline Querier transformer. This would enable more sophisticated transformations and analytics directly within FME, reducing the need for external tools.
4. Seamless Integration:
- SQL Syntax Compatibility: DuckDB supports standard SQL queries similar to SQLite, ensuring a relatively smooth transition with minimal changes to existing workflows. Users will benefit from enhanced performance without needing to extensively rework their SQL queries.
5. Broader Ecosystem Support:
- DuckDB has strong integrations with modern data ecosystems (e.g., Python, R, Parquet), which can open doors for future FME integrations and expand the functionality of the Inline Querier.
6. Open-Source, Actively Developed:
- Like SQLite, DuckDB is open-source but has a much more active development roadmap focused on big data analytics. This would future-proof the Inline Querier transformer by providing new features and optimisations more regularly.
Finally the ability to create macros in duckdb sql and refer to these inline could be very useful indeed, also the native spatial support would really help!
