TL;DR Summary: Cassandra SQL Batches and Maps explains SQL commands and data types in Cassandra, emphasizing its similarities to traditional SQL. It covers the Cassandra SQL shell, complex data types such as collections and maps, and the use of batches for atomic transactions.
- Introduces Cassandra SQL shell commands and examples.
- Explains complex data types: collections, maps, and sets.
- Details the use of batches for atomic operations in applications.
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.
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:
Maps
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
)
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
);
Add some data, i.e., make a sale:
And list it:
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;
Cassandra Guide
- What Is Cassandra? Key Features and Advantages
- How to Setup a Cassandra Cluster
- Cassandra SQL Batches and Maps
- Stress Testing and Performance Tuning Apache Cassandra
- Using JSON with Cassandra
- Configuring Apache Cassandra Data Consistency
- Using Tokens to Distribute Cassandra Data
- Partition Key vs Composite Key vs Clustering Columns in Cassandra
These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.