[jira] [Comment Edited] (CAMEL-24255) camel-duckdb - A component for duckdb and test-infra

["Omar Atie (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CAMEL-24255?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18098983#comment-18098983 ] 

Omar Atie edited comment on CAMEL-24255 at 7/25/26 1:21 AM:
------------------------------------------------------------

I will work on it [~davsclaus] if that's ok ? PR attached 


was (Author: JIRAUSER313980):
I will work on it [~davsclaus] if that's ok ?

> camel-duckdb - A component for duckdb and test-infra
> ----------------------------------------------------
>
>                 Key: CAMEL-24255
>                 URL: https://issues.apache.org/jira/browse/CAMEL-24255
>             Project: Camel
>          Issue Type: New Feature
>            Reporter: Claus Ibsen
>            Assignee: Omar Atie
>            Priority: Major
>             Fix For: 4.x
>
>
> Would be nice with duckdb as a component and test infra so we can have in-process databases and integrate with this database
> following [~davsclaus] requirements above : I'd like to propose a new *camel-duckdb* component and a matching *camel-test-infra-duckdb* module for integrating with [DuckDB|https://duckdb.org/], the in-process analytical SQL database.
> Camel can reach DuckDB today through *camel-jdbc* or *camel-sql* with the official JDBC driver (\{{org.duckdb:duckdb_jdbc}}), but that path treats DuckDB like any other JDBC source: generic \{{PreparedStatement}} usage, no first-class support for DuckDB’s strengths (embedded file/memory databases, fast bulk load via \{{COPY}}, reading Parquet/CSV/JSON as tables, \{{Appender}}-style batch ingest, and \{{ATTACH}}/extensions). Teams building local analytics, CI-friendly pipelines, and “drop a file → query in SQL” flows end up wiring DataSources and SQL strings by hand.
> The idea is a dedicated component on the DuckDB JDBC driver (and optionally a shared \{{java.sql.Connection}} / \{{javax.sql.DataSource}} bean) that exposes embedded analytics workflows as idiomatic endpoint options, plus test infrastructure that runs *in-process* (memory or temp file) so component and IT tests do not require Docker by default.
>   duckdb:analytics/events?operation=insert&table=events&batchSize=1000
> This follows the same product pattern as *camel-clickhouse* (vendor-focused component + \{{camel-test-infra-*}}) rather than stretching *camel-jdbc* with DuckDB-specific URI semantics.
> h2. *Why a dedicated component (vs camel-jdbc / camel-sql)*
>   - *Embedded-first*: first-class \{{databasePath}} (file, \{{:memory:}}, or directory) instead of opaque JDBC URLs in every route.
>   - *Bulk ingest*: \{{COPY ... FROM}} / read functions for CSV, Parquet, JSON — route bodies and \{{camel-file}} drops map naturally to DuckDB load patterns.
>   - *Batch append*: use DuckDB’s JDBC \{{Appender}} (where applicable) for high-throughput inserts from \{{List<Map>}} / POJOs without row-by-row \{{INSERT}}.
>   - *Analytics SQL*: \{{read_parquet()}}, \{{read_csv()}}, \{{read_json()}} in \{{operation=query}} for federated “query files in place” pipelines.
>   - *Testability*: \{{camel-test-infra-duckdb}} provides a shared in-process database for unit and integration tests (no container required for the default profile), with optional remote JDBC URL for CI that prefers an external instance.
> h2. *Design*
>   - *Producer-first* (initial scope): DuckDB is not a message broker; polling consumers remain better served by *camel-sql* if needed. Phase 1 focuses on producer operations; a poll-based consumer can be a follow-up if there is demand.
>   - *Operations (initial):* \{{execute}} (DDL/DML, no result set required), \{{query}} (SQL → body as \{{List<Map>}} or JSON string), \{{insert}} (structured batch into a table), \{{copy}} (bulk load from file path or stream in body), \{{ping}} (connectivity / \{{SELECT 1}}).
>   - *Connection model:* autowire a shared \{{Connection}} or \{{DataSource}} bean, *or* configure \{{databasePath}} / \{{jdbcUrl}} on the component or endpoint. One embedded database per Camel context should be documented to avoid accidental multi-writer issues on the same file.
>   - *Body types:* for \{{insert}} — \{{List<Map<String,Object>>}}, \{{List<POJO>}}, or JSON array string; for \{{copy}} — \{{java.io.File}}, path \{{String}}, or \{{InputStream}} with \{{format}} (CSV, Parquet, JSON); for \{{query}} — SQL string in body or \{{query}} URI option.
>   - *Tests:* \{{camel-test-infra-duckdb}} with \{{DuckDBServiceFactory.createService()}} (in-memory + temp-file implementations); component unit tests without infra; optional IT module using the infra service. AssertJ in new tests per project convention.
>   - *Docs:* component page under \{{components/camel-duckdb}}, catalog JSON, upgrade-guide entry on \{{main}} for the target 4.x release.
> h2. *Use cases*
> {*}Use Case 1: In-process analytics in integration tests and JBang routes\{*}
> Use a file-backed or memory DuckDB for fast local SQL without external services.
> {code:java}
> from("timer:tick?period=5000")
>     .setBody(constant("INSERT INTO metrics VALUES (current_timestamp, 42)"))
>     .to("duckdb:metrics.db?operation=execute");
> {code}
> {*}Use Case 2: Ingest Parquet/CSV dropped by camel-file\{*}
> Load landed files with DuckDB native read/copy instead of parsing in Java.
> {code:java}
> from("file:landing/parquet?include=.*\\.parquet")
>     .to("duckdb:warehouse.db?operation=copy&table=staging_events&format=parquet")
>     .to("duckdb:warehouse.db?operation=query")
>         .constant("INSERT INTO events SELECT * FROM staging_events")
>     .log("Loaded ${header.CamelFileName}");
> {code}
> {*}Use Case 3: Kafka → batch insert into embedded DuckDB\{*}
> Buffer events and append batches for dashboarding or downstream export.
> {code:java}
> from("kafka:events?groupId=local-analytics")
>     .unmarshal().json(JsonArray.class)
>     .aggregate(constant(true), new GroupedBodyAggregationStrategy())
>         .completionSize(500).completionTimeout(2000)
>     .to("duckdb:analytics.db/events?operation=insert&table=events&batchSize=500");
> {code}
> {*}Use Case 4: Federated query over files (read_parquet / read_csv)\{*}
> Run analytics SQL over files without importing them into a permanent table first.
> {code:java}
> from("direct:report")
>     .setBody(constant(
>         "SELECT region, count(*) AS n FROM read_parquet('data/**/*.parquet') GROUP BY region"))
>     .to("duckdb::memory:?operation=query&resultFormat=JSON")
>     .to("platform-http:proxy/report");
> {code}
> {*}Use Case 5: ETL staging — land JSON, merge into curated tables\{*}
> Combine \{{execute}} and \{{query}} for lightweight ETL in a single embedded DB.
> {code:java}
> from("direct:stage")
>     .to("duckdb:etl.db?operation=copy&table=raw_orders&format=JSON")
>     .setBody(constant(
>         "INSERT INTO orders SELECT * FROM raw_orders WHERE id IS NOT NULL"))
>     .to("duckdb:etl.db?operation=execute");
> {code}
> {*}Use Case 6: Health / readiness for routes using embedded DuckDB\{*}
> Verify the database file is open and writable before starting heavy processing.
> {code:java}
> from("timer:health?period=30000")
>     .to("duckdb:app.db?operation=ping")
>     .choice()
>         .when(header("CamelDuckDbPingOk").isEqualTo(true))
>             .log("DuckDB OK")
>         .otherwise()
>             .to("direct:alert")
>     .end();
> {code}
> {*}Use Case 7: Shared test-infra for other components\{*}
> Other modules’ ITs can depend on \{{camel-test-infra-duckdb}} for a consistent embedded database (similar to \{{camel-test-infra-clickhouse}} for ClickHouse).
> {code:java}
> @RegisterExtension
> static DuckDBService db = DuckDBServiceFactory.createService();
> {code}
> h2. *Proposed URI shape and options (initial)*
>   - *Scheme:* \{{duckdb:databasePath}} — file path (e.g. \{{analytics.db}}), \{{:memory:}}, or empty for default in-memory; optional path segment for default schema/table naming
>   - \{{jdbcUrl}} — optional full JDBC URL override (\{{jdbc:duckdb:...}})
>   - \{{operation}} — execute | query | insert | copy | ping (default: \{{execute}} for body-as-SQL, or \{{insert}} when \{{table}} is set — exact default TBD in PR)
>   - \{{table}} — target table for \{{insert}} / \{{copy}}
>   - \{{query}} — static SQL for \{{query}} when body is empty
>   - \{{batchSize}} — split list bodies for \{{insert}}
>   - \{{format}} — csv | parquet | json | auto (for \{{copy}} and some \{{insert}} paths)
>   - \{{readOnly}} — open embedded database read-only where supported
>   - \{{resultFormat}} — for \{{query}}: \{{ListMap}} (default) | \{{JSON}} | \{{Stream}}
>   - \{{username}} / \{{password}} — only when using remote DuckDB via extension/wire protocol (future; mark \{{secret}} on password)
> h2. *Proposed message headers*
>   - \{{CamelDuckDbOperation}} — override endpoint operation
>   - \{{CamelDuckDbDatabasePath}} — override database location for this message
>   - \{{CamelDuckDbTable}} — override target table
>   - \{{CamelDuckDbQuery}} — override SQL for \{{query}} / \{{execute}}
>   - \{{CamelDuckDbRowsWritten}} — (out) rows affected / inserted where available
>   - \{{CamelDuckDbPingOk}} — (out) boolean from \{{ping}}
> h2. *Test infrastructure (camel-test-infra-duckdb)*
>   - \{{DuckDBService}} — lifecycle (start/stop), JDBC URL, optional temp directory cleanup
>   - \{{DuckDBLocalEmbeddedService}} — \{{:memory:}} or temp \{{*.db}} file (default for local/CI)
>   - \{{DuckDBServiceFactory}} — \{{SimpleTestServiceBuilder}} pattern aligned with \{{camel-test-infra-clickhouse}}
>   - \{{RemoteDuckDBInfraService}} — optional JDBC URL from environment for contributors who run a external DuckDB instance (document in README; no Docker Hub requirement for default tests)
>   - Architectures: embedded driver bundles native libs — document supported platforms and use Maven skip properties for arches without published DuckDB JDBC builds if CI requires it (same approach as other native-backed components)
> h2. *Out of scope (initial PR, can be follow-up tickets)*
>   - Full parity with every DuckDB extension (httpfs, postgres scanner, etc.) — enable via \{{PRAGMA}}/session SQL or documented \{{initScript}} option instead
>   - Replacing *camel-sql* polling consumers for DuckDB
>   - DuckDB *server* mode as a hard dependency for default tests (embedded only first)
> h2. *Deliverables*
>   - Maven module \{{components/camel-duckdb}} (Component, Endpoint, Producer, constants, JSON metadata)
>   - Maven module \{{test-infra/camel-test-infra-duckdb}}
>   - Component documentation + user-manual upgrade-guide entry for the target 4.x release
>   - Unit tests + integration tests using test-infra
> I'm happy to implement this following the *camel-clickhouse* / *camel-influxdb2* layout. Feedback welcome on the initial operation set, default connection mode (embedded file vs memory), and whether \{{copy}} should be in v1 or deferred to a second PR.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)