clickhouse

README

ClickHouse

Golang SQL database driver for Yandex ClickHouse. Supported by Kinescope.

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

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

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

Documentation

Index

• Variables
• func Context(parent context.Context, options ...QueryOption) context.Context
• func Named(name string, value interface{}) driver.NamedValue
• func Open(opt *Options) (driver.Conn, error)
• func OpenDB(opt *Options) *sql.DB
• type Auth
• type Compression
• type Conn
• type ConnOpenStrategy
• type Exception
• type Log
• type OpError
  • func (e *OpError) Error() string
• type Options
  • func ParseDSN(dsn string) (*Options, error)
• type ProfileEvent
• type ProfileInfo
• type Progress
• type QueryOption
  • func WithExternalTable(t ...*ext.Table) QueryOption
  • func WithLogs(fn func(*Log)) QueryOption
  • func WithProfileEvents(fn func([]ProfileEvent)) QueryOption
  • func WithProfileInfo(fn func(*ProfileInfo)) QueryOption
  • func WithProgress(fn func(*Progress)) QueryOption
  • func WithQueryID(queryID string) QueryOption
  • func WithQuotaKey(quotaKey string) QueryOption
  • func WithSettings(settings Settings) QueryOption
  • func WithSpan(span trace.SpanContext) QueryOption
  • func WithStdAsync(wait bool) QueryOption
• type QueryOptions
• type ServerVersion
• type Settings

Variables

var (
	ErrBatchAlreadySent               = errors.New("clickhouse: batch has already been sent")
	ErrAcquireConnTimeout             = errors.New("clickhouse: acquire conn timeout. you can increase the number of max open conn or the dial timeout")
	ErrUnsupportedServerRevision      = errors.New("clickhouse: unsupported server revision")
	ErrBindMixedNamedAndNumericParams = errors.New("clickhouse [bind]: mixed named and numeric parameters")
)

var CompressionLZ4 compress.Method = compress.LZ4

Functions

func Context

func Context(parent context.Context, options ...QueryOption) context.Context

func Named

func Named(name string, value interface{}) driver.NamedValue

func Open

func Open(opt *Options) (driver.Conn, error)

func OpenDB

func OpenDB(opt *Options) *sql.DB

Types

type Auth

type Auth struct {
	Database string
	Username string
	Password string
}

type Compression

type Compression struct {
	Method compress.Method
}

type Conn

type Conn = driver.Conn

type ConnOpenStrategy

type ConnOpenStrategy uint8

const (
	ConnOpenInOrder ConnOpenStrategy = iota
	ConnOpenRoundRobin
)

type Exception

type Exception = proto.Exception

type Log

type Log struct {
	Time      time.Time
	TimeMicro uint32
	Hostname  string
	QueryID   string
	ThreadID  uint64
	Priority  int8
	Source    string
	Text      string
}

type OpError

type OpError struct {
	Op         string
	ColumnName string
	Err        error
}

func (*OpError) Error

func (e *OpError) Error() string

type Options

type Options struct {
	TLS              *tls.Config
	Addr             []string
	Auth             Auth
	DialContext      func(ctx context.Context, addr string) (net.Conn, error)
	Debug            bool
	Settings         Settings
	Compression      *Compression
	DialTimeout      time.Duration // default 1 second
	MaxOpenConns     int           // default MaxIdleConns + 5
	MaxIdleConns     int           // default 5
	ConnMaxLifetime  time.Duration // default 1 hour
	ConnOpenStrategy ConnOpenStrategy
}

func ParseDSN

func ParseDSN(dsn string) (*Options, error)

type ProfileEvent

type ProfileEvent struct {
	Hostname    string
	CurrentTime time.Time
	ThreadID    uint64
	Type        string
	Name        string
	Value       int64
}

type ProfileInfo

type ProfileInfo = proto.ProfileInfo

type Progress

type Progress = proto.Progress

type QueryOption

type QueryOption func(*QueryOptions) error

func WithExternalTable

func WithExternalTable(t ...*ext.Table) QueryOption

func WithLogs

func WithLogs(fn func(*Log)) QueryOption

func WithProfileEvents

func WithProfileEvents(fn func([]ProfileEvent)) QueryOption

func WithProfileInfo

func WithProfileInfo(fn func(*ProfileInfo)) QueryOption

func WithProgress

func WithProgress(fn func(*Progress)) QueryOption

func WithQueryID

func WithQueryID(queryID string) QueryOption

func WithQuotaKey

func WithQuotaKey(quotaKey string) QueryOption

func WithSettings

func WithSettings(settings Settings) QueryOption

func WithSpan

func WithSpan(span trace.SpanContext) QueryOption

func WithStdAsync

func WithStdAsync(wait bool) QueryOption

type QueryOptions

type QueryOptions struct {
	// contains filtered or unexported fields
}

type ServerVersion

type ServerVersion = proto.ServerHandshake

type Settings

type Settings map[string]interface{}