ClickHouse

Golang SQL database client for ClickHouse.

Versions

There are two version of this client, v1 and v2, available as separate branches.

v1 is now in a state of a maintenance - we will only accept PRs for bug and security fixes.

Users should use v2 which is production ready and significantly faster than v1.

Supported ClickHouse Versions

The client is tested against the currently supported versions of ClickHouse

Supported Golang Versions

Client Version	Golang Versions
=> 2.0 <= 2.2	1.17, 1.18
>= 2.3	1.18

Key features

• Uses ClickHouse native format for optimal performance. Utilises low level ch-go client for encoding/decoding and compression (versions >= 2.3.0).
• Supports native ClickHouse TCP client-server protocol
• Compatibility with database/sql (slower than native interface!)
• database/sql supports http protocol for transport. (Experimental)
• Marshal rows into structs (ScanStruct, Select)
• Unmarshal struct to row (AppendStruct)
• Connection pool
• Failover and load balancing
• Bulk write support (for database/sql use begin->prepare->(in loop exec)->commit)
• AsyncInsert
• Named and numeric placeholders support
• LZ4/ZSTD compression support
• External data

Support for the ClickHouse protocol advanced features using Context:

• Query ID
• Quota Key
• Settings
• OpenTelemetry
• Execution events:
  • Logs
  • Progress
  • Profile info
  • Profile events

database/sql interface

OpenDB

conn := clickhouse.OpenDB(&clickhouse.Options{
	Addr: []string{"127.0.0.1:9999"},
	Auth: clickhouse.Auth{
		Database: "default",
		Username: "default",
		Password: "",
	},
	TLS: &tls.Config{
		InsecureSkipVerify: true,
	},
	Settings: clickhouse.Settings{
		"max_execution_time": 60,
	},
	DialTimeout: 5 * time.Second,
	Compression: &clickhouse.Compression{
		clickhouse.CompressionLZ4,
	},
	Debug: true,
})
conn.SetMaxIdleConns(5)
conn.SetMaxOpenConns(10)
conn.SetConnMaxLifetime(time.Hour)

DSN

• hosts - comma-separated list of single address hosts for load-balancing and failover
• username/password - auth credentials
• database - select the current default database
• dial_timeout - a duration string is a possibly signed sequence of decimal numbers, each with optional fraction and a unit suffix such as "300ms", "1s". Valid time units are "ms", "s", "m".
• connection_open_strategy - random/in_order (default random).
  • round-robin - choose a round-robin server from the set
  • in_order - first live server is chosen in specified order
• debug - enable debug output (boolean value)
• compress - enable lz4 compression (boolean value)

SSL/TLS parameters:

• secure - establish secure connection (default is false)
• skip_verify - skip certificate verification (default is false)

Example:

clickhouse://username:password@host1:9000,host2:9000/database?dial_timeout=200ms&max_execution_time=60

HTTP Support (Experimental)

The native format can be used over the HTTP protocol. This is useful in scenarios where users need to proxy traffic e.g. using ChProxy or via load balancers.

This can be achieved by modifying the DSN to specify the http protocol.

http://host1:9000,host2:9000/database?dial_timeout=200ms&max_execution_time=60

Alternatively, use OpenDB and specify the interface type.

conn := clickhouse.OpenDB(&clickhouse.Options{
	Addr: []string{"127.0.0.1:8123")},
	Auth: clickhouse.Auth{
		Database: "default",
		Username: "default",
		Password: "",
	},
	Settings: clickhouse.Settings{
		"max_execution_time": 60,
	},
	DialTimeout: 5 * time.Second,
	Compression: &clickhouse.Compression{
		Method: clickhouse.CompressionLZ4,
	},
	Interface: clickhouse.HttpInterface,
})

Compression

ZSTD/LZ4 compression is supported over native and http. This is performed at a block level and is only used for inserts.

If using Open via the std interface and specifying a DSN, compression can be enabled via the compress flag. Currently, this is a boolean flag which enables LZ4 compression.

Other compression methods will be added in future PRs.

TLS/SSL

At a low level all client connect methods (DSN/OpenDB/Open) will use the Go tls package to establish a secure connection. The client knows to use TLS if the Options struct contains a non-nil tls.Config pointer.

Setting secure in the DSN creates a minimal tls.Config struct with only the InsecureSkipVerify field set (either true or false). It is equivalent to this code:

conn := clickhouse.OpenDB(&clickhouse.Options{
	...
    TLS: &tls.Config{
            InsecureSkipVerify: false
	}
	...
    })

This minimal tls.Config is normally all that is necessary to connect to the secure native port (normally 9440) on a ClickHouse server. If the ClickHouse server does not have a valid certificate (expired, wrong host name, not signed by a publicly recognized root Certificate Authority), InsecureSkipVerify can be to true, but that is strongly discouraged.

If additional TLS parameters are necessary the application code should set the desired fields in the tls.Config struct. That can include specific cipher suites, forcing a particular TLS version (like 1.2 or 1.3), adding an internal CA certificate chain, adding a client certificate (and private key) if required by the ClickHouse server, and most of the other options that come with a more specialized security setup.

HTTPS (Experimental)

To connect using HTTPS either:

• Use https in your dsn string e.g.

  https://host1:9000,host2:9000/database?dial_timeout=200ms&max_execution_time=60

• Specify the interface type as HttpsInterface e.g.

conn := clickhouse.OpenDB(&clickhouse.Options{
	Addr: []string{"127.0.0.1:8443")},
	Auth: clickhouse.Auth{
		Database: "default",
		Username: "default",
		Password: "",
	},
	Interface: clickhouse.HttpsInterface,
})

Benchmark

V1 (READ)	V2 (READ) std	V2 (READ) native
1.218s	924.390ms	675.721ms

V1 (WRITE)	V2 (WRITE) std	V2 (WRITE) native	V2 (WRITE) by column
1.899s	1.177s	699.203ms	661.973ms

Install

go get -u github.com/ClickHouse/clickhouse-go/v2

Examples

native interface

• batch
• async insert
• batch struct
• columnar
• scan struct
• bind params

std database/sql interface

• batch
• async insert
• open db
• bind params

ClickHouse alternatives - ch-go

Versions of this client >=2.3.x utilise ch-go for their low level encoding/decoding. This low lever client provides a high performance columnar interface and should be used in performance critical use cases. This client provides more familar row orientated and database/sql semantics at the cost of some performance.

Both clients are supported by ClickHouse.

Third-party alternatives

• Database client/clients:

  • mailru/go-clickhouse (uses the HTTP protocol)
  • uptrace/go-clickhouse (uses the native TCP protocol with database/sql-like API)
  • Drivers with columnar interface:
    • vahid-sohrabloo/chconn

• Insert collectors:

  • KittenHouse
  • nikepan/clickhouse-bulk