README.md
1# ClickHouse [![Build Status](https://travis-ci.org/ClickHouse/clickhouse-go.svg?branch=master)](https://travis-ci.org/ClickHouse/clickhouse-go) [![Go Report Card](https://goreportcard.com/badge/github.com/ClickHouse/clickhouse-go)](https://goreportcard.com/report/github.com/ClickHouse/clickhouse-go) [![codecov](https://codecov.io/gh/ClickHouse/clickhouse-go/branch/master/graph/badge.svg)](https://codecov.io/gh/ClickHouse/clickhouse-go)
2
3Golang SQL database driver for [Yandex ClickHouse](https://clickhouse.yandex/)
4
5## Key features
6
7* Uses native ClickHouse tcp client-server protocol
8* Compatibility with `database/sql`
9* Round Robin load-balancing
10* Bulk write support : `begin->prepare->(in loop exec)->commit`
11* LZ4 compression support (default to use pure go lz4, switch to use cgo lz4 by turn clz4 build tags on)
12
13## DSN
14
15* username/password - auth credentials
16* database - select the current default database
17* read_timeout/write_timeout - timeout in second
18* no_delay - disable/enable the Nagle Algorithm for tcp socket (default is 'true' - disable)
19* alt_hosts - comma separated list of single address host for load-balancing
20* connection_open_strategy - random/in_order (default random).
21 * random - choose random server from set
22 * in_order - first live server is choosen in specified order
23 * 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.
24* 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.
25* 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.
26* debug - enable debug output (boolean value)
27
28SSL/TLS parameters:
29
30* secure - establish secure connection (default is false)
31* skip_verify - skip certificate verification (default is false)
32* tls_config - name of a TLS config with client certificates, registered using `clickhouse.RegisterTLSConfig()`; implies secure to be true, unless explicitly specified
33
34example:
35```
36tcp://host1:9000?username=user&password=qwerty&database=clicks&read_timeout=10&write_timeout=20&alt_hosts=host2:9000,host3:9000
37```
38
39## Supported data types
40
41* UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64
42* Float32, Float64
43* String
44* FixedString(N)
45* Date
46* DateTime
47* IPv4
48* IPv6
49* Enum
50* UUID
51* Nullable(T)
52* [Array(T) (one-dimensional)](https://clickhouse.yandex/reference_en.html#Array(T)) [godoc](https://godoc.org/github.com/ClickHouse/clickhouse-go#Array)
53
54## TODO
55
56* Support other compression methods(zstd ...)
57
58## Install
59```
60go get -u github.com/ClickHouse/clickhouse-go
61```
62
63## Example
64```go
65package main
66
67import (
68 "database/sql"
69 "fmt"
70 "log"
71 "time"
72
73 "github.com/ClickHouse/clickhouse-go"
74)
75
76func main() {
77 connect, err := sql.Open("clickhouse", "tcp://127.0.0.1:9000?debug=true")
78 if err != nil {
79 log.Fatal(err)
80 }
81 if err := connect.Ping(); err != nil {
82 if exception, ok := err.(*clickhouse.Exception); ok {
83 fmt.Printf("[%d] %s \n%s\n", exception.Code, exception.Message, exception.StackTrace)
84 } else {
85 fmt.Println(err)
86 }
87 return
88 }
89
90 _, err = connect.Exec(`
91 CREATE TABLE IF NOT EXISTS example (
92 country_code FixedString(2),
93 os_id UInt8,
94 browser_id UInt8,
95 categories Array(Int16),
96 action_day Date,
97 action_time DateTime
98 ) engine=Memory
99 `)
100
101 if err != nil {
102 log.Fatal(err)
103 }
104 var (
105 tx, _ = connect.Begin()
106 stmt, _ = tx.Prepare("INSERT INTO example (country_code, os_id, browser_id, categories, action_day, action_time) VALUES (?, ?, ?, ?, ?, ?)")
107 )
108 defer stmt.Close()
109
110 for i := 0; i < 100; i++ {
111 if _, err := stmt.Exec(
112 "RU",
113 10+i,
114 100+i,
115 clickhouse.Array([]int16{1, 2, 3}),
116 time.Now(),
117 time.Now(),
118 ); err != nil {
119 log.Fatal(err)
120 }
121 }
122
123 if err := tx.Commit(); err != nil {
124 log.Fatal(err)
125 }
126
127 rows, err := connect.Query("SELECT country_code, os_id, browser_id, categories, action_day, action_time FROM example")
128 if err != nil {
129 log.Fatal(err)
130 }
131 defer rows.Close()
132
133 for rows.Next() {
134 var (
135 country string
136 os, browser uint8
137 categories []int16
138 actionDay, actionTime time.Time
139 )
140 if err := rows.Scan(&country, &os, &browser, &categories, &actionDay, &actionTime); err != nil {
141 log.Fatal(err)
142 }
143 log.Printf("country: %s, os: %d, browser: %d, categories: %v, action_day: %s, action_time: %s", country, os, browser, categories, actionDay, actionTime)
144 }
145
146 if err := rows.Err(); err != nil {
147 log.Fatal(err)
148 }
149
150 if _, err := connect.Exec("DROP TABLE example"); err != nil {
151 log.Fatal(err)
152 }
153}
154```
155
156Use [sqlx](https://github.com/jmoiron/sqlx)
157
158```go
159package main
160
161import (
162 "log"
163 "time"
164
165 "github.com/jmoiron/sqlx"
166 _ "github.com/ClickHouse/clickhouse-go"
167)
168
169func main() {
170 connect, err := sqlx.Open("clickhouse", "tcp://127.0.0.1:9000?debug=true")
171 if err != nil {
172 log.Fatal(err)
173 }
174 var items []struct {
175 CountryCode string `db:"country_code"`
176 OsID uint8 `db:"os_id"`
177 BrowserID uint8 `db:"browser_id"`
178 Categories []int16 `db:"categories"`
179 ActionTime time.Time `db:"action_time"`
180 }
181
182 if err := connect.Select(&items, "SELECT country_code, os_id, browser_id, categories, action_time FROM example"); err != nil {
183 log.Fatal(err)
184 }
185
186 for _, item := range items {
187 log.Printf("country: %s, os: %d, browser: %d, categories: %v, action_time: %s", item.CountryCode, item.OsID, item.BrowserID, item.Categories, item.ActionTime)
188 }
189}
190```
191