Full-Text Search with Spice
Works with v2.0+
Full-text search uses BM25 scoring to retrieve records matching keywords in indexed columns. This cookbook demonstrates how to configure and query full-text search indexes on markdown files from the Spice cookbook repository.
Prerequisites
echo "GITHUB_TOKEN=your_token_here" > .env
echo "GITHUB_TOKEN=your_token_here" > .env
Configuration
The spicepod.yaml in this directory configures a dataset of markdown files from the Spice cookbook with full-text search enabled on the content column:
datasets:
- from: github:github.com/spiceai/cookbook/files/trunk
name: cookbook_files
params:
github_token: ${secrets:GITHUB_TOKEN}
include: "**/*.md"
acceleration:
enabled: true
columns:
- name: content
full_text_search:
enabled: true
row_id:
- path
datasets:
- from: github:github.com/spiceai/cookbook/files/trunk
name: cookbook_files
params:
github_token: ${secrets:GITHUB_TOKEN}
include: "**/*.md"
acceleration:
enabled: true
columns:
- name: content
full_text_search:
enabled: true
row_id:
- path
Key configuration options:
acceleration: enabled: true: Required for full-text search. The index is built on the accelerated data.full_text_search.enabled: Enables BM25 indexing on the column.full_text_search.row_id: Specifies the unique identifier column(s) for referencing results. Only needed on one column per dataset.
Run Spice
Start the Spice runtime:
Wait for the dataset to load and index:
2026-01-21T01:00:00.000000Z INFO runtime::init::dataset: Dataset cookbook_files registered (github:github.com/spiceai/cookbook/files/trunk), acceleration (arrow), results cache enabled.
2026-01-21T01:00:05.000000Z INFO runtime_table::accelerated::refresh_task: Loaded 104 rows (1.13 MiB) for dataset cookbook_files in 4s.
2026-01-21T01:00:05.100000Z INFO runtime: All components are loaded. Spice runtime is ready!
2026-01-21T01:00:00.000000Z INFO runtime::init::dataset: Dataset cookbook_files registered (github:github.com/spiceai/cookbook/files/trunk), acceleration (arrow), results cache enabled.
2026-01-21T01:00:05.000000Z INFO runtime_table::accelerated::refresh_task: Loaded 104 rows (1.13 MiB) for dataset cookbook_files in 4s.
2026-01-21T01:00:05.100000Z INFO runtime: All components are loaded. Spice runtime is ready!
Search with SQL
Start the Spice SQL REPL:
Verify the dataset is loaded:
+---------------+--------------+----------------+------------+
| table_catalog | table_schema | table_name | table_type |
| varchar | varchar | varchar | varchar |
+---------------+--------------+----------------+------------+
| spice | runtime | task_history | BASE TABLE |
| spice | public | cookbook_files | BASE TABLE |
+---------------+--------------+----------------+------------+
Basic Full-Text Search
Search for files containing specific keywords:
SELECT path, _score
FROM text_search(cookbook_files, 'vector search', content)
ORDER BY _score DESC
LIMIT 5;
SELECT path, _score
FROM text_search(cookbook_files, 'vector search', content)
ORDER BY _score DESC
LIMIT 5;
Results (paths and scores vary as the cookbook changes):
+--------------------------------+-------------------+
| path | _score |
| varchar | float64 |
+--------------------------------+-------------------+
| search/elasticsearch/README.md | 7.372053146362305 |
| vectors/s3/README.md | 7.359576225280762 |
| search/README.md | 7.14163875579834 |
| ai/WHEN_TO_USE.md | 7.056568145751953 |
| full-text-search/README.md | 6.641867637634277 |
+--------------------------------+-------------------+
Search for Specific Topics
Find all cookbooks mentioning a particular technology:
SELECT path, _score
FROM text_search(cookbook_files, 'DuckDB acceleration', content)
ORDER BY _score DESC
LIMIT 5;
SELECT path, _score
FROM text_search(cookbook_files, 'DuckDB acceleration', content)
ORDER BY _score DESC
LIMIT 5;
Combine with SQL Filters
Full-text search results can be filtered using standard SQL:
SELECT path, _score
FROM text_search(cookbook_files, 'kubernetes', content)
WHERE path LIKE 'kubernetes/%'
ORDER BY _score DESC
LIMIT 10;
SELECT path, _score
FROM text_search(cookbook_files, 'kubernetes', content)
WHERE path LIKE 'kubernetes/%'
ORDER BY _score DESC
LIMIT 10;
Function Signature
The text_search() function:
text_search(
table IDENTIFIER, -- Dataset name (required, unquoted)
query STRING, -- Keyword or phrase to search (required)
col IDENTIFIER, -- Column name to search (required if multiple indexed columns, unquoted)
limit INTEGER, -- Maximum results returned (optional, defaults to 1000)
include_score BOOLEAN -- Include relevance scores in results (optional, defaults to TRUE)
)
RETURNS TABLE -- Original table columns plus a FLOAT column `_score`
text_search(
table IDENTIFIER, -- Dataset name (required, unquoted)
query STRING, -- Keyword or phrase to search (required)
col IDENTIFIER, -- Column name to search (required if multiple indexed columns, unquoted)
limit INTEGER, -- Maximum results returned (optional, defaults to 1000)
include_score BOOLEAN -- Include relevance scores in results (optional, defaults to TRUE)
)
RETURNS TABLE -- Original table columns plus a FLOAT column `_score`
Search with HTTP API
Query the /v1/search endpoint:
curl -X POST http://localhost:8090/v1/search \
-H 'Content-Type: application/json' \
-d '{
"datasets": ["cookbook_files"],
"text": "getting started",
"additional_columns": ["path"],
"limit": 5
}'
curl -X POST http://localhost:8090/v1/search \
-H 'Content-Type: application/json' \
-d '{
"datasets": ["cookbook_files"],
"text": "getting started",
"additional_columns": ["path"],
"limit": 5
}'
Response (truncated):
{
"results": [
{
"matches": {
"content": ["# AWS RDS for PostgreSQL\n\nWorks with `v1.0+`\n\nFollow these steps to ge..."]
},
"primary_key": {
"path": "postgres/rds/README.md"
},
"_score": 1.12,
"dataset": "cookbook_files"
}
],
"duration_ms": 4
}
{
"results": [
{
"matches": {
"content": ["# AWS RDS for PostgreSQL\n\nWorks with `v1.0+`\n\nFollow these steps to ge..."]
},
"primary_key": {
"path": "postgres/rds/README.md"
},
"_score": 1.12,
"dataset": "cookbook_files"
}
],
"duration_ms": 4
}
Each result contains:
-
matches: the indexed column(s) that matched, mapping column name to an array of matching text.
-
primary_key: the full_text_search.row_id column(s) — path for this dataset.
-
_score: the BM25 relevance score.
-
data: any additional_columns that are not part of the primary key. Requesting only
path (the row_id) returns no data object, because that value is already in primary_key.
Requesting ["name", "size"] instead adds:
"data": { "name": "README.md", "size": 2437 }
"data": { "name": "README.md", "size": 2437 }
When to Use Full-Text Search
Full-text search is optimal for:
- Keyword-based queries: Users searching for specific terms or phrases
- Exact matching: When precise keyword matching matters more than semantic similarity
- Structured text fields: Searching titles, tags, names, or other well-defined text
For semantic similarity (finding related content even with different wording), use vector search (opens in a new tab). For best results, combine both using hybrid search with RRF (opens in a new tab).
References
![dependabot[bot]](https://avatars.githubusercontent.com/in/29110?v=4)
