Jooq: Add support for Clickhouse

[dimiii]

ClickHouse is an open source column-oriented database management system capable of real-time generation of analytical data reports using SQL queries.

https://clickhouse.yandex/
https://clickhouse.yandex/docs/en/query_language/queries/

It has a JDBC driver, FYI: https://github.com/yandex/clickhouse-jdbc

[lukaseder]

Thank you very much for your suggestion.

Right now, ClickHouse doesn't seem to have gained a lot of traction, so the cost/benefit ratio for an integration on our side is not favourable for going forward. We'll keep it in the back of our heads, though.

[dimiii]

is not favourable for going forward

So, there is no point in PR? Do you think it is going to be too difficult for a beginner from the side?
I thought I could manage it taking work on Vertica as an exemplar.

[lukaseder]

So, there is no point in PR?

Thanks for asking first :) We wouldn't want to maintain the integration right now. 20% of the work is adding support for a new dialect. 80% (or even more) is maintaining it in the future.

Do you think it is going to be too difficult for a beginner from the side? I thought I could manage it taking work on Vertica as an exemplar.

It's quite a bit of work (just added support for 4 new dialects over the past weeks), definitely possible, but:

1. Reviewing would be a tremendous amount of work for little gain in this case
2. You don't have the integration tests (they're closed source), so it's not really easy to judge whether your suggestions will be correct, or whether you're breaking anything for the other 25 dialects we're currently supporting.

Having said so, if you think an integration will add value to your business, you are more than welcome to fork jOOQ and maintain the integration on your side. I'll be happy to answer any questions you may have on the user group: https://groups.google.com/forum/#!forum/jooq-user

[lukaseder]

Looking into this, next for a jOOQ 3.15 integration.

[lukaseder]

Known issues will be collected here: https://github.com/jOOQ/jOOQ/issues/10792

[lukaseder]

A few moments into trying to add support, and there are quite the significant issues already, making me question adoption. E.g. the database has no constraints, nor UPDATE / DELETE statements. Also, there are quite a few deviations from standard SQL in SELECT alone:
https://clickhouse.tech/docs/en/sql-reference/statements/select/

This hints at a significant amount of effort in maintaining the integration tests and assessing which of the many jOOQ API features will not be supported.

Upvoters: What's your stake here / your expectations of having jOOQ support for Clickhouse?

[lukaseder]

I'll stop here, for now. I'm happy to discuss a sponsored project. Anyone interested, please reach out to [email protected].

[PHameete]

Came here hoping to get lucky ;-) unfortunately not. I think ClickHouse is gaining popularity for analytical workloads. In my experience it is very fast, and can be a good option when you outgrow your single-node setup, or when you can benefit from reading data in parallel from multiple nodes.

Currently, the only way to query ClickHouse from Java is via JDBC. My hope was to be able to use JOOQ to write type-safe queries and to compose them programmatically. The alternative is generating SQL strings and putting them in prepared statements which is not great.

I'm not sure if this was already the case when you last checked their docs, but UPDATE and DELETE is supported in a non-standard way. See https://clickhouse.tech/docs/en/sql-reference/statements/alter/update/ and https://clickhouse.tech/docs/en/sql-reference/statements/alter/delete/

[lukaseder]

Thanks for your comment, @PHameete. After a few hours attempting to build a Clickhouse integration, I think it was safe to say that everything Clickhouse is pretty non-standard "SQL", as in: It looks kinda SQL-ish, but really isn't SQL. This makes an integration rather effortful. It could be done, sure, but cost/benefit for us as vendors is currently not very good - at least not under the assumption that we continue offering OSS dialects under our own OSS license.

We could break with that, of course, and offer commercial only support for Clickhouse. I'll review this option again for jOOQ 3.15

[PHameete]

Yes I can imagine that will be a big effort... I think jOOQ support would be great to have as a ClickHouse user, given that there are no alternatives to plain JDBC. Thanks for taking your time to consider Lukas :-)

[lukaseder]

Alright. Well, there's still some momentum in that area, given that we'll be adding support for at least BigQuery, Ignite, and Snowflake as well in jOOQ 3.15...

[lukaseder]

I'm afraid, I'll have to keep this in jOOQ 3.16 unless we can find budget from a sponsored project for this. (Coincidentally, a customer is going to sponsor Exasol support, so that will have priority)