ClickHouse: Go (clickhouse-go v2, native protocol)

Loads the demo dataset with a batch insert over the ClickHouse native protocol (TLS port 9440) and prints revenue per country.

The only change

# CLICKHOUSE_ADDR
 clickhouse.internal:9440
 warehouse.internal:9440
# CLICKHOUSE_USER / CLICKHOUSE_PASSWORD
 ClickHouse user
 MachWarehouse user

Run

go mod tidy
CLICKHOUSE_ADDR=warehouse.internal:9440 CLICKHOUSE_PASSWORD=... go run .

Library: github.com/ClickHouse/clickhouse-go/v2 v2.50.0 (Go 1.27). go mod tidy adds github.com/shopspring/decimal, which clickhouse-go uses for Decimal.

Source

The program as it is kept and tested in the Mach Database Warehouse repository; highlighted lines read the connection setting.

main.go

// Command clickhouse-go loads the demo events over the ClickHouse native
// protocol and prints revenue per country. Plain clickhouse-go code: nothing
// here knows about MachWarehouse.
package main

import (
  "context"
  "crypto/tls"
  "encoding/csv"
  "fmt"
  "log"
  "os"
  "strconv"
  "time"

  "github.com/ClickHouse/clickhouse-go/v2"
  "github.com/shopspring/decimal"
)

func env(key, def string) string {
  if v, ok := os.LookupEnv(key); ok {
    return v
  }
  return def
}

func main() {
  ctx := context.Background()
  conn, err := clickhouse.Open(&clickhouse.Options{
    Addr: []string{env("CLICKHOUSE_ADDR", "localhost:9440")},
    Auth: clickhouse.Auth{
      Database: env("CLICKHOUSE_DATABASE", "default"),
      Username: env("CLICKHOUSE_USER", "default"),
      Password: os.Getenv("CLICKHOUSE_PASSWORD"),
    },
    TLS: &tls.Config{},
  })
  if err != nil {
    log.Fatal(err)
  }
  defer conn.Close()

  ddl, err := os.ReadFile("../../dataset/clickhouse.sql")
  if err != nil {
    log.Fatal(err)
  }
  if err := conn.Exec(ctx, string(ddl)); err != nil {
    log.Fatal(err)
  }

  f, err := os.Open("../../dataset/events.csv")
  if err != nil {
    log.Fatal(err)
  }
  defer f.Close()
  records, err := csv.NewReader(f).ReadAll()
  if err != nil {
    log.Fatal(err)
  }
  batch, err := conn.PrepareBatch(ctx, "INSERT INTO events")
  if err != nil {
    log.Fatal(err)
  }
  for _, r := range records[1:] {
    day, _ := time.Parse(time.DateOnly, r[0])
    at, _ := time.Parse(time.DateTime, r[1])
    user, _ := strconv.ParseUint(r[2], 10, 64)
    revenue, _ := decimal.NewFromString(r[5])
    if err := batch.Append(day, at, user, r[3], r[4], revenue); err != nil {
      log.Fatal(err)
    }
  }
  if err := batch.Send(); err != nil {
    log.Fatal(err)
  }

  rows, err := conn.Query(ctx, "SELECT country, count() AS events, toString(sum(revenue)) AS revenue FROM events GROUP BY country ORDER BY sum(revenue) DESC")
  if err != nil {
    log.Fatal(err)
  }
  defer rows.Close()
  for rows.Next() {
    var country, revenue string
    var events uint64
    if err := rows.Scan(&country, &events, &revenue); err != nil {
      log.Fatal(err)
    }
    fmt.Printf("%s\t%d\t%s\n", country, events, revenue)
  }
  if err := rows.Err(); err != nil {
    log.Fatal(err)
  }
}

go.mod

module example.com/machwarehouse/clickhouse-go

go 1.27

require github.com/ClickHouse/clickhouse-go/v2 v2.50.0