Use SAI text analyzers with CQL
You can use Cassandra Query Language (CQL) to create analyzed indexes on text columns, and then use the analyzer operator (:) to run term matching queries against the analyzed text columns.
Term matching finds rows where the search term appears anywhere within the analyzed text field.
To enable the analyzer operator, create a Storage Attached Indexing (SAI) index with a non-tokenizing filter or index_analyzer mapping in the WITH OPTIONS clause.
Index analyzer configuration options
SAI uses the Apache Lucene™ Java Analyzer API to transform text columns into tokens for indexing and querying which can use built-in or custom analyzers.
The analyzer determines how a column’s values will be analyzed before indexing occurs, and then the analyzed index stores values derived from the raw column values. The stored values are dependent on the analyzer configuration options, which include tokenization, filtering, and charFiltering. When the index is used in a query, the analyzer is applied to the query term.
To configure an analyzed index, you must specify the index_analyzer option in the WITH OPTIONS clause.
The value of index_analyzer is either:
-
A single string that selects a built-in analyzer, which is a preconfigured set of tokenizers and filters. For example:
{ "index_analyzer": "standard" } -
A JSON object that defines a custom analyzer with a
tokenizer, optionalfiltersandcharFilters, and any other fields required to configure the analyzer. For example:{ "index_analyzer": { "tokenizer": { "name": "ngram", "args": { "minGramSize": "2", "maxGramSize": "3" } }, "filters": [ { "name": "lowercase" } ] } }
There are many analyzers, tokenizers, and filters from the Apache Lucene™ project (version 9.8.0) that you can use in your index_analyzer configuration.
| Type | Usage | Values |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
For more information and examples, see Create SAI index and SAI examples.
Non-tokenizing filter options
The non-tokenizing filters are case_sensitive, normalize, and ascii.
These options cannot be combined with index_analyzer in the same index.
However, you can configure multiple non-tokenizing filters on the same index for a chained pipeline of non-tokenizing filters.
For example, the following index applies case sensitivity first, then normalization, then Ascii.
CREATE CUSTOM INDEX commenter_case_sensitive_idx ON cycling.comments_vs (commenter)
USING 'StorageAttachedIndex'
WITH OPTIONS = { 'case_sensitive': true, 'normalize': true, 'ascii': true};Non-tokenizing filters also use the analyzer operator when queried.
For explanations of the filters, see SAI options.
Analyzer operator
After creating an analyzed SAI index, use the analyzer operator (:) in your CQL queries, similar to other operators:
SELECT * FROM animals.sharks
WHERE analyzed_column : 'search_term';The analyzer operator has the following restrictions:
-
Only SAI indexes support the
:operator. -
The
:operator requires anindex_analyzerconfigured on the SAI index. -
The analyzed column cannot be part of the Primary Key, including the partition key and clustering columns.
-
The
:operator can be used with onlySELECTstatements. -
The
:operator cannot be used with light-weight transactions, such as a condition for anIFclause.
SAI queries recognize both : and = operators, but = is deprecated for SAI indexes because it can be confused with the equality operator (also represented by =) for non-analyzed columns.
By design, queries on analyzed indexes cannot be as deterministic as true equality matches on non-analyzed columns because the analyzed index processes column values according to the analyzer configuration.
Example: Analyzer term matching
-
Create a table:
CREATE TABLE default_keyspace.products ( id text PRIMARY KEY, val text ); -
Create an SAI index with the
index_analyzeroption and stemming enabled:CREATE CUSTOM INDEX default_keyspace_products_val_idx ON default_keyspace.products(val) USING 'org.apache.cassandra.index.sai.StorageAttachedIndex' WITH OPTIONS = { 'index_analyzer': '{ "tokenizer" : {"name" : "standard"}, "filters" : [{"name" : "porterstem"}] }'}; -
Insert sample rows:
INSERT INTO default_keyspace.products (id, val) VALUES ('1', 'soccer cleats'); INSERT INTO default_keyspace.products (id, val) VALUES ('2', 'running shoes'); INSERT INTO default_keyspace.products (id, val) VALUES ('3', 'hiking shoes'); -
Query to retrieve your data:
- Query on a single term
-
SELECT * FROM default_keyspace.products WHERE val : 'running'; - Query on two separate terms
-
In this case, term matching searches each condition sequentially to find rows that contain both terms.
SELECT * FROM default_keyspace.products WHERE val : 'hiking' AND val : 'shoes'; - Query on a phrase
-
In this case, term matching attempts to retrieve the complete phrase.
SELECT * FROM default_keyspace.products WHERE val : 'soccer cleats';
Example: Analyzer with vector search
You can query analyzed indexes directly, or combine them with vector searches (ANN OF) to combine term matching with semantic similarity for more focused and contextually relevant results.
Similarity search requires an indexed vector column.
For example, if you generate a vector embedding from the phrase "tell me about available shoes", you can use a vector search to get a list of rows with similar vectors. These rows will likely correlate with shoe-related strings.
SELECT * from products
ORDER BY vector ANN OF [6.0,2.0, ... 3.1,4.0]
LIMIT 10;Alternatively, you can filter these search results by a specific keyword, such as hiking:
SELECT * from products
WHERE val : 'hiking'
ORDER BY vector ANN OF [6.0,2.0, … 3.1,4.0]
LIMIT 10;