CREATE SEARCH INDEX 

Defines a new search index on a table.

Defines a new search index on a table.

If the CREATE SEARCH INDEX statement specifies no options:
  • Existing resources are used if they are already uploaded, and no COLUMNS, PROFILES, or CONFIG options are provided.
  • New resources are auto-generated and default values are used if the resources are not already uploaded.

Synopsis 

CREATE SEARCH INDEX [IF NOT EXISTS] 
ON [keyspace_name.]table_name
[WITH COLUMNS column_list [, column_list, ...]
    | PROFILES profile_name [, profile_name, ...]
    | CONFIG { option:value [, option:value, ...] }
    | OPTIONS { option:value [, option:value, ...] }
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.

EBNF 

EBNF syntax:
createSearchIndex ::= 'CREATE' 'SEARCH' 'INDEX' ('IF' 'NOT' 'EXISTS')?
                      'ON' tableName
                      ('WITH' indexOptions)?
tableName         ::= (keyspace '.')? table
indexOptions      ::= indexOption ('AND' indexOption)*
indexOption       ::= 'COLUMNS' columnList
                      | 'PROFILES' profileName (',' profileName)*
                      | 'CONFIG' optionMap
                      | 'OPTIONS' optionMap
columnList        ::= column (',' column)*
column            ::= (columnName | '*')('{' optionMap '}')?
optionMap         ::= '{' (optionName ':' optionValue (',' optionName ':'
                      optionValue)*)? '}'

Railroad diagram:

createSearchIndex
Image shows a railroad diagram for the CREATE SEARCH INDEX search CQL command

tableName
Image shows a railroad diagram of tableName

indexOptions
Image shows a railroad diagram of indexOptions

indexOption
Image shows a railroad diagram of indexOption

columnList
Image shows a railroad diagram of columnList

column
Image shows a railroad diagram of column

optionMap
Image shows a railroad diagram of optionMap

Index options 

Apply one of these index options to create a search index:

COLUMNS column_list
The resource column options for the table.
PROFILES profile_name [',' profile_name, ...]
One or more resource profiles to apply to the search schema.
CONFIG option_map
The resource configuration options for the search index.
OPTIONS option_map
The resource request options configure the entire request.

Shortcuts 

Common shortcuts are supported for config and schema elements.

Shortcuts for config element paths
Shortcut Verb Type Comment
autoCommitTime SET int 10000
defaultQueryField SET, DROP string Not configured when resources are auto generated.
directoryFactory SET string Specify exclusive of directoryFactoryClass.

StandardDirectoryFactory

EncryptedFSDirectoryFactory

directoryFactoryClass SET string Specify exclusive of directoryFactory.

A fully qualified class name for a directory factory.

Default is solr.StandardDirectoryFactory when resources are auto generated.
filterCacheLowWaterMark SET int Default is 128 when resources are auto generated.
filterCacheHighWaterMark SET int Default is 256 when resources are auto generated.
mergeFactor SET int Default is 10 when resources are auto generated.
mergeMaxThreadCount SET int Must configure with mergeMaxMergeCount. Not configured when resources are auto generated.
mergeMaxMergeCount SET int Must configure with mergeMaxThreadCount. Not configured when resources are auto generated.
ramBufferSize SET int Default is 512 when resources are auto generated.
realtime SET boolean Default is false when resources are auto generated.

Column options 

Specify one or more options in a column option map using this syntax:
COLUMNS column_list
These column options are supported for the core:
Option Description
docValues true | false - Create docValues for the column in the generated schema. The default is false when a table does not have an existing core.
copyField true | false - Create a copy field for the column if it is a string type. The default is false when a table does not have an existing core.
excluded true | false - Exclude the column from the search index. The default is false when a table does not have an existing core.
Options applied to the column become the default value of the option for fields that do not have an explicit value defined.

Resource generation profiles 

Resource generation profiles minimize index size. Specify one or more profiles using this syntax:
PROFILES profile_name [',' profile_name, ...]
These profiles are supported for the search index: Using spaceSavings profiles disables auto-generation of docValues.
Profile name Description
spaceSavingAll Applies all options: spaceSavingNoTextfield, spaceSavingNoJoin, and spaceSavingSlowTriePrecision.
spaceSavingNoTextfield No TextFields. Use StrField instead.
spaceSavingNoJoin Do not index a hidden primary key field. Prevents joins across cores.
spaceSavingSlowTriePrecision Sets trie fields precisionStep to '0' for greater space saving but slower querying.
No profiles are applied when resources are auto generated.

Configuration options 

Configuration options override values in the config file. The CONFIG option map can pass options with this syntax:
WITH CONFIG { option:value [, option:value, ...] }
Shortcuts are supported.

Request options 

Request options configure the entire request. The OPTIONS map can pass options with this syntax:
WITH OPTIONS { option:value [, option:value, ...] }
The request options are boolean values:
recovery
  • true - if the search core is unable to load due to corrupted index, recovers it by deleting and recreating the index. The deleteAll flag is set based on the recovery flag unless deleteAll is specifically set.
  • false - no recovery. Default.
reindex
  • true - reindexes the data. Keeps the current index (accepting reads) while the new index is building. Default.
  • false - does not reindex the data.
lenient
  • true - The SchemaGenerator silently ignores fields of type SpatialRecursivePrefixTreeFieldType (CQL PointType).
  • false - Do not ignore fields of type SpatialRecursivePrefixTreeFieldType (CQL PointType).

Examples 

The search index is created with the wiki.solr keyspace and table, and the specified options.

Create search index if it does not exist
CREATE SEARCH INDEX IF NOT EXISTS ON wiki.solr WITH COLUMNS id,val1;

Create the Wiki.Solr index and preserve the case of keyspace and table names

Use quotation marks to preserve case.
CREATE SEARCH INDEX IF NOT EXISTS ON "Wiki"."Solr" WITH COLUMNS id,val1;
Create real time (RT) search index, but don't reindex the data
CREATE SEARCH INDEX ON wiki.solr WITH CONFIG { realtime:true } AND OPTIONS { reindex:false };
Create search index with docValues set for all columns
CREATE SEARCH INDEX ON wiki.solr WITH COLUMNS * { docValues:true };
Apply docValues to all fields in the index if the field type allows it
CREATE SEARCH INDEX ON solr.wiki 
WITH COLUMNS * { docValues : true };

Apply docValues to all applicable fields

Except intField, because docValues has been explicitly set to false for that field.
CREATE SEARCH INDEX ON solr.wiki 
WITH COLUMNS * { docValues : true }, 
intField { docValues : false };
Create docValues for more than one field but not all fields
CREATE SEARCH INDEX ON ks.tb 
WITH COLUMNS col1 
{docValues=true}, col2, col3;