ekas-7 opened a new issue, #871:
URL: https://github.com/apache/wayang/issues/871
### Description
Wayang supports several JDBC-backed platforms (PostgreSQL, SQLite3, generic
JDBC, Trino, Presto, BigQuery) through `wayang-jdbc-template`, but has no
ClickHouse platform. ClickHouse is a widely used column-oriented OLAP engine,
and Wayang's SQL pushdown (filter, projection, join, aggregation, sort, table
sink) suits it well.
I propose adding a `wayang-platforms/wayang-clickhouse` module that follows
the structure of `wayang-trino` / `wayang-presto` / `wayang-bigquery` (all
seven template operators, README, integration tests), rather than the smaller
`wayang-postgres` module.
### Proposed module layout
| Component | Class / file | Notes |
|---|---|---|
| Entry point | `ClickHouse.java` | `plugin()`, `conversionPlugin()`,
`platform()` |
| Platform | `ClickHousePlatform extends JdbcPlatformTemplate` | name
`ClickHouse`, config id `clickhouse`, official `com.clickhouse:clickhouse-jdbc`
driver (Apache 2.0) |
| Operators |
`ClickHouse{TableSource,Filter,Projection,Join,GlobalReduce,ReduceBy,Sort,TableSink}Operator`
| extend the existing `Jdbc*` base classes |
| Mappings | one per operator + `Mappings.ALL` | same as Trino |
| Channels | `ChannelConversions` | SQL -> Java stream, SQL -> Spark RDD |
| Plugins | `ClickHousePlugin`, `ClickHouseConversionsPlugin` | used via
`.withPlugin(ClickHouse.plugin())` |
| Config | `wayang-clickhouse-defaults.properties` |
`wayang.clickhouse.jdbc.{url,user,password}`, cost parameters |
| Build | `wayang-platforms/pom.xml` | new `<module>` |
### ClickHouse-specific points
The generic SQL built in `JdbcExecutor.createSqlString` (`SELECT ... FROM
... JOIN ... ON ... WHERE ... GROUP BY ... ORDER BY`, no trailing `;`) is valid
ClickHouse SQL, so no changes to `wayang-jdbc-template` are planned. The
dialect-specific work is:
1. **Table sink engine.** `JdbcTableSinkOperator` emits `CREATE TABLE <t> AS
<select>` in `overwrite` mode. ClickHouse needs an engine for this, e.g.
`CREATE TABLE <t> ENGINE = MergeTree ORDER BY tuple() AS SELECT ...`. Since the
engine clause must come before `AS`, `ClickHouseTableSinkOperator` would
override `createSqlClause`, with a configurable engine (e.g.
`wayang.clickhouse.tablesink.engine`, default `MergeTree ORDER BY tuple()`).
`INSERT INTO <t> SELECT ...` and `DROP TABLE IF EXISTS` work unchanged.
2. **Identifier quoting.** `SqlIdentifierUtils.escapeAndQuoteIdentifier`
reads the quote character and case folding from `DatabaseMetaData`. ClickHouse
identifiers are case-sensitive; tests will cover `database.table` names.
3. **Type mapping.** `SqlToStreamOperator` uses `ResultSet.getObject`, so
ClickHouse types such as `UInt64` (`BigInteger`), `Date`/`DateTime`,
`Nullable(T)` and `LowCardinality(String)` appear as-is in `Record`s. I will
document the mapping and test the common types.
4. **Joins and NULLs.** ClickHouse builds the hash table from the right side
of a join, and columns are non-nullable by default (`join_use_nulls = 0`).
Behaviour will be documented; no changes to the join SQL are planned.
### Testing plan
- Unit tests for the generated ClickHouse SQL (sink with engine clause,
quoting) that need no server.
- `ClickHouseOperatorsIT` modelled on `TrinoOperatorsIT`: covers all seven
operators, creates and drops its own fixture tables, and is skipped (not
failed) when no server is reachable (`CLICKHOUSE_HOST` / `CLICKHOUSE_PORT`).
- No docker-compose or setup folders in the source tree, per #796.
### Documentation and examples
- `wayang-platforms/wayang-clickhouse/README.md`
- `ClickHouseDemo` in `wayang-applications` plus
`wayang-applications/clickhouse.md`
- Add ClickHouse to the platform list in
`wayang-docs/src/main/resources/index.md`
### Out of scope (possible follow-ups)
- Measured cost-model parameters (initial values will be uncalibrated, as in
`wayang-postgres`); a `ClickHouseCostPilotIT` following
`guides/cost-profiling.md` can follow.
- Registering ClickHouse in `SqlContext`, `JsonPlanBuilder`, or
`MultiContextDeserializer`, which currently hard-code Postgres/SQLite.
- Native (non-JDBC) protocol and ClickHouse-specific functions beyond
pass-through SQL predicates.
### Proposed PR sequence
To keep reviews small, I'd split this into sequential PRs. Each one builds
and passes tests on its own.
| PR | Scope | Depends on |
|---|---|---|
| 1 | Module skeleton: `pom.xml`, `ClickHousePlatform`, `ClickHouse` entry
point, `ClickHouseExecutionOperator`, `ClickHouseTableSource`, channel
conversions, plugins, defaults properties, filter + projection mappings,
SQL-generation unit tests, register in `wayang-platforms/pom.xml` | - |
| 2 | Join, GlobalReduce, ReduceBy and Sort operators + mappings,
`ClickHouseOperatorsIT` (skipped without a server) | PR 1 |
| 3 | `ClickHouseTableSinkOperator` with configurable `ENGINE` clause, sink
tests | PR 1 |
| 4 | Module README, `ClickHouseDemo` + `clickhouse.md` in
`wayang-applications`, docs index entry | PRs 1-3 |
| 5 (optional) | Cost calibration via `ClickHouseCostPilotIT` and measured
defaults | PRs 1-3 |
PRs that depend on an earlier one will be opened as drafts marked "Depends
on #X" and rebased after it merges.
### Questions for maintainers
1. Is the Trino/Presto/BigQuery module structure the preferred reference for
a new SQL platform?
2. Is a configurable sink engine acceptable, or should the sink only support
`INSERT INTO` into pre-created tables?
3. Are integration tests that skip when no server is available acceptable
for CI, or is a Testcontainers-based test preferred?
I'd like to work on this and will open a PR once the approach is agreed.
--
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]