elasticsearch

Allows SELECT queries on documents of 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 function is read-only.

Syntax

elasticsearch('url', 'index'[, 'user', 'password'][, key = value, ...][, structure = 'document JSON'][, SETTINGS ...])
elasticsearch(named_collection[, key = value, ...][, structure = 'document JSON'][, SETTINGS ...])

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

SET enable_elasticsearch = 1;

Arguments

Argument	Description
`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 comma-separated list, or a wildcard pattern, as accepted by the Elasticsearch point in time API. A pattern that matches no index is an error.
`user`	The user name for HTTP basic authentication (optional).
`password`	The password for HTTP basic authentication (optional, requires `user`).

The url, index, user and password arguments can also be given in the key = value form; positional arguments fill these slots in this order, and specifying an argument both positionally and as a key (or the same key twice) is an error. The following arguments can be specified in the key = value form (or as keys of a named collection):

Key	Description
`user`	The user name for HTTP basic authentication.
`password`	The password for HTTP basic authentication.
`bearer_token`	A token sent as `Authorization: Bearer <token>`, for example an Elasticsearch service account token.
`api_key`	An Elasticsearch API key, sent as `Authorization: ApiKey <api_key>`. The value is the base64-encoded `id:api_key` string returned by the create API key API.
`query`	An Elasticsearch Query DSL object in JSON that selects the documents to read. Default: `{"match_all": {}}`.

In addition, the table function accepts:

Key	Description
`structure`	The table structure in the form `'name JSON'`. The structure must contain exactly one column of type `JSON`; the names `_id` and `_index` are reserved. Default: `'document JSON'`.

The read settings keep_alive and page_size can be passed in a trailing SETTINGS clause; see the Elasticsearch table engine for their description.

Authentication

At most one authentication method can be provided:

Basic authentication. Pass user and password.
Bearer token. Pass bearer_token.
API key. Pass api_key.

Without any of them the request is sent anonymously, which only works when security is disabled in Elasticsearch. Store credentials in a named collection to avoid specifying them in each query. A permanent table created from a named collection (with the Elasticsearch table engine or CREATE TABLE ... AS elasticsearch(...)) is registered as a dependency of the collection, so DROP NAMED COLLECTION is blocked while the table exists.

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.

Examples

Count the documents of an index:

SELECT count()
FROM elasticsearch('https://localhost:9200', 'logs', 'elastic', 'changeme');

Read fields of the documents as subcolumns of the JSON column:

SELECT _id, document.timestamp, document.message
FROM elasticsearch('https://localhost:9200', 'logs', api_key = '...')
ORDER BY document.timestamp DESC
LIMIT 10;

Filter on the Elasticsearch side with a Query DSL object and tune the page size:

SELECT _id
FROM elasticsearch('https://localhost:9200', 'logs',
                   bearer_token = '...',
                   query = '{"term": {"level": "error"}}',
                   SETTINGS page_size = 1000);

Use a named collection:

<clickhouse>
    <named_collections>
        <my_elasticsearch>
            <url>https://localhost:9200</url>
            <user>elastic</user>
            <password>changeme</password>
        </my_elasticsearch>
    </named_collections>
</clickhouse>

SELECT * FROM elasticsearch(my_elasticsearch, index = 'logs');

Limitations

The function is read-only; INSERT is not supported.
Predicates are not pushed down. A WHERE condition is applied in ClickHouse after the documents have been downloaded; use the query argument to filter on the Elasticsearch side.
The query is sent with the size of a page. A query whose result depends on size, such as a knn query without k, returns a set of documents that depends on page_size. Specify such parameters explicitly.
Each page is a separate search request, and Elasticsearch evaluates date math such as now-1h in the query anew for each request. The point in time freezes the documents but not the clock, so a time-dependent query can silently skip documents near the moving boundary. Use absolute dates in the query.
Each read opens a point in time, pages through the results with search_after sorted by _shard_doc, and closes the point in time at the end. A page request that fails on any shard, or that times out in Elasticsearch, raises an error instead of returning partial results.
Elasticsearch 7.12 or later is required: the function relies on the point in time API and on the _shard_doc sort field, so older versions and OpenSearch reject the first request.
page_size cannot exceed the index.max_result_window index setting of Elasticsearch (10000 by default).

Elasticsearch table engine

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Syntax

Arguments

Authentication

Virtual columns

Examples

Limitations

​Syntax

​Arguments

​Authentication

​Virtual columns

​Examples

​Limitations

​Related

Syntax

Arguments

Authentication

Virtual columns

Examples

Limitations

Related