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 Feature	Notes
Transaction Support	Early versions of the driver only simulated transaction support, which could have unexpected results.
Response Column Renaming	ResultSet was mutable - for efficiency sake they're now read-only
Multi-Statement SQL	Multi-statement support was only simulated, now it strictly follows 1:1
Named Parameters	Not part of the JDBC spec
Stream-based PreparedStatement	Early 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

• OpenJDK version >= 8

Setup

Maven

Gradle (Kotlin)

Gradle

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:

Property	Default	Description
disable_frameworks_detection	true	Disable frameworks detection for User-Agent
jdbc_ignore_unsupported_values	false	Suppresses SQLFeatureNotSupportedException
clickhouse.jdbc.v1	false	Use older JDBC implementation instead of new JDBC
default_query_settings	null	Allows 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

Supplying Credentials and Settings

Simple Statement

Insert

HikariCP

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:

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:

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