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 is pure go lz4 or switch to use cgo lz4 by turning 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 hosts for load-balancing
• connection_open_strategy - random/in_order (default random).
  • random - choose a random server from the set
  • in_order - first live server is chosen in specified order
  • time_random - choose random (based on the current time) server from the set. This option differs from random because randomness is based on the current time rather than on the number of previous connections.
• block_size - maximum rows in block (default is 1000000). If the rows are larger, the data will be split into several blocks to send to the server. If one block was sent to the server, the data would be persisted on the server disk, and we can't roll back the transaction. So always keep in mind that the batch size is no larger than the block_size if you want an atomic batch insert.
• pool_size - the 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')
• check_connection_liveness - on supported platforms non-secure connections retrieved from the connection pool are checked in beginTx() for liveness before using them. If the check fails, the respective connection is marked as bad and the query retried with another connection. (boolean value, default is 'true')

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) godoc
• Array(Nullable(T))
• Tuple(...T)

TODO

• Support other compression methods(zstd ...)

Install

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

Examples

package main

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

	"github.com/ClickHouse/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/ClickHouse/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/ClickHouse/clickhouse-go/lib/column"
	"log"
	"time"

	"github.com/ClickHouse/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 DeregisterTLSConfig(key string)
• func Open(dsn string) (driver.Conn, error)
• 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 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 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 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
	WriteDateNullable(c int, v *time.Time) error
	WriteDateTime(c int, v time.Time) error
	WriteDateTimeNullable(c int, v *time.Time) error
	WriteUInt8(c int, v uint8) error
	WriteUInt8Nullable(c int, v *uint8) error
	WriteUInt16(c int, v uint16) error
	WriteUInt16Nullable(c int, v *uint16) error
	WriteUInt32(c int, v uint32) error
	WriteUInt32Nullable(c int, v *uint32) error
	WriteUInt64(c int, v uint64) error
	WriteUInt64Nullable(c int, v *uint64) error
	WriteFloat32(c int, v float32) error
	WriteFloat32Nullable(c int, v *float32) error
	WriteFloat64(c int, v float64) error
	WriteFloat64Nullable(c int, v *float64) error
	WriteBytes(c int, v []byte) error
	WriteArray(c int, v interface{}) error
	WriteBytesNullable(c int, v *[]byte) error
	WriteArrayNullable(c int, v *interface{}) error
	WriteString(c int, v string) error
	WriteStringNullable(c int, v *string) error
	WriteFixedString(c int, v []byte) error
	WriteFixedStringNullable(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 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