ClickHouse使用之三 ——go程序操作
1. 得到一个clickhouse连接：
import (
"context"
"fmt"
"log"
"github.com/ClickHouse/clickhouse-go/v2"
"github.com/ClickHouse/clickhouse-go/v2/lib/driver"
)
func connect() (driver.Conn, error) {
var (
ctx = context.Background()
conn, err = clickhouse.Open(&clickhouse.Options{
Addr: []string{"localhost:19000"},
Auth: clickhouse.Auth{
Database: "default",
Username: "default",
Password: "123456",
},
ClientInfo: clickhouse.ClientInfo{
Products: []struct {
Name string
Version string
}{
{Name: "an-example-go-client", Version: "0.1"},
},
},
Debugf: func(format string, v ...interface{}) {
fmt.Printf(format, v)
},
// TLS: &tls.Config{
// InsecureSkipVerify: true,
// },
})
)
if err != nil {
return nil, err
}
if err := conn.Ping(ctx); err != nil {
if exception, ok := err.(*clickhouse.Exception); ok {
fmt.Printf("Exception [%d] %s \n%s\n", exception.Code, exception.Message, exception.StackTrace)
}
return nil, err
}
return conn, nil
}不采用tls安全层的连接代码
2. 插入一行记录：
type TxHash struct {
GameName string `json:"gameName" form:"gameName" gorm:"column:game_name;comment:;size:255;"`
Address string `json:"Address" form:"Address" gorm:"column:Address;comment:;size:42;"`
TxHash string `json:"txHash" form:"txHash" gorm:"column:tx_hash;comment:;size:255;"`
TxType string `json:"txType" form:"txType" gorm:"column:tx_type;comment:交易类型: mint--创造Nft/transfer--交易;size:30;"`
}
func insert(conn driver.Conn, h TxHash) {
// 创建插入语句
query := "INSERT INTO tx_hashes (game_name, tx_hash, Address, tx_type) VALUES (?, ?, ?, ?)"
// 执行插入操作
err := conn.Exec(context.Background(), query, h.GameName, h.TxHash, h.Address, h.TxType)
if err != nil {
log.Fatalf("Error executing insert statement: %v", err)
}
log.Println("Record inserted successfully")
}3. 获取所有数据：
func all(conn driver.Conn) []TxHash {
var txHashes []TxHash
rows, err := conn.Query(context.Background(), "SELECT game_name, tx_hash, Address, tx_type FROM tx_hashes")
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var (
gameName, txHash, address, txType string
)
if err := rows.Scan(
&gameName,
&txHash,
&address,
&txType,
); err != nil {
log.Fatal(err)
}
txHashes = append(txHashes, TxHash{GameName: gameName, TxHash: txHash, Address: address, TxType: txType})
}
return txHashes
}4. 条件筛选：
func getByGameName(conn driver.Conn, gameName string) []TxHash {
var txHashes []TxHash
rows, err := conn.Query(context.Background(), "SELECT game_name, tx_hash, Address, tx_type FROM tx_hashes where game_name=?", gameName)
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var (
gameName, txHash, address, txType string
)
if err := rows.Scan(
&gameName,
&txHash,
&address,
&txType,
); err != nil {
log.Fatal(err)
}
txHashes = append(txHashes, TxHash{GameName: gameName, TxHash: txHash, Address: address, TxType: txType})
}
return txHashes
}5. 完整代码：
package main
import (
"context"
"fmt"
"log"
"github.com/ClickHouse/clickhouse-go/v2"
"github.com/ClickHouse/clickhouse-go/v2/lib/driver"
)
type TxHash struct {
GameName string `json:"gameName" form:"gameName" gorm:"column:game_name;comment:;size:255;"`
Address string `json:"Address" form:"Address" gorm:"column:Address;comment:;size:42;"`
TxHash string `json:"txHash" form:"txHash" gorm:"column:tx_hash;comment:;size:255;"`
TxType string `json:"txType" form:"txType" gorm:"column:tx_type;comment:交易类型: mint--创造Nft/transfer--交易;size:30;"`
}
func insert(conn driver.Conn, h TxHash) {
// 创建插入语句
query := "INSERT INTO tx_hashes (game_name, tx_hash, Address, tx_type) VALUES (?, ?, ?, ?)"
// 执行插入操作
err := conn.Exec(context.Background(), query, h.GameName, h.TxHash, h.Address, h.TxType)
if err != nil {
log.Fatalf("Error executing insert statement: %v", err)
}
log.Println("Record inserted successfully")
}
func all(conn driver.Conn) []TxHash {
var txHashes []TxHash
rows, err := conn.Query(context.Background(), "SELECT game_name, tx_hash, Address, tx_type FROM tx_hashes")
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var (
gameName, txHash, address, txType string
)
if err := rows.Scan(
&gameName,
&txHash,
&address,
&txType,
); err != nil {
log.Fatal(err)
}
txHashes = append(txHashes, TxHash{GameName: gameName, TxHash: txHash, Address: address, TxType: txType})
}
return txHashes
}
func getByGameName(conn driver.Conn, gameName string) []TxHash {
var txHashes []TxHash
rows, err := conn.Query(context.Background(), "SELECT game_name, tx_hash, Address, tx_type FROM tx_hashes where game_name=?", gameName)
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var (
gameName, txHash, address, txType string
)
if err := rows.Scan(
&gameName,
&txHash,
&address,
&txType,
); err != nil {
log.Fatal(err)
}
txHashes = append(txHashes, TxHash{GameName: gameName, TxHash: txHash, Address: address, TxType: txType})
}
return txHashes
}
func main() {
conn, err := connect()
if err != nil {
panic((err))
}
allBefore := all(conn)
fmt.Printf("allBefore tx_hases: %d\n", len(allBefore))
gameName := "Gamemmmmmmm"
newHash := TxHash{GameName: gameName, Address: "address", TxHash: "txhash", TxType: "xxxxx"}
insert(conn, newHash)
allAfter := all(conn)
fmt.Printf("allAfter tx_hases: %d\n", len(allAfter))
gameNames := getByGameName(conn, gameName)
for _, gn := range gameNames {
if gn.GameName != gameName {
panic(gn)
}
}
}
func connect() (driver.Conn, error) {
var (
ctx = context.Background()
conn, err = clickhouse.Open(&clickhouse.Options{
Addr: []string{"localhost:19000"},
Auth: clickhouse.Auth{
Database: "default",
Username: "default",
Password: "123456",
},
ClientInfo: clickhouse.ClientInfo{
Products: []struct {
Name string
Version string
}{
{Name: "an-example-go-client", Version: "0.1"},
},
},
Debugf: func(format string, v ...interface{}) {
fmt.Printf(format, v)
},
// TLS: &tls.Config{
// InsecureSkipVerify: true,
// },
})
)
if err != nil {
return nil, err
}
if err := conn.Ping(ctx); err != nil {
if exception, ok := err.(*clickhouse.Exception); ok {
fmt.Printf("Exception [%d] %s \n%s\n", exception.Code, exception.Message, exception.StackTrace)
}
return nil, err
}
return conn, nil
}
