我的开源
go查询clickhouse数据库
背景
初体验
环境配置
安装gopls
go install -v golang.org/x/tools/gopls@latest
会安装在 GOPATH下，可以通过 go env查看，默认在 ~/go/中。
go.mod配置
设置环境变量
export GO111MODULE="on"
export GOPROXY="https://mirrors.aliyun.com/goproxy/"
配置go.mod，内容如下
module clickhouse_demo
go 1.19
require github.com/ClickHouse/clickhouse-go/v2 latest
执行
go mod tidy
连接数据库
package main
import (
"fmt"
"github.com/ClickHouse/clickhouse-go/v2"
)
func main() {
conn, err := clickhouse.Open(&clickhouse.Options{
Addr: []string{"127.0.0.1:9000"},
Auth: clickhouse.Auth{
Database: "system",
Username: "default",
Password: "",
},
})
if err != nil {
fmt.Print("err 1")
}
v, err := conn.ServerVersion()
if err != nil {
fmt.Print("err 2")
}
fmt.Println(v.String())
}
结果
ClickHouse (box) server version 23.6.2 revision 54464 (timezone Asia/Shanghai)
使用sqlx连接
模块
require github.com/jmoiron/sqlx v1.3.5
代码
package main
import (
"fmt"
_ "github.com/ClickHouse/clickhouse-go"
"github.com/jmoiron/sqlx"
)
func main() {
dsn := "clickhouse://localhost:9000?database=dbname&password=dbpass&dial_timeout=200ms&max_execution_time=60"
db, err := sqlx.Connect("clickhouse", dsn)
if err != nil {
fmt.Printf("connect DB failed, err:%v\n", err)
return
}
db.SetMaxOpenConns(20) // 设置数据库的最大打开连接数。
db.SetMaxIdleConns(10) // 设置空闲连接池中的最大连接数。
if err != nil {
fmt.Println("err 1")
} else {
fmt.Println(db)
}
}
结果
$ go run clickhouse.go
&{0xc00009a8f0 clickhouse false 0xc0000949f0}
执行查询
sql := "select name from users limit 10;"
rows, err := connect.Queryx(sql)
if err != nil {
fmt.Println("connect DB Queryx, err:%v\n", err)
return
}
var list []interface{}
for rows.Next() {
result := map[string]interface{}{}
err := rows.MapScan(result)
if err != nil {
fmt.Println("scan err:%v", err)
return
}
fmt.Println(result)
t, err := json.Marshal(result)
fmt.Println("xxx：%s%v\n", string(t), err)
list = append(list, result)
}
t, err := json.Marshal(list)
fmt.Println("list：%s%v\n", string(t), err)
执行查询2
type StockName struct {
Name string
Cname string
Symbol string
}
symbols := list.New()
stockNames := []StockName{}
connect.Select(&stockNames, "SELECT name, cname, symbol FROM us_stock_name LIMIT 10")
for i := 0; i != len(stockNames)-1; i = i + 1 {
symbols.PushBack(stockNames[i].Symbol)
}
for j := symbols.Front(); j != nil; j = j.Next() {
fmt.Println(j.Value)
}