ES|QL Across Multiple Indices/Clusters and When It Complements vs Replaces Query DSL/Aggregations

Learning outcomes

AtlasMart now stores production telemetry in several daily indices and has remote regional clusters from Chapter 22. A single analytical question may span local indices, remote indices, or both. ES|QL can query those sources, but “works across clusters” does not mean identical latency, security, enrichment placement, or failure behavior to cross-cluster Search API.

1. Multi-index source patterns

FROM can target index patterns, aliases, data streams, and—in supported cross-cluster configurations—remote-cluster-qualified sources. The result table is typed. If the same field has incompatible mappings across sources, the analytical surface can expose type conflicts; that is a schema-governance problem, not a reason to cast blindly.

POST /_query?format=txt
{
  "query": """
    FROM atlasmart-telemetry-2026.09.10, atlasmart-telemetry-2026.09.11
    | WHERE environment == "prod"
    | STATS requests = SUM(request_count), errors = SUM(error_count) BY service
    | EVAL error_rate = TO_DOUBLE(errors) / TO_DOUBLE(requests)
    | SORT error_rate DESC
  """
}

Use aliases or data streams when they express a stable application contract. A wildcard that accidentally captures an old index with a different type for request_count can turn an otherwise valid analysis into a type-resolution failure or a semantic mismatch.

2. Cross-cluster ES|QL is distributed analytics

With remote clusters configured, ES|QL can reference sources such as region_b:atlasmart-telemetry-*. The local cluster coordinates a distributed analytical plan. WAN latency, remote availability, credentials, and version compatibility therefore enter the query's correctness envelope.

POST /_query?format=txt
{
  "query": """
    FROM atlasmart-telemetry-*, region_b:atlasmart-telemetry-*
    | WHERE environment == "prod"
    | STATS requests = SUM(request_count), errors = SUM(error_count) BY service
    | EVAL error_rate = TO_DOUBLE(errors) / TO_DOUBLE(requests)
    | SORT errors DESC
  """
}

3. ENRICH/LOOKUP placement across clusters

Current ES|QL has explicit cross-cluster rules for ENRICH and LOOKUP JOIN. Default/remote execution can require consistent reference data on involved clusters. Coordinator modes can centralize enrichment/lookup but increase data movement and coordinator work. Some command ordering is restricted because pipeline-breaking commands move execution to the coordinating side.

Do not solve missing lookup data by silently copying a stale snapshot to every region. Version the reference dataset and include its version in analytical evidence.

4. Query DSL and ES|QL overlap—but do not collapse

Both interfaces can filter and aggregate. Query DSL remains the native search contract for scored document retrieval, highlighting, detailed search options, and the full aggregation tree. ES|QL is often clearer for chained transformations, derived columns, tabular analysis, and analysts who think in pipelines. “Can express” is different from “should use.”

5. Same requirement, two interfaces

Use the shared telemetry fixture (or equivalent daily indices) and execute the service-level rollup via both APIs. Compare exact requests/errors/average semantics first. Then measure the client/runtime cost separately.

PUT atlasmart-telemetry-v23
{
  "settings": {"number_of_shards": 1, "number_of_replicas": 0},
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "@timestamp": {"type": "date"},
      "service": {"type": "keyword"},
      "environment": {"type": "keyword"},
      "tenant_id": {"type": "keyword"},
      "request_count": {"type": "long"},
      "error_count": {"type": "long"},
      "duration_ms": {"type": "double"}
    }
  }
}

POST atlasmart-telemetry-v23/_bulk?refresh=true
{"index":{"_id":"e01"}}
{"@timestamp":"2026-09-11T10:00:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":100,"error_count":2,"duration_ms":120}
{"index":{"_id":"e02"}}
{"@timestamp":"2026-09-11T10:01:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":120,"error_count":3,"duration_ms":140}
{"index":{"_id":"e03"}}
{"@timestamp":"2026-09-11T10:02:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":80,"error_count":4,"duration_ms":220}
{"index":{"_id":"e04"}}
{"@timestamp":"2026-09-11T10:03:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":90,"error_count":5,"duration_ms":260}
{"index":{"_id":"e05"}}
{"@timestamp":"2026-09-11T10:05:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":130,"error_count":1,"duration_ms":110}
{"index":{"_id":"e06"}}
{"@timestamp":"2026-09-11T10:06:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":140,"error_count":2,"duration_ms":130}
{"index":{"_id":"e07"}}
{"@timestamp":"2026-09-11T10:07:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":100,"error_count":2,"duration_ms":210}
{"index":{"_id":"e08"}}
{"@timestamp":"2026-09-11T10:08:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":110,"error_count":3,"duration_ms":250}
{"index":{"_id":"e09"}}
{"@timestamp":"2026-09-11T10:10:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":150,"error_count":1,"duration_ms":100}
{"index":{"_id":"e10"}}
{"@timestamp":"2026-09-11T10:11:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":160,"error_count":1}
{"index":{"_id":"e11"}}
{"@timestamp":"2026-09-11T10:12:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":120,"error_count":6,"duration_ms":280}
{"index":{"_id":"e12"}}
{"@timestamp":"2026-09-11T10:13:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":130,"error_count":7,"duration_ms":300}

POST atlasmart-telemetry-v23/_search
{
  "size": 0,
  "query": {"bool": {"filter": [
    {"term": {"environment": "prod"}},
    {"range": {"@timestamp": {"gte": "2026-09-11T10:00:00Z", "lt": "2026-09-11T10:15:00Z"}}}
  ]}},
  "aggs": {
    "services": {
      "terms": {"field": "service", "size": 20},
      "aggs": {
        "requests": {"sum": {"field": "request_count"}},
        "errors": {"sum": {"field": "error_count"}},
        "avg_ms": {"avg": {"field": "duration_ms"}},
        "p95_ms": {"percentiles": {"field": "duration_ms", "percents": [95]}}
      }
    }
  }
}

POST /_query?format=txt
{
  "query": """
    FROM atlasmart-telemetry-v23
    | WHERE environment == "prod"
      AND @timestamp >= "2026-09-11T10:00:00Z"
      AND @timestamp <  "2026-09-11T10:15:00Z"
    | STATS requests=SUM(request_count), errors=SUM(error_count),
            avg_ms=AVG(duration_ms), p95_ms=PERCENTILE(duration_ms,95)
      BY service
    | EVAL error_rate=TO_DOUBLE(errors)/TO_DOUBLE(requests)
    | SORT service
  """
}

The response shapes differ: Query DSL returns aggregation objects/buckets, while ES|QL returns typed columns and row values. A test should normalize both into the same domain records before comparing.

6. Boundary case: analytical language for latency-critical lookup

workload=A: GET/product lookup via Search API
workload=B: equivalent ES|QL filtered row query
same_dataset=true
same_auth=true
same_concurrency=true
same_warmup=true
same_network=true
p50_A=MEASURED
p95_A=MEASURED
p99_A=MEASURED
p50_B=MEASURED
p95_B=MEASURED
p99_B=MEASURED
cpu_A=MEASURED
cpu_B=MEASURED
choice=BASED_ON_REQUIREMENT_AND_EVIDENCE

7. Production gate

Promote cross-index/cross-cluster ES|QL only after schema compatibility, remote-cluster participation, authorization, reference-data versioning, query result semantics, and p95/p99 under representative WAN conditions are verified. Security scopes should name only the required sources; a pipeline language is not a reason to grant wildcard read access.

DELETE atlasmart-telemetry-v23

Next, move to OpenSearch and learn PPL and SQL on their own terms.

References

• Elastic ES|QL reference — Current language model, commands, functions, and limitations.
• Elastic ES|QL REST API — /_query, formats, privileges, and async-query surface.
• Elastic ES|QL syntax — Source and processing-command pipeline semantics.
• Elastic ES|QL STATS — Grouping, filtered aggregates, and null handling.
• Elastic ES|QL ENRICH — Query-time use of executed enrich policies.
• Elastic ES|QL LOOKUP JOIN — Lookup-index joins and current cross-cluster behavior.
• Elastic ES|QL across clusters — Remote-index syntax, enrich/lookup placement, and security considerations.
• Elastic ES|QL limitations — Result-size, type, and feature boundaries.
• OpenSearch SQL and PPL — Current SQL/PPL mental models and entry points.
• OpenSearch PPL — Pipeline syntax and SQL-plugin requirement.
• OpenSearch SQL — Relational syntax and response formats.
• OpenSearch SQL/PPL API — Query, explain, cursor, and format APIs.
• OpenSearch Query Workbench — Interactive SQL/PPL workflow and read-only boundary.
• OpenSearch Dashboards notebooks — Markdown, SQL, PPL, visualization, and reporting workflow.
• OpenSearch PPL lookup — Dimension-table enrichment semantics.
• OpenSearch PPL join — Join forms, limits, and performance-sensitive modes.
• Elasticsearch release notes — verify the pinned server baseline and current ES|QL status.
• OpenSearch version history — verify the pinned OpenSearch/Dashboards baseline.