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, you can 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" />
If the requestHandler is not already set, the CQLSearchHandler is automatically inserted.

CQL Solr considerations 

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
Use the count() function in CQL Solr queries to return the number of rows that satisfy the Solr query:
SELECT count(*) FROM table WHERE solr_query = '...';

Using count() in combination with LIMIT or facets results in an error.

Column aliases are not supported in solr_query queries.

Queries for tuples and UDTs 

DSE Search supports indexing and querying of advanced data types, including tuples and user-defined types (UDT).
  • The tuple data type holds fixed-length sets of typed positional fields. Use a tuple as an alternative to a user-defined type.
  • A user-defined type (UDT) facilitates handling multiple fields of related information in a table. UDTs are a specialization of tuples. All examples and documentation references to tuples apply to both tuples and UDTs.

    Applications that require multiple tables can be simplified to use fewer tables by using a user-defined type to represent the related fields of information instead of storing the information in a separate table.

Configuration and schema requirements apply. See UDT query examples.

Using CQL partition key restrictions with Solr queries 

Solr CQL queries support restriction to a single partition key. Partition key restrictions work only when _partitionKey is explicitly indexed or the schema explicitly includes all of the components of the Cassandra 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, valuetext, PRIMARYKEY ((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';.