Open-source APMGolang ORMGolang HTTP routerBlog
Golang ClickHouse

Golang ClickHouse

Installation

go get github.com/uptrace/go-clickhouse@latest

Connecting to ClickHouse

To connect to ClickHouse:

import "github.com/uptrace/go-clickhouse/ch" db := ch.Connect( // clickhouse://<user>:<password>@<host>:<port>/<database>?sslmode=disable	ch.WithDSN("clickhouse://localhost:9000/default?sslmode=disable"), )

To see the executed queries in the console, install chdebug query hook:

import "github.com/uptrace/go-clickhouse/chdebug" db.AddQueryHook(chdebug.NewQueryHook(	chdebug.WithVerbose(true),	chdebug.FromEnv("CHDEBUG"), ))

Now you are ready to execute queries using database/sql API:

res, err := db.ExecContext(ctx, "SELECT 1") var num int err := db.QueryRowContext(ctx, "SELECT 1").Scan(&num)

Or using the query builder:

res, err := db.NewSelect().ColumnExpr("1").Exec(ctx) var num int err := db.NewSelect().ColumnExpr("1").Scan(ctx, &num)

Options

You can specify the following options in a DSN (connection string):

  • ?sslmode=verify-full - enable TLS.
  • ?sslmode=disable - disables TLS.
  • ?dial_timeout=5s - timeout for establishing new connections.
  • ?read_timeout=5s - timeout for socket reads.
  • ?write_timeout=5s - timeout for socket writes.
  • ?timeout=5s - sets all three timeouts described above.

go-clickhouse treats all unknown options as ClickHouse query settings, for example, ?prefer_column_name_to_alias=1 enables the corresponding ClickHouse settingopen in new window.

In addition to DSN, you can also use ch.Optionopen in new window to configure the client:

db := ch.Connect(	ch.WithAddr("localhost:9000"),	ch.WithTLSConfig(&tls.Config{InsecureSkipVerify: true}),	ch.WithUser("test"),	ch.WithPassword("test"),	ch.WithDatabase("test"),	ch.WithTimeout(5 * time.Second),	ch.WithDialTimeout(5 * time.Second),	ch.WithReadTimeout(5 * time.Second),	ch.WithWriteTimeout(5 * time.Second),	ch.WithQuerySettings(map[string]interface{}{ "prefer_column_name_to_alias": 1, }), )

Or use a DSN and options together:

db := ch.Connect(	ch.WithDSN("clickhouse://default:@localhost:9000/uptrace?sslmode=verify-full"),	ch.WithTLSConfig(tlsConfig), )

Server timezone

You can avoid a lot of confusion by configuring ClickHouse to use UTC timezoneopen in new window by changing config.xml:

<?xml version="1.0" ?> <clickhouse> <timezone>UTC</timezone> </clickhouse>

Models

go-clickhouse uses struct-based models to build queries and scan results. A typical model looks like this:

type Span struct {	ch.CHModel `ch:"partition:toYYYYMM(time)"`	ID uint64	Text string `ch:",lc"` // low cardinality column	Time time.Time `ch:",pk"` // ClickHouse primary key to be used in order by }

Having a model, you can create and drop tables:

// Create spans table. res, err := db.NewCreateTable().Model((*Span)(nil)).Exec(ctx) // Drop spans table. res, err := db.NewDropTable().Model((*Span)(nil)).Exec(ctx) // Drop table if they exist and create new tables. err := db.ResetModel(ctx, (*Model1)(nil), (*Model2)(nil))

Insert rows:

// Insert a single span. span := &Span{Name: "admin"} res, err := db.NewInsert().Model(span).Exec(ctx) // Insert multiple spans (bulk-insert). spans := []Span{span1, span2} res, err := db.NewInsert().Model(&spans).Exec(ctx)

And select rows scanning the results:

// Select a span by an id. span := new(Span) err := db.NewSelect().Model(span).Where("id = ?", 1).Scan(ctx) // Select first 10 spans. var spans []Span err := db.NewSelect().Model(&spans).OrderExpr("id ASC").Limit(10).Scan(ctx)

Scanning query results

When it comes to scanning query results, go-clickhouse is very flexible and allows scanning into structs:

span := new(Span) err := db.NewSelect().Model(span).Limit(1).Scan(ctx)

Into scalars:

var id int64 var name string err := db.NewSelect().Model((*Span)(nil)).Column("id", "name").Limit(1).Scan(ctx, &id, &name)

Into a map[string]interface{}:

var m map[string]interface{} err := db.NewSelect().Model((*Span)(nil)).Limit(1).Scan(ctx, &m)

And into slices of the types above:

var spans []Span err := db.NewSelect().Model(&spans).Limit(1).Scan(ctx) var ids []int64 var names []string err := db.NewSelect().Model((*Span)(nil)).Column("id", "name").Limit(1).Scan(ctx, &ids, &names) var ms []map[string]interface{} err := db.NewSelect().Model((*Span)(nil)).Scan(ctx, &ms)

What's next

By now, you should have basic understanding of the API. Next, learn how to define models and write queries.

Last Updated: