Using CQL Solr queries

DataStax Enterprise supports production-grade implementation of CQL Solr queries. 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. 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. Some features are not supported. Before using CQL Solr queries, configure the solrconfig to handle CQL queries if necessary.

The row retrieval phase of CQL Solr queries uses LOCAL_ONE consistency level for reads and can actuate read repair. In contrast, HTTP Solr queries do local/internal reads and do not actuate read repairs.

Required configuration for Solr queries

When you automatically generate resources, the solrconfig.xml resource file already contains the request handler for running CQL Solr queries. If you do not automatically generate resources and want to run CQL Solr queries using custom resources, you must add the following handler to the solrconfig.xml file:

<requestHandler class="com.datastax.bdp.search.solr.handler.component.CqlSearchHandler" name="solr_query" />

You cannot add any components.

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:

Solr query
JSON query

Using a Solr query expression

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 partition key restrictions with Solr CQL 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*'"

Using a JSON query expression

You format the query expression as a JSON string. The JSON-based query expression supports local parameters in addition to the following parameters:

{
  "q": <query expression (string)>, 
  "fq": <filter query expression(s) (string or array of strings)>,  
  "facet": <facet query expression (object)>,
  "sort": <sort expression (string)>, 
  "start": <start index(number)>,
  "commit": <true/false (boolean)>,
  "route.partition": <partition routing expression (array of strings)>, 
  "route.range": <range routing expression (array of strings)>, 
  "query.name": <query name (string)>
}

For example:

SELECT id FROM nhanes_ks.nhanes WHERE solr_query=' {"q":"ethnicity:Asian"}';

SELECT id FROM nhanes_ks.nhanes WHERE solr_query='{"q":"ethnicity:Mexi*", "sort":"id asc"}' LIMIT 3;

SELECT * FROM mykeyspace.mysolr WHERE solr_query='{"q" : "{!edismax}quotes:yearning or kills"}';

Note: To use Solr Extended DisMax Query Parser (eDisMax) with solr_query, you must include defaultSearchField in your schema.

Field, query, and range faceting with a JSON query

Specify the facet parameters inside a facet JSON object to perform field, query, and range faceting inside Solr queries. The query syntax is less verbose to specify facets by:

Specifying each facet parameter without the facet prefix that is required by HTTP APIs.
Expressing multiple facet fields and queries inside a JSON array.

A field facet example:

SELECT * FROM solr WHERE solr_query='{"q":"id:*","facet":{"field":"type"}}';

A query facet example:

SELECT * FROM solr WHERE solr_query='{"q":"id:*","facet":{"query":"type:0"}}';

Multiple queries example:

SELECT * FROM solr WHERE solr_query='{"q":"id:*","facet":{"query":["type:0","type:1"]}}';

Range facet example:

SELECT * FROM solr WHERE solr_query='{"q":"id:*","facet":{range":"type", "f.type.range.start":-10, "f.type.range.end":10, "range.gap":1}}}';

The returned result is formatted as a single row with each column corresponding to the output of a facet (either field, query, or range). The value is represented as a JSON blob because facet results can be complex and nested. For example:

facet_fields           | facet_queries
------------------------+-------------------------
 {"type":{"0":2,"1":1}} | {"type:0":2,"type:1":1}

Warning: Solr range facets before, after, and between might return incorrect and inconsistent results on multinode clusters. See SOLR-6187 and SOLR-6375.

JSON query commit option

If you are executing custom queries after bulk document loading, and the normal auto soft commit is disabled or extremely infrequent, and you want the latest data to be visible to your query, you can use the JSON query commit option to ensure that all pending updates are soft-committed before the query runs. By default, the commit option is set to false.

For example:

SELECT id FROM nhanes_ks.nhanes WHERE solr_query='{"q":"ethnicity:Asia*", "commit":true}' LIMIT 50;

Warning: Do not use the JSON commit option for live operations against a production cluster. DataStax recommends using the JSON commit option only when you would otherwise be forced to issue a commit though the Solr HTTP interface. The commit option is not a replacement for the normal auto soft commit process.

JSON query name option

Using the following syntax you can name your queries, which can be useful for tagging and JMX operations, for example.

SELECT id FROM nhanes_ks.nhanes WHERE solr_query=' {"query.name":"Asian subjects", "q":"ethnicity:Asia*", "commit":"true"}' LIMIT 50;

Restrictions on using solr_query

DataStax Enterprise does not support the CQL solr_query syntax for searching CQL-based cores on a cluster node that is not started as a Solr node. Using the solr_query syntax from a Thrift-based client, such as Hector, is discouraged and not supported for production environments. Instead, use one of the DataStax drivers.