In Cassandra Lunch #99, we discuss the CQL Arithmetic Operators that are now supported in Cassandra 4.0! The live recording of Cassandra Lunch, which includes a more in-depth discussion and a demo, is embedded below in case you were not able to attend live. Subscribe to our YouTube Channel to keep up to date and watch Cassandra Lunches live at 12 PM EST on Thursdays!
In Cassandra Lunch #99, we discuss the CQL Arithmetic Operators that are now supported in Cassandra 4.0 with a live recording of the demo embedded below! Additionally, you can follow along using the GitHub walkthrough; as well as, in the walkthrough section below!
In Cassandra 4.0, Cassandra supports five arithmetic operators on numbers:
- +
- –
- *
- /
- %
Operators can be used in the SELECT INSERT, UPDATE, and DELETE statements to perform some computations. These operators can also be used on DataStax Astra.
An important thing to take into account when using operators is the return type of the operation:
Cassandra will try to infer the type of the literals to determine how the operation must be performed. If the literal is an integer, Cassandra will look at it and consider it as an int, a bigint, or a varint if the type can hold this value. For floats, C* will consider them as either doubles or decimals. In some cases, Cassandra knows the return type of the expression and can use it to infer the type of the literals.
In order to control Cassandra and avoid precision issues, you can use type hints or casting. A type hint is a way to explicitly tell Cassandra what will be the type of the parameter. Type hints can also be used to control the type of a literal. Otherwise, you can cast into a type of literal, which we have done in the walkthrough portion below.
In terms of operator precedence, Cassandra follows the almighty PEMDAS. The *, /, and % operators have a higher precedence level than + and – operator and if two operators in an expression have the same precedence level, they will be evaluated left to right based on their positions in the expression. When it comes to parentheses, they can be used to modify the order in which the operations must be performed within an expression and everything within parentheses will be evaluated first to yield a single value before that value can be used by any operator outside the parentheses.
Additionally, addition or subtraction of durations from timestamps and dates is supported in Cassandra 4.0. Something that Cassandra notes is that the Java libraries used internally by Cassandra, ignore leap seconds, so keep that in mind as well when doing arithmetic operations on timestamps and dates.
As mentioned above, we have a walkthrough that you can follow along with to learn how to do arithmetic operations in Cassandra! This walkthrough is entirely browser based via Gitpod; however, if you prefer, you can run this on docker as well since we are running docker in Gitpod. Additionally, you can follow along below, or follow along on the GitHub README.md itself. The live recording of the demo is also embedded below!
1. Cassandra
1.1 – Run Cassandra
We can run Docker on Gitpod, but as mentioned above, you can run this demo using your local Docker as well.
1.1.1 – Start Cassandra.
docker run --name cassandra -p 9042:9042 -d cassandra:latest
If running on Gitpod, remember to make the 9042 port public when the dialog shows up on the bottom right-hand corner
1.1.2 – Copy CQL File to Container
docker cp /workspace/example-CQL-arithmetic-operators/stats.cql cassandra:/
1.1.3 – Run CQL File
docker exec -it cassandra cqlsh -f /stats.cql
1.2 – Start CQLSH.
docker exec -it cassandra cqlsh
2. Addition Operator
At the time of creating this repo, the 2022 MLB season has just started up. Nowadays, data is heavily ingrained into professional sports, but baseball especially with Sabermetrics (list of just offensive statistics)
2.1 – Calculate OPS
select season, slg, obp, slg + obp as "ops" from demo.jeter_world_series_stats ;
3. Subtraction Operator
3.1 – Calculate At Bats
select season, ab, tpa - bb - ibb - sac - hbp as "calc_ab" from demo.jeter_world_series_stats ;
4. Division Operator
4.1 – Calculate OBP
select season, obp, (h + bb + ibb + hbp) / (ab + bb + ibb + hbp + sac) as "calc_obp" from demo.jeter_world_series_stats where season = 2000;
Hmm, something doesn’t look right… Because h, bb, ibb, hbp, ab, and sac are int types according to the schema, they are doing integer arithemetic. Because we want to generate a decimal value, we need to CAST them to be something else like FLOAT for example.
4.2 – Calculate OBP with CAST
select obp, (CAST(h as FLOAT) + CAST(bb as FLOAT) + CAST(ibb as FLOAT) + CAST(hbp as FLOAT)) / (CAST(ab as FLOAT) + CAST(bb as FLOAT) + CAST(ibb as FLOAT) + CAST(hbp as FLOAT) + CAST(sac as FLOAT)) as "calc_obp" from demo.jeter_world_series_stats where season = 2000;
5. Multiplication Operator
5.1 – Calculate ERA
select season, era, (er * 9) / ip as "calc_era" from demo.mariano_world_series_stats where season > 1997 and season <2001 ALLOW FILTERING;
6. Arithmetic Operator and Aggregrate Function
6.1 – Calculate Multiple Ratios using SUM and CAST
select SUM(ab) as "total_at_bats", SUM(r) as "total_runs", SUM(rbi) as "total_rbis", SUM(so) as "total_strike_outs", SUM(cast(rbi as FLOAT))/SUM(cast(ab as FLOAT)) as "ab_rbi_ratio", SUM(cast(so as FLOAT))/SUM(cast(ab as FLOAT)) as "ab_so_ratio" from demo.jeter_world_series_stats;
Cassandra.Link
Cassandra.Link is a knowledge base that we created for all things Apache Cassandra. Our goal with Cassandra.Link was to not only fill the gap of Planet Cassandra but to bring the Cassandra community together. Feel free to reach out if you wish to collaborate with us on this project in any capacity.
We are a technology company that specializes in building business platforms. If you have any questions about the tools discussed in this post or about any of our services, feel free to send us an email!
