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]

Reply via email to