Explore documentation
  1. Welcome to Warehouse 👋
  2. Ingesting data
    1. Ingesting events
    2. S3 vs NVMe hosted data
    3. Host events in your S3 bucket
  3. Querying data
    1. Querying events & time series
    2. Writing ClickHouse SQL queries
      1. -Merge & -State aggregators
      2. Writing fast s3Cluster queries
    3. Query speeds
    4. Ad-hoc SQL API
    5. Generate client-facing APIs
    6. Visualizing data
  4. Vector embeddings
    1. Introduction to embeddings
    2. Using built-in embeddings
    3. Bringing your own embeddings
    4. Data structures and indices
  5. Use cases
    1. Retrieval Augmented Generation
  6. Management
    1. Controlling costs

-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: -Merge and -State combinators

Interested in getting a number of events?

When querying events, this is as simple as

Querying the number of JSON events
SELECT COUNT(*)
FROM {{source}}

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

Querying the number of JSON events using time series
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.

Need help?

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

Previous article

Querying events & time series

Next article

Writing fast s3Cluster queries