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

# Elasticsearch Gateway

> Use Kibana or OpenSearch Dashboards against Pinot through the Elasticsearch-compatible gateway.

The StarTree Pinot Elasticsearch Gateway exposes a read-only Elasticsearch-compatible API for Pinot tables. It lets tools such as Kibana and OpenSearch Dashboards discover Pinot tables as indexes, issue Elasticsearch Query DSL requests, and receive Elasticsearch-shaped JSON responses.

<Info>
  The gateway is a query translation layer. It does not copy data out of Pinot and it does not turn Pinot into a writable Elasticsearch cluster.
</Info>

## Architecture

```text theme={null}
Kibana / OpenSearch Dashboards
  -> Elasticsearch REST API + Query DSL
  -> StarTree proxy /es endpoint
  -> Elasticsearch Gateway
  -> Pinot broker SQL
  -> Elasticsearch-shaped JSON response
```

The gateway is mounted under the StarTree proxy `/es` base path when enabled.

## When to Use

Use the Elasticsearch Gateway when:

* Observability or BI users already know Kibana or OpenSearch Dashboards.
* You want to browse Pinot tables without exporting data into Elasticsearch.
* Your dashboard uses common `_search`, `_count`, `_msearch`, field-caps, mapping, and single-level aggregation requests.
* You want Pinot text indexes to back simple full-text Query DSL clauses.

Use Pinot SQL directly when you need joins, complex expressions, multi-stage query planning, or Query DSL features outside the gateway's supported subset.

## Enable the Gateway

The gateway is disabled by default. Enable it on the StarTree proxy:

```properties theme={null}
pinot.proxy.elasticsearch.gateway.enabled=true
pinot.proxy.elasticsearch.gateway.cluster.name=pinot
pinot.proxy.elasticsearch.gateway.version=7.10.2
```

Point Kibana or OpenSearch Dashboards at the proxy `/es` path.

OpenSearch Dashboards:

```yaml theme={null}
opensearch.hosts: ["http://<proxy-host>:8123/es"]
opensearch.ignoreVersionMismatch: true
```

Kibana:

```yaml theme={null}
elasticsearch.hosts: ["http://<proxy-host>:8123/es"]
```

<Warning>
  Kibana saved objects require a writable `.kibana` index. Pinot is read-only through this gateway, so route `.kibana*` traffic to a real Elasticsearch or OpenSearch cluster if the UI needs to save index patterns, dashboards, or preferences.
</Warning>

## Verify the Endpoint

Check the handshake:

```bash theme={null}
curl -s "http://localhost:8123/es/" | jq '{cluster_name, version: .version.number}'
```

Example response:

```json theme={null}
{
  "cluster_name": "pinot",
  "version": "7.10.2"
}
```

List Pinot tables as Elasticsearch indexes:

```bash theme={null}
curl -s "http://localhost:8123/es/_cat/indices?format=json" | jq '.[].index'
```

Resolve a table pattern:

```bash theme={null}
curl -s "http://localhost:8123/es/_resolve/index/baseball*" | jq
```

## Supported Endpoints

| Endpoint                                | Purpose                                                  |
| --------------------------------------- | -------------------------------------------------------- |
| `GET /es/`                              | Version and root handshake.                              |
| `GET /es/_cluster/health`               | Cluster bootstrap probe.                                 |
| `GET /es/_cluster/settings`             | Cluster settings probe.                                  |
| `GET /es/_nodes`                        | Node metadata probe.                                     |
| `GET /es/_cat/indices`                  | Index/table discovery.                                   |
| `GET /es/_resolve/index/{expression}`   | Resolve wildcard index expressions.                      |
| `GET` or `POST /es/{index}/_field_caps` | Field capability discovery from Pinot schema.            |
| `GET /es/{index}/_mapping`              | Elasticsearch-style mapping generated from Pinot schema. |
| `GET` or `POST /es/{index}/_search`     | Query one Pinot table.                                   |
| `POST /es/_msearch`                     | Query multiple searches in one request.                  |
| `POST /es/{index}/_count`               | Count matching rows.                                     |

Write endpoints such as `_doc`, `_bulk`, and index creation are not supported and return an error.

## Query Examples

### Basic Search

```bash theme={null}
curl -s -X POST "http://localhost:8123/es/baseballStats/_search" \
  -H "Content-Type: application/json" \
  -d '{
    "size": 2,
    "_source": ["playerName", "yearID", "runs"],
    "sort": [{"yearID": "desc"}]
  }' | jq '.hits.hits[]._source'
```

Equivalent Pinot SQL shape:

```sql theme={null}
SELECT playerName, yearID, runs
FROM baseballStats
ORDER BY yearID DESC
LIMIT 2
```

### Range Filter

```bash theme={null}
curl -s -X POST "http://localhost:8123/es/baseballStats/_count" \
  -H "Content-Type: application/json" \
  -d '{
    "query": {
      "range": {
        "yearID": {
          "gte": 2000
        }
      }
    }
  }' | jq '.count'
```

Equivalent Pinot SQL shape:

```sql theme={null}
SELECT COUNT(*)
FROM baseballStats
WHERE yearID >= 2000
```

### Terms Aggregation

```bash theme={null}
curl -s -X POST "http://localhost:8123/es/baseballStats/_search" \
  -H "Content-Type: application/json" \
  -d '{
    "size": 0,
    "aggs": {
      "byTeam": {
        "terms": {
          "field": "teamID",
          "size": 3
        },
        "aggs": {
          "totalHits": {
            "sum": {
              "field": "hits"
            }
          }
        }
      }
    }
  }' | jq '.aggregations.byTeam.buckets'
```

Equivalent Pinot SQL shape:

```sql theme={null}
SELECT teamID, COUNT(*), SUM(hits)
FROM baseballStats
GROUP BY teamID
ORDER BY SUM(hits) DESC
LIMIT 3
```

## Query DSL Support

Supported leaf clauses:

| Clause                                         | Translation                                               |
| ---------------------------------------------- | --------------------------------------------------------- |
| `match_all`                                    | No filter.                                                |
| `match_none`                                   | Unsatisfiable predicate.                                  |
| `term`, `terms`                                | Equality or `IN` predicates.                              |
| `range`                                        | Numeric or timestamp range predicates.                    |
| `exists`                                       | Not-null predicate.                                       |
| `prefix`, `wildcard`                           | SQL string predicates.                                    |
| `match`, `match_phrase`, `match_phrase_prefix` | `TEXT_MATCH` on text-indexed columns, fallback otherwise. |
| `multi_match`                                  | OR across supported target fields.                        |
| `query_string`, `simple_query_string`          | Pragmatic subset mapped to text or string predicates.     |

Supported compound clause:

| Clause                        | Translation |
| ----------------------------- | ----------- |
| `bool.must` and `bool.filter` | `AND`.      |
| `bool.should`                 | `OR`.       |
| `bool.must_not`               | Negation.   |

Supported aggregations:

* One bucket aggregation level: `date_histogram`, `terms`, or `histogram`.
* Metric sub-aggregations: `avg`, `sum`, `min`, `max`, `value_count`, and `cardinality`.
* Metric-only aggregations.

Sorting, paging, field selection, `_source`, and `track_total_hits` are supported. Field names ending in `.keyword` are normalized to the underlying Pinot column.

## Full-Text Search

Full-text Query DSL clauses use Pinot's Lucene-backed `TEXT_MATCH` only when the target column has a Pinot text index.

Add a text index to searchable string columns:

```json theme={null}
{
  "fieldConfigList": [
    {
      "name": "message",
      "encodingType": "RAW",
      "indexTypes": ["TEXT"]
    }
  ]
}
```

Example match query:

```bash theme={null}
curl -s -X POST "http://localhost:8123/es/logs/_search" \
  -H "Content-Type: application/json" \
  -d '{
    "size": 10,
    "query": {
      "match_phrase": {
        "message": "payment timeout"
      }
    },
    "_source": ["ts", "service", "message"]
  }'
```

Text-indexed translation:

```sql theme={null}
TEXT_MATCH("message", '"payment timeout"')
```

On columns without a text index, the gateway falls back to exact-match or `LIKE` behavior. It does not reproduce Elasticsearch relevance scoring; results are unranked unless the request includes an explicit sort.

## Create an Index Pattern

In Kibana or OpenSearch Dashboards:

1. Open the index-pattern or data-view creation screen.
2. Enter a Pinot table name such as `baseballStats`.
3. Select an epoch-millis `LONG` or timestamp column as the time field if the table has one.
4. Open Discover and query the table.

The UI gets field metadata from:

```bash theme={null}
curl -s "http://localhost:8123/es/baseballStats/_field_caps?fields=*" | jq '.fields | keys'
```

## Limitations

* Read/query path only. Writes, bulk ingest, and index creation are not supported.
* Saved objects require a separate writable Elasticsearch/OpenSearch store.
* Nested bucket aggregations are not supported.
* `date_histogram` assumes an epoch-millis numeric or timestamp time column.
* Offset paging with `from > 0` is stable only when the request supplies a sort.
* Query DSL support is intentionally partial and optimized for common dashboard/discovery workflows.

## Troubleshooting

| Symptom                                  | Cause                                                       | Fix                                                                                                           |
| ---------------------------------------- | ----------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------- |
| UI cannot connect                        | Gateway flag is off or URL does not include `/es`.          | Enable `pinot.proxy.elasticsearch.gateway.enabled=true` and set the UI host to `http://<proxy-host>:8123/es`. |
| Version mismatch warning                 | The gateway advertises an Elasticsearch-compatible version. | Set `opensearch.ignoreVersionMismatch: true` for OpenSearch Dashboards.                                       |
| Index pattern cannot be saved            | Kibana saved objects need a writable `.kibana` index.       | Route `.kibana*` to real OpenSearch/Elasticsearch.                                                            |
| Discover is empty but `_search` works    | Selected time range excludes all rows.                      | Widen the time range or create a non-time-based index pattern.                                                |
| Full-text query behaves like exact match | Column has no Pinot text index.                             | Add `indexTypes: ["TEXT"]` for the target column and reload segments if needed.                               |
| Nested aggregation fails                 | Only one bucket level is supported.                         | Flatten the visualization or query Pinot SQL directly.                                                        |