ClickHouse

Writes events to a ClickHouse table using its native batch insert. ClickHouse is well suited to this workload since analytics events are append-only, high volume, and usually queried in aggregate.

Requires a clickhouse datastore to supply the connection.

Name should be "clickhouse"

Table Setup

Tilegroxy never issues DDL, you must create the table yourself before starting tilegroxy. This keeps the credentials tilegroxy uses free of schema privileges and leaves partitioning and retention under your control.

The following matches the default column names:

CREATE TABLE tile_events (
    time      DateTime,
    layer     LowCardinality(String),
    z         UInt8,
    x         UInt32,
    y         UInt32,
    user_id   String,
    extra     Map(String, String)
) ENGINE = MergeTree()
ORDER BY (layer, time)

The extra column receives everything selected via fields and extraFields, with values rendered as strings. Using a map instead of dedicated columns means changing which fields you collect never requires a schema migration.

For a table that expires old data automatically, add a TTL:

ALTER TABLE tile_events MODIFY TTL time + INTERVAL 90 DAY

Configuration options:

Also accepts the batching parameters described in Analytics.

Parameter Description Type Required Default

Datastore

The ID of the datastore to use. The datastore must have a type of "clickhouse". Also see the Datastores documentation.

string

Yes

None

Table

The table to insert events into. May be schema qualified. Must be a plain identifier

string

Yes

None

ID

An identifier for this destination, used in logs to attribute analytics messages

string

No

clickhouse

Columns

Overrides for the default column names. Keys are the logical field names: time, layer, z, x, y, user_id, extra

map[string]string

No

None

Fields

Additional attributes to record. See Analytics

string[]

No

None

ExtraFields

Arbitrary additional attributes. See Analytics

map[string]string

No

None

Example:

datastores:
  - name: clickhouse
    id: ch-0
    host: clickhouse.internal
    database: analytics
    user: tilegroxy
    password: env.CLICKHOUSE_PASSWORD

analytics:
  name: clickhouse
  datastore: ch-0
  table: tile_events
  fields:
    - duration
    - bytes
  batch:
    maxSize: 5000
    maxAge: 30

Using an existing table with different column names:

analytics:
  name: clickhouse
  datastore: ch-0
  table: analytics.map_usage
  columns:
    layer: layer_id
    user_id: uid
    extra: attributes