go使用连接池连接clickhouse

package main

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

    _ "github.com/ClickHouse/clickhouse-go"
)

// 连接池的最大数量
const maxConnections = 10

func connect() (*sql.DB, error) {
    // 创建连接池
    pool, err := sql.Open("clickhouse", "tcp://127.0.0.1:9000?debug=true")
    if err != nil {
        return nil, err
    }

    // 设置连接池的最大连接数
    pool.SetMaxOpenConns(maxConnections)

    // 设置连接池中最多可以有多少空闲连接
    pool.SetMaxIdleConns(maxConnections / 2)

    // 设置连接的最大空闲时间
    pool.SetConnMaxLifetime(time.Minute * 5)

    // 测试连接是否可用
    if err = pool.Ping(); err != nil {
        return nil, err
    }

    return pool, nil
}

func main() {
    db, err := connect()
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // 构造插入语句
    stmt := `
    INSERT INTO mytable (id, name, age)
    VALUES
    `
    for i := 0; i < 1000; i++ {
        stmt += fmt.Sprintf("(%d, 'Name%d', %d),", i, i, i%100)
    }
    stmt = stmt[:len(stmt)-1]

    // 执行插入语句
    result, err := db.Exec(stmt)
    if err != nil {
        panic(err)
    }

    // 输出插入结果
    fmt.Println(result.RowsAffected())
}


2. 如果有很多字段，可以这样写

package main

import (
    "encoding/json"
    "fmt"

    "github.com/kshvakov/clickhouse"
)

type User map[string]interface{}

func main() {
    // 连接clickhouse
    ch, err := clickhouse.Open("tcp://127.0.0.1:9000?debug=true")
    if err != nil {
        fmt.Printf("Failed to connect to clickhouse: %v\n", err)
        return
    }
    defer ch.Close()

    // 解析json数据
    var users []User
    if err := json.Unmarshal([]byte(jsonData), &users); err != nil {
        fmt.Printf("Failed to parse json data: %v\n", err)
        return
    }

    // 获取json数据中的所有键
    keys := make([]string, 0, len(users[0]))
    for key := range users[0] {
        keys = append(keys, key)
    }

    // 构造插入语句
    stmt := fmt.Sprintf("INSERT INTO mytable (%s) VALUES", strings.Join(keys, ", "))

    // 插入json数据
    insert, err := ch.Insert(stmt)
    if err != nil {
        fmt.Printf("Failed to create insert statement: %v\n", err)
        return
    }
    for _, user := range users {
        values := make([]interface{}, 0, len(keys))
        for _, key := range keys {
            values = append(values, user[key])
        }
        if err := insert.Send(values); err != nil {
            fmt.Printf("Failed to send data: %v\n", err)
            return
        }
    }
    if err := insert.Close(); err != nil {
        fmt.Printf("Failed to close insert statement: %v\n", err)
        return
    }
}