Search index commands

In DSE Search, a search index is an Apache Solr™ core. Each DSE Search index uses an internally stored index configuration pair (schema.xml and solrconfig.xml) that is automatically generated when the index is created. DSE stores the Solr search index configuration pair (schema and config) in the solr_admin.solr_resources table and persists it to other search nodes in the cluster.

Manage DSE Search indexes using the following CQL commands:

CREATE SEARCH INDEX: Generates a new search index on an existing table with default schema and config.
DESCRIBE SEARCH INDEX: Displays the active or pending schema or config in XML format.
ALTER SEARCH INDEX CONFIG: Modifies the search index config. After modifying, use reload to push the changes live.
ALTER SEARCH INDEX SCHEMA: Modifies the search index schema. After modifying, use reload to push the changes live.
RELOAD SEARCH INDEX: Loads pending changes to the index schema and config. Some changes such as adding or removing indexed fields require a rebuild.
REBUILD SEARCH INDEX: Reconstructs the search index using the active schema and config.
COMMIT SEARCH INDEX: Forces a reload of data into the index after data is added, modified, or removed from the corresponding CQL table.
DROP SEARCH INDEX: Removes the search index and corresponding files.

Native CQL search queries

You can query the search index using native CQL statements, without using solr_query syntax with ALLOW FILTERING as required in earlier versions of DSE.

Use native CQL SELECT statements to filter results using the following syntax:

SELECT <selectors> FROM <table>
  WHERE <indexed_column_expression>
  [LIMIT <n>]
  [ORDER BY <column_name>]

<indexed_column_expression> is the WHERE statement in the following native CQL search query examples.

Querying all columns equally

Query directly any search-indexed column, even when you don’t know if the column is part of the primary key or if it has been specified in full.

SELECT * FROM customer.region1 WHERE title='Anaisa Pye'

SELECT * FROM customer.region1 WHERE id LIKE '237%'

Operators

Query any column that is indexed using standard CQL operators, including LIKE, IS NOT NULL, and != operators.

SELECT * FROM customer.region1 WHERE id LIKE '237%'

SELECT * FROM customer.region1 WHERE title IS NOT NULL

SELECT * FROM customer.region1 WHERE title!='Anaisa Pye'

SELECT * FROM customer.region1.solr WHERE title='Anaisa Pye'

SELECT * FROM customer.region1 WHERE field >0 and field <=100

SELECT * FROM customer.region1 WHERE title LIKE 'Anais%'

SELECT * FROM customer.region1 WHERE title LIKE '%naisa'

SELECT * FROM customer.region1 WHERE title LIKE '%nais%'

SELECT * FROM customer.region1 WHERE title IN ('Anaisa Pye', 'Karlsbrunn')

SELECT * FROM customer.region1 WHERE collection CONTAINS 'Anaisa Pye'

Collections

Lists and sets support only the CONTAINS operator.

SELECT * FROM customer.region1 WHERE title IS NOT NULL AND collection CONTAINS 'Anaisa Pye' AND age > 35

Maps

Query maps using dynamicfield with a map entry equality syntax:

SELECT * FROM customer.region1 WHERE dynamicfield['dynamicfield_key1'] = 'value1'

Prepared statements

To reduce the workload on the coordinator by removing the overhead of parsing the query, use two question marks as input parameters.

SELECT id FROM customer.region1 WHERE title LIKE ? limit ?

Tokenized text

The default for CQL text type is solr.StrField, in contrast with the solr.TextField text type in previous DSE versions. Queries use this criteria for text columns to be considered tokenized/analyzed:

Indexed by	Tokenized	Query behavior
solr.StrField	no	An exact or wildcard match performs as expected
solr.TextField	yes	Uses single term phrase query matching.

Indexed by

Tokenized

Query behavior

solr.StrField

An exact or wildcard match performs as expected

solr.TextField

yes

Uses single term phrase query matching.

Tokenized text is restricted with LIKE, IS NOT NULL, and single token match. Multi-token match is not supported.

StrField and TextField

In these examples, id is indexed by StrField and title is indexed by TextField

id           | body | date | solr_query | title
-------------+------+------+------------+-----------------
test1 	| null | null |  null      | This is a test1
test2 text   | null | null |  null      | This is a test2

This query matches nothing, since there is not an exact match against id.

SELECT * FROM customer.region1 WHERE id LIKE 'test';

This query uses wildcard * to match both rows.

select * from customer.region1 where id LIKE 'test%';

This query has undefined behavior for title multi-token match.

select * from customer.region1 where id LIKE 'test%' and title LIKE 'is a';

Matches no rows, id matches but title doesn’t.

SELECT * FROM customer.region1 WHERE id LIKE 'test%' AND title LIKE 'is a';

Matches both rows as expected.

SELECT * FROM customer.region1 WHERE id LIKE 'test%' AND title LIKE 'is';

Matches both rows as expected, with single token match for title.

SELECT * FROM customer.region1 WHERE id LIKE 'test%' AND title LIKE 'test%';

Tuples and UDTs

Queries on tuples and user-defined types (UDT) support only the equal sign (=) operator. The query must provide all elements of the tuple or UDT. Queries with nested Tuple or UDTs or collections are not supported due to operator scope constraint.

This example query provides all elements of the tuple.

SELECT * FROM customer.region1 WHERE skill={name:'masonry',type:'type1'}