August | 2016 | tsoft.se

For a long time it has not been possible to do a SELECT * FROM table WHERE firstname like ‘t%’; in Cassandra like you could in eg.. MySQL or any other Relation Database for that matter.

In Cassandra v3.4 this is now possible, BUT it requires some extra to do it right, and that is why I created this blog post cause I had trouble finding it.

The solution is to create a separate index, and not the secondary indexes that Cassandra came with, but a different index, called a SASI index.

This is what I have

CREATE TABLE bth.employee (
    id int,
    lastname text,
    firstname text,
    dateofbirth date,
    PRIMARY KEY (id, lastname, firstname)
) WITH CLUSTERING ORDER BY (lastname ASC, firstname ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'bucket_high': '1.5', 'bucket_low': '0.5', 'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'enabled': 'true', 'max_threshold': '32', 'min_sstable_size': '50', 'min_threshold': '4', 'tombstone_compaction_interval': '86400', 'tombstone_threshold': '0.2', 'unchecked_tombstone_compaction': 'false'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE bth.employee (

id int,

lastname text,

firstname text,

dateofbirth date,

PRIMARY KEY (id, lastname, firstname)

) WITH CLUSTERING ORDER BY (lastname ASC, firstname ASC)

AND bloom_filter_fp_chance = 0.01

AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}

AND comment = ''

AND compaction = {'bucket_high': '1.5', 'bucket_low': '0.5', 'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'enabled': 'true', 'max_threshold': '32', 'min_sstable_size': '50', 'min_threshold': '4', 'tombstone_compaction_interval': '86400', 'tombstone_threshold': '0.2', 'unchecked_tombstone_compaction': 'false'}

AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}

AND crc_check_chance = 1.0

AND dclocal_read_repair_chance = 0.1

AND default_time_to_live = 0

AND gc_grace_seconds = 864000

AND max_index_interval = 2048

AND memtable_flush_period_in_ms = 0

AND min_index_interval = 128

AND read_repair_chance = 0.0

AND speculative_retry = '99PERCENTILE';

And the content of it looks like this

cqlsh:bth> select * from bth.employee;

 id | lastname    | firstname | dateofbirth
----+-------------+-----------+-------------
  1 |    eriksson |    tobias |  1973-06-11
  2 |  holgersson |    marcus |  1972-08-01
  7 |    eriksson |     linda |  1974-06-11
  3 | abrahamsson |    ingvar |  1959-10-05

(4 rows)
cqlsh:bth>

cqlsh:bth> select * from bth.employee;

id | lastname | firstname | dateofbirth

----+-------------+-----------+-------------

1 | eriksson | tobias | 1973-06-11

2 | holgersson | marcus | 1972-08-01

7 | eriksson | linda | 1974-06-11

3 | abrahamsson | ingvar | 1959-10-05

(4 rows)

cqlsh:bth>

And now I would like to search for all the rows that has a first name that starts with a ‘t’

In SQL that would have been :

SELECTÂ * FROMÂ bth.employee WHEREÂ firstname LIKEÂ ‘t%’;

In fact we could have done that on any column …. but in Cassandra it would result in something like this:

cqlsh:bth> SELECT * FROM bth.employee WHERE firstname like 't%';
InvalidRequest: code=2200 [Invalid query] message="firstname LIKE '<term>%' restriction is only supported on properly indexed columns"
cqlsh:bth>

cqlsh:bth> SELECT * FROM bth.employee WHERE firstname like 't%';

InvalidRequest: code=2200 [Invalid query] message="firstname LIKE '<term>%' restriction is only supported on properly indexed columns"

cqlsh:bth>

In Cassandra we first has to decide on which columns this should be possible, by creating an index like this:

CREATE CUSTOM INDEX employee_firstname_idx ON bth.employee (firstname) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer', 'case_sensitive': 'false'};

1	CREATE CUSTOM INDEX employee_firstname_idx ON bth.employee (firstname) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer', 'case_sensitive': 'false'};

And so you can now do the following

cqlsh:bth>SELECT * FROM bth.employee WHERE firstname LIKE 't%';

 id | lastname | firstname | dateofbirth
----+----------+-----------+-------------
  1 | eriksson |    tobias |  1973-06-11

(1 rows)
cqlsh:bth>

cqlsh:bth>SELECT * FROM bth.employee WHERE firstname LIKE 't%';

id | lastname | firstname | dateofbirth

----+----------+-----------+-------------

1 | eriksson | tobias | 1973-06-11

(1 rows)

cqlsh:bth>

But what if you decide that I would like to know all the employees that ends with an ‘s’ in their name, so something like this:

cqlsh:bth> SELECT * FROM bth.employee WHERE firstname like '%s';
InvalidRequest: code=2200 [Invalid query] message="firstname LIKE '%<term>' restriction is only supported on properly indexed columns"
cqlsh:bth>

cqlsh:bth> SELECT * FROM bth.employee WHERE firstname like '%s';

InvalidRequest: code=2200 [Invalid query] message="firstname LIKE '%<term>' restriction is only supported on properly indexed columns"

cqlsh:bth>

So to be able to search for something that contains we have to change the index like this instead:

CREATE CUSTOM INDEX employee_firstname_idx ON bth.employee (firstname) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'CONTAINS', 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer', 'case_sensitive': 'false'};

1	CREATE CUSTOM INDEX employee_firstname_idx ON bth.employee (firstname) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'CONTAINS', 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer', 'case_sensitive': 'false'};

And now you can run that query again:

cqlsh:bth> SELECT * FROM bth.employee WHERE firstname like '%s';

 id | lastname   | firstname | dateofbirth
----+------------+-----------+-------------
  1 |   eriksson |    tobias |  1973-06-11
  2 | holgersson |    marcus |  1972-08-01

(2 rows)
cqlsh:bth>

cqlsh:bth> SELECT * FROM bth.employee WHERE firstname like '%s';

id | lastname | firstname | dateofbirth

----+------------+-----------+-------------

1 | eriksson | tobias | 1973-06-11

2 | holgersson | marcus | 1972-08-01

(2 rows)

cqlsh:bth>

You can read more about the SASI index hereÂ https://docs.datastax.com/en/cql/3.3/cql/cql_reference/refCreateSASIIndex.html

Enjoy!

-Tobias

I was just playing around with Cassandra WRITETIME and thought it was somewhat difficult to figure out the date / timestamp of a number like this (microseconds since EPOC) 1470645914253000.

So in my example it looked like this

cqlsh:bth> select id, writetime(dateofbirth) from bth.employee;

 id | writetime(dateofbirth)
----+------------------------
  1 |       1470645914253000
  2 |       1470645977177000
  7 |       1470948508799001
  3 |       1470645977178000

(4 rows)
cqlsh:bth>

cqlsh:bth> select id, writetime(dateofbirth) from bth.employee;

id | writetime(dateofbirth)

----+------------------------

1 | 1470645914253000

2 | 1470645977177000

7 | 1470948508799001

3 | 1470645977178000

(4 rows)

cqlsh:bth>

So I figured why not create a UDF that would solve this for me

That turned out to be a little bit of a challenge …

I thought that I could do like this

CREATE FUNCTION bth.ts2date ( input bigint )
	RETURNS NULL ON NULL INPUT
    RETURNS text 
    LANGUAGE java
    AS $$
    	if( input > 0L ) {
    		long ms = input / 1000L;
    		Date date=new Date(ms);
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss,SSS");
    		return sdf.format(date);
    	} else return null;
    $$;

CREATE FUNCTION bth.ts2date ( input bigint )

RETURNS NULL ON NULL INPUT

RETURNS text

LANGUAGE java

AS $$

if( input > 0L ) {

long ms = input / 1000L;

Date date=new Date(ms);

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss,SSS");

return sdf.format(date);

} else return null;

$$;

BUT NO, YOU CAN NOT!!!

There are several WRONGS in here it turns out

First off you have to turn on
enable_user_defined_functions: true
in the conf/cassandra.yaml file
All classes has to be fully qualified, so Date would be java.util.Date, and so on…
The division operator ‘/’ can not be used !!! however +,- and * works fine. surely this must be a bug … this called for some thinking…

The error I got when trying to use the code above without fully qualified names was

cqlsh> CREATE FUNCTION ts2date ( input bigint )
   ... RETURNS NULL ON NULL INPUT
   ...     RETURNS text 
   ...     LANGUAGE java
   ...     AS $$
   ...     if( input != null ) {
   ...     Date date=new Date(mills);
InvalidRequest: code=2200 [Invalid query] message="Functions must be fully qualified with a keyspace name if a keyspace is not set for the session"

cqlsh> CREATE FUNCTION ts2date ( input bigint )

... RETURNS NULL ON NULL INPUT

... RETURNS text

... LANGUAGE java

... AS $$

... if( input != null ) {

... Date date=new Date(mills);

InvalidRequest: code=2200 [Invalid query] message="Functions must be fully qualified with a keyspace name if a keyspace is not set for the session"

And the reason, if I got it right, is that you can not do imports.

The error I got when trying to use the division ‘/’ operator was this:

cqlsh:bth> CREATE FUNCTION bth.ts2date ( input bigint )
       ... RETURNS NULL ON NULL INPUT
       ...     RETURNS text 
       ...     LANGUAGE java
       ...     AS $$
       ...     if( input > 0L ) {
       ...     java.util.Date date=new java.util.Date(input/1000);
Invalid syntax at line 7, char 49
      java.util.Date date=new java.util.Date(input/1000);
                                                  ^
cqlsh:bth>

cqlsh:bth> CREATE FUNCTION bth.ts2date ( input bigint )

... RETURNS NULL ON NULL INPUT

... RETURNS text

... LANGUAGE java

... AS $$

... if( input > 0L ) {

... java.util.Date date=new java.util.Date(input/1000);

Invalid syntax at line 7, char 49

java.util.Date date=new java.util.Date(input/1000);

cqlsh:bth>

The code that works looks like this, using java.math.BigDecimal to solve it was perhaps a so-so solution, but it works:

CREATE FUNCTION bth.ts2date ( input bigint ) 
RETURNS NULL ON NULL INPUT     
RETURNS text      
LANGUAGE java     
AS $$     
	if( input > 0L ) {     
		java.math.BigDecimal t = java.math.BigDecimal.valueOf(1000L);     
		java.math.BigDecimal inp = java.math.BigDecimal.valueOf(input);     
		java.math.BigDecimal mst = inp.divide(t); 
		long ms = mst.longValue();     
		java.util.Date date=new java.util.Date(ms);     
		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss,SSS");     
		return sdf.format(date);     
	} else return null;     
$$;

CREATE FUNCTION bth.ts2date ( input bigint )

RETURNS NULL ON NULL INPUT

RETURNS text

LANGUAGE java

AS $$

if( input > 0L ) {

java.math.BigDecimal t = java.math.BigDecimal.valueOf(1000L);

java.math.BigDecimal inp = java.math.BigDecimal.valueOf(input);

java.math.BigDecimal mst = inp.divide(t);

long ms = mst.longValue();

java.util.Date date=new java.util.Date(ms);

java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss,SSS");

return sdf.format(date);

} else return null;

$$;

So now my output in cqlsh.sh looks like this now

cqlsh:bth> select id, ts2date(writetime(dateofbirth)) from bth.employee;

 id | bth.ts2date(writetime(dateofbirth))
----+-------------------------------------
  1 |             2016-08-08 10:45:14,253
  2 |             2016-08-08 10:46:17,177
  7 |             2016-08-11 22:48:28,799
  3 |             2016-08-08 10:46:17,178

(4 rows)
cqlsh:bth>

cqlsh:bth> select id, ts2date(writetime(dateofbirth)) from bth.employee;

id | bth.ts2date(writetime(dateofbirth))

----+-------------------------------------

1 | 2016-08-08 10:45:14,253

2 | 2016-08-08 10:46:17,177

7 | 2016-08-11 22:48:28,799

3 | 2016-08-08 10:46:17,178

(4 rows)

cqlsh:bth>

That is a lot better !

tsoft.se

Tobias – With a Passion For Software Development

Monthly Archives: August 2016

SQL LIKE operation in Cassandra, is possible in v3.4+

UDF/User Defined Functions in Cassandra 3.x