Clickhouse: Why not use JDBC native for the offical support?

[ggservice007]

The Http version JDBC is often bad performance.

For example, sometimes some query run normally, but the same query run abnormally on undefined time.

Why not use native JDBC?
https://github.com/housepower/ClickHouse-Native-JDBC.

[filimonov]

Can you provide some samples when Native 'beats' HTTP? With Native the client need to convert columns to rows by it's own, and it's hard to do that faster than Clickhouse itself. At the same time overhead of HTTP itself is minimal. Native gives better protocol features, i.e. you can exchange some extra metainformation with server, but performance should be quite the same.

[den-crane]

We have no problems with official JDBC(http).
It's superfast and super stable.
In one project (JDBC(http)) we run about 1 mil. queries every day (over Europe - latency 60ms). Half of queries retrieve more that 500 thousands rows and >10 GB each (huge jsons in column).
Try to tune timeouts i.e. socket_timeout.

[alex-zaitsev]

In our benchmarks native driver above (ClickHouse-Native-JDBC) is significantly slower than official HTTP JDBC when querying data. See this issue: https://github.com/housepower/ClickHouse-Native-JDBC/issues/70

[ggservice007]

<dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient</artifactId>
            <version>4.5.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpmime</artifactId>
            <version>4.5.2</version>
</dependency>

@den-crane
OkHttp is better ?

https://square.github.io/okhttp/

[ggservice007]

@den-crane @filimonov
Any idea the HTTP JDBC with HikariCP.

光 HikariCP・A solid, high-performance, JDBC connection pool at last.

https://github.com/brettwooldridge/HikariCP

maximumPoolSize=100
minimumIdle=50
validationTimeout=5000
idleTimeout=60000
maxLifetime=120000
connectionTestQuery=SELECT 1
readOnly=false

Sometime the SQL can not work normally.

[filimonov]

@ggservice007 What exactly is the problem?

Do you have any real (performance) issues with official https://github.com/yandex/clickhouse-jdbc driver? Otherwise, it sounds like attempts to fix the things that are not broken / make some premature optimizations.

In some usage scenarios, connection pools can be very useful. You can use http://commons.apache.org/proper/commons-dbcp/ or any other connection pool depending on your preferences.

Sometime the SQL can not work normally.

What does it mean? Do you get some exceptions? Timeouts? Wrong results?

[ggservice007]

Why a lot of handlers?

[ggservice007]

@filimonov
I use mybatis. My program almost run normally. But sometime error occurs.

The error occurred while setting parameters
SQL: select * from some_table where one = ?
exception, code: 1002

[filimonov]

exception, code: 1002

Those issues may be related:
https://github.com/yandex/clickhouse-jdbc/issues/122
https://github.com/yandex/clickhouse-jdbc/issues/174

[alex-krash]

We've faced performance issues with official driver, when working with Date/DateTime fields - it uses parsing of incoming string into object. Issue was solved, when support for RowBinary read was implemented - https://github.com/yandex/clickhouse-jdbc/blob/a86eb8a65c25788f767e51a619f62073dbbfeddb/src/main/java/ru/yandex/clickhouse/ClickHouseStatement.java#L25
It does not work "out-of-the-box", e.g. it extends JDBC API, but it is blazing fast.

[blinkov]

At the moment "official" JDBC driver is supported by Yandex.Metrica team who develops it and uses it in production. This is the main reason for it's status, performance has nothing to do with it.

Other users are free to choose whatever implementation better fits their needs, this might include performance of select/insert queries into consideration.