> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

> The engine allows reading documents from an index in Elasticsearch.

# Elasticsearch table engine

[Experimental feature](https://clickhouse.com/docs/reference/settings/beta-and-experimental-features#experimental-features)

The `Elasticsearch` engine allows reading documents from an index in [Elasticsearch](https://www.elastic.co/elasticsearch). Each document is returned as one row: its `_source` is exposed as a single column of the [JSON](/docs/reference/data-types/newjson) type, and the document id and index name are available as virtual columns.

The engine is read-only. Documents are fetched with the `_search` API in pages, using a [point in time](https://www.elastic.co/docs/reference/elasticsearch/rest-apis/paginate-search-results#search-after) so that a query sees a consistent snapshot of the index even while it is being modified.

Elasticsearch 7.12 or later is required: the engine relies on the point in time API and on the `_shard_doc` sort field. OpenSearch is not supported, because it has no `_shard_doc` field. An older server rejects the first request with an error.

## Creating a table

```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db.]table_name
[(
    name1 JSON
)]
ENGINE = Elasticsearch('url', 'index'[, 'user', 'password'][, key = value, ...])
[SETTINGS keep_alive = 60, page_size = 10000]
```

> [!NOTE]
>
> The Elasticsearch integration is experimental. Enable it with the [`enable_elasticsearch`](/docs/reference/settings/session-settings/enable#enable_elasticsearch) setting:
>
> ```sql theme={null}
> SET enable_elasticsearch = 1;
> ```

The column list is optional. When omitted, the table has a single column `document` of type `JSON`. When specified, it must contain exactly one column, and its type must be `JSON`, optionally with type hints and skip paths. `MATERIALIZED`, `ALIAS` and `EPHEMERAL` columns are not supported, and the names `_id` and `_index` are reserved for the virtual columns.

**Engine parameters**

* `url` — The base URL of the Elasticsearch cluster, for example `https://localhost:9200`. It must be an `http` or `https` URL without credentials, query string or fragment.
* `index` — The index to read. It can also be an alias, a data stream, a comma-separated list, a wildcard pattern, or a date math expression such as `<logs-{now/d}>`, as accepted by the Elasticsearch point in time API. The value is the literal name and must not be URL-encoded. A pattern that matches no index is an error.
* `user`, `password` — Optional credentials for HTTP basic authentication.

The parameters can also be passed as a [named collection](/docs/concepts/features/configuration/server-config/named-collections) with `key = value` overrides. See the [`elasticsearch` table function](/docs/reference/functions/table-functions/elasticsearch#arguments) for the full list of keys and the description of the [authentication methods](/docs/reference/functions/table-functions/elasticsearch#authentication).

**Engine settings**

| Setting | Default | Description |
| - | - | - |
| `keep_alive` | `60` | How long, in seconds, Elasticsearch keeps the point in time open between two consecutive page requests. |
| `page_size` | `10000` | The number of documents requested from Elasticsearch per request. Each page becomes one block, so this setting also sets the block size of the read; [`max_block_size`](/docs/reference/settings/session-settings/max#max_block_size) has no effect. Values above the `index.max_result_window` index setting of Elasticsearch (`10000` by default) are rejected by Elasticsearch. |

The settings cannot be changed with `ALTER TABLE ... MODIFY SETTING`. To change them, recreate the table.

## Virtual columns

* `_id` — The document id. Type: `String`.
* `_index` — The name of the index the document was read from. Type: `LowCardinality(String)`.

When a query reads only virtual columns, the document `_source` is not requested from Elasticsearch. An index that does not store `_source` (the `_source` field is disabled in its mapping) can be read only through the virtual columns: a query that reads the `JSON` column from such an index fails with an error instead of returning empty documents.

## Usage example

```sql theme={null}
CREATE TABLE logs
ENGINE = Elasticsearch('https://localhost:9200', 'logs-*', 'elastic', 'changeme')
SETTINGS page_size = 5000;

SELECT _index, _id, document.message
FROM logs
WHERE document.level = 'error'
LIMIT 10;
```

Filter on the Elasticsearch side with a Query DSL object:

```sql theme={null}
CREATE TABLE errors_since_october
ENGINE = Elasticsearch('https://localhost:9200', 'logs-*',
                       api_key = '...',
                       query = '{"bool": {"filter": [{"term": {"level": "error"}}, {"range": {"@timestamp": {"gte": "2026-10-01T00:00:00Z"}}}]}}');
```

Use absolute dates in the `query`: date math such as `now-1h` is evaluated anew for each page, so it can skip documents. See the [limitations of the `elasticsearch` table function](/docs/reference/functions/table-functions/elasticsearch#limitations).

## Related

* [`elasticsearch` table function](/docs/reference/functions/table-functions/elasticsearch)