CREATE CUSTOM INDEX

Creates a storage-attached index.

Creates a Storage-Attached Indexing (SAI) index. You can create multiple secondary indexes on the same database table, with each SAI index based on any column in the table. Exception: there is no need to define an SAI index based on the partition key when it's comprised of only one column; in this case, SAI issues an invalid query message.

Supported databases:
  • DataStax Astra
  • DataStax Enterprise 6.8.3 and later

You can also define an SAI index using one of the columns in the table's composite partition key. A composite partition key means that the partition is based on multiple columns. In this case with an SAI index, you would specify just one of the columns that comprises the composite partition key. You could define a separate SAI index that specifies another single column from the composite partition key.

Defining one or more SAI indexes based on any column in a database table (with the rules noted above) subsequently gives you the ability to run performant queries that specify the indexed column. See the SAI guide.

Synopsis

CREATE CUSTOM INDEX [ IF NOT EXISTS ] [ index_name ]
  ON [ keyspace_name.]table_name (column_name)
     [ (keys(map_name)) ] 
     [ (values(map_name)) ] 
     [ (entries(map_name)) ]
  USING 'StorageAttachedIndex' 
  [ WITH OPTIONS = { option_map } ] ;
Table 1. Legend
Syntax conventions Description
UPPERCASE Literal keyword.
Lowercase Not literal.
Italics Variable value. Replace with a user-defined value.
[] Optional. Square brackets ( [] ) surround optional command arguments. Do not type the square brackets.
( ) Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.
| Or. A vertical bar ( | ) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
... Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.
'Literal string' Single quotation ( ' ) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
{ key : value } Map collection. Braces ( { } ) enclose map collections or key value pairs. A colon separates the key and the value.
<datatype1,datatype2> Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
cql_statement; End CQL statement. A semicolon ( ; ) terminates all CQL statements.
[--] Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.
' <schema> ... </schema> ' Search CQL only: Single quotation marks ( ' ) surround an entire XML schema declaration.
@xml_entity='xml_entity_type' Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.
index_name
Optional identifier for index. If no name is specified, the default is used, table_name_column_name_idx. Enclose in quotes to use special characters or to preserve capitalization.
column_name

The name of the table column on which the SAI index is being defined. SAI allows alphanumeric characters and underscores only. SAI returns InvalidRequestException if you try to define an index on a column name that contains other characters, and does not create the index.

map_name
Used with collections, identifier of the map_name specified in CREATE TABLE ... map(map_name). See SAI collection map examples with keys, values, and entries.
option_map

Define options in JSON simple format.

The following WITH OPTIONS option_map choices apply only to SAI indexes based on string columns. Not applicable with numeric column SAI indexes. See examples below.
Option Description
case_sensitive Ignore case in matching string values.

Default: true.

normalize When set to true, perform Unicode normalization on indexed strings.

SAI supports Normalization Form C (NFC) Unicode. When set to true, SAI normalizes the different versions of a given Unicode character to a single version, retaining all the marks and symbols in the index. For example, SAI would change the character Å (U+212B) to Å (U+00C5).

When implementations keep strings in a normalized form, equivalent strings have a unique binary representation. See Unicode Standard Annex #15, Unicode Normalization Forms.

Default: false.

About query operators

Tip: The supported query operators for tables with SAI indexes:
  • Numerics: =, <, >, <=, >=, AND
  • Strings: =, CONTAINS, CONTAINS key, CONTAINS VALUES, AND

Not supported: LIKE, IN, OR

Examples

These examples define SAI indices for the cycling.cyclist_semi_pro table, which is demonstrated in the SAI quick start.
CREATE CUSTOM INDEX lastname_sai_idx ON cycling.cyclist_semi_pro (lastname) 
USING 'StorageAttachedIndex' 
WITH OPTIONS = {'case_sensitive': false, 'normalize': true }; 

CREATE CUSTOM INDEX age_sai_idx ON cycling.cyclist_semi_pro (age) 
USING 'StorageAttachedIndex';

CREATE CUSTOM INDEX country_sai_idx ON cycling.cyclist_semi_pro (country) 
USING 'StorageAttachedIndex'
WITH OPTIONS = {'case_sensitive': false, 'normalize': true }; 

CREATE CUSTOM INDEX registration_sai_idx ON cycling.cyclist_semi_pro (registration) 
USING 'StorageAttachedIndex'; 

For sample queries that find data in cycling.cyclist_semi_pro via these sample SAI indices, see Submit CQL queries. Also refer to Examine SAI column index and query rules.

SAI collection map examples with keys, values, and entries

These examples demonstrate using collection maps with multiple types in SAI indexes. For related information, see Creating collections and Using map type.

First, create the keyspace:
CREATE KEYSPACE demo3 WITH replication = 
       {'class': 'SimpleStrategy', 'replication_factor': '1'};
USE demo3;
Create an audit table, with a collection map named text_map.
CREATE TABLE demo3.audit ( id int PRIMARY KEY , text_map map<text, text>);
Create multiple SAI collection maps on the same column, each using keys, values, and entries.
CREATE CUSTOM INDEX ON demo3.audit (keys(text_map)) USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX ON demo3.audit (values(text_map)) USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX ON demo3.audit (entries(text_map)) USING 'StorageAttachedIndex';
Insert some data:
INSERT INTO demo3.audit (id, text_map) values (1, {'Carlos':'Perotti', 'Marcel':'Silva'});
INSERT INTO demo3.audit (id, text_map) values (2, {'Giovani':'Pasi', 'Frances':'Giardello'});
INSERT INTO demo3.audit (id, text_map) values (3, {'Mark':'Pastore', 'Irene':'Cantona'});
Query all example:
SELECT * FROM demo3.audit;
 id | text_map
----+---------------------------------------------
  1 | {'Carlos': 'Perotti', 'Marcel': 'Silva'}
  2 | {'Frances': 'Giardello', 'Giovani': 'Pasi'}
  3 | {'Irene': 'Cantona', 'Mark': 'Pastore'}

(3 rows)
Query example that uses the SAI index with collection map type entries:
SELECT * FROM demo3.audit WHERE text_map['Irene'] = 'Cantona' AND text_map['Mark'] = 'Pastore';
 id | text_map
----+-----------------------------------------
  3 | {'Irene': 'Cantona', 'Mark': 'Pastore'}

(1 rows)
Query example with CONTAINS KEY that uses the SAI with collection map type keys:
SELECT * FROM demo3.audit WHERE text_map CONTAINS KEY 'Giovani';
 id | text_map
----+---------------------------------------------
  2 | {'Frances': 'Giardello', 'Giovani': 'Pasi'}

(1 rows)
Query example with CONTAINS that uses the SAI index with map type values:
SELECT * FROM demo3.audit WHERE text_map CONTAINS 'Silva';
 id | text_map
----+------------------------------------------
  1 | {'Carlos': 'Perotti', 'Marcel': 'Silva'}

(1 rows)