【轻知识】3分钟，golang 操作 clickhouse

安装clickhouse与使用

docker pull yandex/clickhouse-server

如果pull出现 docker pull yandex/clickhouse-server
Using default tag: latest
Error response from daemon: Get https://registry-1.docker.io/v2/: net/http: TLS handshake timeout

请换源。另一种方式是用dig命令然后配置hosts。这个方式有可能dig出来的ip都不好使。

docker run -d --name ck-server --ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 -p 9009:9009 yandex/clickhouse-server

docker exec -it ck-server /bin/bash

clickhouse-client #进入数据库

建库、建表、建测试数据（均为本地测试）

create database statics engine=Ordinary;

use statics;

create table mysql_slow_log (
    id UInt16,
    user_name String,
    host String,
    sql String,
    rows_examined UInt16,
    exec_time UInt16,
    query_time String,
    create_time date
)engine=MergeTree(create_time, (id), 8192);

insert into mysql_slow_log  (user_name, host, sql, rows_examined, exec_time, query_time, create_time) values('xiaoming', '127.0.0.1', 'select * from music', 3000, 1587021607, '0.333', '2020-04-16 15:32:17');

go操作

govendor fetch github.com/ClickHouse/clickhouse-go
govendor fetch github.com/jmoiron/sqlx

项目的vendor 目录用的govendor 来管理的

error: RPC failed; curl 56 LibreSSL SSL_read: SSL_ERROR_SYSCALL, errno 54
fatal: The remote end hung up unexpectedly

解决：git config http.postBuffer 524288000
代码（参考 clickhouse-go 的示例代码）

func (slowLog MysqlSlowLogModel) GetAllFromCk(offset int, pageSize int)([]MysqlSlowLogModel, error) {
    connect, err := sqlx.Open("clickhouse", "tcp://127.0.0.1:9000?debug=true")
    if err != nil {
        return nil, errors.New("ck connect failover")
    }
    var items []MysqlSlowLogModel

    sql := "select * from statics.mysql_slow_log";
    if offset >=0 && pageSize > 0{
        sql = fmt.Sprintf("%s limit %d, %d", sql, offset, pageSize) 
    }

    if err := connect.Select(&items, sql); err != nil {
        return nil, err
    }
    return items, nil
}

其他的操作api，在sqlx.go文件中找。readme中有示例。

界面展示

参考资料：

*《DOCKER 安装clickhouse》https://www.jianshu.com/p/362252f2284b
*《大数据实时分析领域黑马开源ClickHouse》https://time.geekbang.org/column/article/40817