Search docs
Jump to a page or section

Writing Effective Queries

How to write SQL that finds the right fields and returns the right answer in Telemetry Machine.

Open in Claude

Telemetry Machine runs SQL with Apache DataFusion. Schema Management explains how its columns come from your telemetry.

Bound every query by time

A query scans the events in its time range. Start with a narrow range, such as the last hour, and widen it only if the result is empty.

Use the right column names

  • Trace fields have no span. prefix: use name, kind, duration_ns, and status_code.
  • status_code is a number: 0 is unset, 1 is OK, and 2 is an error.
  • Attributes have a prefix: attr. for span, log, and data-point attributes, and resource. for resource attributes. An HTTP route is attr.http.route; a service is resource.service.name.
  • Put double quotes around names that contain dots: "attr.http.route".

Cast attribute values

Attribute values are stored as strings, even when the instrumentation sent a number or a boolean. Cast them for numeric comparisons and math:

SQL
CAST("attr.http.response.status_code" AS BIGINT) >= 500

Functions

Every DataFusion SQL function works, such as approx_percentile_cont, date_bin, and date_trunc. There is no time_bucket: use date_bin. Telemetry Machine adds the functions below.

JSON

Arrays, key-value lists, and span events are stored as JSON text. The datafusion-functions-json extension reads them. A path is a list of keys and array indexes.

FunctionReturnsOperator
json_get_str, json_get_int, json_get_float, json_get_boolThe value, typed
json_as_text(json, path…)Any value as text->>
json_get(json, path…)A JSON value->
json_contains(json, path…)Whether the path exists?
json_get_json, json_get_array, json_length, json_object_keysNested JSON, a list, a size, keys
SQL
json_get_str("attr.http.request.header.user_agent", 0)

Counters

OpenTelemetry cumulative sums keep a running total that drops to zero when a process restarts. These window functions handle the reset:

  • counter_increase(value): the increase since the previous row.
  • counter_rate(value, _time): the increase per second since the previous row.

Partition by the series and order by time:

SQL
counter_rate(value_double, _time) OVER (
  PARTITION BY "resource.service.instance.id" ORDER BY _time)

Histogram percentiles

otlp_hist_percentile(bounds, counts, quantile) adds up the buckets of OTLP explicit-bucket histograms in a group and estimates a percentile. The quantile must be a literal.

SQL
otlp_hist_percentile("hist.explicit_bounds", "hist.bucket_counts", 0.99)

Percentiles of raw values

uddsketch_calc(quantile, uddsketch_state(value)) gives an approximate percentile, such as of duration_ns. uddsketch_error(state) gives its relative error.

Find root spans

A root span has no parent: "trace.parent_span_id" = '' OR "trace.parent_span_id" IS NULL.

Check renamed attributes

Telemetry Machine keeps old and new semantic-convention names as separate columns, such as attr.http.status_code and attr.http.response.status_code. If a query stopped matching after an instrumentation upgrade, check the schema for both names.

Expect nulls in older rows

A field that appeared recently is null in rows that arrived before it. Filter on IS NOT NULL when that matters.