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

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

And the content of it looks like this

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:

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

And so you can now do the following

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:

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

And now you can run that query again:

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



Posted in Uncategorized | 3 Comments

UDF/User Defined Functions in Cassandra 3.x

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

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


There are several WRONGS in here it turns out

  1. First off you have to turn on
    enable_user_defined_functions: true
    in the conf/cassandra.yaml file
  2. All classes has to be fully qualified, so Date would be java.util.Date, and so on…
  3. 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

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:

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

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

That is a lot better !

Posted in Cassandra, JAVA | 1 Comment

Cassandra set the writetime explicitly with a PreparedStatement

This is a quick one, I wanted to set the writetime of a row explicitly when I populate the database for testing purposes. We use the writetime of a column to filter them out.

It required some looking around to find out how to do this…. so I figured I write an article about it.

The timestamp will be set for ALL cells in this row (well not the primary key, cause it does not have a timestamp, but the others).

The timestamp is given as milliseconds since EPOC, so lots of digits :-).

A prepared statement would then look like this (Scala code)

TTL and TIMESTAMP can both be set like this, i.e. with [ttl] and [timestamp]


Posted in Cassandra, JAVA, Scala | 1 Comment

Apache SPARK and Cassandra and SQL

This is a short intro to start using Apache SPARK with Cassandra, running SQL on the Cassandra tables.

Note that I am not running a SPARK cluster, I am running “local”, to me this is really convenient, not having to run a SPARK server and workers for something so small. So for playing around with SPARK and Cassandra this is really good.

I am using Scala and SBT.

Something I was struggling hard with, to get the dependency versions right. It is crucial that you do not do like I did first, use version 1.5.2 of Spark and 1.5.0 for SparkCassandraConnector, this will NOT work. I constantly got exception with java.lang.NoSuchMethodException, so incredibly frustrating to try out version after version.


A small Scala program to show how it works


The output…



Posted in Uncategorized | Tagged , , , , , , , , , | 2 Comments

SBT Good to know…

Dependecy problems

I have been having some difficulties figuring out what depends on what. I found the following set plugins which I think can be really helpful;




Be sure to install GraphWiz first, I used Homebrew on my Mac

brew install graphviz

and I also had to create a config file

with the following content

The readme explains how to use it pretty well, simply start sbt CLI

It will give you a graph that looks something like this (it is in SVG format so it is searchable!!!) Now you should see which package/jar is using which, and also where the different versions clash…dependency-graph


Show the class path for the run command


Posted in SBT, Scala | 1 Comment

Create an MBean (JMX) in Scala

Create the MBean like this

NOTE, that the interface/trait must end with MBean in the name

And this is how you register your MBean

And the simply launch Java Mission Control (imc), attach to the JVM, and modify your MBean attributes as you like.


Posted in Uncategorized | 2 Comments

SBT module not found, why ?

I have an build.sbt file that looks like this

But for some reason I can’t get slf4j downloaded from the Maven repository (http://mvnrepository.com)

If I search the Maven Repository, I can clearly see that the version I intend to use is there.

Running “sbt compile” from the command line will result in the following output, and here it is time to pay attention to the details, look at what it is trying to do !!!

As you can see above the package (jar) it tries to download is not slf4j-api it is slf4j-api_2.11;1.7.10

The build.sbt file uses the double and single % (percent) character and this is what makes the difference.  The %% makes SBT append the project specified scala version to the package name, resulting in a name “slf4j-api_2.11”.

BUT that name does not exist in the Maven Repository, however, the “slf4j-api” does

So by simply choosing one instead of two %, the problem will go away 🙂

Thus, the built.sbt should look like this instead

For reference go to https://www.playframework.com/documentation/2.1.1/SBTDependencies to read more about how SBT handles decencies and the % and %%.

I read this article to finally get this right http://stackoverflow.com/questions/17461453/build-scala-and-symbols-meaning

Posted in Uncategorized | Leave a comment

See who is connected to the same network / WIFI

There are tools out there, such as Angry IP Scanner, iNet or IP Scanner,  but this can easily be done using a bash script, not as nice looking perhaps but it does the job.

The script :

Which should give you an output that looks like this

That’s it


Posted in Uncategorized | 1 Comment

Övervakning av min IVT-490 värmepump med Raspberry Pi

I denna artikeln ska jag försöka beskriva mitt lilla projekt med att övervaka min IVT-490 värmepump med en Raspberry P.

Jag har hämtat mycket kunskap från dessa två forum-trådar :

Min värmepump har ett interface som ser ut såhär :
IMG_20120218_180001 (Custom)

Pinout_framsida (Custom)

Pinout_baksida (Custom)

Man behöver slå på variabeldumpning på pannan för att få ut information. Man kommer in i Admin mode genom att hålla in högra knappen i 5-10 sekunder. Sedan letar man upp “Installation och igångkörning” och där finner man “Variabeldumpning till PC”. Med det påslaget så får man ut data 1 gång per minut. (source; Niclas Hedlund)

Citerar Niclas.Hedlund om interfacet från Värmepumpsforum:

Baudraten är 9600baud och 8 databitar, 1stopbit och ingen paritet.
Tänk på att TxD som är markerat på bilden är data UT från pumpen, och RxD data IN till pumpen.

Då jag inte lyckats få tag på en kontakt så fick jag använda mig av en plastpåseklämma (som man köper på MAXI) som jag vänt på och satt två skruvar i, och så har jag silvertejp också…. ja ja det är ju på egen risk 🙂



EDIT: Nu funkade inte min plastklämma så bra så jag lödde dit trådarna istället, nu funkar det bättre. MEN så hörde Fredrik Winkvist av sig och berättade vilken kontakt man skulle ha, och den ska funka perfekt. För er som söker så heter den “2.54mm Pitch 2×5 Pin Double Row Angle IDC Pin Headers 10 Pins”. Ett stort Tack till Fredrik, här är en länk till eBay som säkert blir outdated men så länge den finns kvar kan länken vara bra: http://www.ebay.com/itm/20-Pcs-2-54mm-Pitch-2×5-Pin-Double-Row-Angle-IDC-Pin-Headers-10-Pins-/310784076520?pt=LH_DefaultDomain_0&hash=item485c2caae8


Product Name IDC Pin Header
Position 5
Pin Number 10
Row 2
Mounting Angle 90 Degree
Pin Pitch 2.54mm/0.1″
Pin Length 4mm/ 0.16″     12mm/ 0.47″
Total Size 19 x 9.5 x 22mm/ 0.75″ x 0.37″ x 0.87″ ( L*W*H)
Material Plastic, Metal
Color Black, Silver Tone
Net Weight 66g
Package Content 20 x Angle IDC Pin Headers


Features double row, 90 degree mounting angle, male pin header, electronic component, IDC connector.

This pin header is an ideal connector for the PCB board and intergrated circuit, used widely in the computer, breadboard, LCD, TV and other home appliances.

När detta var gjort var jag tvungen att koppla in det via typ USB, så jag köpte en sådan

serial-usbhär på ebay. http://www.ebay.co.uk/itm/6PIN-CP2102-USB-2-0-to-UART-TTL-Electronic-Connector-Serial-Converter-Module-/390568169609?pt=UK_Computing_Parallel_Serial_PS_2&hash=item5aefad5089
Varje minut så skickar den ut 37-parametrar som är ;-separerade, se EXCEL filen; Loggning-2. (loggfilen kommer också från Värmepumpsforumet; inskickat av Larsmaja)

En rad kan se ut såhär :

Där alla temperaturer är heltal med 1 decimals noggrannhet, så 315 betyder 31,5 grader Celsius.
360 är sekvensummer
315 är Framledningstemperatur (GT1), dvs 31,5 grader
33 är Utetemperatur (GT2), dvs 3,1 grader
478 är Tapvarmvatten (GT3-1), dvs 47,8 grader
445 är Varmvatten(GT3-2), dvs 44,5 grader
447 är Värmevatten(GT3-3), dvs 44,7 grader
-512 är Rumstemperatur(GT5), men jag har inte den givaren så den visar -512
774 är Hetgastemperatur(GT6), dvs 77,4 grader

I bilden nedan ser man de olika givarna GT1-7.ivt-490-givare-diagram

På min Raspberry pi skrev jag ett enkelt script som läser från USB-serieporten och postar på en websida som jag har gjort, där jag helt enkelt lägger in det i en MySQL databas )oerhört enkelt, bara timestamp och så det ;-separerade datan (två kolumner, pga av att jag ville komma igång, skulle såklart haft 38 kolumner så man kunde göra lite mer SQL, men det får bli en annan gång…)

Jag har noterat att ibland så ramlar mina trådar ur, och så får jag inget resultat, då blir serieporten inte nåbar 🙂 och då får jag lite roliga felutskrifter… och såklart blir diagrammen dåligt uppdaterade….

Jag har gjort en websida (PHP and Javascript) där jag använt Highcharts för att rendera diagrammen. Ta en titt här http://tsoft.se/varmepanna/

Skärmavbild 2015-03-08 kl. 12.53.42

Fredrik Winkvist var dessutom lite uppfinningsrik och berättade att man använda strömförsörjningen från värmepumpens 5Volt ut till Raspberry Pi;n. Ta en kik på bilden nedan.


Förbättringsförslag är alltid välkomna, så hör av er !

Mitt nästa projekt, när jag får tid…. är att mäta strömförbrukningen hemma…


Posted in Raspberry Pi | 33 Comments

Calculate the minimum waste when cutting planks…

I made a web application which will calculate the most optimum layout of your pieces you would like to cut from a plank. This helps in figuring out how many planks of a certain size you would have to buy, and also gives you which pieces should be cut out on each plank.

Not sure how many times I have been sitting there, trying to figure out how many to buy, and now I simply thought that a small web-app would be nice. In fact I tried to google this, but it turns out that most people say stuff like, well add 10-15% to the total length, but that just does not work for me, that is way to inaccurate.

This page is currently in Swedish only, but until it has been translated, I bet you will understand anyway, the user interface is pretty easy to use (should not require any translation I think)…

anyway, here is the link; spill.tsoft.se



Posted in optimization, tool, web | Leave a comment