3

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

 2 years ago
source link: https://studygolang.com/articles/28061?fr=sidebar
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

安装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');

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中有示例。

ebe8c0e9351bb21c8b3d8f447ffe1cc2.png

参考资料：

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

有疑问加站长微信联系（非本文作者）

280

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK