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

# Semantic Search

> Configure text-based semantic search on Pinot tables using hidden embedding projections.

StarTree Semantic Search lets users issue text-only SQL predicates such as `SEMANTIC_MATCH(body, 'usb-c dock', 20)`. StarTree resolves the text column to an embedding profile, embeds the query text, rewrites the query to Pinot vector search on a hidden embedding column, and runs the rewritten query against Pinot.

<Info>
  This page covers the operator-facing configuration path. Users who query the table only need the SQL syntax and the columns that are enabled for semantic search.
</Info>

## How It Works

```text theme={null}
User SQL
  -> SEMANTIC_MATCH(body, 'usb-c dock', 20)
  -> broker resolves semantic profile for docs.body
  -> broker embeds the query text
  -> query rewrites to VECTOR_SIMILARITY on docs__semantic.body_embedding
  -> Pinot servers execute vector search
```

Users do not reference embedding columns, model IDs, providers, or credentials in SQL. Those details are configured through semantic profiles and bindings.

## When to Use

Use Semantic Search when:

* Users search by natural language rather than exact keywords.
* You want embeddings managed behind a familiar SQL predicate.
* You need metadata filters, such as tenant, category, or region, to combine with semantic similarity.
* You can maintain a projection table that stores embeddings and mirrored filter columns.

Use direct vector search when users already provide embedding vectors or when you need full control over the vector-search SQL shape.

## Components

| Component               | Role                                                                                   |
| ----------------------- | -------------------------------------------------------------------------------------- |
| Semantic profile        | Defines provider, model, dimensions, distance function, and credential reference.      |
| Semantic binding        | Maps one user-visible `(table, text column)` pair to a profile and projection table.   |
| Query rewriter          | Rewrites `SEMANTIC_MATCH` into vector similarity search.                               |
| Embedding service       | Calls embedding providers with caching, batching, retry, and circuit-breaker behavior. |
| Projection table        | Stores hidden embedding vectors plus join/filter columns.                              |
| Embedding pipeline      | Generates embeddings asynchronously for realtime rows.                                 |
| `EmbeddingBackfillTask` | Backfills missing/stale embeddings and handles profile changes.                        |

## Configure Profiles

A profile hides provider, model, dimensions, distance function, and credentials.

```json theme={null}
{
  "name": "managed-default",
  "providerType": "openai",
  "modelId": "text-embedding-3-small",
  "dimensions": 1536,
  "distanceFunction": "COSINE",
  "credentialRef": "env:OPENAI_API_KEY"
}
```

Supported provider types include:

| Provider Type       | Description                              |
| ------------------- | ---------------------------------------- |
| `openai`            | OpenAI Embeddings API.                   |
| `azure_openai`      | Azure OpenAI Service.                    |
| `cohere`            | Cohere Embed API.                        |
| `voyage`            | Voyage AI embeddings.                    |
| `vertex_ai`         | Google Vertex AI embeddings.             |
| `bedrock`           | AWS Bedrock embeddings.                  |
| `jina`              | Jina AI embeddings.                      |
| `mistral`           | Mistral embeddings.                      |
| `openai_compatible` | Any OpenAI-compatible endpoint.          |
| `tei`               | Text Embeddings Inference local runtime. |
| `vllm`              | vLLM local runtime.                      |
| `ollama`            | Ollama local runtime.                    |
| `onnx`              | In-process ONNX Runtime, when available. |

## Configure Bindings

A binding maps a source table and text column to the projection table and profile.

```json theme={null}
{
  "sourceTable": "docs",
  "textColumn": "body",
  "projectionTable": "docs__semantic",
  "embeddingColumn": "body_embedding",
  "profileName": "managed-default",
  "topKOversampleFactor": 3.0,
  "filterColumnMirrors": ["tenant_id", "category"]
}
```

The mirrored filter columns let Pinot apply structured predicates before or during vector search. Mirror columns that are common in `WHERE` clauses, such as tenant, workspace, category, language, or region.

## Enable the Query Rewriter

Add the semantic query rewriter to the broker query rewriter chain:

```properties theme={null}
pinot.broker.query.rewriter.class.names=\
  org.apache.pinot.sql.parsers.rewriter.SomeExistingRewriter,\
  ai.startree.pinot.semantic.rewriter.SemanticSearchQueryRewriter
```

Keep existing rewriters in the chain. Append the semantic rewriter in the same broker configuration used by the tenant that serves semantic queries.

## Credential References

Credential references can point to environment variables, direct config, or a secret manager integration.

```properties theme={null}
credentialRef=env:OPENAI_API_KEY
credentialRef=config:my-api-key-value
credentialRef=secret:openai/api-key
```

<Warning>
  Do not store provider API keys directly in table configs or query text. Prefer environment variables, mounted secrets, or StarTree-supported secret manager references.
</Warning>

## Create a Projection Table

The projection table stores embeddings and mirrored filter columns. It is separate from the user-facing source table.

Example schema:

```sql theme={null}
CREATE TABLE docs__semantic (
  doc_id         STRING,
  body_embedding FLOAT ARRAY,
  tenant_id      STRING,
  category       STRING,
  text_hash      STRING,
  source_version LONG,
  embed_profile  STRING,
  embed_status   STRING
)
```

Add a vector index to the embedding column in the projection table config:

```json theme={null}
{
  "fieldConfigList": [
    {
      "name": "body_embedding",
      "encodingType": "RAW",
      "indexes": {
        "vector": {
          "vectorIndexType": "HNSW",
          "distanceFunction": "COSINE",
          "dimensions": 1536
        }
      }
    }
  ]
}
```

Match the projection table's vector dimensions and distance function to the profile.

## Query Syntax

### Basic Semantic Search

```sql theme={null}
SELECT doc_id, title
FROM docs
WHERE SEMANTIC_MATCH(body, 'usb-c dock for macbook pro', 20)
LIMIT 20
```

### With Metadata Filters

```sql theme={null}
SELECT doc_id, title, category
FROM docs
WHERE tenant_id = 'acme'
  AND category = 'accessories'
  AND SEMANTIC_MATCH(body, 'usb-c dock for macbook pro', 20)
LIMIT 20
```

### Rewritten Query Shape

The broker rewrites the semantic predicate to a vector predicate against the projection table:

```sql theme={null}
SELECT doc_id, title, category
FROM docs__semantic
WHERE tenant_id = 'acme'
  AND category = 'accessories'
  AND VECTOR_SIMILARITY(body_embedding, ARRAY[0.012, -0.034, ...], 60)
LIMIT 20
```

In this example, `60` comes from `topK=20` multiplied by `topKOversampleFactor=3.0`.

## Realtime Embedding Pipeline

Realtime ingestion does not block on embedding. The expected flow is:

1. Raw rows ingest immediately into the source table.
2. The embedding scheduler queues work for semantic columns.
3. Embedding workers batch calls to the configured provider.
4. Embeddings are published to the projection table.
5. Retryable failures use backoff.
6. Permanent failures move to a dead-letter or failed state for inspection.

Embedding jobs use status values such as:

| Status             | Meaning                                        |
| ------------------ | ---------------------------------------------- |
| `PENDING`          | Queued and not processed yet.                  |
| `IN_PROGRESS`      | Currently being embedded.                      |
| `READY`            | Embedding was written to the projection table. |
| `FAILED_RETRYABLE` | Retry is scheduled.                            |
| `FAILED_PERMANENT` | Retries exhausted or error is not retryable.   |

To avoid stale writes, embedding results include the source text hash and source version. If the source row changed after the embedding job started, the stale result is discarded.

## Backfill and Re-Embedding

Use `EmbeddingBackfillTask` for:

* Source rows that do not yet have embeddings.
* Rows whose text changed.
* Rows embedded with an older profile version.
* Re-embedding after changing providers, models, dimensions, or distance function.

Example task config:

```json theme={null}
{
  "taskType": "EmbeddingBackfillTask",
  "configs": {
    "sourceTable": "docs",
    "textColumn": "body",
    "profileName": "managed-default"
  }
}
```

Schedule backfill through the standard minion task workflow after the projection table and binding exist.

## Local Model Settings

For local providers such as TEI, vLLM, Ollama, and ONNX, configure a cache directory and model allowlist.

```properties theme={null}
pinot.semantic.search.local.model.cache.dir=/var/pinot/semantic-models
pinot.semantic.search.local.model.idle.timeout.minutes=30
pinot.semantic.search.model.allowlist=BAAI/bge-small-en-v1.5,BAAI/bge-large-en-v1.5
```

Use revision-pinned models for reproducible embeddings.

## Failure Modes

| Mode              | Behavior                                                                      |
| ----------------- | ----------------------------------------------------------------------------- |
| `FAIL_CLOSED`     | Query fails if an embedding cannot be obtained.                               |
| `FAIL_OPEN`       | Query returns an explicit error rather than silently degrading.               |
| `FALLBACK_FILTER` | Query falls back to non-semantic filtering when other predicates are present. |

Choose failure behavior based on whether semantic recall is required for correctness or is an optional ranking/filtering enhancement.

## Observability

Track the embedding service and pipeline with metrics such as:

| Metric                                 | Meaning                              |
| -------------------------------------- | ------------------------------------ |
| `semantic.query.embedding.latency_ms`  | Broker-side query embedding latency. |
| `semantic.query.cache.hit_rate`        | Query embedding cache hit rate.      |
| `semantic.provider.request.latency_ms` | Provider request latency.            |
| `semantic.provider.request.error_rate` | Provider request error rate.         |
| `semantic.pipeline.queue_lag`          | Pending embedding work.              |
| `semantic.pipeline.batch_size`         | Worker batch size.                   |
| `semantic.backfill.progress`           | Backfill progress.                   |
| `semantic.local.model.load_time_ms`    | Local model load or download time.   |

## Security Notes

* SQL queries reference only source columns and text; they do not expose provider credentials.
* Credentials should be resolved from environment variables or a secret manager.
* Keep model allowlists tight for local runtimes.
* Avoid returning raw embedding vectors to end users unless your application explicitly needs them.

## Troubleshooting

| Symptom                                   | Likely Cause                                                               | Fix                                                                             |
| ----------------------------------------- | -------------------------------------------------------------------------- | ------------------------------------------------------------------------------- |
| `SEMANTIC_MATCH` is not recognized        | Broker query rewriter is not enabled.                                      | Add `SemanticSearchQueryRewriter` to `pinot.broker.query.rewriter.class.names`. |
| Query fails with missing binding          | The `(table, textColumn)` pair has no semantic binding.                    | Add or correct the binding for the source table and column.                     |
| Query returns fewer results than expected | Projection rows are missing, stale, or failed.                             | Run `EmbeddingBackfillTask` and inspect embedding job status.                   |
| Provider calls are slow                   | Cache miss, provider latency, or small batch size.                         | Check provider latency metrics and query cache hit rate.                        |
| Vector index errors                       | Projection table dimensions or distance function do not match the profile. | Recreate/reload the projection table with matching vector index settings.       |