OpenSearch PPL and SQL Interfaces: Pipeline/Relational Mental Models, Supported Operations, and Limitations

Learning outcomes

OpenSearch offers Query DSL plus SQL and Piped Processing Language (PPL) through its SQL plugin. PPL resembles ES|QL visually because both use pipes, but shared punctuation does not imply shared commands, functions, null rules, joins, endpoints, or execution plans. SQL introduces a separate relational mental model again.

1. PPL is a pipeline, but not ES|QL

OpenSearch PPL begins with search source=... (or the abbreviated source=...) and chains commands. The current language includes where, eval, fields, stats, sort, head, timechart, lookup, join, and many more. Each command has OpenSearch-specific syntax and settings.

POST /_plugins/_ppl?format=jdbc
{
  "query": "source=atlasmart-telemetry-v23 | where environment = 'prod' AND @timestamp >= timestamp('2026-09-11 10:00:00') AND @timestamp < timestamp('2026-09-11 10:15:00') | stats sum(request_count) as requests, sum(error_count) as errors, avg(duration_ms) as avg_ms, percentile(duration_ms, 95) as p95_ms by span(@timestamp, 5m), service | eval error_rate = errors / requests | sort + `span(@timestamp,5m)`, + service"
}

OpenSearch PPL aggregation functions ignore nulls for AVG/SUM and do not count null/missing values for COUNT(field). Percentiles are approximate. High-cardinality grouped results can also inherit approximation from underlying distributed bucket aggregation behavior.

2. SQL is a relational lens over document data

OpenSearch SQL maps index→table, document→row, field→column. That analogy is useful but incomplete: mappings, nested/object behavior, distributed aggregations, search text semantics, and plugin limitations still come from OpenSearch. Use SQL for analysts or tools that naturally speak SELECT/WHERE/GROUP BY, not because OpenSearch suddenly becomes a transactional relational database.

POST /_plugins/_sql?format=json
{
  "query": """
    SELECT service,
           SUM(request_count) AS requests,
           SUM(error_count) AS errors,
           AVG(duration_ms) AS avg_ms,
           MAX(duration_ms) AS max_ms
    FROM atlasmart-telemetry-v23
    WHERE environment = 'prod'
    GROUP BY service
    ORDER BY service
  """
}

OpenSearch SQL’s documented aggregate surface is not identical to PPL or ES|QL. This example deliberately reports MAX(duration_ms) rather than claiming a portable SQL percentile function. For the chapter’s p95 requirement, use the verified PPL percentile function or native Query DSL percentiles unless the exact deployed SQL engine/version documents an equivalent. For time bucketing, likewise verify the deployed SQL date/time functions instead of copying a warehouse-specific DATE_TRUNC expression.

3. Explain is a diagnostic bridge, not a cost oracle

The SQL/PPL API exposes explain/translation facilities, and Query Workbench has an Explain action. Use them to understand how a statement maps into OpenSearch operations and to diagnose unsupported constructs. Do not treat translated DSL as proof of identical performance to a hand-designed search request.

POST /_plugins/_ppl/_explain
{
  "query": "source=atlasmart-telemetry-v23 | where environment='prod' | stats sum(error_count) as errors by service | sort - errors"
}

POST /_plugins/_sql/_explain
{
  "query": "SELECT service, SUM(error_count) AS errors FROM atlasmart-telemetry-v23 WHERE environment='prod' GROUP BY service"
}

Record the plugin version, translated plan, shard count, and measured query stats. Plan readability can reveal an accidental broad scan, but only runtime evidence proves cost.

4. Enrichment and joins have their own PPL semantics

Current PPL supports lookup for dimension-style enrichment and join for combining datasets. This is not Elasticsearch's enrich-policy or lookup-index model. PPL join behavior also has settings such as subsearch output limits and some join types that are disabled by default because of cost.

Create the ordinary OpenSearch dimension index first. Unlike the Elasticsearch lookup-index example, this PPL lookup example does not claim or require Elasticsearch index.mode=lookup semantics.

PUT atlasmart-service-directory-v23
{
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "service": {"type": "keyword"},
      "owner":   {"type": "keyword"},
      "tier":    {"type": "keyword"}
    }
  }
}

POST /atlasmart-service-directory-v23/_bulk?refresh=true
{"index":{"_id":"catalog-api"}}
{"service":"catalog-api","owner":"catalog-team","tier":"tier-1"}
{"index":{"_id":"checkout-api"}}
{"service":"checkout-api","owner":"payments-team","tier":"tier-0"}

POST /_plugins/_ppl
{
  "query": "source=atlasmart-telemetry-v23 | stats sum(error_count) as errors by service | lookup atlasmart-service-directory-v23 service replace owner, tier | fields service, owner, tier, errors | sort - errors"
}

5. Cross-cluster PPL is a remote-query contract, not a local shortcut

OpenSearch PPL can address a configured remote cluster in the source expression. The coordinator still depends on the remote-cluster connectivity, authentication/authorization, compatible mappings, WAN behavior, and failure semantics established in Chapter 22. A remote source does not make remote shards local, and a successful federated query does not prove replication or disaster-recovery readiness.

POST /_plugins/_ppl
{
  "query": "source=region_b:atlasmart-telemetry-* | where environment='prod' | stats sum(request_count) as requests, sum(error_count) as errors by service | sort - errors"
}

6. Query Workbench and API result shapes

Query Workbench runs on-demand SQL/PPL, can show tabular results, and can explain/translate queries. Its SQL/PPL workflow is read-only; it does not turn SQL UPDATE or DELETE into document writes. The REST API supports multiple response formats. SQL and PPL result/cursor handling is therefore another interface contract to test in client code.

7. Security and explicit-index boundary

PPL/SQL requests place index names inside the request body. OpenSearch documents that this shares access-policy concerns with multi-search style APIs. If rest.action.multi.allow_explicit_index=false, SQL/PPL endpoints are disabled. Even when enabled, Security-plugin roles must authorize the actual target indices. A Dashboards tenant controls saved-object visibility, not index authorization.

# As atlasmart-analyst: should succeed if read permission is granted.
curl -k -u atlasmart-analyst:$PASS   -H 'Content-Type: application/json'   -d '{"query":"source=atlasmart-telemetry-v23 | head 1"}'   https://localhost:9201/_plugins/_ppl

# Query a forbidden index and require 403/authorization failure.
# A successful response here means the role is too broad.

8. Reproducible lab and null test

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 /_plugins/_ppl
{
  "query": "source=atlasmart-telemetry-v23 | where service='catalog-api' AND @timestamp >= timestamp('2026-09-11 10:10:00') | stats count() as rows, count(duration_ms) as duration_values, sum(request_count) as requests, avg(duration_ms) as avg_ms"
}

# Invariant to verify: rows=2, duration_values=1, requests=310, avg_ms=100.

If the result differs, do not “fix” the query until you inspect the mapping, function semantics, and plugin version. Differences can reveal real language semantics.

9. Production judgment

DELETE atlasmart-telemetry-v23
DELETE atlasmart-service-directory-v23

Next, place these languages inside Dashboards/notebook workflows without losing resource and security boundaries.

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.