--- title: Query external datasets with ES|QL Data Federation description: Query external data with ES|QL Data Federation. Learn how the engine reduces storage reads, query external and indexed data together, and troubleshoot common issues. url: https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-querying products: - Elasticsearch applies_to: - Elastic Cloud Serverless: Unavailable - Elastic Stack: Experimental since 9.5 --- # 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. ```esql FROM my_dataset ``` For a hands-on example, refer to [get started with ES|QL Data Federation](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-quickstart). ## How external data is read 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. ### Column selection Use [`KEEP`](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/commands/keep) or [`DROP`](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/commands/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. ### Partition pruning 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. A filter on a column inside the file can skip folders too, when the dataset sets [`partition_spec`](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-partition-spec). `WHERE ts > "2024-03-15T00:00:00Z"` skips folders whose time range does not overlap that filter. `::datetime` on the ISO string is optional: ES|QL casts a keyword literal to datetime on comparison with a date column. Path keys you do not list in the spec still prune on their own name. 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 [`partition_detection`](/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-dataset-settings#partition-detection). ### Filter and limit pushdown [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/commands/where) conditions and [`LIMIT`](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/commands/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. ```esql 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](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-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. ### Caching Elasticsearch caches dataset metadata on each node so that repeated queries against the same dataset don't discover files and read file metadata again each time: - **File listings** are reused for a fixed time before Elasticsearch lists the storage again. A file added to or removed from the bucket becomes visible once the listing expires. - **Inferred schemas** are invalidated when the underlying file changes. - Elastic Stack: Planned Additionally, a schema is reused for at most 20 minutes by default. - Elastic Stack: Planned **Footers of columnar files**, such as Parquet footers, are kept while queries keep reading them, and dropped 5 minutes after the last read by default. Footer entries are keyed by path and file length rather than by the object's version, so a file overwritten in place at the same byte length shares a cache entry with its predecessor. A write cap bounds how long a stale footer can be served: at the default TTL of 5 minutes, raw footer bytes are force-expired within 30 minutes regardless of how often they are read. Parsed footers can remain longer — up to approximately 60 minutes at the default — because a parsed-cache eviction may reload from raw bytes still within their own cap, starting a new parsed-entry clock. The data in your files is not cached. Every query reads the rows it needs from object storage, so running the same query again or refreshing a dashboard reads the data again. To change cache sizes and TTLs, refer to [caching settings](/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-cluster-settings#caching). ### File discovery limits A dataset's resource path can use [glob patterns](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-patterns) to match many files. These cluster settings bound file discovery: - Elastic Stack: Planned `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](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-cluster-settings) for details. ## Query across datasets and indices Datasets share the same namespace as indices, data streams, aliases, and [ES|QL views](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-views), so `FROM` resolves each name independently. ```esql 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. To see which source each row came from, request a metadata column. For details, refer to [Use metadata columns](#use-metadata-columns). - Elastic Stack: Planned Use `METADATA _name` and `METADATA _class`. `_name` returns the dataset name for dataset rows and the index name for index rows. `_class` returns `index` or `dataset`, so a query can tell the two apart without knowing the names in advance. `_index` returns `null` for dataset rows, because a dataset is not an index. - Elastic Stack: Experimental in 9.5 Use `METADATA _index`, which returns the dataset name for dataset rows. `_class` and `_name` are not available. ### Match datasets with wildcards - Elastic Stack: Planned 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: ```esql 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`](/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-cluster-settings#query-defaults). A value set in the query overrides the request body, which overrides the cluster default. ## Use metadata columns [Metadata columns](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-metadata-fields) are available using the `METADATA` directive: | Column | Returned for a dataset | |-------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | `_class` Elastic Stack: Planned | `dataset` | | `_name` Elastic Stack: Planned | The dataset name. | | `_file.path`, `_file.name`, `_file.directory`, `_file.size`, `_file.modified` | The object each row was read from. | | `_ignored` | null | | `_index_mode`, `_tsid`, `_size` | null | | `_score` | Elastic Stack: Planned `0.0`, or a real per-row value under a scoring `MATCH` or `MATCH_PHRASE`. Refer to [Use search functions](#use-search-functions).Elastic Stack: Experimental in 9.5 null | | `_index` | Elastic Stack: Planned nullElastic Stack: Experimental in 9.5 The dataset name. | | `_id` | Elastic Stack: Planned nullElastic Stack: Experimental in 9.5 A synthetic row ID. | | `_version` | Elastic Stack: Planned nullElastic Stack: Experimental in 9.5 The file modification time. | | `_source` | Elastic Stack: Planned nullElastic Stack: Experimental in 9.5 The row as JSON. | Elastic Stack: Planned `_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. For example, this query returns file-level metadata for each matching row: ```esql FROM access_logs METADATA _file.path, _file.name, _file.size | KEEP _file.path, _file.name, _file.size, status_code | LIMIT 10 ``` Elastic Stack: Planned 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. ## Use search functions [Search functions](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/functions-operators/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. 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. How dataset rows contribute to `METADATA _score` depends on the version: - Elastic Stack: Planned `MATCH` and `MATCH_PHRASE` on dataset rows contribute to the relevance score based on the `boost` option and the query terms matched. Scoring does not use BM25, because a dataset has no index statistics. - Elastic Stack: Experimental in 9.5 Dataset rows do not contribute to `_score`. Runtime `MATCH` on a dataset requires the query value's type to match the field's type. How it handles options and analyzers depends on the version: - Elastic Stack: Planned Text is analyzed with the standard analyzer unless a values analyzer is declared through [`TO_TEXT`](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/functions-operators/type-conversion-functions/to_text)'s `analyzer` option. `MATCH`'s own `analyzer` option applies to the query string only, and defaults to the values analyzer. - Elastic Stack: Experimental in 9.5 `MATCH` does not accept function options, and text is analyzed with a fixed standard analyzer. The following search functions are available for datasets: | Function | Stack | |----------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------| | [`MATCH`](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/functions-operators/search-functions/match) | Elastic Stack: Experimental since 9.5 | | [`MATCH_PHRASE`](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/functions-operators/search-functions/match_phrase) | Elastic Stack: Planned | | `_score` for dataset rows | Elastic Stack: Planned | ## Limitations This feature is experimental. It is not intended for production use and there are no guarantees around performance, scale, or stability in this release. 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. | Operation | Reason | Error | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | `LOOKUP JOIN`, with a dataset as the lookup target | A dataset works as the left (source) side of the join. The lookup target must be an Elasticsearch index. | `LOOKUP JOIN against a dataset is not supported; dataset(s) requested: [...]` | | `TS` (time series) | A time-series source must be an Elasticsearch index. | `TS command is not supported for datasets; dataset(s) requested: [...]` | | Search functions | Search functions work on datasets as runtime search functions, scanning values row by row without an inverted index. Availability varies by version. Refer to the [availability table](#use-search-functions). | `… cannot operate on [], which is not a field from an index mapping (the source is a federated data source, not an index)` | | `KNN` | `KNN` requires a vector field from an index mapping, which a dataset does not have. | `… cannot operate on [], which is not a field from an index mapping (the source is a federated data source, not an index)` | | More than 8 sources in one `FROM` Elastic Stack: Experimental in 9.5 | A `FROM` that includes datasets runs one execution branch per resolved source, up to a limit of 8 branches. Query fewer sources together. | | | More sources in one `FROM` than `max_branch_count_per_merge` allows Elastic Stack: Planned | A `FROM` that includes datasets runs one execution branch per resolved source. The limit is the `esql.query.max_branch_count_per_merge` cluster setting (default 8). Query fewer sources together, or raise the limit. | | | A column with conflicting types across sources | When you query a dataset together with other sources and the same column has types that cannot be reconciled, the query fails rather than returning mixed types. | `Column [] has conflicting data types in subqueries` | | A file column whose name matches a requested `METADATA` name Elastic Stack: Planned | The engine-generated value replaces the file column. Rename the file column in the dataset mapping to keep both. | A warning names the dropped column. | | Document-level security (DLS) and field-level security (FLS) | A dataset's `read` grant cannot carry document- or field-level security. Queries where DLS or FLS applies to a dataset are rejected during authorization. The same check covers [ES|QL views](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-views). | `Datasets with document or field level security restrictions are not supported. Remove DLS/FLS restrictions from the affected datasets in the role definition, or exclude them from the request.` | | [Cross-cluster search](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-cross-clusters) Elastic Stack: Planned | Only local datasets can be queried. A dataset on a remote cluster is invisible: a wildcard that matches its name returns that cluster's indices beside it, and naming it directly resolves to nothing, so the remote's `skip_unavailable` setting decides whether the query fails or that cluster is skipped. | `Unknown index [:]`, when `skip_unavailable` is `false`. | | [Cross-cluster search](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-cross-clusters) Elastic Stack: Experimental in 9.5 | Only local datasets can be queried. A query that matches a remote dataset fails. | `ES\|QL queries with remote datasets are not supported. Matched [...]` | | Snapshot and restore Elastic Stack: Planned | Data sources and datasets are included in snapshots when `include_global_state=true`. After a restore, data source configuration (non-credential settings) and datasets are recovered, but credentials must be re-entered because they are stripped from the snapshot blob. Data sources and datasets that were not present at snapshot time are removed when global state is restored. | | | Snapshot and restore Elastic Stack: Experimental in 9.5 | Data sources and datasets cannot be snapshotted or restored. | | | Archived S3 storage classes | Objects in the S3 Glacier Flexible Retrieval or S3 Glacier Deep Archive storage classes cannot be read in real time. The same applies to objects that have transitioned to the Archive Access or Deep Archive Access tiers within S3 Intelligent-Tiering. Objects in other Intelligent-Tiering tiers are not affected. A `GetObject` call to archived objects returns `InvalidObjectState` until the object is restored. Restore the objects before querying. S3 Glacier Instant Retrieval is also not affected because it supports real-time access. | Elastic Stack: Planned `External data object [] is archived (HTTP 403 InvalidObjectState). It is in storage class []; restore it, or wait for a restore in progress to finish, before reading it.` | | Parquet MAP, nested LIST, and VARIANT | These complex types are not currently supported and return null. STRUCT is supported and flattened to dot-notation column names (for example, `address.city`). | | | `null` elements inside a Parquet LIST | An ES|QL multivalued field cannot hold `null`, so a `null` element inside a list is omitted and the column returns fewer values than the file holds. A list of `[1, null, 2]` reads as `[1, 2]`, and a list whose elements are all `null` reads as `null`. The response includes a warning naming the affected columns. | | ## Troubleshooting If a query against a dataset returns unexpected results or errors, check the following common causes. 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. To check which source each row came from, refer to [Query across datasets and indices](#query-across-datasets-and-indices). Complex Parquet types MAP, nested LIST, and VARIANT also return null, because they are not currently supported. Add [`KEEP`](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/commands/keep) to select only the columns you need, add a [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/commands/where) filter, and add a [`LIMIT`](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/commands/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. 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](/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-security#credential-encryption) for details. 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. Lower the listing cache TTL when new or removed files must be visible sooner. Refer to [caching settings](/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-cluster-settings#caching). 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](/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-quickstart#check-field-mappings) in the quickstart. [Declare the schema explicitly](/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-schema#declare-a-schema-explicitly) to control column types, or adjust the `datetime_format` setting for [CSV and TSV](/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-dataset-settings#csv-datetime-format) or [NDJSON](/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-dataset-settings#ndjson-datetime-format). If some rows have null values for a column that exists in other files, review [schema inference and resolution](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-schema). Credential and permission errors appear at query time, not when the data source or dataset is created. For each access denied message and the permission it needs, refer to [troubleshoot access errors](/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-s3#s3-access-errors) for Amazon S3, and verify that the region is correct. ## Next steps Use the following resources to tune queries and dataset behavior: - To adjust caching TTLs, file-discovery limits, or request concurrency, refer to [cluster settings](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-cluster-settings). - To control column types or rename columns, [declare the dataset schema explicitly](/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-data-federation-schema#declare-a-schema-explicitly). - For general ES|QL tuning advice that also applies to datasets, refer to [optimize ES|QL query performance](https://www.elastic.co/elastic/docs-builder/docs/4493/reference/query-languages/esql/esql-query-performance).