Is There Any Query for Cassandra as Same as SQL:Like Condition

Is there any query for Cassandra as same as SQL:LIKE Condition?

Simple answer: there is no equivalent of LIKE

https://docs.datastax.com/en/cql/3.3/cql/cql_reference/cqlSelect.html

Here is the command reference for v0.8:

http://www.datastax.com/docs/0.8/references/cql#cql-reference

If you maintain another set of rows that hold references to a username:

row: username:bab -> col:babu1, col:babar
row: username:babu -> col:babur

Effectively you are cheating by pre-populating all of the results that you would normally search with in the RDBMS world. Storage is cheap in comparison to what it was years ago ... which is why this is now an accepted approach. It's less intensive on the CPU and Memory to retrieve a pre-populated list of information.

How to use like operator in cassandra database?

It's not supported until 3.4+ but should upgrade to 3.11.latest. You may want to configure your index as well, an excellent walkthrough is here: http://www.doanduyhai.com/blog/?p=2058

Alternative for OR condition after where clause in select statement Cassandra

Simple answer: there is no equivalent of OR

http://www.datastax.com/docs/0.8/dml/using_cql

Here is the command reference for v0.8:

http://www.datastax.com/docs/0.8/references/cql#cql-reference

SELECT [FIRST N] [REVERSED] FROM [USING ]
[WHERE ] [LIMIT N];

..

The WHERE clause provides for filtering the rows that appear in results. The clause can filter on a key name, or range of keys, and in the case of indexed columns, on column values. Key filters are specified using the KEY keyword, a relational operator, (one of =, >, >=, <, and <=), and a term value. When terms appear on both sides of a relational operator it is assumed the filter applies to an indexed column. With column index filters, the term on the left of the operator is the name, the term on the right is the value to filter on.

Command to search a part of value in a table in cassandra

You could make use of SASI index to do this type "LIKE" querying. SASI was an improvement open sourced (contributed by Apple) to Cassandra community. This index gets created for every SSTable being flushed to disk and doesn't maintain a separate table. Hence less disk usage, no separate memtable/bloom filter/partition index (less memory) and minimal overhead.

Essentially there are three modes

PREFIX - Used to serve LIKE queries based on prefix of indexed column
CONTAINS - Used to serve LIKE queries based on whether the search term exists in the indexed column
SPARSE - Used to index data that is sparse (every term/column value has less than 5 matching keys). For example range queries that span large timestamps.

With this Index on say "description" column, you can satisfy query

SELECT description FROM projects WHERE description LIKE '%hello%';

Reference documentation on SASI index.

Does CQL support the OR operator in a WHERE clause?

According this link, the 'OR' operator is not supported by CQL.

You can only use AND operator with primary key columns (partition key and clustering columns keys).

The 'IN' operator is supported but there are several limitations see this link.

But there is a way to query cassandra with less limitations. You can use external tools like Apache drill, Apache Spark, PrestoDB for exemple to query cassandra (and other datasources) with SQL. This tools provide and SQL language to query many différent datasources.

But be careful, this is very dangerous with big data volume if your query is not optimized. But some tools use specific query constraint to run query more efficient (prestoDB optimaze query to use cassandra partition key)

Cassandra equivalent of group by

GROUP BY is supported from version 3.10 check improvement ticket here and Cassandra official documentation.

---

Related Topics