-Merge & -State aggregators

While the SQL syntax for querying JSON events is intuitive, the SQL syntax required to query time series stored on local fast NVMe drives can be a little tricky.

In order to be extremely fast, time series are heavily compressed and leverage ClickHouse's -Merge and -State combinators.

In practice, this means that you need to be mindful about how to query a particular data type based on its definition.

Example: Getting the number of events

When querying JSON events, this is as simple as

SELECT COUNT(*)
FROM {{source}}

If you want to get the same result from time series, you need to query

SELECT countMerge(events_count)
FROM {{source}}

As the physical number of rows stored locally on your NVMe drives will be different to the number of events ingested.

Read more about -State and -Merge aggregators on the official ClickHouse blog.

Where can I see available combinators?

1. Go to Warehouse -> Sources.
2. Select your source and click Configure.
3. Switch to the Time series on NVMe SSD tab.
4. Expand the time series details to see the functions to Query with:

Need help?

We're here for you! Let us know at hello@betterstack.com.