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
randombecause 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)
}
}
-
1.SELECT语句语法 [WITH expr_list|(subquery)] SELECT [DISTINCT] expr_list [FROM [db.]table | (subquery) | table_function] [FINAL] [SAMPLE sample_coeff] [ARRAY JOIN ...] [GLOBAL] [ANY|ALL|ASOF] [INNER|LEF
-
1.分布式DDL某数据节点的副本不执行 (1)问题：使用分布式 ddl 执行命令 create table on cluster xxxx 某个节点上没有创建表，但是 client 返回正常，查看日志有如下报错。 <Error> xxx.xxx: Retrying createReplica(), because some other replicas were created at the sa
-
在 clickhouse 20.6 版本之前要查看 SQL 语句的执行计划需要设置日志级别为 trace 才能可以看到，并且只能真正执行 sql，在执行日志里面查看。在 20.6 版本引入了原生的执行计划的语法。在 20.6.3 版本成为正式版本的功能。 1.基本语法 EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...]
-
上一篇：（14. 高级-MaterializeMySQL 引擎）学习笔记 1 分布式 DDL 某数据节点的副本不执行 问题：使用分布式 ddl 执行命令 create table on cluster xxxx 某个节点上没有创建表，但是 client 返回正常，查看日志有如下报错。 <Error> xxx.xxx: Retrying createReplica(), because some o
-
Click提供了丰富的接口来访问数据库以及数据库管理系统，如下表： 接口类型 描述 http ClickHouse默认提供了HTTP接口，通过http进行数据库和数据库管理系统相关操作。ClickHouse默认监听8123端口（可修改），http接口比其他接口受到更多的限制，但是兼容性更好，文档齐全，上手简单 tcp 用于命令行客户端通信，集群服务器之间的通信，以及其他C++程序 command-
-
package edgedb import ( "database/sql" "errors" "fmt" "sort" "strings" "time" "gorm.io/driver/clickhouse" "gorm.io/driver/postgres" "gorm.io/gorm" ) var ( pgConn *gorm.DB ckConn *gorm.DB
-
ClickHouse是俄罗斯第一大搜索引擎Yandex开发的列式储存数据库.令人惊喜的是,这个列式储存数据库的性能大幅超越了很多商业MPP数据库软件,比如Vertica,InfiniDB. 相比传统的数据库软件,ClickHouse要快100-1000X: 100Million 数据集: ClickHouse比Vertica约快5倍,比Hive快279倍,比My SQL快801倍 1Billion
-
主要内容：1.1 打开相关端口或关闭防火墙,1.2 关闭SELinux,1.3 取消打开文件限制,1.4 安装依赖,1.5 启动客户端1.1 打开相关端口或关闭防火墙 1.2 关闭SELinux 1.3 取消打开文件限制 1.4 安装依赖 在软件安装位置创建目录： 将上面4个文件上传到创建的目录下 在创建的目录下执行安装命令： 运行完成后，查看安装情况： 接下来修改配置文件 将<listen_host>::</listen_host>的注释打开（该项配置表示可以在任意IP访问服务） 保存配置，接
-
主要内容：1.ClickHouse是什么,2.ClickHouse的特点,3.ClickHouse的适用场景,4.ClickHouse为何那么快,5.EMR ClickHouse架构1.ClickHouse是什么 ClickHouse是俄罗斯Yandex开发的一款基于列式存储的开源查询数据库，基于语言开发的。ClickHouse在 2016 年开源，在计算引擎里算是一个后起之秀，在内存数据库领域号称是最快的。 另外需要注意的是，ClickHouse并不是基于Hadoop生态的，而是采用 Loca
-
主要内容：1.Hive的数据文件,2.Hive的存储系统,3.Hive计算引擎与ClickHouse计算引擎的差异,4.ClickHouse比Hive快的原因Hive是Hadoop生态系统中事实上的数据仓库标准。Hive是建立在Hadoop生态中的数据仓库中间件，其本身并不提供存储与计算能力。Hive的存储引擎使用HDFS，计算引擎使用MapReduce或Spark。 Hive本质上是一个元数据管理平台，通过对存储于HDFS上的数据文件附加元数据，赋予HDFS上的文件以数据库表的语义。并对外提供
-
主要内容：1.ClickHouse介绍,2.StarRocks介绍1.ClickHouse介绍 ClickHouse是面向联机分析处理（OLAP）的开源分析引擎。最初由俄罗斯第一搜索引擎Yandex开发，于2016年开源，开发语言为C++。由于其优良的查询性能，PB级的数据规模，简单的架构，在国内外公司被广泛采用。 它是列存数据库，具有完备的DBMS功能，备份列式存储和数据压缩。它的MPP架构易于扩展，易于维护。除此之外，它支持向量化的查询，完善的SQL以及实时
-
Debian/Ubuntu 用户 新建 /etc/apt/sources.list.d/clickhouse.list，内容为 deb https://mirrors.tuna.tsinghua.edu.cn/clickhouse/deb/stable/ main/ RHEL/CentOS 用户 新建 /etc/yum.repos.d/clickhouse.repo，内容为 [repo.yand