Advanced Topics

Full-Text Search

Master full-text search with Cassandra using SASI indexes, Elasticsearch integration, and search best practices!

🔍 What is Full-Text Search?

The E-Commerce Search Problem 🛒

Imagine you're building an e-commerce site with 1 million products in Cassandra:

🔍 Customer searches: "wireless bluetooth headphones"
📱 Should find products with ANY of those words
⭐ Ranked by relevance (not just alphabetical)
🎯 Support fuzzy matching ("bluetooh" → "bluetooth")
⚡ Return results in < 100ms

Problem: Cassandra is designed for partition key lookups, NOT text searches!

Traditional CQL: ❌ Can't search within text
Solution: ✅ Full-text search using SASI, Elasticsearch, or Solr!

Full-Text Search Explained

Full-Text Search (FTS) = Searching for words/phrases WITHIN text fields, with features like:

Key Features:

🔍 Substring Matching: Find "blue" in "bluetooth"
📊 Relevance Ranking: Most relevant results first
🎯 Fuzzy Matching: Handle typos ("latop" → "laptop")
💬 Multi-word Queries: "wireless headphones"
⚡ Fast: Sub-100ms response times
📈 Faceted Search: Filter by category, price, brand

❓ Why Cassandra Struggles with Search

Cassandra's Search Limitations

Cassandra is optimized for partition key lookups, not text searches:

What Works in Cassandra

-- ✅ Exact partition key lookup (FAST!)
SELECT * FROM products 
WHERE product_id = '12345';  ← O(1) lookup, ~1ms

-- ✅ Clustering key range (FAST!)
SELECT * FROM orders 
WHERE user_id = 'alice' 
AND order_date > '2024-01-01';  ← Efficient range scan

What DOESN'T Work in Cassandra

-- ❌ CONTAINS queries (NOT SUPPORTED!)
SELECT * FROM products 
WHERE name CONTAINS 'bluetooth';  ← ERROR!

-- ❌ LIKE queries (NOT SUPPORTED!)
SELECT * FROM products 
WHERE name LIKE '%headphone%';  ← ERROR!

-- ❌ Full-text search (NOT SUPPORTED!)
SELECT * FROM products 
WHERE description SEARCH 'wireless bluetooth';  ← ERROR!

Why These Don't Work

🔑 Partition Key Required: WHERE clause MUST include partition key
📊 No Secondary Indexes on Text: Can't index text efficiently
⚡ Performance: Scanning all partitions = full table scan (SLOW!)
🎯 Architecture: Cassandra optimized for writes, not complex queries

🎯 3 Approaches to Full-Text Search

📊

1. SASI Indexes

Built into Cassandra

✅ Native Cassandra feature
✅ No external dependencies
✅ Basic text search (LIKE)
⚠️ Limited features
⚠️ No ranking/relevance
⚠️ Performance overhead

Use for: Simple text matching

🔎

2. Elasticsearch

Industry Standard

✅ Advanced full-text search
✅ Relevance ranking
✅ Fuzzy matching, facets
✅ Analytics capabilities
⚠️ Requires separate cluster
⚠️ Sync complexity (CDC)

Use for: Production search (most common)

☀️

3. Apache Solr

DataStax Integration

✅ Deep Cassandra integration
✅ Full-text search + analytics
✅ No CDC needed (built-in)
⚠️ DataStax Enterprise only
⚠️ Commercial license
⚠️ Higher complexity

Use for: DataStax Enterprise customers

📊 SASI Indexes (SSTable Attached Secondary Index)

What is SASI?

SASI is Cassandra's native indexing that allows LIKE queries on text columns. It's built directly into Cassandra (no external tools needed).

Creating SASI Index

-- Create table
CREATE TABLE products (
    product_id uuid PRIMARY KEY,
    name text,
    description text,
    category text,
    price decimal
);

-- Create SASI index for text search
CREATE CUSTOM INDEX product_name_idx
ON products (name)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
    'mode': 'CONTAINS',        -- CONTAINS, PREFIX, SPARSE
    'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
    'tokenization_enable_stemming': 'true',
    'tokenization_locale': 'en',
    'tokenization_skip_stop_words': 'true'
};

-- Index on category for exact match
CREATE CUSTOM INDEX product_category_idx
ON products (category)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
    'mode': 'PREFIX'
};

SASI Index Modes

CONTAINS Mode

Allows substring matching anywhere

-- Finds "blue" in "bluetooth"
WHERE name LIKE '%blue%'

Use for: Full-text search fields

PREFIX Mode

Matches from start of string

-- Finds "electronics"
WHERE category LIKE 'electr%'

Use for: Categories, autocomplete

SPARSE Mode

Optimized for low-cardinality

-- Exact match on status
WHERE status = 'active'

Use for: Status, boolean fields

Querying with SASI

-- Substring search
SELECT * FROM products 
WHERE name LIKE '%bluetooth%';

-- Prefix search
SELECT * FROM products 
WHERE category LIKE 'electr%';

-- Multi-field search (AND/OR)
SELECT * FROM products 
WHERE name LIKE '%wireless%' 
AND category LIKE 'electr%';

-- Range + text search
SELECT * FROM products 
WHERE name LIKE '%headphone%' 
AND price >= 50 
AND price <= 200;

SASI Limitations

⚠️ Performance: Slower than Elasticsearch (no relevance ranking)
⚠️ Disk Usage: SASI indexes can be large (50-100% of data size)
⚠️ Memory: Index data loaded into memory (high RAM usage)
⚠️ No Fuzzy: No built-in typo tolerance
⚠️ No Ranking: Results in arbitrary order
⚠️ Rebuild: Index rebuild can be slow on large tables

When to Use SASI

✅ Small to medium datasets (< 100GB)
✅ Simple text matching (no ranking needed)
✅ Internal tools (not customer-facing)
✅ No budget for Elasticsearch
✅ Quick prototypes

🔎 Elasticsearch Integration (Recommended)

Why Elasticsearch?

Elasticsearch (ES) is the industry standard for full-text search. It's what powers search for Netflix, GitHub, Uber, Airbnb, and thousands of companies.

Architecture: Cassandra + Elasticsearch

┌─────────────────┐
│   Application   │
└────┬───────┬────┘
     │       │
     │       ├─────────────→ 🔍 Read (Search queries)
     │       │               ┌──────────────────┐
     │       └──────────────→│  Elasticsearch   │
     │                       │   (Search index)  │
     ↓                       └──────────────────┘
     ✍️ Write                        ↑
┌──────────────┐             │ Sync via CDC
│  Cassandra   │─────────────┘
│ (Source of   │
│   Truth)     │
└──────────────┘

Data Flow:
1. Application writes to Cassandra (source of truth)
2. CDC captures changes in real-time
3. CDC consumer syncs to Elasticsearch
4. Application reads from ES for search
5. Application reads from Cassandra for detail pages

Setup: Cassandra → ES Sync

# Step 1: Enable CDC on Cassandra table
ALTER TABLE products WITH cdc = true;

# Step 2: Install Kafka + Debezium CDC connector
# (See Change Data Capture tutorial)

# Step 3: Create Elasticsearch index mapping
PUT /products
{
  "mappings": {
    "properties": {
      "product_id": { "type": "keyword" },
      "name": { 
        "type": "text",
        "analyzer": "english"
      },
      "description": { 
        "type": "text",
        "analyzer": "english"
      },
      "category": { "type": "keyword" },
      "price": { "type": "float" },
      "created_at": { "type": "date" }
    }
  }
}

# Step 4: Kafka consumer syncs CDC → ES
# (Automatic with Kafka Connect ES sink)

Searching with Elasticsearch

# Python Example with elasticsearch-py
from elasticsearch import Elasticsearch

es = Elasticsearch(['localhost:9200'])

# Simple full-text search
result = es.search(
    index="products",
    body={
        "query": {
            "multi_match": {
                "query": "wireless bluetooth headphones",
                "fields": ["name^2", "description"],  # name 2x weight
                "fuzziness": "AUTO"  # Typo tolerance
            }
        }
    }
)

# Advanced: Filtered + Faceted search
result = es.search(
    index="products",
    body={
        "query": {
            "bool": {
                "must": [
                    { "match": { "name": "headphones" } }
                ],
                "filter": [
                    { "term": { "category": "electronics" } },
                    { "range": { "price": { "gte": 50, "lte": 200 } } }
                ]
            }
        },
        "aggs": {
            "brands": { "terms": { "field": "brand" } },
            "price_ranges": {
                "range": {
                    "field": "price",
                    "ranges": [
                        { "to": 50 },
                        { "from": 50, "to": 100 },
                        { "from": 100 }
                    ]
                }
            }
        }
    }
)

Elasticsearch Features

🎯 Relevance Ranking

BM25 scoring algorithm
Field boosting (name^2)
Custom scoring functions
Best matches first

🔤 Fuzzy Matching

Typo tolerance (Levenshtein)
"latop" → "laptop"
Configurable fuzziness
Phonetic matching

📊 Faceted Search

Aggregations by category
Price ranges
Brand filters
Dynamic facets

🌍 Multi-Language

Language analyzers (40+)
Stemming & lemmatization
Stop words
Character filters

Elasticsearch Advantages

✅ Industry Standard: Battle-tested, widely used
✅ Fast: Sub-100ms search on billions of documents
✅ Feature-Rich: Fuzzy, facets, suggestions, highlighting
✅ Scalable: Horizontal scaling, sharding
✅ Analytics: Kibana for visualization
✅ Community: Huge ecosystem, plugins

☀️ Apache Solr with DataStax Enterprise

DataStax Enterprise Search

DataStax Enterprise (DSE) includes Apache Solr deeply integrated with Cassandra - no CDC needed!

How DSE Search Works

-- Enable search on table (DSE only)
CREATE SEARCH INDEX ON products;

-- Solr index automatically created
-- Data synced automatically (no CDC required!)

-- Search using CQL!
SELECT * FROM products 
WHERE solr_query = 'name:bluetooth AND price:[50 TO 200]';

-- Fuzzy search
SELECT * FROM products 
WHERE solr_query = 'name:bluetooh~2';  ← 2 char typo tolerance

✅ DSE Search Pros

✅ No CDC needed (built-in sync)
✅ Query with CQL (familiar)
✅ Managed by DataStax
✅ Integrated monitoring
✅ Solr features (facets, etc)

❌ DSE Search Cons

❌ Commercial license ($$$$)
❌ Vendor lock-in (DataStax)
❌ No open-source version
❌ Less community than ES
❌ Higher complexity

⚖️ SASI vs Elasticsearch vs Solr Comparison

Feature	SASI	Elasticsearch	DSE Solr
Cost	✅ Free	⚠️ ES cluster	❌ DSE license
Setup Complexity	✅ Easy	⚠️ Medium	⚠️ Medium
Relevance Ranking	❌ No	✅ Yes (BM25)	✅ Yes
Fuzzy Matching	❌ No	✅ Yes	✅ Yes
Performance	⚠️ Slow	✅ Fast	✅ Fast
Data Sync	✅ Automatic	⚠️ CDC needed	✅ Built-in
Scalability	❌ Limited	✅ Excellent	✅ Excellent
Best For	Prototypes	Production	DSE customers

✅ Full-Text Search Best Practices

✅ DO These

Use Elasticsearch for production
Keep Cassandra as source of truth
Sync via CDC (real-time)
Monitor ES cluster health
Use proper ES analyzers
Implement fallback to Cassandra
Cache popular searches
Test search relevance

❌ DON'T Do These

Use SASI for production search
Make ES the source of truth
Ignore sync lag monitoring
Skip ES cluster redundancy
Use default analyzers blindly
Assume ES is always available
Index everything (be selective)
Ignore relevance tuning

🎯 Full-Text Search Summary

You now understand full-text search with Cassandra!

📚 Key Takeaways:

🔍 Cassandra alone can't do text search (partition key required)
📊 SASI = Simple LIKE queries (prototypes only)
🔎 Elasticsearch = Industry standard (recommended for production)
☀️ DSE Solr = DataStax-only (commercial)
🔄 Use CDC to sync Cassandra → Elasticsearch
⚡ ES provides: ranking, fuzzy, facets, sub-100ms search
✅ Cassandra = source of truth, ES = search index

For production search: Cassandra + Elasticsearch is the winning combo! 🔍🚀

Responsive Ad