Machine Learning & Big Data Blog

Cassandra SQL Batches and Maps

Mongosh commands.
2 minute read
Walker Rowe

There are too many SQL commands and data types to look at all of them in one blog post, so we will look at a few here.

In order the understand the examples below you should have some understanding of regular, i.e., Oracle or other RDBMS, SQL, as Cassandra SQL is almost the same. And to get started with Cassandra, you can read what wrote here. Look at that even if you already know and have installed Cassandra so that you can the SQL from there to set up a table and index that we will start with here.

(This article is part of our Cassandra Guide. Use the right-hand menu to navigate.)

Cassandra SQL Shell

First step, to open the Cassandra SQL command line enter: cqlsh.

Paste in the SQL below. If you looked at that other blog post you will have some data here.

select * from Library.book;

Here is another query.

(Note that Cassandra requires the use of single and not double quotes, or you will get the error no viable alternative at input.)

select ssn from Library.patron where checkedOut contains '1234';

Collections, Maps, and Sets

Cassandra supports complex data types including user-defined objects, collections, maps, and sets.

For example. if we type:

describe library.patron;

We can see that commands that were used to create the table. Below is a truncated view of that as the complete view shows lots of system administration type options, which we will look at in another post.

Below you can see that the column checkedout is a set of type text. A set is a collection of unique values.

CREATE TABLE library.patron (
ssn int PRIMARY KEY,
checkedout set<text>
)

Maps

Maps store values in {key->value} format.

First create this keyspace.

CREATE KEYSPACE shopping
WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 3 };

Then create a shopping cart for an ecommerce app where the items purchased are a map. The first field is the customer order number. The second is the combination of what they bought and how much, e.g, {“shoes”, 1}.

CREATE TABLE shopping.cart (
customerOrder int PRIMARY KEY,
items map<text,int>
);

Add some data, i.e., make a sale:

INSERT INTO shopping.cart (customerOrder, items) 
VALUES (
  1,
 {'toys' : 12, 'socks': 5});

And list it:

select * from shopping.cart;
customerorder | items
---------------+--------------------------
1 | {'socks': 5, 'toys': 12}

Batches

With an ecommerce application you would want to use batches.

Batches ensure atomicity, which RDBMS programmers would say ensure referential integrity. This ensures that if any of the SQL statements in a batch have an error then none of the statements are applied.

That’s import for, for example, a shopping cart app. We would only want to update the inventory if the sales transaction worked.

To illustrate that first create an inventory table and put an item into it:

CREATE TABLE shopping.inventory (
      item text PRIMARY KEY,
      quantity int
);
INSERT INTO shopping.inventory (item, quantity) VALUES ('eggs', 48);

Now make a sale of 4 eggs in a BATCH:

BEGIN BATCH
INSERT INTO shopping.cart (customerOrder, items) 
VALUES (
2,
{'eggs' : 4}
);
UPDATE shopping.inventory
SET quantity = 44
where item = 'eggs';
APPLY BATCH;

Learn ML with our free downloadable guide

This e-book teaches machine learning in the simplest way possible. This book is for managers, programmers, directors – and anyone else who wants to learn machine learning. We start with very basic stats and algebra and build upon that.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing blogs@bmc.com.

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
Learn more about BMC ›

About the author

Walker Rowe

Walker Rowe is an American freelancer tech writer and programmer living in Cyprus. He writes tutorials on analytics and big data and specializes in documenting SDKs and APIs. He is the founder of the Hypatia Academy Cyprus, an online school to teach secondary school children programming. You can find Walker here and here.