Select ClickHouse
API
For the full list of supported methods, see SelectQuery.
db.NewSelect().
With("cte_name", subquery).
WithAlias("name", "? + ?", args...).
Model(&strct).
Model(&slice).
Column("col1", "col2"). // quotes column names
ColumnExpr("col1, col2"). // arbitrary unsafe expression
ColumnExpr("count(*)").
ColumnExpr("count(?)", ch.Ident("id")).
ColumnExpr("(?) AS alias", subquery).
ExcludeColumn("col1"). // all columns except col1
ExcludeColumn("*"). // exclude all columns
Table("table1", "table2"). // quotes table names
TableExpr("table1 AS t1"). // arbitrary unsafe expression
TableExpr("(?) AS alias", subquery).
ModelTableExpr("table1 AS t1"). // overrides model table name
Sample("1000000"). // sample 1 million rows
Join("JOIN table2 AS t2 ON t2.id = t1.id").
Join("LEFT JOIN table2 AS t2").JoinOn("t2.id = t1.id").
Where("id = ?", 123).
Where("name LIKE ?", "my%").
Where("? = 123", ch.Ident("id")).
Where("id IN (?)", ch.In([]int64{1, 2, 3})).
Where("id IN (?)", subquery).
Where("FALSE").WhereOr("TRUE").
WhereGroup(" AND ", func(q *ch.SelectQuery) *ch.SelectQuery {
return q.WhereOr("id = 1").
WhereOr("id = 2")
}).
Group("col1", "col2"). // quotes column names
GroupExpr("lower(col1)"). // arbitrary unsafe expression
Order("col1 ASC", "col2 DESC"). // quotes column names
OrderExpr("col1 ASC NULLS FIRST"). // arbitrary unsafe expression
Having("column_name > ?", 123).
Limit(100).
Offset(100).
Setting("prefer_column_name_to_alias = 1").
Scan(ctx)
Example
To select data from ClickHouse, define a model and use SelectQuery:
type Span struct {
ID uint64
Name string
}
span := new(Span)
err := db.NewSelect().Model(span).Where("id = ?", 123).Scan(ctx)
To select multiple rows:
var spans []Span
err := db.NewSelect().Model(&spans).Limit(1000).Scan(ctx)
You can also select into a map:
var m map[string]interface{}
err := db.NewSelect().Model((*Span)(nil)).Where("id = ?", 123).Scan(ctx, &m)
Columnar
If you want to select whole columns from ClickHouse, you can use the ,columnar
option:
type Model struct {
ch.CHModel `ch:",columnar"`
Col1 []string
Col2 []uint64
Col3 []time.Time
}
model := new(Model)
err := db.NewSelect().Model(model).Limit(1000).Scan(ctx)
Count rows
go-clickhouse provides Count helper to generate count()
queries:
count, err := db.NewSelect().Model((*User)(nil)).Count(ctx)
Because selecting and counting rows is a common operation, the client also provides ScanAndCount:
var users []User
count, err := db.NewSelect().Model(&users).Limit(20).ScanAndCount(ctx)
if err != nil {
panic(err)
}
fmt.Println(users, count)
Subqueries
You can use go-clickhouse queries as a subquery:
subq := db.NewSelect().Model((*Book)(nil)).Where("author_id = ?", 1)
err := db.NewSelect().Model().TableExpr("(?) AS book", subq).Scan(ctx, &books)
SELECT * FROM (
SELECT "book"."id", "book"."title", "book"."text"
FROM "books" AS "book"
WHERE (author_id = 1)
) AS book