go-zero整合单机版ClickHouse并实现增删改查

go-zero整合单机版ClickHouse并实现增删改查

本教程基于go-zero微服务入门教程，项目工程结构同上一个教程。

本教程主要实现go-zero框架整合单机版ClickHouse，并暴露接口实现对ClickHouse数据的增删改查。

go-zero微服务入门教程：https://blog.csdn.net/u011019141/article/details/136233473
本文源码：https://gitee.com/songfayuan/go-zero-demo （教程源码分支：5.zero整合单机ClickHouse）

准备工作

• 如不熟悉go-zero项目的，请先查看上一篇go-zero微服务入门教程。
• 请自行安装好单机版ClickHouse，建议采用docker安装。

安装依赖

项目工程父级目录下执行如下指令安装依赖：

# 下载安装clickhouse-go
go get github.com/ClickHouse/clickhouse-go/v2
# 下载安装otel/sdk/trace
go get -u go.opentelemetry.io/otel/sdk/trace

rpc新增ClickHouse配置

以下操作在rpc模块执行。

sys.yaml

sys.yaml配置文件新增ClickHouse配置信息，如下：

# ClickHouse配置
ClickHouse:Addr:- "192.168.2.204:9000"Auth:Database: dsmsUsername: defaultPassword:Table: http_captureInterval: 3MaxNum: 1000MaxChunkBytes: 10485760Debug: falseDatasource: clickhouse://default:@192.168.2.204:9000/dsms?debug=false

config.go

config.go文件中新增ClickHouseConf配置信息，如下：

package configimport ("github.com/zeromicro/go-zero/zrpc""go-zero-demo/common/task/kafkaconf"
)type Config struct {zrpc.RpcServerConfClickHouse ClickHouseConf
}type ClickHouseAuthConf struct {Database stringUsername stringPassword string `json:",optional"`
}type ClickHouseConf struct {Addr []stringAuth ClickHouseAuthConfOpt1 struct {MaxIdleConns int `json:",optional"`MaxOpenConns int `json:",optional"`} `json:",optional"`Opt2 struct {MaxIdleConns int `json:",optional"`MaxOpenConns int `json:",optional"`} `json:",optional"`Table   stringColumns []string `json:",optional"`Debug   bool     `json:",default=false"`Datasource string `json:",optional,default="`
}

servicecontext.go

servicecontext.go文件新增ClickHouse配置信息，完整代码如下：

package serverimport ("github.com/ClickHouse/clickhouse-go/v2""go-zero-demo/rpc/sys/internal/config""log""time"
)type ServiceContext struct {Config      config.ConfigClickhouseConn clickhouse.Conn
}func NewServiceContext(c config.Config) *ServiceContext {return &ServiceContext{Config:      c,ClickhouseConn: NewClickHouseConn(c.ClickHouse),}
}// NewClickHouseConn 创建clickhouse链接 【clickhouse-go】
func NewClickHouseConn(ckConfig config.ClickHouseConf) clickhouse.Conn {maxIdleConns := 20if ckConfig.Opt2.MaxIdleConns > 0 {maxIdleConns = ckConfig.Opt2.MaxIdleConns}maxOpenConns := 2000if ckConfig.Opt2.MaxOpenConns > 0 {maxOpenConns = ckConfig.Opt2.MaxOpenConns}conn, err := clickhouse.Open(&clickhouse.Options{Addr: ckConfig.Addr,Auth: clickhouse.Auth{Database: ckConfig.Auth.Database,Username: ckConfig.Auth.Username,Password: ckConfig.Auth.Password,},Settings: clickhouse.Settings{"max_execution_time": 60,},DialTimeout:  30 * time.Second,MaxIdleConns: maxIdleConns,MaxOpenConns: maxOpenConns,Debug:        ckConfig.Debug,})if err != nil {log.Fatalf("error: 启动clickhouse client失败, %v", err)}return conn
}

编写rpc服务

修改sys.proto文件

新增ClickHouse操作请求的配置，如下：

message ClickhouseReq{string name = 1;string nickName = 2;string password = 3;string email = 4;
}message  ClickhouseResp{string name = 1;string nickName = 2;string password = 3;string email = 4;
}service Sys{// clickhouse增删改查rpc ClickhouseAdd(ClickhouseReq)returns(ClickhouseResp);rpc ClickhouseDelete(ClickhouseReq)returns(ClickhouseResp);rpc ClickhouseUpdate(ClickhouseReq)returns(ClickhouseResp);rpc ClickhouseGet(ClickhouseReq)returns(ClickhouseResp);
}

用goctl生成rpc代码

生成方法同上篇文章，自行查看。

编写API Gateway代码

编写api文件

clickhouse.api

在api目录下创建新目录doc/clickhouse，在clickhouse目录下创建clickhouse.api文件。

syntax = "v1"info(title: "clickhouse操作相关"desc: "clickhouse操作相关"author: "宋发元"
)type (ApiClickhouseReq {Name     string `json:"name"`NickName string `json:"nickName"`Password string `json:"password,optional"`Email    string `json:"email"`}ApiClickhouseResp {Code    int64       `json:"code"`Message string      `json:"message"`Data    ApiClickhouseReq `json:"data"`}ApiClickhouseGetReq {Key string `form:"key"`}
)@server (group : clickhouse/testprefix : /clickhouse/test
)service admin-api{@doc(summary : "Clickhouse新增数据")@handler ClickhouseAddpost /clickhouseAdd(ApiClickhouseReq)returns(ApiClickhouseResp)@doc(summary : "Clickhouse删除数据")@handler ClickhouseDeletepost /clickhouseDelete(ApiClickhouseReq)returns(ApiClickhouseResp)@doc(summary : "Clickhouse修改数据")@handler ClickhouseUpdatepost /clickhouseUpdate(ApiClickhouseReq)returns(ApiClickhouseResp)@doc(summary : "Clickhouse查询数据")@handler ClickhouseGetget /clickhouseGet(ApiClickhouseGetReq)returns(ApiClickhouseResp)
}

admin.api

在api/doc/admin.api文件添加配置信息。

import "clickhouse/clickhouse.api"

用goctl生成API Gateway代码

生成方法同上篇文章，自行查看。但是此处要基于admin.api文件去生成代码，如果基于clickhouse.api生成，则生成的代码只有clickhouse.api定义的接口代码，其他api文件定义的接口代码不被生成。

修改API Gateway代码调用rpc服务

clickhouseaddlogic.go

修改api/internal/logic/clickhouse/test/clickhouseaddlogic.go里的ClickhouseAdd方法，如下：

func (l *ClickhouseAddLogic) ClickhouseAdd(req *types.ApiClickhouseReq) (resp *types.ApiClickhouseResp, err error) {res, err := l.svcCtx.Sys.ClickhouseAdd(l.ctx, &sysclient.ClickhouseReq{Name:     req.Name,NickName: req.NickName,Password: req.Password,Email:    req.Email,})if err != nil {reqJson, _ := json.Marshal(res)logx.WithContext(l.ctx).Errorf("新增Clickhouse信息失败，请求参数：%s，异常信息：%s", reqJson, err.Error())return nil, rpcerror.New(err)}return &types.ApiClickhouseResp{Code:    200,Message: "新增成功",Data: types.ApiClickhouseReq{Name:     req.Name,NickName: req.NickName,Password: req.Password,Email:    req.Email,},}, nil
}

clickhousedeletelogic.go

修改api/internal/logic/clickhouse/test/clickhousedeletelogic.go里的ClickhouseDelete方法，如下：

func (l *ClickhouseDeleteLogic) ClickhouseDelete(req *types.ApiClickhouseReq) (resp *types.ApiClickhouseResp, err error) {l.svcCtx.Sys.ClickhouseDelete(l.ctx, &sysclient.ClickhouseReq{})return &types.ApiClickhouseResp{Code:    200,Message: "删除成功",Data:    types.ApiClickhouseReq{},}, nil
}

clickhousegetlogic.go

修改api/internal/logic/clickhouse/test/clickhousegetlogic.go里的ClickhouseGet方法，如下：

func (l *ClickhouseGetLogic) ClickhouseGet(req *types.ApiClickhouseGetReq) (resp *types.ApiClickhouseResp, err error) {param := &sysclient.ClickhouseReq{}copier.Copy(param, req)getRes, err := l.svcCtx.Sys.ClickhouseGet(l.ctx, param)if err != nil {resJson, _ := json.Marshal(getRes)logx.WithContext(l.ctx).Errorf("获取数据测试：操作失败，请求参数param = %s，异常信息errMsg = %s", resJson, err.Error())return nil, rpcerror.New(err)}return &types.ApiClickhouseResp{Code:    200,Message: "操作成功",Data: types.ApiClickhouseReq{Name:     getRes.Name,NickName: getRes.NickName,Password: getRes.Password,Email:    getRes.Email,},}, nil
}

clickhouseupdatelogic.go

修改api/internal/logic/clickhouse/test/clickhouseupdatelogic.go里的ClickhouseUpdate方法，如下：

func (l *ClickhouseUpdateLogic) ClickhouseUpdate(req *types.ApiClickhouseReq) (resp *types.ApiClickhouseResp, err error) {updateRes, err := l.svcCtx.Sys.ClickhouseUpdate(l.ctx, &sysclient.ClickhouseReq{Name:     req.Name,NickName: req.NickName,Password: req.Password,Email:    req.Email,})if err != nil {resJson, _ := json.Marshal(updateRes)logx.WithContext(l.ctx).Errorf("Clickhouse更新数据测试：操作失败，请求参数param = %s，异常信息errMsg = %s", resJson, err.Error())return nil, rpcerror.New(err)}return &types.ApiClickhouseResp{Code:    200,Message: "操作成功",Data: types.ApiClickhouseReq{Name:     updateRes.Name,NickName: updateRes.NickName,Password: updateRes.Password,Email:    updateRes.Email,},}, nil
}

修改rpc代码调用crud代码

clickhouseaddlogic.go

• 修改rpc/sys/internal/logic/clickhouseaddlogic.go，如下内容：

// clickhouse增删改查
func (l *ClickhouseAddLogic) ClickhouseAdd(in *sysclient.ClickhouseReq) (*sysclient.ClickhouseResp, error) {// 创建表createTable(l.ctx, l.svcCtx.ClickhouseConn)// 插入数据insertData(l.ctx, l.svcCtx.ClickhouseConn, "Alice", "Ally", "password123", "[e-mail]")insertData(l.ctx, l.svcCtx.ClickhouseConn, "Bob", "Bobby", "password456", "[e-mail]")// 批量插入数据batchInsertData(l.ctx, l.svcCtx.ClickhouseConn, []User{{"Alice", "Ally", "password123", "[e-mail]"},{"Bob", "Bobby", "password456", "[e-mail]"},{"Charlie", "Char", "password789", "[e-mail]"},})return &sysclient.ClickhouseResp{}, nil
}// 创建表
func createTable(ctx context.Context, conn clickhouse.Conn) {query := `CREATE TABLE IF NOT EXISTS demo (Name String,NickName String,Password String,Email String) ENGINE = MergeTree()ORDER BY Name`err := conn.Exec(ctx, query)if err != nil {log.Fatal(err)}fmt.Println("成功创建demo表...")
}// 插入数据
func insertData(ctx context.Context, conn clickhouse.Conn, name, nickName, password, email string) {batch, err := conn.PrepareBatch(ctx, "INSERT INTO demo (Name, NickName, Password, Email)")if err != nil {log.Fatal(err)}err = batch.Append(name, nickName, password, email)if err != nil {log.Fatal(err)}err = batch.Send()if err != nil {log.Fatal(err)}fmt.Println("成功插入数据...")
}// 批量插入数据
func batchInsertData(ctx context.Context, conn clickhouse.Conn, users []User) {batch, err := conn.PrepareBatch(ctx, "INSERT INTO demo (Name, NickName, Password, Email)")if err != nil {log.Fatal(err)}for _, user := range users {err := batch.Append(user.Name, user.NickName, user.Password, user.Email)if err != nil {log.Fatal(err)}}err = batch.Send()if err != nil {log.Fatal(err)}fmt.Println("成功批量插入数据...")
}

clickhousedeletelogic.go

• 修改rpc/sys/internal/logic/clickhousedeletelogic.go，如下内容：

func (l *ClickhouseDeleteLogic) ClickhouseDelete(in *sysclient.ClickhouseReq) (*sysclient.ClickhouseResp, error) {// 删除数据deleteData(l.ctx, l.svcCtx.ClickhouseConn, "Bob")return &sysclient.ClickhouseResp{}, nil
}// 删除数据
func deleteData(ctx context.Context, conn clickhouse.Conn, name string) {query := `ALTER TABLE demo DELETE WHERE Name = ?`err := conn.Exec(ctx, query, name)if err != nil {log.Fatal(err)}fmt.Println("删除数据成功...")
}

clickhouseupdatelogic.go

• 修改rpc/sys/internal/logic/clickhouseupdatelogic.go，如下内容：

func (l *ClickhouseUpdateLogic) ClickhouseUpdate(in *sysclient.ClickhouseReq) (*sysclient.ClickhouseResp, error) {// 更新数据updateData(l.ctx, l.svcCtx.ClickhouseConn, "Alice", "newpassword321")return &sysclient.ClickhouseResp{}, nil
}// 更新数据
func updateData(ctx context.Context, conn clickhouse.Conn, name, newPassword string) {query := `ALTER TABLE demo UPDATE Password = ? WHERE Name = ?`err := conn.Exec(ctx, query, newPassword, name)if err != nil {log.Fatal(err)}fmt.Println("Data updated successfully")
}

clickhousegetlogic.go

• 修改rpc/sys/internal/logic/clickhousegetlogic.go，如下内容：

func (l *ClickhouseGetLogic) ClickhouseGet(in *sysclient.ClickhouseReq) (*sysclient.ClickhouseResp, error) {// 查询数据queryData(l.ctx, l.svcCtx.ClickhouseConn)return &sysclient.ClickhouseResp{}, nil
}// 查询数据
func queryData(ctx context.Context, conn clickhouse.Conn) {rows, err := conn.Query(ctx, "SELECT Name, NickName, Password, Email FROM demo")if err != nil {log.Fatal(err)}defer rows.Close()for rows.Next() {var name, nickName, password, email stringif err := rows.Scan(&name, &nickName, &password, &email); err != nil {log.Fatal(err)}fmt.Printf("Name: %s, NickName: %s, Password: %s, Email: %s\n", name, nickName, password, email)}
}

完整调用演示

最后，在根目录go-zero-demo执行下命令。

go mod tidy

运行rpc服务

运行方法同上篇文章，具体查看教程go-zero微服务入门教程完整调用演示部分。

运行api

运行方法同上篇文章，具体查看教程go-zero微服务入门教程完整调用演示部分。

api调用

以下调用采用curl进行，你也可以用postman调用。

新增接口

 songfayuan@MacBook-Pro  ~  curl -X POST -H "Content-Type: application/json" -d '{"name":"songfayuan","nickName":"宋发元","email":"[e-mail]"}' localhost:8888/clickhouse/test/clickhouseAdd{"code":200,"message":"操作成功","data":{"name":"songfayuan","nickName":"宋发元","password":"","email":"[e-mail]"}}%

删除接口

 songfayuan@MacBook-Pro  ~  curl -X POST -H "Content-Type: application/json" -d '{"name":"songfayuan","nickName":"宋发元6666","email":"[e-mail]"}' localhost:8888/clickhouse/test/clickhouseDelete{"code":200,"message":"操作成功","data":{"name":"","nickName":"","password":"","email":""}}%

修改接口

 songfayuan@MacBook-Pro  ~  curl -X POST -H "Content-Type: application/json" -d '{"name":"songfayuan","nickName":"宋发元6666","email":"[e-mail]"}' localhost:8888/clickhouse/test/clickhouseUpdate{"code":200,"message":"操作成功","data":{"name":"songfayuan","nickName":"宋发元6666","password":"","email":"[e-mail]"}}%

查询接口

 songfayuan@MacBook-Pro  ~  curl "localhost:8888/clickhouse/test/clickhouseGet?key=121212"{"code":200,"message":"操作成功","data":{"name":"songfayuan","nickName":"宋发元6666","password":"","email":"[e-mail]"}}%