CQL queries

Since distributed queries contact many shards, making queries more tolerant of shard failures ensures more successful completions. Use shards.failover and shards.tolerant parameters to define query failover and tolerance of shard failures during JSON queries. Valid configurations:

Failover true, tolerant false (default)

"shards.failover": true,
"shards.tolerant": false,

Enables query failover and disables fault tolerance. Attempt to retry the failed shard requests when errors indicate that there is a reasonable chance of recovery. If any of the nodes (shards) that we scatter fail before the query is complete, retry the shard query against a replica.

Failover false, tolerant true

"shards.failover": false,
"shards.tolerant": true,

Disable query failover. Enable fault tolerance. Make the query succeed, even if the query only partially succeeded, and did not succeed for all nodes.

Failover false, tolerant false

"shards.failover": false,
"shards.tolerant": false,

Disable query failover. Disable fault tolerance.

Failover and tolerance of partial results cannot coexist in the same query. Queries support enabling tolerance for only one parameter.

Simple search string	mytestuser1?
Solr query	name:mytestuser1\?
CQL Solr query	solr_query='{"q":"name:mytestuser1\\?"}'

Complex search string	(1+1):2
Solr query	e:\(1\+1\)\:2
CQL Solr query	solr_query='{"q":"e:\\(1\\+1\\)\\:2"}'

Specify the TZ parameter to overwrite the default TimeZone (UTC) that is used for adding and rounding in date math. The local rules for the specified time zone, including the start and end of daylight saving time (DST) if any, determine when each arbitrary day starts. The time zone rules impact the rounding and adding of DAYs, but also cascades to rounding of HOUR, MIN, MONTH, and YEAR. For example, specifying a different time zone changes the result: The value of the TZ parameter can be any zone ID that is supported by the java TimeZone class.

Specify the facet parameters inside a facet JSON object to perform field, query, and range faceting inside Solr queries. Distributed pivot faceting is supported. 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.

Faceted search example 

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

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"]}}';

Distributed pivot faceting example 

SELECT id FROM table WHERE solr_query='{"q":"id:*","facet":{"pivot":"type,value","limit":"-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}

Range by date facet example 

SELECT * FROM solr WHERE solr_query='{"q":"business_date:*","facet":{"range":"business_date", "f.business_date.range.start":"2015-01-01T00:00:00Z", "f.business_date.range.end":"2015-08-01T00:00:00Z", "f.business_date.range.gap":"+1MONTH"}}';

During a distributed query, every node is responsible for a set of Cassandra token ranges. A shard is the node/ranges combination. The shard token range is reported:

• In the shards.info response for HTTP queries.
• In the system_traces.events table for HTTP queries that provide cassandra.trace=true and CQL Solr queries that enable tracing at the driver level.

To use a single pass distributed query instead of the standard two-pass query, specify the distrib.singlePass Boolean parameter in the JSON query expression:

SELECT * FROM ks.cf WHERE solr_query = '{"q" : "*:*", "distrib.singlePass" : true}' 

Using a single-pass distributed query has an operational cost that includes potentially more disk and network overhead. With single-pass queries, each node reads all rows that satisfy the query and returns them to the coordinator node. An advanced feature, a single-pass distributed query saves one network round trip transfer during the retrieval of queried rows. A regular distributed query performs two network round trips, the first one to retrieve IDs from Solr that satisfy the query and another trip to retrieve only the rows that satisfy the query from Cassandra, based on IDs from the first step. Single-pass distributed queries are most efficient when most of the documents found are returned in the search results, and they are not efficient when most of the documents found will not be returned to the coordinator node.

Use the following syntax to name your queries to support metrics and monitoring for performance objects. Naming queries 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*"}' LIMIT 50;

For example:

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

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.

To dynamically enable pagination when cql_solr_query_paging: off in dse.yaml, use the "paging":"driver" parameter:

cqlsh> select id from wiki.solr where solr_query='{"q":"*", "sort":"id asc", "paging":"driver"}';