What a GUI for Elasticsearch has to do#
Most people reach for an Elasticsearch GUI for five jobs: see which indices exist and what fields they hold, run a query without hand-writing JSON, read the result in a form that fits the question, get the data out, and let a colleague do the same without handing over the cluster password. A tool that does the first three is a browser; one that does all five is a workbench. Here is how FlatQL covers each.
Browse indices and mappings#
A connection lists its indices and aliases. Each index page has a Fields tab with every mapped field and its type, so you know before you type that country is a keyword and description is text. In the worksheet, SHOW TABLES lists the indices and DESCRIBE organizations prints the mapping as rows, and the editor autocompletes field names from the mapping. The distinction between keyword and text is the one that trips most people up; the glossary entry is short.
Run queries#
Two kinds of statement share a worksheet. Full SQL goes to FlatQL's engine, which pushes filters, sorts, limits and GROUP BY to the cluster and runs joins, windows and functions itself. An Elasticsearch request is a line such as GET /organizations/_search followed by a JSON body, sent as written. ⌘↵ runs the statement under the cursor. A third option, Cluster SQL, hands the statement to x-pack SQL on the cluster when you want its dialect. And when the question is easier to say than to write, Ask FlatQL drafts the SQL or DSL from your mappings and puts it at the cursor; nothing runs until you press Run.
See results#
A result is a grid first, with a value panel for any cell and sort, freeze and hide from the header. The same result can be a bar, line, area or scatter chart, a pivot with rows, columns and values, raw JSON, or a Markdown table on the clipboard. The DSL tab shows what the cluster was asked. Details on the results page.
Export#
CSV, TSV and JSON exports stream from the cluster to disk as the rows arrive, with a progress bar, up to 5,000,000 rows. Excel files are built in the browser and capped at 100,000 rows. Exporting is a Pro feature; see exporting results.
Share safely#
Sharing is where most GUIs stop. A browser extension on one laptop keeps the password in that laptop's storage, and the second analyst gets the password in a chat message. A team stores a connection once. The credentials are encrypted server-side and never shown to members; what a member gets is a role with read or write per index pattern, checked by FlatQL on every request. Production connections start read-only, and sign-ins, access changes, writes and exports land in an audit log. This is covered on the teams page and in security.
The desktop app#
Two kinds of cluster never reach a web app: the one on your own machine and the one behind the office VPN. The web app is one way in. A desktop app for macOS is coming soon, with Windows and Linux after it; it is not downloadable yet. It runs the same workbench but talks to clusters from your machine, so a cluster on localhost:9200 or behind a VPN is reachable, and passwords and API keys stay in the OS keychain rather than on a server. A 4-digit PIN locks the app when you step away, and your account is checked each time it opens. A connection can be synced to your web account if you want it in both places, and only then. Read more on the desktop app page or the overview.
One statement, and what it sent#
Founding-year range and distinct-domain count per status, for three countries:
SELECT status,
MIN(founded_year) AS oldest,
MAX(founded_year) AS newest,
COUNT(DISTINCT primary_domain) AS domains
FROM organizations
WHERE country IN ('USA', 'GBR', 'DEU')
GROUP BY status
ORDER BY domains DESC;
The IN list is a terms filter, the grouping key is a keyword, and the three aggregates are ones the cluster computes, so the whole GROUP BY runs as a composite aggregation in a POST /organizations/_search. Approximate COUNT(DISTINCT) becomes a cardinality aggregation:
{
"size": 0,
"query": {
"bool": {
"filter": [
{ "terms": { "country": ["USA", "GBR", "DEU"] } }
]
}
},
"aggs": {
"g": {
"composite": {
"size": 1000,
"sources": [
{ "k0": { "terms": { "field": "status", "missing_bucket": true } } }
]
},
"aggs": {
"m0": { "min": { "field": "founded_year" } },
"m1": { "max": { "field": "founded_year" } },
"m2": { "cardinality": { "field": "primary_domain" } }
}
}
}
}
FlatQL pages through after_key until the buckets are complete, then sorts them by domains in the engine, because an ORDER BY on an aggregate is not pushed. The result footer reads "GROUP BY was computed by Elasticsearch aggregations." Pushdown describes the rules for every clause.
Without FlatQL#
Kibana Dev Tools. The console in Kibana is the reference place to send Query DSL, with autocomplete for the request body and the response as JSON. It needs a Kibana deployment next to the cluster, it speaks DSL only, and results are JSON rather than a grid. For a cluster you already run Kibana on, it is the quickest way to try a request; FlatQL vs Kibana goes through the differences in detail.
curl and jq. curl -u user:pass https://es:9200/organizations/_search -H 'Content-Type: application/json' -d @query.json | jq '.hits.hits[]._source' works anywhere the API is reachable. Credentials end up in shell history and scripts, and every aggregation response has to be reshaped by hand.
Scripts with a client library. The official Python or JavaScript clients, a notebook, and pandas for the join or the chart. Flexible, reproducible, and a small program per question.
Lightweight browsers. Tools such as elasticvue show indices, mappings and documents in a browser extension or a local page and are good for a quick look at a cluster. FlatQL vs elasticvue compares them, and the roundup of Elasticsearch GUI tools places eight to ten of them by use case.
Connect a cluster in a minute#
The Free plan gives one cluster the full engine, Show as DSL and Ask FlatQL, with no card and no expiry; pricing lists the one-time Pro licence. Register, paste a URL and an API key, and run SHOW TABLES.