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

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
• 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
• debug - enable debug output (boolean value)

SSL/TLS parameters:

• secure - establish secure connection (default is false)
• skip_verify - skip certificate verification (default is true)

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
• Enum
• UUID
• Nullable(T)
• Array(T) (one-dimensional) godoc

TODO

• Compression

Install

go get -u github.com/kshvakov/clickhouse

Example

package main

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

	"github.com/kshvakov/clickhouse"
)

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 (?, ?, ?, ?, ?, ?)")
	)

	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)
	}

	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 := connect.Exec("DROP TABLE example"); err != nil {
		log.Fatal(err)
	}
}

Use sqlx

package main

import (
	"log"
	"time"

	"github.com/jmoiron/sqlx"
	_ "github.com/kshvakov/clickhouse"
)

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{}) *types.Array
• func ArrayDate(v []time.Time) *types.Array
• func ArrayDateTime(v []time.Time) *types.Array
• func ArrayFixedString(len int, v interface{}) *types.Array
• func Open(dsn string) (driver.Conn, error)
• func SetLogOutput(output io.Writer)
• type Clickhouse
  • func OpenDirect(dsn string) (Clickhouse, error)
• type ColumnWriter
• type Date
  • func (date Date) Value() (driver.Value, error)
• type DateTime
  • func (datetime DateTime) Value() (driver.Value, error)
• type Exception
  • func (e *Exception) Error() string
• type IP
  • func (ip IP) MarshalBinary() ([]byte, error)
  • func (ip *IP) Scan(value interface{}) (err error)
  • func (ip IP) String() string
  • func (ip IP) Value() (driver.Value, error)
• type UUID
  • func (str UUID) MarshalBinary() ([]byte, error)
  • func (str *UUID) Scan(v interface{}) error
  • func (str UUID) Value() (driver.Value, error)

Constants

const (
	DefaultDatabase     = "default"
	DefaultUsername     = "default"
	DefaultReadTimeout  = time.Minute
	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")
)

var InvalidUUIDFormatError = errors.New("invalid UUID format")

Functions

func Array

func Array(v interface{}) *types.Array

func ArrayDate

func ArrayDate(v []time.Time) *types.Array

func ArrayDateTime

func ArrayDateTime(v []time.Time) *types.Array

func ArrayFixedString

func ArrayFixedString(len int, v interface{}) *types.Array

func Open

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

func SetLogOutput

func SetLogOutput(output io.Writer)

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 *types.Array) 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 time.Time

Truncate timezone

clickhouse.Date(time.Date(2017, 1, 1, 0, 0, 0, 0, time.Local)) -> time.Date(2017, 1, 1, 0, 0, 0, 0, time.UTC)

func (Date) Value

func (date Date) Value() (driver.Value, error)

type DateTime

type DateTime time.Time

Truncate timezone

clickhouse.DateTime(time.Date(2017, 1, 1, 0, 0, 0, 0, time.Local)) -> time.Date(2017, 1, 1, 0, 0, 0, 0, time.UTC)

func (DateTime) Value

func (datetime DateTime) Value() (driver.Value, error)

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 IP

type IP net.IP

IP column type

func (IP) MarshalBinary

func (ip IP) MarshalBinary() ([]byte, error)

func (*IP) Scan

func (ip *IP) Scan(value interface{}) (err error)

Scan implements the driver.Valuer interface, json field interface

func (IP) String

func (ip IP) String() string

String implements the fmt.Stringer interface

func (IP) Value

func (ip IP) Value() (driver.Value, error)

Value implements the driver.Valuer interface, json field interface Alignment on the right side

type UUID

type UUID string

this type will be deprecated because the ClickHouse server (>=1.1.54276) has a built-in type UUID

func (UUID) MarshalBinary

func (str UUID) MarshalBinary() ([]byte, error)

func (*UUID) Scan

func (str *UUID) Scan(v interface{}) error

func (UUID) Value

func (str UUID) Value() (driver.Value, error)