ch

README

ch — ClickHouse constructs

FINAL, PREWHERE, LIMIT BY, ARRAY JOIN, conditional aggregates and SETTINGS.

This is not a ClickHouse client, and the README should not be read as claiming one. It exists to prove the extension points carry a second engine, and it has never been executed against a server — say so rather than implying coverage. It is also where a capability is declined by not implementing an interface: clickhouse.Dialect omits SupportsReturning entirely, so a RETURNING call does not compile rather than failing at runtime.

Full documentation

The page for this package is docs/ch/README.md, with runnable snippets — every one of them copied from a test whose expected output go test checks. The documentation index is ../docs/README.md and the repository README is ../README.md.

Documentation

Overview

Package ch holds the ClickHouse dialect and what only ClickHouse has: PREWHERE, FINAL, SAMPLE, ARRAY JOIN, LIMIT BY, SETTINGS, the argMax and uniq aggregates, the -If combinator and the toStartOf* time functions.

Four ClickHouse rules a library author must know are in the builder spec: no transactions, LEFT JOIN misses give defaults not NULL under the default join_use_nulls, UPDATE and DELETE are asynchronous mutations, and inserts are batches.

Index

• Constants
• Variables
• func ArgMax[T, U any](value sqlb.Expr[T], by sqlb.Expr[U]) sqlb.Expr[T]
• func Count() sqlb.Expr[uint64]
• func CountIf(cond sqlb.Predicate) sqlb.Expr[uint64]
• func Is(d sqlb.Dialect) bool
• func SumIf[T any](e sqlb.Expr[T], cond sqlb.Predicate) sqlb.Expr[T]
• func ToStartOfDay(e sqlb.Expr[time.Time]) sqlb.Expr[time.Time]
• func ToStartOfHour(e sqlb.Expr[time.Time]) sqlb.Expr[time.Time]
• func Uniq[T any](e sqlb.Expr[T]) sqlb.Expr[uint64]
• func UniqExact[T any](e sqlb.Expr[T]) sqlb.Expr[uint64]
• type Dialect
• type SelectClause
  • func ArrayJoin[T any](col sqlb.Expr[[]T], alias string) (SelectClause, sqlb.Expr[T])
  • func Final() SelectClause
  • func LimitBy(n int, exprs ...sqlb.SQLer) SelectClause
  • func Prewhere(preds ...sqlb.Predicate) SelectClause
  • func Sample(ratio float64) SelectClause
  • func Settings(settings ...Setting) SelectClause
• type Setting
  • func JoinUseNulls() Setting

Constants

const (
	SettingJoinUseNulls = "join_use_nulls"
	SettingMaxThreads   = "max_threads"
)

Setting names. Named because a typo in a setting is silently ignored by the server, which is the worst failure mode.

const DialectClickHouse = sqlb.DialectClickHouse

DialectClickHouse is the engine name reported by the dialect. It is an alias for the core constant, kept so an engine guard in this package reads without an extra import.

Variables

var ErrEmptySetting = errors.New("sqlb/ch: SETTINGS needs at least one named setting")

ErrEmptySetting is recorded for SETTINGS with no settings, or a Setting{} with no name: Setting has exported fields and is written as a literal, and either rendered SQL ClickHouse rejects.

Functions

func ArgMax

func ArgMax[T, U any](value sqlb.Expr[T], by sqlb.Expr[U]) sqlb.Expr[T]

ArgMax renders argMax(value, by): the value from the row where by is largest. Typed as the value's type.

func Count

func Count() sqlb.Expr[uint64]

Count renders count().

func CountIf

func CountIf(cond sqlb.Predicate) sqlb.Expr[uint64]

CountIf renders countIf(cond).

func Is

func Is(d sqlb.Dialect) bool

Is reports whether d is the ClickHouse dialect.

func SumIf

func SumIf[T any](e sqlb.Expr[T], cond sqlb.Predicate) sqlb.Expr[T]

SumIf renders sumIf(e, cond), the -If combinator on sum.

func ToStartOfDay

func ToStartOfDay(e sqlb.Expr[time.Time]) sqlb.Expr[time.Time]

ToStartOfDay truncates a time to the day.

func ToStartOfHour

func ToStartOfHour(e sqlb.Expr[time.Time]) sqlb.Expr[time.Time]

ToStartOfHour truncates a time to the hour. Typed time.Time in and out, so a bucket cannot be compared with a string.

func Uniq

func Uniq[T any](e sqlb.Expr[T]) sqlb.Expr[uint64]

Uniq renders uniq(e), the approximate distinct count.

func UniqExact

func UniqExact[T any](e sqlb.Expr[T]) sqlb.Expr[uint64]

UniqExact renders uniqExact(e), the exact distinct count.

Types

type Dialect

type Dialect = clickhouse.Dialect

Dialect is sqlb.ClickHouseDialect.

The dialect lives in the core beside PostgresDialect and SQLiteDialect, so all three are in one place and a caller naming an engine does not have to import that engine's construct package. This alias exists so ch.Dialect{} still reads naturally next to ch.Final() and the rest.

type SelectClause

type SelectClause = sqlb.Clause[Dialect, sqlb.SelectKind]

SelectClause is a clause for a SELECT on ClickHouse: an alias for the core's typed clause, so a statement of another dialect refuses it at compile time. ClickHouse constructs here are all SELECT clauses.

func ArrayJoin

func ArrayJoin[T any](col sqlb.Expr[[]T], alias string) (SelectClause, sqlb.Expr[T])

ArrayJoin renders ARRAY JOIN col AS alias and returns the element expression, typed T, in the same call so the element type is checked.

func Final

func Final() SelectClause

Final renders FINAL after the driving table, forcing a ReplacingMergeTree to collapse rows at read time.

func LimitBy

func LimitBy(n int, exprs ...sqlb.SQLer) SelectClause

LimitBy renders LIMIT n BY exprs after ORDER BY: the first n rows per group, which is how "latest event per user" is written without a window function.

func Prewhere

func Prewhere(preds ...sqlb.Predicate) SelectClause

Prewhere renders PREWHERE (preds) before WHERE. ClickHouse reads only the PREWHERE columns first and filters before reading the rest, which is the cheapest filter position for a wide table.

func Sample

func Sample(ratio float64) SelectClause

Sample renders SAMPLE ratio, for approximate queries over a sampled table.

func Settings

func Settings(settings ...Setting) SelectClause

Settings renders SETTINGS name = value, ... at the tail. Values are bound.

type Setting

type Setting struct {
	Name  string
	Value any
}

Setting is one SETTINGS name = value pair. A slice keeps render order deterministic; a map would not.

func JoinUseNulls

func JoinUseNulls() Setting

JoinUseNulls is the setting that makes a LEFT JOIN miss a NULL instead of a default value, so Nullable[T] scans as intended.