ClickHouse

Golang SQL database driver for ClickHouse.

Key features

• Uses native ClickHouse TCP client-server protocol
• Compatibility with database/sql (slower than native interface!)
• 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 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

Benchmark

V2 (READ) std	V2 (READ) native
924.390ms	675.721ms

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

Install

go get -u github.com/kevinma2010/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

Alternatives

• Database drivers

  • 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
    • go-faster/ch

• Insert collectors:

  • KittenHouse
  • nikepan/clickhouse-bulk

Useful projects

• clickhouse-backup
• go-graphite