Skip to main content

​
Search in ClickStack and Elastic

ClickHouse is a SQL-native engine, designed from the ground up for high-performance analytical workloads. In contrast, Elasticsearch provides a SQL-like interface, transpiling SQL into the underlying Elasticsearch query DSL — meaning it isn’t a first-class citizen, and feature parity is limited. ClickHouse not only supports full SQL but extends it with a range of observability-focused functions, such as argMax, histogram, and quantileTiming, that simplify querying structured logs, metrics, and traces. For simple log and trace exploration, the ClickStack UI (HyperDX) provides a Lucene-style syntax for intuitive, text-based filtering for field-value queries, ranges, wildcards, and more. This is comparable to the Lucene syntax in Elasticsearch and elements of the Kibana Query Language. The search interface supports this familiar syntax but translates it behind the scenes into efficient SQL WHERE clauses, making the experience familiar for Kibana users while still allowing users to leverage the power of SQL when needed. This allows you to exploit the full range of string search functions, similarity functions and date time functions in ClickHouse. Below, we compare the Lucene query languages of ClickStack and Elasticsearch.

​
ClickStack search syntax vs Elasticsearch query string

Both ClickStack and Elasticsearch provide flexible query languages to enable intuitive log and trace filtering. While Elasticsearch’s query string is tightly integrated with its DSL and indexing engine, ClickStack supports a Lucene-inspired syntax that translates to ClickHouse SQL under the hood. The table below outlines how common search patterns behave across both systems, highlighting similarities in syntax and differences in backend execution.

​
Exists/missing differences

Unlike Elasticsearch, where a field can be entirely omitted from an event and therefore truly “not exist,” ClickHouse requires all columns in a table schema to exist. If a field isn’t provided in an insert event:
  • For Nullable fields, it will be set to NULL.
  • For non-nullable fields (the default), it will be populated with a default value (often an empty string, 0, or equivalent).
In ClickStack, we use the latter as Nullable is not recommended. This behavior means that checking whether a field “exists”” in the Elasticsearch sense isn’t directly supported. Instead, you can use field:* or field != '' to check for the presence of a non-empty value. It is thus not possible to distinguish between truly missing and explicitly empty fields. In practice, this difference rarely causes issues for observability use cases, but it’s important to keep in mind when translating queries between systems.
Last modified on June 23, 2026