clickhouse

README

ClickHouse

Golang SQL database driver for Yandex ClickHouse

Key features

• Uses native ClickHouse tcp client-server protocol
• Compatibility with database/sql
• Round Robin load-balancing
• Bulk write support : begin->prepare->(in loop exec)->commit
• LZ4 compression support (default to use pure go lz4, switch to use cgo lz4 by turn clz4 build tags on)
• External Tables support

DSN

• username/password - auth credentials
• database - select the current default database
• read_timeout/write_timeout - timeout in second
• no_delay - disable/enable the Nagle Algorithm for tcp socket (default is 'true' - disable)
• alt_hosts - comma separated list of single address host for load-balancing
• connection_open_strategy - random/in_order (default random).
  • random - choose random server from set
  • in_order - first live server is choosen in specified order
  • time_random - choose random(based on current time) server from set. This option differs from random in that randomness is based on current time rather than on amount of previous connections.
• block_size - maximum rows in block (default is 1000000). If the rows are larger then the data will be split into several blocks to send them to the server. If one block was sent to the server, the data will be persisted on the server disk, we can't rollback the transaction. So always keep in mind that the batch size no larger than the block_size if you want atomic batch insert.
• pool_size - maximum amount of preallocated byte chunks used in queries (default is 100). Decrease this if you experience memory problems at the expense of more GC pressure and vice versa.
• debug - enable debug output (boolean value)
• compress - enable lz4 compression (integer value, default is '0')

SSL/TLS parameters:

• secure - establish secure connection (default is false)
• skip_verify - skip certificate verification (default is false)
• tls_config - name of a TLS config with client certificates, registered using clickhouse.RegisterTLSConfig(); implies secure to be true, unless explicitly specified

example:

tcp://host1:9000?username=user&password=qwerty&database=clicks&read_timeout=10&write_timeout=20&alt_hosts=host2:9000,host3:9000

Supported data types

• UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64
• Float32, Float64
• String
• FixedString(N)
• Date
• DateTime
• IPv4
• IPv6
• Enum
• UUID
• Nullable(T)
• Array(T) (one-dimensional) godoc

TODO

• Support other compression methods(zstd ...)

Install

go get -u github.com/c3mb0/clickhouse-go

Example

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	"github.com/c3mb0/clickhouse-go"
)

func main() {
	connect, err := sql.Open("clickhouse", "tcp://127.0.0.1:9000?debug=true")
	if err != nil {
		log.Fatal(err)
	}
	if err := connect.Ping(); err != nil {
		if exception, ok := err.(*clickhouse.Exception); ok {
			fmt.Printf("[%d] %s \n%s\n", exception.Code, exception.Message, exception.StackTrace)
		} else {
			fmt.Println(err)
		}
		return
	}

	_, err = connect.Exec(`
		CREATE TABLE IF NOT EXISTS example (
			country_code FixedString(2),
			os_id        UInt8,
			browser_id   UInt8,
			categories   Array(Int16),
			action_day   Date,
			action_time  DateTime
		) engine=Memory
	`)

	if err != nil {
		log.Fatal(err)
	}
	var (
		tx, _   = connect.Begin()
		stmt, _ = tx.Prepare("INSERT INTO example (country_code, os_id, browser_id, categories, action_day, action_time) VALUES (?, ?, ?, ?, ?, ?)")
	)
	defer stmt.Close()

	for i := 0; i < 100; i++ {
		if _, err := stmt.Exec(
			"RU",
			10+i,
			100+i,
			clickhouse.Array([]int16{1, 2, 3}),
			time.Now(),
			time.Now(),
		); err != nil {
			log.Fatal(err)
		}
	}

	if err := tx.Commit(); err != nil {
		log.Fatal(err)
	}

	rows, err := connect.Query("SELECT country_code, os_id, browser_id, categories, action_day, action_time FROM example")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var (
			country               string
			os, browser           uint8
			categories            []int16
			actionDay, actionTime time.Time
		)
		if err := rows.Scan(&country, &os, &browser, &categories, &actionDay, &actionTime); err != nil {
			log.Fatal(err)
		}
		log.Printf("country: %s, os: %d, browser: %d, categories: %v, action_day: %s, action_time: %s", country, os, browser, categories, actionDay, actionTime)
	}

	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}

	if _, err := connect.Exec("DROP TABLE example"); err != nil {
		log.Fatal(err)
	}
}

Use sqlx

package main

import (
	"log"
	"time"

	"github.com/jmoiron/sqlx"
	_ "github.com/c3mb0/clickhouse-go"
)

func main() {
	connect, err := sqlx.Open("clickhouse", "tcp://127.0.0.1:9000?debug=true")
	if err != nil {
		log.Fatal(err)
	}
	var items []struct {
		CountryCode string    `db:"country_code"`
		OsID        uint8     `db:"os_id"`
		BrowserID   uint8     `db:"browser_id"`
		Categories  []int16   `db:"categories"`
		ActionTime  time.Time `db:"action_time"`
	}

	if err := connect.Select(&items, "SELECT country_code, os_id, browser_id, categories, action_time FROM example"); err != nil {
		log.Fatal(err)
	}

	for _, item := range items {
		log.Printf("country: %s, os: %d, browser: %d, categories: %v, action_time: %s", item.CountryCode, item.OsID, item.BrowserID, item.Categories, item.ActionTime)
	}
}

External tables support

package main

import (
	"database/sql"
    "database/sql/driver"
	"fmt"
    "github.com/c3mb0/clickhouse-go/lib/column"
	"log"
	"time"

	"github.com/c3mb0/clickhouse-go"
)

func main() {
	connect, err := sql.Open("clickhouse", "tcp://127.0.0.1:9000?debug=true")
	if err != nil {
		log.Fatal(err)
	}
	if err := connect.Ping(); err != nil {
		if exception, ok := err.(*clickhouse.Exception); ok {
			fmt.Printf("[%d] %s \n%s\n", exception.Code, exception.Message, exception.StackTrace)
		} else {
			fmt.Println(err)
		}
		return
	}

	_, err = connect.Exec(`
		CREATE TABLE IF NOT EXISTS example (
			country_code FixedString(2),
			os_id        UInt8,
			browser_id   UInt8,
			categories   Array(Int16),
			action_day   Date,
			action_time  DateTime
		) engine=Memory
	`)

	if err != nil {
		log.Fatal(err)
	}
	var (
		tx, _   = connect.Begin()
		stmt, _ = tx.Prepare("INSERT INTO example (country_code, os_id, browser_id, categories, action_day, action_time) VALUES (?, ?, ?, ?, ?, ?)")
	)
	defer stmt.Close()

	for i := 0; i < 100; i++ {
		if _, err := stmt.Exec(
			"RU",
			10+i,
			100+i,
			clickhouse.Array([]int16{1, 2, 3}),
			time.Now(),
			time.Now(),
		); err != nil {
			log.Fatal(err)
		}
	}

	if err := tx.Commit(); err != nil {
		log.Fatal(err)
	}

	col, err := column.Factory("country_code", "String", nil)
	if err != nil {
		log.Fatal(err)
	}
	countriesExternalTable := clickhouse.ExternalTable{
		Name: "countries",
		Values: [][]driver.Value{
			{"RU"},
		},
		Columns: []column.Column{col},
	}
	
    rows, err := connect.Query("SELECT country_code, os_id, browser_id, categories, action_day, action_time "+
            "FROM example WHERE country_code IN ?", countriesExternalTable)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var (
			country               string
			os, browser           uint8
			categories            []int16
			actionDay, actionTime time.Time
		)
		if err := rows.Scan(&country, &os, &browser, &categories, &actionDay, &actionTime); err != nil {
			log.Fatal(err)
		}
		log.Printf("country: %s, os: %d, browser: %d, categories: %v, action_day: %s, action_time: %s", country, os, browser, categories, actionDay, actionTime)
	}

	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}

	if _, err := connect.Exec("DROP TABLE example"); err != nil {
		log.Fatal(err)
	}
}

Documentation

Index

• Constants
• Variables
• func Array(v interface{}) interface{}
• func ArrayDate(v []time.Time) interface{}
• func ArrayDateTime(v []time.Time) interface{}
• func ArrayFixedString(len int, v interface{}) interface{}
• func DeregisterDial()
• func DeregisterTLSConfig(key string)
• func Open(dsn string) (driver.Conn, error)
• func RegisterDial(dial DialFunc)
• func RegisterTLSConfig(key string, config *tls.Config) error
• func SetLogOutput(output io.Writer)
• func WithQueryID(ctx context.Context, queryID string) context.Context
• type Clickhouse
  • func OpenDirect(dsn string) (Clickhouse, error)
• type ColumnWriter
• type Date
• type DateTime
• type DialFunc
• type Exception
  • func (e *Exception) Error() string
• type ExternalTable
• type UUID

Constants

const (
	// DefaultDatabase when connecting to ClickHouse
	DefaultDatabase = "default"
	// DefaultUsername when connecting to ClickHouse
	DefaultUsername = "default"
	// DefaultConnTimeout when connecting to ClickHouse
	DefaultConnTimeout = 5 * time.Second
	// DefaultReadTimeout when reading query results
	DefaultReadTimeout = time.Minute
	// DefaultWriteTimeout when sending queries
	DefaultWriteTimeout = time.Minute
)

Variables

var (
	ErrInsertInNotBatchMode = errors.New("insert statement supported only in the batch mode (use begin/commit)")
	ErrLimitDataRequestInTx = errors.New("data request has already been prepared in transaction")
)

Functions

func Array

func Array(v interface{}) interface{}

func ArrayDate

func ArrayDate(v []time.Time) interface{}

func ArrayDateTime

func ArrayDateTime(v []time.Time) interface{}

func ArrayFixedString

func ArrayFixedString(len int, v interface{}) interface{}

func DeregisterDial

func DeregisterDial()

DeregisterDial deregisters the custom dial function.

func DeregisterTLSConfig

func DeregisterTLSConfig(key string)

DeregisterTLSConfig removes the tls.Config associated with key.

func Open

func Open(dsn string) (driver.Conn, error)

Open the connection

func RegisterDial

func RegisterDial(dial DialFunc)

RegisterDial registers a custom dial function.

func RegisterTLSConfig

func RegisterTLSConfig(key string, config *tls.Config) error

RegisterTLSConfig registers a custom tls.Config to be used with sql.Open.

func SetLogOutput

func SetLogOutput(output io.Writer)

SetLogOutput allows to change output of the default logger

func WithQueryID

func WithQueryID(ctx context.Context, queryID string) context.Context

Put query ID into context and use it in ExecContext or QueryContext

Types

type Clickhouse

type Clickhouse interface {
	Block() (*data.Block, error)
	Prepare(query string) (driver.Stmt, error)
	Begin() (driver.Tx, error)
	Commit() error
	Rollback() error
	Close() error
	WriteBlock(block *data.Block) error
}

Interface for Clickhouse driver

func OpenDirect

func OpenDirect(dsn string) (Clickhouse, error)

type ColumnWriter

type ColumnWriter interface {
	WriteDate(c int, v time.Time) error
	WriteDateTime(c int, v time.Time) error
	WriteUInt8(c int, v uint8) error
	WriteUInt16(c int, v uint16) error
	WriteUInt32(c int, v uint32) error
	WriteUInt64(c int, v uint64) error
	WriteFloat32(c int, v float32) error
	WriteFloat64(c int, v float64) error
	WriteBytes(c int, v []byte) error
	WriteArray(c int, v interface{}) error
	WriteString(c int, v string) error
	WriteFixedString(c int, v []byte) error
}

Interface for Block allowing writes to individual columns

type Date

type Date = types.Date

type DateTime

type DateTime = types.DateTime

type DialFunc

type DialFunc func(network, address string, timeout time.Duration, config *tls.Config) (net.Conn, error)

DialFunc is a function which can be used to establish the network connection. Custom dial functions must be registered with RegisterDial

type Exception

type Exception struct {
	Code       int32
	Name       string
	Message    string
	StackTrace string
	// contains filtered or unexported fields
}

func (*Exception) Error

func (e *Exception) Error() string

type ExternalTable

type ExternalTable struct {
	Name    string
	Values  [][]driver.Value
	Columns []column.Column
}

type UUID

type UUID = types.UUID