Semantic Search - StarTree Docs

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.

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.

How It Works

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.

{
  "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.

{
  "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:

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.

credentialRef=env:OPENAI_API_KEY
credentialRef=config:my-api-key-value
credentialRef=secret:openai/api-key

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

Create a Projection Table

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

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:

{
  "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

SELECT doc_id, title
FROM docs
WHERE SEMANTIC_MATCH(body, 'usb-c dock for macbook pro', 20)
LIMIT 20

With Metadata Filters

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:

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:

Raw rows ingest immediately into the source table.
The embedding scheduler queues work for semantic columns.
Embedding workers batch calls to the configured provider.
Embeddings are published to the projection table.
Retryable failures use backoff.
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:

{
  "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.

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.

​How It Works

​When to Use

​Components

​Configure Profiles

​Configure Bindings

​Enable the Query Rewriter

​Credential References

​Create a Projection Table

​Query Syntax

​Basic Semantic Search

​With Metadata Filters

​Rewritten Query Shape

​Realtime Embedding Pipeline

​Backfill and Re-Embedding

​Local Model Settings

​Failure Modes

​Observability

​Security Notes

​Troubleshooting

How It Works

When to Use

Components

Configure Profiles

Configure Bindings

Enable the Query Rewriter

Credential References

Create a Projection Table

Query Syntax

Basic Semantic Search

With Metadata Filters

Rewritten Query Shape

Realtime Embedding Pipeline

Backfill and Re-Embedding

Local Model Settings

Failure Modes

Observability

Security Notes

Troubleshooting