muskanmahajan37/clickhouse4j

Lighter and faster alternative for the official ClickHouse JDBC driver

Clickhouse4j - lighter and faster alternative for the official ClickHouse JDBC driver

The main differences between this and the official driver are:

• Removed Guava, Jackson and Apache Http Client dependencies;
• Smaller size - 850kb vs 5.6mb of the original driver (7x smaller jar size)
• A bunch of micro optimizations were applied (for example, batch inserts are now 40% faster)
• CopyManager added;
• Support for JSON, JSONCompact select;
• Compiled against Java 8 and many other things

Usage

<dependency>
    <groupId>cc.blynk.clickhouse</groupId>
    <artifactId>clickhouse4j</artifactId>
    <version>1.4.4</version>
</dependency>

CopyManager usage

CopyManager is utility class that helps to read / write the queries from / to the file/stream/reader.

Select from DB to File

String query = "SELECT * from copy_manager_test.my_table FORMAT CSVWithNames";
Path outputFile = ...;

try (CopyManager copyManager = CopyManagerFactory.create(dataSource)) {
    copyManager.copyFromDb(query, outputFile);
}
//outputFile now has all the data and headers from the copy_manager_test DB and my_table table

Select from DB to File with prepared statement

try (Connection connection = dataSource.getConnection();
     PreparedStatement ps = connection.prepareStatement(sql);
     CopyManager copyManager = CopyManagerFactory.create(connection)) {
        ps.setLong(1, id);
        copyManager.copyFromDb(ps, outputStream);
}

Insert from File to DB

String query = "INSERT INTO copy_manager_test.my_table FORMAT CSV";
Path inputFile = ...;

try (CopyManager copyManager = CopyManagerFactory.create(dataSource)) {
    copyManager.copyToDb(query, inputFile);
}

//DB copy_manager_test and my_table table now has all csv data from the inputFile

Select as JSON

ResultSet rs = connection.createStatement().executeQuery("SELECT * FROM test.my_table FORMAT JSON");
if (rs.next()) {
    return rs.getString("json");
}

//respone example:

{
	"meta":
	[
		{
			"name": "created",
			"type": "DateTime"
		},
		{
			"name": "value",
			"type": "Int32"
		}
	],

	"data":
	[
		{
			"created": "2019-11-17 11:31:22",
			"value": 1
		},
		{
			"created": "2019-11-17 11:31:22",
			"value": 2
		}
	],

	"rows": 2,

	"statistics":
	{
		"elapsed": 0.000312306,
		"rows_read": 2,
		"bytes_read": 16
	}
}

Migration from the official driver

All you need to do is replace:

ru.yandex.clickhouse.ClickHouseDriver to cc.blynk.clickhouse.ClickHouseDriver

URL syntax: jdbc:clickhouse://<host>:<port>[/<database>], e.g. jdbc:clickhouse://localhost:8123/test

JDBC Driver Class: cc.blynk.clickhouse.ClickHouseDriver

additionally, if you have a few instances, you can use BalancedClickhouseDataSource.

Build requirements

In order to build the jdbc client one needs to have jdk 1.8 or higher.

Compiling with maven

mvn package -DskipTests=true

To build a jar with dependencies use

mvn package assembly:single -DskipTests=true