Elasticsearch table engine

The Elasticsearch engine allows reading documents from an index in Elasticsearch. Each document is returned as one row: its _source is exposed as a single column of the JSON 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 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

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]

The Elasticsearch integration is experimental. Enable it with the enable_elasticsearch setting:

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 with key = value overrides. See the elasticsearch table function for the full list of keys and the description of the authentication methods. 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` 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

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:

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.

elasticsearch table function

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Creating a table

Virtual columns

Usage example

​Creating a table

​Virtual columns

​Usage example

​Related

Creating a table

Virtual columns

Usage example

Related