Skip to main content
Skip to main content
Edit this page

JDBC Driver

clickhouse-jdbc implements the standard JDBC interface using the latest java client. We recommend using the latest java client directly if performance/direct access is critical.

Note

If you're looking for a prior version of the JDBC driver docs, please see here.

Changes from 0.7.x​

In 0.8 we tried to make the driver more strictly follow the JDBC specification, so there are some removed features that may affect you:

Old FeatureNotes
Transaction SupportEarly versions of the driver only simulated transaction support, which could have unexpected results.
Response Column RenamingResultSet was mutable - for efficiency sake they're now read-only
Multi-Statement SQLMulti-statement support was only simulated, now it strictly follows 1:1
Named ParametersNot part of the JDBC spec
Stream-based PreparedStatementEarly version of the driver allowed for non-jdbc usage of PreparedStatement - if you desire such options, we recommend looking at the Java Client and its examples.
Note

Date is stored without timezone, while DateTime is stored with timezone. This can lead to unexpected results if you're not careful.

Environment requirements​

Setup​

<!-- https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc -->
<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.8.0</version>
    <classifier>shaded-all</classifier>    
</dependency>

Configuration​

Driver Class: com.clickhouse.jdbc.ClickHouseDriver

URL Syntax: jdbc:(ch|clickhouse)[:<protocol>]://endpoint1[,endpoint2,...][/<database>][?param1=value1&param2=value2][#tag1,tag2,...], for example:

  • jdbc:clickhouse:http://localhost:8123
  • jdbc:clickhouse:https://localhost:8443?ssl=true

Connection Properties:

Beyond standard JDBC properties, the driver supports the ClickHouse-specific properties offered by the underlying java client. Where possible methods will return an SQLFeatureNotSupportedException if the feature is not supported. Other custom properties include:

PropertyDefaultDescription
disable_frameworks_detectiontrueDisable frameworks detection for User-Agent
jdbc_ignore_unsupported_valuesfalseSuppresses SQLFeatureNotSupportedException
clickhouse.jdbc.v1falseUse older JDBC implementation instead of new JDBC
default_query_settingsnullAllows passing of default query settings with query operations

Supported data types​

JDBC Driver supports the same data formats as the underlying java client.

Handling Dates, Times, and Timezones​

java.sql.Date, java.sql.Time, and java.sql.Timestamp can complicate how Timezones are calculated - though they're of course supported, you may want to consider using the java.time package. ZonedDateTime and OffsetDateTime are both great replacements for java.sql.Timestamp, java.sql.Date, and java.sql.Time.

Creating Connection​

String url = "jdbc:ch://my-server:8123/system";

Properties properties = new Properties();
DataSource dataSource = new DataSource(url, properties);//DataSource or DriverManager are the main entry points
try (Connection conn = dataSource.getConnection()) {
... // do something with the connection

Supplying Credentials and Settings​

String url = "jdbc:ch://localhost:8123?jdbc_ignore_unsupported_values=true&socket_timeout=10";

Properties info = new Properties();
info.put("user", "default");
info.put("password", "password");
info.put("database", "some_db");

//Creating a connection with DataSource
DataSource dataSource = new DataSource(url, info);
try (Connection conn = dataSource.getConnection()) {
... // do something with the connection
}

//Alternate approach using the DriverManager
try (Connection conn = DriverManager.getConnection(url, info)) {
... // do something with the connection
}

Simple Statement​


try (Connection conn = dataSource.getConnection(...);
    Statement stmt = conn.createStatement()) {
    ResultSet rs = stmt.executeQuery("select * from numbers(50000)");
    while(rs.next()) {
        // ...
    }
}

Insert​

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO mytable VALUES (?, ?)")) {
    ps.setString(1, "test"); // id
    ps.setObject(2, LocalDateTime.now()); // timestamp
    ps.addBatch();
    ...
    ps.executeBatch(); // stream everything on-hand into ClickHouse
}

HikariCP​

// connection pooling won't help much in terms of performance,
// because the underlying implementation has its own pool.
// for example: HttpURLConnection has a pool for sockets
HikariConfig poolConfig = new HikariConfig();
poolConfig.setConnectionTimeout(5000L);
poolConfig.setMaximumPoolSize(20);
poolConfig.setMaxLifetime(300_000L);
poolConfig.setDataSource(new ClickHouseDataSource(url, properties));

try (HikariDataSource ds = new HikariDataSource(poolConfig);
     Connection conn = ds.getConnection();
     Statement s = conn.createStatement();
     ResultSet rs = s.executeQuery("SELECT * FROM system.numbers LIMIT 3")) {
    while (rs.next()) {
        // handle row
        log.info("Integer: {}, String: {}", rs.getInt(1), rs.getString(1));//Same column but different types
    }
}

More Information​

For more information, see our GitHub repository and Java Client documentation.

Troubleshooting​

Logging​

The driver uses slf4j for logging, and will use the first available implementation on the classpath.

Resolving JDBC Timeout on Large Inserts​

When performing large inserts in ClickHouse with long execution times, you may encounter JDBC timeout errors like:

Caused by: java.sql.SQLException: Read timed out, server myHostname [uri=https://hostname.aws.clickhouse.cloud:8443]

These errors can disrupt the data insertion process and affect system stability. To address this issue you may need to adjust a few timeout settings in the client's OS.

Mac OS​

On Mac OS, the following settings can be adjusted to resolve the issue:

  • net.inet.tcp.keepidle: 60000
  • net.inet.tcp.keepintvl: 45000
  • net.inet.tcp.keepinit: 45000
  • net.inet.tcp.keepcnt: 8
  • net.inet.tcp.always_keepalive: 1

Linux​

On Linux, the equivalent settings alone may not resolve the issue. Additional steps are required due to the differences in how Linux handles socket keep-alive settings. Follow these steps:

  1. Adjust the following Linux kernel parameters in /etc/sysctl.conf or a related configuration file:

    • net.inet.tcp.keepidle: 60000
    • net.inet.tcp.keepintvl: 45000
    • net.inet.tcp.keepinit: 45000
    • net.inet.tcp.keepcnt: 8
    • net.inet.tcp.always_keepalive: 1
    • net.ipv4.tcp_keepalive_intvl: 75
    • net.ipv4.tcp_keepalive_probes: 9
    • net.ipv4.tcp_keepalive_time: 60 (You may consider lowering this value from the default 300 seconds)

  2. After modifying the kernel parameters, apply the changes by running the following command:

sudo sysctl -p

After Setting those settings, you need to ensure that your client enables the Keep Alive option on the socket:

properties.setProperty("socket_keepalive", "true");