Using CQL Solr queries in DSE Search 

DataStax Enterprise supports production-grade implementation of CQL Solr queries in DSE Search. You can develop CQL-centric applications supporting full-text search without having to work with Solr-specific APIs.

DataStax Enterprise supports production-grade implementation of CQL Solr queries in DSE Search. You can develop CQL-centric applications supporting full-text search without having to work with Solr-specific APIs. Only full text search queries are supported in this release. Using CQL, DSE Search supports partial document updates that enable you to modify existing information while maintaining a lower transaction cost. Before using CQL Solr queries in DSE Search, configure solrconfig.xml to handle CQL queries.

Required configuration for CQL Solr queries in DSE Search 

Using CQL solr_query syntax is supported only on nodes where search is enabled.

When you automatically generate resources, the solrconfig.xml file already contains the request handler for running CQL Solr queries in DSE Search. If you do not automatically generate resources and want to run CQL Solr queries using custom resources, verify or manually add the CqlSearchHandler handler to the solrconfig.xml file:
<requestHandler class="com.datastax.bdp.search.solr.handler.component.CqlSearchHandler" name="solr_query" />

CQL Solr considerations 

  • CQL Solr queries are defaulted to an equivalent LIMIT 10.
  • The row retrieval phase of CQL Solr queries uses the LOCAL_ONE consistency level for reads. In contrast, HTTP Solr queries use local/internal reads.
  • When using cqlsh, pagination is on by default. Solr pagination is transparently set. Solr restrictions apply to pagination. Queries with smaller result sets will see increased performance with paging off.
  • To turn pagination off, use the CQL PAGING command:
    PAGING OFF
Note: Limitations and known Apache Solr issues apply to DSE Search queries. For example, incorrect SORT results for tokenized text fields.

CQL Solr query syntax 

You can run CQL Solr queries using the SELECT statement that includes the search expression.

Synopsis

SELECT select expression
 FROM table
 [WHERE solr_query = 'search expression'] [LIMIT n]
There are two types of search expressions:

Search queries with CQL 

The Solr query expression uses the syntax supported by the Solr q parameter. For example:
SELECT * FROM keyspace.table WHERE solr_query='name: cat name: dog -name:fish'
When you name specific columns, DSE Search retrieves only the specified columns and returns the columns as part of the resulting rows. DSE Search supports projections (SELECT a, b, c...) only, not functions, for the select expression. The following example retrieves only the name column:
SELECT name FROM keyspace.table WHERE solr_query='name:cat name:dog -name:fish'
Use the LIMIT clause to specify how many rows to return. The following example retrieves only 1 row:
SELECT * FROM keyspace.table WHERE solr_query='name:cat name:dog -name:fish' LIMIT 1

You cannot use CQL Solr queries to set the consistency level, ordering, or specify WHERE clauses other than the solr_query one. The consistency level for CQL Solr queries is ONE by default and should not be changed; otherwise, the query will return an error.

Using CQL partition key restrictions with Solr queries 

Solr CQL queries support restriction to a single partition key. In your schema, you can override _partitionKey when not using joins.

Example:
SELECT id, date, value FROM keyspace.table WHERE id = 'series1' AND solr_query='value:bar*'" 
CQL partition key restrictions work only with fully specified partition keys. For example, with this table:
CREATE TABLE vtbl (k1 text, k2 text, value text, PRIMARY KEY ((k1, k2)))
Avoid using a query like this:
SELECT * FROM vtbl WHERE k1 = '50' AND solr_query='value:*'
Use a filter query against the partially specified composite partition key:
SELECT * FROM valuetable WHERE solr_query='{"q":"value:*", "fq":"k1:50"}'

Using the Solr token function 

Solr CQL queries support limited use of the CQL token function. The token function enables targeted search that restricts the nodes queried to reduce latency.
Note: Using the Solr token function is for advanced users only and is supported only in specific use cases.
Example:
SELECT id, value FROM keyspace.table WHERE token(id) >= -3074457345618258601 AND token(id) <= 3074457345618258603 AND solr_query='id:*'
Example with an open range:
SELECT id, value FROM keyspace.table WHERE token(id) >= 3074457345618258604 AND solr_query='id:*'
Constraints apply to using the token function with Solr CQL queries:
  • token() cannot be used with route.range or route.partition
  • Wrapping token() ranges are not supported
  • A specified token() range must be owned by a single node; ranges cannot span multiple nodes
  • Because DSE uses the Solr single-pass queries, only the fields that are declared in the Solr schema are returned in the query results. If you have columns that do not need to be indexed, but still need to be returned by using a token-restricted query, you can declare the columns as stored non-indexed fields in your schema.xml file.

Secondary indexes (2i) in queries 

Use solr_query to query secondary indexes (2i) that are created by Solr. Use:

SELECT * from users WHERE solr_query = '{"q":"irc:jdoe"}';
The secondary indexes created by Solr cannot be used as a Cassandra 2i index in a cqlsh query. For example, this syntax fails: SELECT * FROM users WHERE irc = 'jdoe';.