CREATE CUSTOM INDEX

Creates a Storage-Attached Indexing (SAI) index using a table column other than the partition key. SAI is a beta feature.

Note: You cannot have a Solr-based DSE Search index and an SAI index defined for the same database table. For more information, see the SAI topics.

Synopsis

CREATE CUSTOM INDEX [ IF NOT EXISTS ] [ index_name ]
  ON [keyspace_name.]table_name (column_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.

OPTIONS

Define options in JSON simple format.

During the SAI beta, the following options apply to StorageAttachedIndex:


Option	Description
case_sensitive	Ignore case in matching. Default is case-sensitive indexing, setting: `true`.
normalize	When set to `true`, perform Unicode normalization on indexed text. SAI supports Normalization Form C (NFC) Unicode. Default: `false`.

Example

This example uses the cycling.cyclist_name table. Its primary key is the id column.

Creating an SAI index on a column other than the table's partition key.

Create an SAI index on column lastname:

CREATE CUSTOM INDEX lastname_allcase ON cyclist_name (lastname) USING 'StorageAttachedIndex'
WITH OPTIONS = { 'case_sensitive': false, 'normalize': true };

The 'normalize': true option results in SAI changing, for example, the character ç to c in the index.