Cluster SQL mode with x-pack SQL and the OpenSearch SQL plugin

Full SQL runs statements in FlatQL's own engine with pushdown to the cluster. Cluster SQL hands the statement to the SQL endpoint the cluster already has, unchanged.

Two engines, one toggle#

The worksheet toolbar has a two-way switch: Full SQL and Cluster SQL. The choice is remembered per connection.

• Full SQL parses the statement as Snowflake-style SQL, pushes filters, sorts, limits and GROUP BY aggregations to the cluster as Query DSL, and runs joins, CTEs, window functions and the rest in the embedded engine. See the SQL overview.
• Cluster SQL sends the text to the cluster's own SQL endpoint and shows what comes back. FlatQL does not rewrite the statement beyond a quoting fix and a default LIMIT (below).

The same statement in both modes:

SELECT country, COUNT(*) AS orgs
FROM organizations
WHERE founded_year >= 2015
GROUP BY country
ORDER BY orgs DESC
LIMIT 10;

Full SQL turns it into one composite aggregation request. Cluster SQL posts it to /_sql (Elasticsearch) or /_plugins/_sql (OpenSearch) and the cluster plans it. The result grid is the same.

How the endpoint is detected#

Three endpoints exist in the wild and FlatQL discovers which one answers on first use, then remembers it for the connection:

The order of attempts depends on what the root endpoint reports: an OpenSearch distribution tries the plugin first, then Open Distro, then x-pack; a URL on amazonaws.com without an OpenSearch marker tries Open Distro first because legacy Amazon Elasticsearch domains block /_sql; everything else starts with x-pack. A 404, 405 or "no handler found" moves to the next candidate. Any other error means the endpoint exists and rejected the statement, and that error is shown to you with a hint. If a remembered endpoint disappears (cluster upgraded, plugin removed), FlatQL rediscovers once.

When none of the three answers, the message is "SQL is not available on this cluster" with the paths tried. Full SQL still works on that cluster, because it only needs _search.

What is sent#

x-pack SQL. POST /_sql?format=json with fetch_size paging and field_multi_value_leniency: true, following the cursor until your LIMIT or the row count is reached, then closing the cursor. The Show as DSL tab shows the output of /_sql/translate for the statement, labelled "translated by the cluster".

OpenSearch and Open Distro. POST /_plugins/_sql?format=jdbc (or the Open Distro path). Two adjustments: double-quoted index names in FROM and JOIN are rewritten to backticks, since the plugin treats double quotes as string literals; and a LIMIT equal to the requested row count is appended when the statement has none. The plugin has two engines inside it. FlatQL tries the legacy engine first (it understands _id filters and backticks), then the newer engine (more functions), and reports the first error when both refuse. No translated DSL is available for the plugin.

What Cluster SQL cannot do#

The endpoints share a shape: one index, filters, GROUP BY on keyword or numeric fields with COUNT, SUM, AVG, MIN, MAX, ORDER BY, LIMIT. Beyond that they differ from each other and from Full SQL:

• No joins, no subqueries, no CTEs, no window functions, no QUALIFY.
• Scalar function sets differ between x-pack, the plugin's two engines and Open Distro. A function that works on one cluster may not exist on another.
• Sorting or grouping on a text field needs its keyword sub-field spelled out, because the endpoint does not pick it for you. Full SQL does.
• Identifier quoting differs: double quotes on x-pack, backticks on the plugin (FlatQL converts the FROM list only).
• The user needs the indices:data/read/sql privilege on Elasticsearch; reading with _search alone is not enough.
• Results are not paginated past the LIMIT. Raise it in the statement for more rows.

When the cluster rejects a statement with a parsing or "not supported" error and the engine is available, FlatQL runs the statement in Full SQL instead and adds the note "The cluster's own SQL rejected this statement, so it ran with Full SQL instead." You get rows either way, and the note tells you which path they came from.

Team access rules apply in both modes. In Cluster SQL, FlatQL reads the index names out of the statement before sending it and checks them against the member's grants.

When to use which#

Use Full SQL by default: joins across indices, CTEs, window functions, Snowflake functions, MATCH, index patterns with mixed mappings, and anything you plan to export at scale. Every result shows what was pushed.

Use Cluster SQL when you want the cluster's own answer: checking what x-pack SQL or the plugin returns for a statement you will run from a JDBC driver, Kibana or an application; using a function or syntax specific to that endpoint; or comparing performance on a simple aggregation the cluster plans well. It is also a quick way to confirm a cluster's SQL feature is enabled and licensed.

The limits page lists the caps of each path, and Elasticsearch SQL limitations explains why the native endpoints stop where they do.