Please support Clickhouse

[cameronbraid]

Support for https://clickhouse.yandex/ would be a great addition.

[paveltiunov]

@cameronbraid Hey Cameron! Thanks for posting this!

Do you have production Clickhouse instance? Do you think you can spearhead Clickhouse driver development if we can help you?

Thanks!

[cameronbraid]

I am evaluating both clickhouse and cube.js

So I may not be in the best position to do that.

Though, if I find both of these technologies address our needs I am sure willing to help.

[paveltiunov]

@cameronbraid Hey Cameron! Sounds great! In case you can find some time for that here's driver implementation guide: https://github.com/statsbotco/cube.js/blob/master/CONTRIBUTING.md#implementing-driver

[inikolaev]

@paveltiunov I'm also looking at ClickHouse + Cube.js and could take a look into implementing a driver.

[inikolaev]

I've started some initial work on the driver here: https://github.com/inikolaev/cube.js/tree/clickhouse-driver

I'm using https://github.com/apla/node-clickhouse library to make calls to ClickHouse which uses HTTP interface. I haven't yet tested it though.

[paveltiunov]

@inikolaev Hey Igor! That's great! Please let me know how it goes and if you have any questions regarding implementation. Please don't hesitate to contact me directly in our Slack. Thanks!

[cameronbraid]

@inikolaev That's a great start, some things to consider :

Is a pool needed ? Since the protocol is http based isn't it sufficient to share the same ClickHouse instance ?

For query cancelling:

• the clickhouse http api allows you to specify a query_id query string parameter which can then be killed via `KILL QUERY WHERE query_id = ${query_id}`
• you can pass this via the clickhouse api as a second argument to the querying function, for example { queryOptions : { query_id: getNextQueryId() } . and could implement getNextQueryId within the driver to return a GUID prefixed with cubejs for example

[cameronbraid]

@paveltiunov are there any existing tests to test driver implementations, or query implementations ? I did a quick scan of the codebase but couldn't find anything.

[cameronbraid]

I've just submitted a PR to @inikolaev which includes a fix to the query to return array of object, as well as a docker-compose based setup of clickhouse and a test file to verify the driver implementation

inikolaev#1

I'm taking a look at doing a ClickHouseQuery class next

[inikolaev]

@inikolaev That's a great start, some things to consider :

Is a pool needed ? Since the protocol is http based isn't it sufficient to share the same ClickHouse instance ?

I wasn't sure whether Node`s http/https modules do any connection pooling on their own.

For query cancelling:

• the clickhouse http api allows you to specify a query_id query string parameter which can then be killed via `KILL QUERY WHERE query_id = ${query_id}`
• you can pass this via the clickhouse api as a second argument to the querying function, for example { queryOptions : { query_id: getNextQueryId() } . and could implement getNextQueryId within the driver to return a GUID prefixed with cubejs for example

Good idea! I was following the MySQL driver which seems to simply cancel all queries running for too long. @paveltiunov what is the intended behaviour? The implementation for Postgres seems to be cancelling the currently running query.

[cameronbraid]

@inikolaev It looks like the clickhouse driver only supports raw sql strings, so parameter substitution is going to have to be done to support parameterised query calls like driver.query("select ?", [1])

[cameronbraid]

@inikolaev I have pushed a change to my branch which starts on the implementation of a ClickHouseQuery class see packages/cubejs-schema-compiler/adapter/ClickHouseQuery.js

to test, run docker-compose up in the packages/cubejs-schema-compiler folder (will convert to test containers at some point) then run

yarn test --grep DataSchemaCompiler which works apart from the 'export import test' which I skipped

yarn test --grep "simple join" which doesn't work due to ClickHouse not supporting OR in join expressions - this is an interesting challenge.

error is :

Error: Invalid expression for JOIN ON. Expected equals expression, got (`visitors.created_at_date` = q_1.`visitors.created_at_date`) OR (isNull(`visitors.created_at_date`) AND isNull(q_1.`visitors.created_at_date`)). 
Supported syntax: JOIN ON Expr([table.]column, ...) = Expr([table.]column, ...) [AND Expr([table.]column, ...) = Expr([table.]column, ...) ...] (version 19.5.2.6 (official build))

I edited packages/cubejs-schema-compiler/test/DataSchemaCompilerTest.js and packages/cubejs-schema-compiler/test/GraphBuilderTest.js to use a new ClickHouseDbRunner instead of the DbRunner which is based on postgresql. Ideally the test suite will run both versions, but this is more a quick way of testing which parts of ClickHouse work and which don't

[paveltiunov]

Hey guys! Great team play here! Well done! 👍
@cameronbraid There's no universal test suite for drivers but the idea is great! I think worth adding an issue where we can discuss it.
@inikolaev Yeah. MySQL cancel implementation is a little bit awkward. Should use CONNECTION_ID() instead of killing all the problematic processes. If you can obtain some query/process id on or before query execution and then kill it by id that's ideal implementation.