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)

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)

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/ClickHouse/clickhouse-go

Example

package main

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

	"github.com/Tokumicn/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/Tokumicn/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)
	}
}

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)
• type Clickhouse
  • func OpenDirect(dsn string) (Clickhouse, error)
• type ColumnWriter
• type Date
• type DateTime
• type Exception
  • func (e *Exception) Error() string
• 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

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 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 UUID

type UUID = types.UUID