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) godoc
- Tuple(...T)
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/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
- type ColumnWriter
- type Date
- type DateTime
- type Exception
- type ExternalTable
- type UUID
Constants ¶
View Source
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 ¶
View Source
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 ArrayDateTime ¶
func ArrayFixedString ¶
func ArrayFixedString(len int, v interface{}) interface{}
func DeregisterTLSConfig ¶ added in v1.3.7
func DeregisterTLSConfig(key string)
DeregisterTLSConfig removes the tls.Config associated with key.
func RegisterTLSConfig ¶ added in v1.3.7
RegisterTLSConfig registers a custom tls.Config to be used with sql.Open.
func SetLogOutput ¶ added in v1.3.2
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 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 Exception ¶
type ExternalTable ¶ added in v1.4.5
Source Files ¶
- array.go
- bootstrap.go
- clickhouse.go
- clickhouse_exception.go
- clickhouse_ping.go
- clickhouse_profile_info.go
- clickhouse_progress.go
- clickhouse_read_block.go
- clickhouse_read_meta.go
- clickhouse_send_external_data.go
- clickhouse_send_query.go
- clickhouse_write_block.go
- connect.go
- helpers.go
- query_settings.go
- result.go
- rows.go
- stmt.go
- tls_config.go
- word_matcher.go
- write_column.go
Directories ¶
Path | Synopsis |
---|---|
lib
|
|
cityhash102
* COPY from https://github.com/zentures/cityhash/ NOTE: The code is modified to be compatible with CityHash128 used in ClickHouse
|
* COPY from https://github.com/zentures/cityhash/ NOTE: The code is modified to be compatible with CityHash128 used in ClickHouse |
Click to show internal directories.
Click to hide internal directories.