Flow的採集與分析---clickhorse的golang驅動

這是一個建立於 的文章，其中的資訊可能已經有所發展或是發生改變。

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 mainimport (    "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 mainimport (    "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。