OpenSearch SQL

The Federated PPL Query Engine allows users to execute Piped Processing Language (PPL) queries across multiple, heterogeneous data sources. While PPL was traditionally limited to OpenSearch, this framework enables observability solutions (like Event Explorer, Notebooks, and Trace Analytics) to leverage data from external metric stores such as Prometheus and AWS CloudWatch.

Key capabilities include:

• Querying data from multiple configured data sources using a single PPL interface.
• Correlating metrics and logs from different data sources.
• Injecting data from various sources into ML commands.

Limitations:

• No Cross-Datasource Joins: Join queries spanning different data sources are currently out of scope.
• Single Node Execution: Distributed query execution is not supported; all execution occurs on a single coordination node.

OpenSearch SQL allows you to extract insights from your data using standard SQL or the Piped Processing Language (PPL) syntax. You can use common SQL features like aggregations, group by, and where clauses to investigate data. Results can be read as either JSON documents or CSV tables, providing flexibility for different downstream workflows.

Key capabilities include:

• Basic filtering and aggregation.
• Complex queries: semi-structured data querying, JOINs, set operations, and sub-queries.
• Specialized OpenSearch functions for advanced analytics and visualization.

The V3 engine (Calcite integration) focuses on enhancing the PPL language. In the initial 3.0.0 release, the following new commands were introduced to improve query capabilities:

• Join: For joining datasets.
• Lookup: For performing lookups.
• Subquery: For executing subqueries within PPL.

The ppl-rest-spi is a Service Provider Interface (SPI) that allows OpenSearch plugins to contribute read-only data as queryable tables in PPL using the rest '<name>' command.

Key characteristics:

• No Grammar Changes: You don't need to add new PPL commands or keywords.
• Composition: Contributed endpoints compose with standard PPL pipes like | where, | stats, and | head.
• Type Safety: To avoid cross-classloader issues, the SPI exchanges row values as plain java.lang types. Every endpoint surfaces a single response column containing a serialized JSON string. Users then use spath or json_extract to pull specific fields and cast() to convert them to numeric types if needed.

Piped Processing Language (PPL) is a query language used in OpenSearch for data exploration and discovery. It uses a sequence of commands delimited by pipes (|) to process data and return results. PPL is designed as a human-friendly interface for ad hoc queries, making it easier to explore log, monitoring, and observability data compared to the more complex Query DSL.

Queries typically start with a search command, followed by a series of processing commands.

source=accounts
| where age > 18
| fields firstname, lastname

While QueryManager is the high-level interface, QueryService is the low-level interface of the core engine.

Each Plan is responsible for deciding how to execute a query by using the QueryService to perform the following tasks:

• Analyze the query
• Plan the execution
• Optimize the query
• Execute the query

V3 scripts use a Calcite expression tree encoded as a base64 string. To improve cache hit ratios, the expression tree is standardized (e.g., replacing field names and literals with placeholders like ?i) and specific information is moved into a params object.

When using V3 scripts, the lang must be set to opensearch_compounded_script and the source JSON must include "langType":"calcite".

{
    "script": {
        "source": "{\"langType\":\"calcite\", \"script\":\"<V3_SCRIPT_ENCODED_BYTE_STRING>\"}",
        "lang": "opensearch_compounded_script",
        "params": {
            "utcTimestamp": 17630261838681530000,
            "SOURCES": [0, 2, 2, 1],
            "DIGESTS": ["age", 35, "u35", "email"]
        }
    }
}