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:
  • The existing resources are used if they are already uploaded.
  • 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_map
    | OPTIONS option_map:value [, option_map: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' columnFamilyName
                      ('WITH' indexOptions)?
columnFamilyName  ::= (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 ALTER SEARCH INDEX search CQL command

columnFamilyName
Image shows a railroad diagram of columnFamilyName

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

Shortcuts for config and schema elements

Shortcuts for common elements are supported for config and schema elements.

Shortcuts for config element paths
Shortcut Verb Type Comment
autoCommitTime SET int 10000
defaultQueryField SET, DROP string standard - StandardDirectoryFactory

encrypted - EncryptedFSDirectoryFactory

directoryFactory SET string solr.StandardDirectoryFactory
directoryFactoryClass SET string A fully qualified class name for a directory factory.
filterCacheLowWaterMark SET int  
filterCacheHighWaterMark SET int  
mergeFactor SET int Default is 10.
mergeMaxThreadCount SET int  
mergeMaxMergeCount SET int  
ramBufferSize SET int Default is 512.
realtime SET boolean  
typeMappingVersion SET int Default is 2.

Index options

Index options
Option Description
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.

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 search core:
Option Description
docValues true | false - Create docValues for the column in the generated schema. Default: false
copyField true | false - Create a copy field for the column if it is a string type. Default: false
excluded true | false - Exclude the column from the search index. Default: false
Options applied to the column become the default value of the option for fields that do not have an explicit value for the option.

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', allowing for greater space saving but slower querying.

Configuration options

Specify resource generation configuration options with a configuration option map.

Configuration options override values that are typically available in the /en/datastax_enterprise/5.1/datastax_enterprise/search/configSolrconfXml.html file. The CONFIG option map can pass options with this syntax:
WITH CONFIG option_name:value [, option_name:value, ...]
The key value pairs support these valid configuration options for the search index.
Option Description
defaultQueryField The schema field to use when no field is specified in queries. Default: none
autoCommitTime The maximum auto soft commit time in milliseconds. Default: 10000 (10 seconds)
indexMergeFactor How many segments of equal size to build before merging them into a single segment. Default: 10
indexRamBufferSize The index ram buffer size in MB. Default: 512
directoryFactoryClass The fully qualified class name of the directory factory. Default: solr.StandardDirectoryFactory
typeMappingVersion The Solr/Cassandra type mapping version. Default: 2
realtime true | false - Enable live indexing (also known as RT). Default: false

Request options

Request options configure the entire request. The OPTIONS map can pass options with this syntax:
WITH OPTIONS option_name:value [, option_name:value, ...]
The key value pairs can be one or more of these request options.
Option Description
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.
  • false - does not reindex the data. Default.
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;
To create the Wiki.Solr index and preserve the case of keyspace and table names, use quotation marks:
CREATE SEARCH INDEX IF NOT EXISTS ON "Wiki"."Solr" WITH COLUMNS id,val1;
Create real time (RT) search index and reindex the data:
CREATE SEARCH INDEX ON wiki.solr WITH CONFIG { realtime:true } AND OPTIONS { reindex:true };
Create search index with docvalues set for all columns:
CREATE SEARCH INDEX ON wiki.solr WITH COLUMNS * { docValues:true };

Specifying columns

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 };