ClickHouse列数据库的Golang 驱动

关键特性

• 使用原生 ClickHouse tcp client-server 协议
• 兼容 database/sql 库
• 实现了轮训算法的负载均衡

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
• 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
• debug - enable debug output (boolean value)

SSL/TLS 参数

• secure - 建立安全连接，默认为false
• skip_verify - 跳过安全认证 默认是true

支持的数据类型

• UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64
• Float32, Float64
• String
• FixedString(N)
• Date
• DateTime
• Enum
• UUID
• Nullable(T)
• Array(T) (one-dimensional) godoc

Install

go get -u github.com/kshvakov/clickhouse

示例

package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"

    "github.com/kshvakov/clickhouse"
)

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 (?, ?, ?, ?, ?, ?)")
    )

    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)
    }

    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 := connect.Exec("DROP TABLE example"); err != nil {
        log.Fatal(err)
    }
}

Use sqlx

支持golang database/sql扩展。

package main

import (
    "log"
    "time"

    "github.com/jmoiron/sqlx"
    _ "github.com/kshvakov/clickhouse"
)

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)
    }
}

总结

基本上该驱动兼容 database/sql 库，所以有意义的工作就是让gorm这种orm支持clickhouse或是自己写一个orm。

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