Query external datasets with ES|QL Data Federation

A dataset is a read source for the standard ES|QL pipeline. You query it with FROM like an index, and every processing command works the same way it does for an index.

FROM my_dataset
		

For a hands-on example, refer to get started with ES|QL Data Federation.

When you query a dataset, Elasticsearch reads data from object storage (such as Amazon S3) rather than from a local index. This means every column and every row that a query touches results in network I/O. The query engine applies several optimizations automatically, and there are things you can do to help it read less data.

Use KEEP or DROP to select only the columns your query needs. For Parquet files, column selection pushes down to the reader so that unrequested columns are never fetched from storage. For CSV and NDJSON, the full row is read but unrequested columns are discarded early.

In practice, this can make a significant difference. A filtered query over a Parquet dataset that selects three columns reads roughly a third of the bytes that the same query reads without column selection.

When a dataset's resource path uses Hive-style partitioning (for example, year=2024/month=3/), the engine detects partition keys automatically and promotes them to queryable columns. A WHERE condition on a partition column evaluates during file discovery, before any data is read. On a two-year monthly-partitioned dataset, WHERE year = 2024 AND month = 3 skips 23 out of 24 partitions at zero I/O cost.

Pruning applies when the partition filter comes before any LIMIT, SORT, or STATS in the query. If one of those commands sits between FROM and the WHERE on a partition column, pruning is silently skipped and every partition is read. Try to put partition filters first.

For details on partition detection modes, refer to dataset settings.

WHERE conditions and LIMIT reduce how much data a query reads, but how far they push down depends on the format. For Parquet files, filters push down into the reader itself: the engine uses row-group statistics and page indexes to skip data that cannot match the filter. Only row groups whose statistics overlap the filter condition are read, and within those row groups, late materialization reads predicate columns first and materializes other columns only for rows that survive the filter.

For CSV and NDJSON, filters do not reach the reader: every row must be read and parsed, but rows that fail the filter are discarded before further processing.

FROM access_logs
| WHERE status_code >= 500
| KEEP @timestamp, status_code, request_path
| LIMIT 100
		

The general query performance advice in optimize ES|QL query performance applies to datasets too. In particular, adding a WHERE, a KEEP, and a LIMIT are the three most effective ways to reduce how much data a query reads from storage.

Elasticsearch caches file metadata (schemas and file listings) so that repeated queries against the same dataset do not re-discover files each time. Cached schemas are invalidated when the underlying files change, so a schema stays cached for as long as it stays correct. There is no schema TTL. Only the file-listing cache uses a TTL (5 minutes by default) configurable through cluster settings.

A dataset's resource path can use glob patterns to match many files. These cluster settings bound file discovery:

• esql.external.max_listed_objects (default 1,000,000): the maximum number of objects visited while listing a glob, including keys that do not match the pattern and keys dropped by exclusion. Applied independently to each glob listing. A comma-separated resource of N globs therefore does N listings; a rewrite-empty fallback can list the same glob again. The kept-files cap (esql.external.max_discovered_files) is shared across that list.
• esql.external.max_discovered_files (default 25,000): the maximum number of files a single dataset keeps after listing filters (_file.*).
• esql.external.max_glob_expansion (default 100): the maximum number of concrete paths a brace pattern ({a,b,c}) expands to. Past this cap, the engine falls back to listing the storage instead of failing.

If your dataset exceeds these limits, narrow the resource path or adjust the settings. Refer to cluster settings for details.

Datasets share the same namespace as indices, data streams, aliases, and ES|QL views, so FROM resolves each name independently.

_class and _name are available from 9.6. On 9.5, use METADATA _index, which returns the dataset name for dataset rows in that version.

FROM speedtest_data, network_incidents METADATA _class, _name
| KEEP _class, _name, category, severity, avg_d_kbps, avg_lat_ms
| LIMIT 10
		

When sources have different schemas, columns that do not exist in a given source return null for rows from that source. Use METADATA _name to see which source each row came from: it returns the dataset name for dataset rows and the index name for index rows. METADATA _class returns what kind of source a row came from — index or dataset — so a query can tell the two apart without knowing the names in advance.

_index does not answer this question on a dataset. It names an index, and a dataset is not one, so it returns null for dataset rows. In earlier versions it returned the dataset name.

By default, a wildcard does not match datasets. FROM speedtest_data reads the dataset, while FROM speedtest* resolves to indices, data streams, aliases, and views only. Registering a dataset therefore does not change what an existing wildcard query reads.

To let wildcards discover datasets, enable the wildcards_match_datasets query setting:

SET wildcards_match_datasets = true;
FROM speedtest*
		

You can also send it in the _query request body as "settings": {"wildcards_match_datasets": true}, or change the cluster-wide default with esql.query.settings.wildcards_match_datasets. A value set in the query overrides the request body, which overrides the cluster default.

Metadata columns are available using the METADATA directive:

_index, _id, _version and _source return null on a dataset. A dataset is not an index, and files carry no document identity, version, or stored source. In 9.5, these fields returned synthetic values (dataset name, row ID, file modification time, row-as-JSON) instead of null.

_class and _name answer the same two questions on every source. On an index they return index and the concrete index name; on a dataset, dataset and the dataset name. A FROM that names both kinds can separate the rows without knowing in advance which names resolve to which.

For example, this query returns file-level metadata for each matching row:

FROM access_logs METADATA _file.path, _file.name, _file.size
| KEEP _file.path, _file.name, _file.size, status_code
| LIMIT 10
		

If a field named in the METADATA clause matches a file column, the engine-generated value takes precedence and the file column is dropped. A warning identifies the file column that was dropped. To keep both values, rename the file column in the dataset mapping. If the query omits METADATA, the file column remains available as a regular data column.

Search functions can filter dataset rows by evaluating the query against values read from the files. This runtime search does not use an inverted index. When using METADATA _score, MATCH and MATCH_PHRASE on dataset rows contribute to the relevance score based on the boost option and the query terms matched — not BM25, as there are no index statistics for a dataset. In earlier versions, dataset rows do not contribute to _score.

Because there is no inverted index, search functions on a dataset evaluate by scanning values row by row. For large datasets where search is the primary access pattern, consider ingesting the data into Elasticsearch for indexed search performance.

Runtime MATCH on a dataset requires the query value's type to match the field's type. Text is analyzed with the standard analyzer unless a values analyzer is declared through TO_TEXT's analyzer option; MATCH's own analyzer option applies to the query string only, defaulting to the values analyzer.

The following search functions are available for datasets:

The limitations below include operations that require structures available only in an Elasticsearch index, such as the inverted index, doc values, or time series metadata, as well as unsupported data shapes. Unsupported operations fail with a clear error; representation limitations are described in the table.

If a query against a dataset returns unexpected results or errors, check the following common causes.

Unexpected nulls in query results

If you query a dataset and an index together with FROM, columns that do not exist in one source return null for rows from that source. Use METADATA _name to check which source each row came from; on 9.5, use METADATA _index. Separately, complex Parquet types MAP, nested LIST, and VARIANT return null because they are not currently supported.

Slow queries

Add KEEP to select only the columns you need, add a WHERE filter, and add a LIMIT. For Parquet datasets, these push down to the reader and can significantly reduce the amount of data read from storage. Check the number of files your dataset's resource path resolves to. Large file counts increase query planning time.

503 error when creating a data source with credentials

Elasticsearch encrypts credentials before storing them. If the cluster state encryption key is not available, the request returns 503 SERVICE_UNAVAILABLE. Refer to credential encryption for details.

New files not appearing in query results

Elasticsearch caches file listings for each dataset. A file added to or removed from the bucket might not show up until the listing cache expires. The default listing cache TTL is 5 minutes. Lower esql.external.cache.listing.ttl when new or removed files must be visible sooner. Refer to cluster settings.

Columns with unexpected types or missing values

When Elasticsearch infers a dataset's schema from its files, it might infer types differently than you expect. For example, a date column might appear as a keyword if the values do not match the default datetime format. To inspect the inferred field mappings, refer to check field mappings in the quickstart. Use dataset mappings to declare column types explicitly, or adjust the datetime_format setting. If some rows have null values for a column that exists in other files, check the dataset's schema_resolution setting.

Access denied or connection errors

Credential and permission errors appear at query time, not when the data source is created. If a query returns an access denied error, verify that the credentials in the data source have the required permissions (such as s3:ListBucket and s3:GetObject) and that the region is correct.

• To adjust caching TTLs, file-discovery limits, or request concurrency, refer to cluster settings.
• To control column types or rename columns, declare dataset mappings.
• For general ES|QL tuning advice that also applies to datasets, refer to optimize ES|QL query performance.