CREATE INDEX (OpenSearch)

An OpenSearch index is a custom index that uses the OpenSearchIndex class to replicate row mutations from a HCD table to an OpenSearch index in real time. You can then use OpenSearch to run full-text searches, search nested data structures, and run aggregation queries on your HCD data.

Unlike SAI and 2i indexes, OpenSearch indexes don’t store index data with the table’s SSTables. HCD captures writes at the coordinator level and forwards them to OpenSearch through a durable write-ahead log and a batched processing pipeline.

OpenSearchIndex captures all write types, including standard writes, BATCH writes, and lightweight transactions (LWT). For more information, see Linearizable consistency.

Before you create an OpenSearch index, you must enable the OpenSearch replicator and configure the connection to your OpenSearch cluster on each node that you want to replicate from. OpenSearch indexes require HCD 2.0.8 or later.

For prerequisites and setup instructions, see Integrate OpenSearch with Hyper-Converged Database (HCD).

Create an OpenSearch index

To create an OpenSearch index, define the table name, the optional columns to replicate, and the OpenSearchIndex index class.

If the table already contains data, the existing data is indexed in OpenSearch during the execution of this statement, unless you set skipBuildIfTableNotEmpty to true. After the index has been created, HCD forwards every subsequent change to the table.

This index type cannot be altered. If you need to modify an index, you must drop the index, and then create a new index.

CREATE CUSTOM INDEX [ IF NOT EXISTS ] <index_name>
  ON [ <keyspace_name>.]<table_name> ( [ <column_name> [, <column_name> ... ] ] )
  USING 'OpenSearchIndex'
  [ WITH OPTIONS = { <option_map> } ] ;

Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`< >`	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.

Options

IF NOT EXISTS: Allows silent skip by checking for existing indexes with the same name. If a match is found, the operation silently does nothing (no error is thrown). If no match is found, the operation creates the index.
index_name: Unique identifier for the index in HCD. This name is separate from the indexName option, which names the index in OpenSearch.

Enclose in quotes to preserve capitalization.

Must be unique across all indexes in the keyspace. Although indexes apply to a specific table, they are created at the keyspace level.
keyspace_name: Optional. The name of the keyspace that contains the table to index. If no name is specified, the current keyspace is used.
table_name: The name of the table to index.

A table can have only one OpenSearch index. You can’t create an OpenSearch index on a table that has static columns, counter columns, or dynamically masked columns. For all restrictions, see Tables that cannot be indexed.
column_name: Optional. The non-primary-key columns to replicate to OpenSearch. Leave the parentheses empty to replicate every column.

Primary key columns are always included.

You can also list columns with the columnsToIndex option. If you set both, HCD combines them.

HCD validates column types. Some types, such as non-frozen UDTs, might not be allowed in the column list, but OpenSearch can still index them using the columnsToIndex option.
WITH OPTIONS = { <option_map> }: The <option_map> is a map of options and their values defined in JSON simple format. Pass all values as strings.

Options aren’t required. For the supported options, see Index options.

Index options

The following options are specific to OpenSearch indexes. For the complete reference, see Index options.

Option Default Description

Option	Default	Description
`indexName`	`hcd_<keyspace>_<table>`	Name of the OpenSearch index to create or use. Must be all lowercase and must not start with `_` or `-`.
`createIndexIfNotExists`	`true`	When `true`, HCD creates the OpenSearch index on `CREATE INDEX`. Set to `false` when you pre-provision the index in OpenSearch.
`columnsToIndex`	All columns	Comma-separated list of non-primary-key columns to replicate to OpenSearch.
`skipBuildIfTableNotEmpty`	`false`	When `true`, HCD skips the initial SSTable scan even when the table already contains data. HCD indexes only writes made after `CREATE INDEX`.
`numShards`	`1`	Number of primary shards for the OpenSearch index. You can’t change this value after you create the index.
`numReplicas`	`0`	Number of replica shards per primary shard. Set to `1` or higher for high availability. Requires at least `numReplicas + 1` OpenSearch nodes.
`applyDefaultSchema`	`true`	When `true`, HCD derives the OpenSearch field mappings from the table schema. Set to `false` to suppress the automatic mapping.
`applyCustomSchema`	`false`	When `true`, HCD uses the mapping in `customMappingsJson` instead of the derived defaults.
`customMappingsJson`	None	A JSON string containing a complete OpenSearch type mapping object. Requires `applyCustomSchema` to be `true`.
`unpackJsonFields`	None	Comma-separated list of columns that contain a serialized JSON object. HCD expands each column’s top-level properties into individual OpenSearch fields.
`propertiesFromJsonFields`	All properties	Comma-separated list of JSON property names to keep after expansion. Used only when `unpackJsonFields` is set.
`indexNonPrimaryReplicas`	`true`	When `true`, every replica independently processes each mutation. Set to `false` so that only the primary replica processes rows, which avoids duplicate work.

indexName

hcd_<keyspace>_<table>

Name of the OpenSearch index to create or use. Must be all lowercase and must not start with _ or -.

createIndexIfNotExists

true

When true, HCD creates the OpenSearch index on CREATE INDEX. Set to false when you pre-provision the index in OpenSearch.

columnsToIndex

All columns

Comma-separated list of non-primary-key columns to replicate to OpenSearch.

skipBuildIfTableNotEmpty

false

When true, HCD skips the initial SSTable scan even when the table already contains data. HCD indexes only writes made after CREATE INDEX.

numShards

1

Number of primary shards for the OpenSearch index. You can’t change this value after you create the index.

numReplicas

0

Number of replica shards per primary shard. Set to 1 or higher for high availability. Requires at least numReplicas + 1 OpenSearch nodes.

applyDefaultSchema

true

When true, HCD derives the OpenSearch field mappings from the table schema. Set to false to suppress the automatic mapping.

applyCustomSchema

false

When true, HCD uses the mapping in customMappingsJson instead of the derived defaults.

customMappingsJson

None

A JSON string containing a complete OpenSearch type mapping object. Requires applyCustomSchema to be true.

unpackJsonFields

None

Comma-separated list of columns that contain a serialized JSON object. HCD expands each column’s top-level properties into individual OpenSearch fields.

propertiesFromJsonFields

All properties

Comma-separated list of JSON property names to keep after expansion. Used only when unpackJsonFields is set.

indexNonPrimaryReplicas

true

When true, every replica independently processes each mutation. Set to false so that only the primary replica processes rows, which avoids duplicate work.

The CQL shell (cqlsh) DESCRIBE INDEX command isn’t supported for OpenSearch indexes. To view the index definition, use DESCRIBE TABLE or DESCRIBE KEYSPACE instead.

Query with an OpenSearch index

You search OpenSearch data from CQL with the expr() function, which takes the index name and an OpenSearch query in JSON format.

SELECT id, title FROM blog.articles WHERE expr(articles_search, '{"match": {"title": "search"}}');

You can also query the documents directly in OpenSearch. For more query types, see Filter CQL queries with OpenSearch indexes.

OpenSearch index examples

The examples use the following table. Each example is an alternative configuration for the table, so don’t run them in sequence.

CREATE TABLE blog.articles (
    id    int PRIMARY KEY,
    title text,
    body  text,
    tags  set<text>
);

Basic index

Replicate the title and body columns to an OpenSearch index named articles-search. This example assumes that you enabled the OpenSearch replicator and configured the connection.

CREATE CUSTOM INDEX IF NOT EXISTS articles_search
  ON blog.articles ()
  USING 'OpenSearchIndex'
  WITH OPTIONS = {
    'indexName':              'articles-search',
    'createIndexIfNotExists': 'true',
    'columnsToIndex':         'title,body'
  };

High availability

Spread search load across shards, and keep one replica copy of each shard for redundancy. Index only the columns that you need for search to reduce storage overhead. This configuration requires at least two OpenSearch nodes.

CREATE CUSTOM INDEX IF NOT EXISTS articles_search
  ON blog.articles ()
  USING 'OpenSearchIndex'
  WITH OPTIONS = {
    'indexName':              'articles-search',
    'createIndexIfNotExists': 'true',
    'numShards':              '8',
    'numReplicas':            '1',
    'columnsToIndex':         'title,body,tags'
  };

Custom field mappings

Override the derived schema with a custom OpenSearch mapping when you need specific analyzers, field types, or sub-fields.

CREATE CUSTOM INDEX IF NOT EXISTS articles_search
  ON blog.articles ()
  USING 'OpenSearchIndex'
  WITH OPTIONS = {
    'indexName':          'articles-search',
    'applyCustomSchema':  'true',
    'customMappingsJson': '{
      "properties": {
        "id":    { "type": "keyword" },
        "title": { "type": "text", "analyzer": "english" },
        "body":  { "type": "text" }
      }
    }'
  };

The customMappingsJson value is a JSON string inside a CQL string literal. Escape inner single quotes by doubling them ('').

Pre-provisioned index

Tell HCD not to create the OpenSearch index when you manage it externally.

CREATE CUSTOM INDEX IF NOT EXISTS articles_search
  ON blog.articles ()
  USING 'OpenSearchIndex'
  WITH OPTIONS = {
    'indexName':              'articles-search',
    'createIndexIfNotExists': 'false'
  };

Skip the initial backfill

Index only writes made after CREATE INDEX, and leave existing rows out of OpenSearch until you run a separate backfill.

CREATE CUSTOM INDEX IF NOT EXISTS articles_search
  ON blog.articles ()
  USING 'OpenSearchIndex'
  WITH OPTIONS = {
    'indexName':                'articles-search',
    'skipBuildIfTableNotEmpty': 'true'
  };

For more examples, including JSON field unpacking and deferring the mapping to an OpenSearch index template, see Index examples.

CREATE INDEX (OpenSearch)

Create an OpenSearch index

Options

Index options

Query with an OpenSearch index

OpenSearch index examples

See also

Was this helpful?

Give Feedback