[
https://issues.apache.org/jira/browse/CAMEL-24255?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Omar Atie updated CAMEL-24255:
------------------------------
Description:
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.
was:Would be nice with duckdb as a component and test infra so we can have
in-process databases and integrate with this database
> 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
> 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)
