OpenSearch GUI with full SQL

OpenSearch clusters, three ways#

OpenSearch arrives in three shapes: a self-hosted OpenSearch 1 or 2 cluster, an Amazon OpenSearch Service domain, and the older Open Distro for Elasticsearch distribution that some Amazon domains still run. FlatQL treats them the same way it treats Elasticsearch. You give it a URL and a credential, it uses the cluster's HTTP API with that credential, and nothing is installed on the cluster. Connection steps are on the connect a cluster page; an Amazon domain connects with its endpoint URL and the user you already have.

Two things set OpenSearch apart in day-to-day use, and this page is about both: the data is often logs spread across daily indices, and the cluster ships its own SQL plugin with a workbench UI.

The OpenSearch SQL plugin and Query Workbench#

The OpenSearch SQL plugin answers POST /_plugins/_sql with SQL and PPL. Query Workbench, in OpenSearch Dashboards, is its UI: the Amazon OpenSearch Service documentation describes it as a page that "lets you run on-demand SQL queries, translate SQL into its REST equivalent, and view and save results as text, JSON, JDBC, or CSV", and the OpenSearch documentation notes that "access to data is read-only".

The plugin's SQL is useful and bounded. Its own limitations page states that aggregation applies to fields only ("You can only apply aggregation to fields"), that a subquery in FROM is unsupported when the outer query has GROUP BY or ORDER BY, that "the JOIN query does not support aggregations on the joined result", and that pagination "only supports basic queries". The join itself, per the complex queries page, supports inner, cross and left outer joins, but "You can only join two indexes" and "You can't use GROUP BY or ORDER BY for results".

FlatQL does not replace the plugin; it sits beside it. Full SQL statements run in FlatQL's engine, which needs only _search. When you want the plugin's own dialect or its PPL-adjacent behaviour, switch the statement to Cluster SQL and it is sent to /_plugins/_sql as written, with one courtesy: double-quoted index names in FROM are rewritten to the backticks the plugin expects. FlatQL discovers which endpoint a cluster serves on first use, trying /_plugins/_sql, then /_opendistro/_sql, then /_sql, and remembers the answer per connection. FlatQL vs OpenSearch SQL sets the two side by side.

Logs on logs-*: one statement#

Warnings and errors from one region since the start of yesterday, grouped by service and status code with request counts and latency:

SELECT service, status_code,
       COUNT(*) AS requests,
       AVG(duration_ms) AS avg_ms,
       MAX(duration_ms) AS slowest_ms
FROM "logs-*"
WHERE "@timestamp" >= '2026-11-03T00:00:00Z'
  AND level IN ('warn', 'error')
  AND region = 'eu-west-1'
GROUP BY service, status_code
ORDER BY requests DESC
LIMIT 50;

"logs-*" is an index pattern used as a table, quoted because of the hyphen and the star. All three predicates compare a field with a literal and both grouping keys are plain fields, so the statement meets the engine's condition: "GROUP BY: pushed as a composite aggregation when every grouping key is a plain groupable field, every aggregate is COUNT / SUM / AVG / MIN / MAX (plus approximate COUNT DISTINCT) on a plain field, and the WHERE was pushed in full." The request is a POST /logs-*/_search; COUNT(*) is read from each bucket's doc_count, so only the two metrics need sub-aggregations:

{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        { "range": { "@timestamp": { "gte": "2026-11-03T00:00:00Z" } } },
        { "terms": { "level": ["warn", "error"] } },
        { "term": { "region": "eu-west-1" } }
      ]
    }
  },
  "aggs": {
    "g": {
      "composite": {
        "size": 1000,
        "sources": [
          { "k0": { "terms": { "field": "service", "missing_bucket": true } } },
          { "k1": { "terms": { "field": "status_code", "missing_bucket": true } } }
        ]
      },
      "aggs": {
        "m1": { "avg": { "field": "duration_ms" } },
        "m2": { "max": { "field": "duration_ms" } }
      }
    }
  }
}

The engine pages with after_key until the buckets are complete (up to 50,000 groups), then applies ORDER BY requests DESC and LIMIT 50 itself, since a sort on an aggregate and a limit after GROUP BY are not pushed. The notes under the grid say "GROUP BY was computed by Elasticsearch aggregations."

To bucket the same logs by hour, add DATE_TRUNC('hour', "@timestamp") as a grouping key. A derived key is not a plain field, so the time filter is still pushed but the hourly grouping is computed in the engine over a scan of up to 50,000 rows per index; keep the time window tight.

What the rest of the workbench adds on OpenSearch#

• Worksheets with SQL and raw requests together. GET /_cat/indices?v and a SELECT live in the same sheet; ⌘↵ runs the one under the cursor.
• Show as DSL. Every SQL result carries the body above, so you can paste it into a Dashboards dev tools console or an alerting monitor unchanged. See Show as DSL.
• Charts, pivots, exports. Bar, line, area and scatter charts on the result; CSV, TSV and JSON exports that stream to disk up to 5,000,000 rows on Pro.
• Ask FlatQL. Describe the question in plain words; the assistant reads the logs-* mapping and one sample document and writes the SQL or the DSL. It does not run anything on its own.
• Teams. Store the Amazon domain credentials once, give analysts read on logs-* and nothing else, and let FlatQL enforce it on every request. Production connections are read-only by default.

Without FlatQL#

Query Workbench. Already in Dashboards if you run it, read-only, and able to translate SQL into the REST call. It is the right place to test what the plugin's SQL does, within the join and aggregation limits quoted above, and its exports are the result of one query page.

Dashboards Discover and visualisations. Good for browsing log lines over a time picker and for saved charts. A GROUP BY with two keys and two metrics is a data table visualisation configured by clicks rather than typed, and there is no join. FlatQL vs Kibana applies to Dashboards as well, since it is a fork of Kibana.

awscurl or signed requests from a script. For domains using IAM authentication, a signed request to /_plugins/_sql or /_search from Python or the CLI. Precise and scriptable, with every aggregation response reshaped by hand.

Lightweight browsers. Cluster browsers such as elasticvue work with OpenSearch too and are good for mappings and single documents; FlatQL vs elasticvue compares them.

Connect your OpenSearch cluster#

One cluster is free, with the full SQL engine, Show as DSL and Ask FlatQL; the one-time Pro licence on the pricing page adds unlimited clusters and exports. Create an account, paste your domain endpoint, and run the logs-* statement above.