Loading

Get started with ES|QL Data Federation

This guide walks you through connecting Elasticsearch to external data and querying it with ES|QL. By the end, you have a working data source, a dataset, and a query returning results from external storage.

The example uses the Ookla Open Speedtest dataset (GitHub), a publicly accessible collection of internet performance metrics aggregated by geographic tile (a small area on the map). It mirrors a common observability pattern: analyzing network performance data stored in cloud storage alongside operational data indexed in Elasticsearch. Because the bucket allows anonymous access, you can follow along without AWS credentials.

Warning

This feature is experimental. It is not intended for production use and there are no guarantees around performance, scale, or stability in this release.

Make sure you have the following:

  • An Elasticsearch deployment running version 9.5 or later, with ES|QL Data Federation enabled.
  • An Enterprise subscription for Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, or self-managed deployments.
  • The cluster manage privilege to create data sources.
  • The index manage privilege to create datasets.
Important

This quickstart queries a public S3 bucket. If queries return 503 errors, the bucket may be temporarily throttled due to high traffic. Wait a few minutes and try again.

These steps walk you through registering a data source, creating a dataset, and querying federated data with ES|QL.

  1. Register a data source

    A data source defines the connection to an external storage system, including its type, region, and credentials. Once registered, any number of datasets can reference it.

    This example registers a data source that points at a public S3 bucket with anonymous access.

    				PUT /_query/data_source/ookla_speedtest
					{
  "type": "s3",
  "settings": {
    "region": "us-east-1",
    "auth": "anonymous"
  }
}
    1. Enables anonymous access for public buckets. For private data, refer to the authentication overview.

    A successful request returns {"acknowledged": true}.

    curl -X PUT "${ELASTICSEARCH_URL}/_query/data_source/ookla_speedtest" \
  -H "Authorization: ApiKey ${API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
  "type": "s3",
  "settings": {
    "region": "us-east-1",
    "auth": "anonymous"
  }
}'

    A successful request returns {"acknowledged": true}.

    1. Go to Data management > ES|QL Data Federation.
    2. On the Data sources tab, click Connect data source.
    3. Set Data source type to Amazon S3.
    4. Enter ookla_speedtest as the Name.
    5. Set Region to us-east-1.
    6. Under Authentication, from the Preferred method menu, select Anonymous.
    7. Click Connect.

    Confirm the data source was created:

    				GET /_query/data_source/ookla_speedtest
		
    curl -X GET "${ELASTICSEARCH_URL}/_query/data_source/ookla_speedtest" \
  -H "Authorization: ApiKey ${API_KEY}"

    The new data source appears on the Data sources tab, showing its type and region:

    The Data sources tab listing the ookla_speedtest data source
    Note

    Creating a data source does not validate connectivity to the external system. To verify that a data source is working, create a dataset that references it and run a query. If the credentials or endpoint are incorrect, the query returns an error.

  2. Create a dataset

    A dataset points at specific files within a data source and makes them queryable as a virtual index. It references a data source by name and specifies a resource path that identifies the files to read.

    This example creates a dataset over one quarter of Ookla's fixed-broadband performance data. Each Parquet file contains speedtest results aggregated into geographic tiles. The key columns are:

    • avg_d_kbps, avg_u_kbps: average download and upload throughput per geographic tile, in kbps
    • avg_lat_ms: average latency per geographic tile, in milliseconds
    • tests, devices: number of speedtests and unique devices per geographic tile

    For the full column reference, refer to the Ookla Open Data tile attributes.

    				PUT /_query/dataset/speedtest_fixed
					{
  "data_source": "ookla_speedtest",
  "resource": "s3://ookla-open-data/parquet/performance/type=fixed/year=2024/quarter=1/*.parquet"
}
    1. The name of the data source to connect through.
    2. A glob pattern matching all Parquet files for Q1 2024 fixed-broadband tests. The * wildcard matches any filename in that directory.

    A successful request returns {"acknowledged": true}.

    curl -X PUT "${ELASTICSEARCH_URL}/_query/dataset/speedtest_fixed" \
  -H "Authorization: ApiKey ${API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
  "data_source": "ookla_speedtest",
  "resource": "s3://ookla-open-data/parquet/performance/type=fixed/year=2024/quarter=1/*.parquet"
}'

    A successful request returns {"acknowledged": true}.

    1. Select the Datasets tab, then click Add dataset.

    2. Select ookla_speedtest as the Data source.

    3. Enter speedtest_fixed as the Name.

    4. In Resource, enter the resource path that selects the files to read:

      s3://ookla-open-data/parquet/performance/type=fixed/year=2024/quarter=1/*.parquet

    5. Set Format to Parquet.

    6. Click Add.

    Show the completed Add dataset flyout
    Add dataset flyout configured for the Ookla Q1 2024 fixed-broadband Parquet files

    Confirm the dataset was created:

    				GET /_query/dataset/speedtest_fixed
		
    curl -X GET "${ELASTICSEARCH_URL}/_query/dataset/speedtest_fixed" \
  -H "Authorization: ApiKey ${API_KEY}"

    The new dataset appears on the Datasets tab, showing its data source and resource:

    The Datasets tab listing the speedtest_fixed dataset

  3. Check field mappings

    Before writing queries, check what field mappings Elasticsearch inferred from the Parquet files. Query the dataset with LIMIT 1 to return a single row with all columns:

    				POST /_query
					{
  "query": "FROM speedtest_fixed | LIMIT 1"
}
		
    curl -X POST "${ELASTICSEARCH_URL}/_query" \
  -H "Authorization: ApiKey ${API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
  "query": "FROM speedtest_fixed | LIMIT 1"
}'
		
    FROM speedtest_fixed
| LIMIT 1

    The response lists every column name and its inferred type:

    View column names and types 
    { "name": "quadkey", "type": "keyword" }
{ "name": "tile", "type": "keyword" }
{ "name": "tile_x", "type": "double" }
{ "name": "tile_y", "type": "double" }
{ "name": "avg_d_kbps", "type": "long" }
{ "name": "avg_u_kbps", "type": "long" }
{ "name": "avg_lat_ms", "type": "long" }
{ "name": "avg_lat_down_ms", "type": "integer" }
{ "name": "avg_lat_up_ms", "type": "integer" }
{ "name": "tests", "type": "long" }
{ "name": "devices", "type": "long" }
{ "name": "type", "type": "keyword" }
{ "name": "year", "type": "integer" }
{ "name": "quarter", "type": "integer" }

    The type, year, and quarter columns come from Hive-style partition paths in the S3 bucket. Elasticsearch detects these automatically when partition_detection is set to auto (the default).

    Tip

    If a column has an unexpected type, you can override it with a dataset mapping.

  4. Run your first query

    Once a dataset exists, query it with FROM just like any Elasticsearch index. This query selects the key performance columns and returns five rows:

    				POST /_query
					{
  "query": """
    FROM speedtest_fixed
    | KEEP avg_d_kbps, avg_u_kbps, avg_lat_ms, tests
    | LIMIT 5
  """
}
		
    curl -X POST "${ELASTICSEARCH_URL}/_query" \
  -H "Authorization: ApiKey ${API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
  "query": "FROM speedtest_fixed | KEEP avg_d_kbps, avg_u_kbps, avg_lat_ms, tests | LIMIT 5"
}'
		
    FROM speedtest_fixed
| KEEP avg_d_kbps, avg_u_kbps, avg_lat_ms, tests
| LIMIT 5

    If the query returns results, your data source is working. You can now use the full range of ES|QL processing commands on this dataset.

  5. Explore the data

    Now that the dataset is working, try some more expressive queries.

    Convert units and filter for the fastest geographic tiles

    EVAL creates new columns from expressions. Here it converts the raw kbps values to Mbps using ROUND, then WHERE filters for geographic tiles with a meaningful sample size. KEEP selects only the columns you need in the output, which also reduces the data read from storage for Parquet files.

    				POST /_query
					{
  "query": """
    FROM speedtest_fixed
    | EVAL download_mbps = ROUND(avg_d_kbps / 1000.0, 1),
           upload_mbps = ROUND(avg_u_kbps / 1000.0, 1)
    | WHERE tests > 100
    | SORT download_mbps DESC
    | KEEP download_mbps, upload_mbps, avg_lat_ms, tests, devices
    | LIMIT 10
  """
}
		
    curl -X POST "${ELASTICSEARCH_URL}/_query" \
  -H "Authorization: ApiKey ${API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
  "query": "FROM speedtest_fixed | EVAL download_mbps = ROUND(avg_d_kbps / 1000.0, 1), upload_mbps = ROUND(avg_u_kbps / 1000.0, 1) | WHERE tests > 100 | SORT download_mbps DESC | KEEP download_mbps, upload_mbps, avg_lat_ms, tests, devices | LIMIT 10"
}'
		
    FROM speedtest_fixed
| EVAL download_mbps = ROUND(avg_d_kbps / 1000.0, 1),
       upload_mbps = ROUND(avg_u_kbps / 1000.0, 1)
| WHERE tests > 100
| SORT download_mbps DESC
| KEEP download_mbps, upload_mbps, avg_lat_ms, tests, devices
| LIMIT 10

    Break down speeds by test volume

    CASE evaluates conditions in order and returns the first match, with the last argument as the default. STATS ... BY groups the results and computes one row per bucket. You can nest scalar functions like ROUND around aggregate functions like AVG in the same expression. This query buckets geographic tiles by how many tests they recorded and compares average speeds across buckets.

    				POST /_query
					{
  "query": """
    FROM speedtest_fixed
    | EVAL download_mbps = avg_d_kbps / 1000.0
    | EVAL bucket = CASE(
        tests < 10, "< 10 tests",
        tests < 100, "10-99 tests",
        tests < 1000, "100-999 tests",
        ">= 1000 tests")
    | STATS avg_download = ROUND(AVG(download_mbps), 1),
            avg_latency = ROUND(AVG(avg_lat_ms), 1),
            tile_count = COUNT(*)
        BY bucket
    | SORT avg_download DESC
  """
}
		
    curl -X POST "${ELASTICSEARCH_URL}/_query" \
  -H "Authorization: ApiKey ${API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
  "query": "FROM speedtest_fixed | EVAL download_mbps = avg_d_kbps / 1000.0 | EVAL bucket = CASE(tests < 10, \"< 10 tests\", tests < 100, \"10-99 tests\", tests < 1000, \"100-999 tests\", \">= 1000 tests\") | STATS avg_download = ROUND(AVG(download_mbps), 1), avg_latency = ROUND(AVG(avg_lat_ms), 1), tile_count = COUNT(*) BY bucket | SORT avg_download DESC"
}'
		
    FROM speedtest_fixed
| EVAL download_mbps = avg_d_kbps / 1000.0
| EVAL bucket = CASE(
    tests < 10, "< 10 tests",
    tests < 100, "10-99 tests",
    tests < 1000, "100-999 tests",
    ">= 1000 tests")
| STATS avg_download = ROUND(AVG(download_mbps), 1),
        avg_latency = ROUND(AVG(avg_lat_ms), 1),
        tile_count = COUNT(*)
    BY bucket
| SORT avg_download DESC

    Analyze speed distributions with percentiles

    MEDIAN returns the 50th percentile and PERCENTILE(field, 95) returns the value at the 95th percentile, showing the speed that only 5% of geographic tiles exceed. Together they reveal how speeds are distributed, not just the average.

    				POST /_query
					{
  "query": """
    FROM speedtest_fixed
    | WHERE tests > 50
    | STATS
        median_down = ROUND(MEDIAN(avg_d_kbps) / 1000.0, 1),
        p95_down = ROUND(PERCENTILE(avg_d_kbps, 95) / 1000.0, 1),
        median_latency = ROUND(MEDIAN(avg_lat_ms), 0),
        p95_latency = ROUND(PERCENTILE(avg_lat_ms, 95), 0),
        tiles = COUNT(*)
  """
}
		
    curl -X POST "${ELASTICSEARCH_URL}/_query" \
  -H "Authorization: ApiKey ${API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
  "query": "FROM speedtest_fixed | WHERE tests > 50 | STATS median_down = ROUND(MEDIAN(avg_d_kbps) / 1000.0, 1), p95_down = ROUND(PERCENTILE(avg_d_kbps, 95) / 1000.0, 1), median_latency = ROUND(MEDIAN(avg_lat_ms), 0), p95_latency = ROUND(PERCENTILE(avg_lat_ms, 95), 0), tiles = COUNT(*)"
}'
		
    FROM speedtest_fixed
| WHERE tests > 50
| STATS
    median_down = ROUND(MEDIAN(avg_d_kbps) / 1000.0, 1),
    p95_down = ROUND(PERCENTILE(avg_d_kbps, 95) / 1000.0, 1),
    median_latency = ROUND(MEDIAN(avg_lat_ms), 0),
    p95_latency = ROUND(PERCENTILE(avg_lat_ms, 95), 0),
    tiles = COUNT(*)

  6. Query federated and indexed data together

    Datasets share the same namespace as regular indices, so you can query both in a single FROM. This lets you combine external data with indexed data in a single query.

    First, create an index with a few sample documents to query alongside the dataset:

    				PUT /network_incidents
					{
  "mappings": {
    "properties": {
      "category":     { "type": "keyword" },
      "severity":     { "type": "keyword" },
      "duration_min": { "type": "integer" }
    }
  }
}
		
    curl -X PUT "${ELASTICSEARCH_URL}/network_incidents" \
  -H "Authorization: ApiKey ${API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
  "mappings": {
    "properties": {
      "category":     { "type": "keyword" },
      "severity":     { "type": "keyword" },
      "duration_min": { "type": "integer" }
    }
  }
}'

    Then index a few documents:

    				POST /_bulk
					{"index":{"_index":"network_incidents"}}
{"category":"outage","severity":"high","duration_min":45}
{"index":{"_index":"network_incidents"}}
{"category":"degradation","severity":"medium","duration_min":12}
{"index":{"_index":"network_incidents"}}
{"category":"outage","severity":"low","duration_min":8}
		
    curl -X POST "${ELASTICSEARCH_URL}/_bulk" \
  -H "Authorization: ApiKey ${API_KEY}" \
  -H "Content-Type: application/x-ndjson" \
  -d '
{"index":{"_index":"network_incidents"}}
{"category":"outage","severity":"high","duration_min":45}
{"index":{"_index":"network_incidents"}}
{"category":"degradation","severity":"medium","duration_min":12}
{"index":{"_index":"network_incidents"}}
{"category":"outage","severity":"low","duration_min":8}
'

    Now query both sources together. FROM resolves each name independently, whether it is an index, data stream, alias, ES|QL view, or dataset. Use METADATA _index to see where each row came from:

    				POST /_query
					{
  "query": """
    FROM speedtest_fixed, network_incidents METADATA _index
    | KEEP _index, category, severity, duration_min, avg_d_kbps, avg_lat_ms
    | SORT _index ASC, duration_min DESC NULLS LAST
    | LIMIT 5
  """
}
		
    curl -X POST "${ELASTICSEARCH_URL}/_query" \
  -H "Authorization: ApiKey ${API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
  "query": "FROM speedtest_fixed, network_incidents METADATA _index | KEEP _index, category, severity, duration_min, avg_d_kbps, avg_lat_ms | SORT _index ASC, duration_min DESC NULLS LAST | LIMIT 5"
}'
		
    FROM speedtest_fixed, network_incidents METADATA _index
| KEEP _index, category, severity, duration_min, avg_d_kbps, avg_lat_ms
| SORT _index ASC, duration_min DESC NULLS LAST
| LIMIT 5

    The _index column shows where each row came from. Columns that do not exist in a given source return null. The speedtest values in your results will differ. Execution metadata is omitted here:

    {
  "columns": [
    { "name": "_index", "type": "keyword" },
    { "name": "category", "type": "keyword" },
    { "name": "severity", "type": "keyword" },
    { "name": "duration_min", "type": "integer" },
    { "name": "avg_d_kbps", "type": "long" },
    { "name": "avg_lat_ms", "type": "long" }
  ],
  "values": [
    ["network_incidents", "outage",      "high",   45,   null, null],
    ["network_incidents", "degradation", "medium", 12,   null, null],
    ["network_incidents", "outage",      "low",     8,   null, null],
    ["speedtest_fixed",   null,           null,   null, 158062, 223],
    ["speedtest_fixed",   null,           null,   null,  64266, 165]
  ]
}

The quickstart uses a public bucket with anonymous access. To connect to a private bucket, supply credentials when registering the data source. Several authentication methods are available. For example, using static credentials:

				PUT /_query/data_source/my_s3_logs
					{
  "type": "s3",
  "description": "Production logs bucket",
  "settings": {
    "region": "us-east-1",
    "auth": "static_credentials",
    "access_key": "<AWS_ACCESS_KEY_ID>",
    "secret_key": "<AWS_SECRET_ACCESS_KEY>"
  }
}
Important

When a data source includes credentials, Elasticsearch encrypts them before storing them in the cluster state, using the cluster state encryption key. This key is available automatically in most deployments. If it is not available, a request that includes credentials returns a 503 error. Refer to credential encryption for details.

Credential values are never returned in API responses. When you retrieve a data source, secrets are replaced by ::es_redacted::.

To remove the resources created in this guide, delete the dataset first because a data source cannot be deleted while datasets reference it. Then delete the data source and the sample index:

				DELETE /_query/dataset/speedtest_fixed
				DELETE /_query/data_source/ookla_speedtest
				DELETE /network_incidents
		
curl -X DELETE "${ELASTICSEARCH_URL}/_query/dataset/speedtest_fixed" \
  -H "Authorization: ApiKey ${API_KEY}"

curl -X DELETE "${ELASTICSEARCH_URL}/_query/data_source/ookla_speedtest" \
  -H "Authorization: ApiKey ${API_KEY}"

curl -X DELETE "${ELASTICSEARCH_URL}/network_incidents" \
  -H "Authorization: ApiKey ${API_KEY}"

Now that you have a working data source and dataset, you can: