# Writing Effective Queries

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

Telemetry Machine runs SQL with [Apache DataFusion](https://datafusion.apache.org/user-guide/sql/index.html). [Schema Management](https://telemetrymachine.com/docs/schema-management) explains how its columns come from your telemetry.

**For agents.** With the Telemetry Machine MCP server:

1. **Start an investigation by listing the datasets.** Each has a type: traces, logs, metrics, or events.

   ```json
   list_datasets {}
   ```

2. **Read a dataset's schema before you query it.** The schema gives each field's name, type, and notes. `search` narrows the fields.

   ```json
   get_schema { "dataset": "shop-prod-traces", "search": "route" }
   ```

3. **Get a field's values before you filter or group by it.**

   ```json
   get_field_values { "dataset": "shop-prod-traces", "field": "attr.http.route" }
   ```

4. **Query.** `since` bounds the time range.

   ```json
   run_query {
     "dataset": "shop-prod-traces",
     "description": "Error count by route",
     "since": "1h",
     "select": "\"attr.http.route\", count(*) AS errors",
     "where": "status_code = 2",
     "group_by": "\"attr.http.route\"",
     "order_by": "errors DESC"
   }
   ```

   `raw_sql` takes a whole query instead, for joins, subqueries, and window functions. It reads the dataset as `__table__` and must bound `timestamp_unix_ns` itself.

## 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](https://datafusion.apache.org/user-guide/sql/scalar_functions.html) 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`](https://github.com/datafusion-contrib/datafusion-functions-json) extension reads them. A path is a list of keys and array indexes.

| Function                                                             | Returns                           | Operator |
| -------------------------------------------------------------------- | --------------------------------- | -------- |
| `json_get_str`, `json_get_int`, `json_get_float`, `json_get_bool`    | The 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_keys` | Nested 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.

---

All pages: https://telemetrymachine.com/docs/llms.txt