> ## 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.

> Allows SELECT queries to be performed on documents of an index in Elasticsearch.

# elasticsearch

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

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

## Syntax

```sql theme={null}
elasticsearch('url', 'index'[, 'user', 'password'][, key = value, ...][, structure = 'document JSON'][, SETTINGS ...])
elasticsearch(named_collection[, key = value, ...][, structure = 'document JSON'][, SETTINGS ...])
```

> [!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;
> ```

## 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](https://www.elastic.co/docs/explore-analyze/query-filter/languages/querydsl) 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](/docs/reference/engines/table-engines/integrations/elasticsearch#creating-a-table) for their description.

## Authentication

At most one authentication method can be provided:

1. **Basic authentication**. Pass `user` and `password`.
2. **Bearer token**. Pass `bearer_token`.
3. **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](/docs/concepts/features/configuration/server-config/named-collections) 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:

```sql theme={null}
SELECT count()
FROM elasticsearch('https://localhost:9200', 'logs', 'elastic', 'changeme');
```

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

```sql theme={null}
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:

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

Use a named collection:

```xml theme={null}
<clickhouse>
    <named_collections>
        <my_elasticsearch>
            <url>https://localhost:9200</url>
            <user>elastic</user>
            <password>changeme</password>
        </my_elasticsearch>
    </named_collections>
</clickhouse>
```

```sql theme={null}
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).

## Related

* [`Elasticsearch` table engine](/docs/reference/engines/table-engines/integrations/elasticsearch)