A primary key or row key is the unique identifier of a row, in much the same way as the primary key of a table from a relational database system. It provides quick and random access to the rows. Since the rows are sharded (distributed) among the nodes, and each node just has a subset of rows, the primary keys are distributed too. Cassandra uses the partitioner (cluster-level setting) and replica placement strategy (keyspace-level setting) to locate the nodes that own a particular row. On a node, an index file and sample index is maintained locally and can be looked up via binary search followed by a short sequential read. (For more information, refer to Chapter 2, Cassandra Architecture.)

The problem with primary keys is that their location is governed by partitioners. Partitioners use a hash function to convert a row key into a unique number (called token) and then read/write happens from the node that owns this token. This means that if you use a partitioner that does not use a hash that follows the key's natural ordering, chances are that you can't sequentially read the keys just by accessing the next token on the node.

The following code snippet shows an example of this. The row keys 1234 and 1235 should naturally fall next to each other if they are not altered (or if an order preserving partitioner is used). However, if we take a consistent MD5 hash of these values, we can see that the two values are far away from each other. There's a good chance that they might not even live on the same machine. Here is an example of how two seemingly consecutive row keys have MD5 hash that is far apart:

ROW KEY | MD5 HASH VALUE
--------+----------------------------------
1234    | 81dc9bdb52d04dc20036dbd8313ed055
1235    | 9996535e07258a7bbfd8b132435c5962

Let's take an example of two partitioners: ByteOrderPartitioner that preserves lexical ordering by bytes, and RandomPartitioner that uses an MD5 hash to generate a row key. Let's assume that we have a users_visits table with a row key, <city>_<userId>. ByteOrderPartioner will let you iterate through rows to get more users from the same city in much the same way as a SortedMap interface does (for more detail, visit http://docs.oracle.com/javase/6/docs/api/java/util/SortedMap.html). However, in RandomPartioner, the key being the MD5 hash value of <city>_<userId>, the two consecutive userIds from the same city may be such that there are records for a different city in between. So, we cannot just iterate and expect grouping to work, like accessing entries of HashMap. (Ideally, you would not want to use a row key <city>_<userId> for grouping. You would create a compound key with <city> and <userId>. The purpose of the preceding example was just to show that consecutive row keys may have records between them.)

We will see partitioners in further detail in Chapter 4, Deploying a Cluster. But using the obviously better looking partitioner ByteOrderPartitioner is assumed to be a bad practice. There are a couple of reasons for this; the major reason being an uneven row key distribution across nodes. This can potentially cause a hotspot in the ring.

Keyspace is the logical container of tables just like a databaseor a schema in a RDBMS. Keyspace also holds some of the global settings applied to all the tables in it.

Cassandra needs two things from you to specify when creating a keyspace; one, the name of the keyspace, and the other is the replication strategy. Optionally, you can specify IF EXISTS clause if you are writing a script that adds tables to an existing keyspace and do not want to error out on the first line that creates the keyspace. You may want to specify whether you wanted a durable write. Note that switching off the durable write may be a bad idea. And generally, you would not want to disable durable write. While I agree that there is some performance gain by disabling durable write as it bypasses the commit log, it does so at the cost of possible data loss. You may get some performance gain just by moving commit log to a separate disk by changing the setting in cassandra.yaml. Here is how you create a keyspace:

CREATE { KEYSPACE | SCHEMA } [IF NOT EXISTS]

The REPLICATION setting takes a map. If you are using cqlsh, you need to type a JSON object to specify it. This setting is to specify how you want your data to be replicated across the nodes. There are two options to do this: SimpleStrategy and NetworkTopologyStrategy.

{ 'class' : 'SimpleStrategy', 'replication_factor' : <positive_integer> }

{ 'class' : 'NetworkTopologyStrategy'[, '<datacenter_name>' : <positive_integer>, '<datacenter_name>' : <positive_integer>, ...] }

# Single data center, replication factor: 3
{ 'class' : 'NetworkTopologyStrategy', 'DC1' : 3}

# Three data centers, with RF as 3, 2, and 1
{ 'class' : 'NetworkTopologyStrategy', 'DC_NY' : 3, 'TokyoDC' : 2, 'DC_Hadoop': 1}

# Two data centers with three replica each
{ 'class' : 'NetworkTopologyStrategy', 'DC1' : 3, 'DC2' : 3}

# Keyspace with SimpleStrategy
CREATE KEYSPACE IF NOT EXISTS demo_keyspace
WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 3 }

# Keyspace with NetworkTopologyStrategy
CREATE KEYSPACE IF NOT EXISTS demo_keyspace1
WITH REPLICATION = {'class': 'NetworkTopologyStrategy', 'DC1': 3, 'DC2': 1}

# Keyspace with no durable writes
CREATE KEYSPACE IF NOT EXISTS demo_keyspace2
WITH REPLICATION = {'class': 'NetworkTopologyStrategy', 'DC1': 3, 'DC2': 1}
AND DURABLE_WRITES = 'false'

You can alter keyspace properties like REPLICATION and DURABLE_WRITES, but the names cannot be altered. Here is the CQL syntax for that:

ALTER {KEYSPACE | SCHEMA} keyspace_name
WITH {
  REPLICATION = json_object |
  DURABLE_WRITES =  {true | false} |
  REPLICATION = json_object AND DURABLE_WRITES = {true | false}
}

Table creation in CQL3 is similar to how it's done in SQL. At the very least, you need to specify the table name, the name and types of the columns, and the primary key. The primary key can be single valued (made of a single column) or composite (made of more than one column). For more detail on keys, refer to the A brief introduction to the data model section in Chapter 1, Quick Start. In the former case, the primary key works as a partition key (or shard key). So, rows of such a table are distributed across the nodes based on the hash value of the primary key. In the latter case, where primary key constitutes of more than one column, the first column works as partition key. That means rows with same partition key stay in one wide row. (Refer to the The column family section.) This can be handy when you want to group things naturally. The following code snippet can be used to create a table:

CREATE TABLE [ IF NOT EXISTS ] [keyspace_name.]table_name
( column_definition, column_definition, ...)
[WITH property [ AND property ... ]]

Column definition, in most of the common cases, will be as follows:

column_name column_type [STATIC | PRIMARY KEY]

Let's take a look at the following example:

CREATE TABLE mytab (id int PRIMARY KEY, name varchar, article text, is_paying_user boolean, phone_numbers set <varchar>);

You may define PRIMARY KEY separately, like we do in the case of the compound key:

CREATE TABLE team_match (city varchar, team_name varchar, is_participating boolean, player_id set <int>, PRIMARY KEY(city, team_name));

With Cassandra 2.1, you could create tables that have custom types, and on the same working principle it lets you create tuples. There is a special way to declare a tuple or custom type. You use the frozen keyword for them. The pattern is as follows:

column_name frozen<tuple<type [, type, ...]>> [PRIMARY KEY]
column_name frozen<user_defined_type> [PRIMARY KEY]

So, what does frozen signify? Cassandra serializes the tuple or user defined type into one cell. It may be noted that although the collections live in separate cells, if you have nested collections, the inner collections are serialized into one cell. This means when you update a user defined type or tuple, it pulls the whole thing, updates it, and inserts it again. But Cassandra might allow individual updates in version 3.0 and above. So, the frozen keyword is just a safety feature to avoid conflicts when you migrate to the third version. For now, you cannot create a column of user defined type, or tuple type without adding the frozen keyword. For more details on frozen, look at the Jira ticket (https://issues.apache.org/jira/browse/CASSANDRA-7857). Let's see the frozen keyword in action:

cqlsh:demo_cql> CREATE TYPE address (line1 varchar, line2 varchar, city varchar, state varchar, postalcode int);

cqlsh:demo_cql> CREATE TABLE customer (id int PRIMARY KEY, name varchar, addr address);

code=2200 [Invalid query] message="Non-frozen User-Defined types are not supported, please use frozen<>"

cqlsh:demo_cql> CREATE TABLE customer (id int PRIMARY KEY, name varchar, addr frozen<address>);

cqlsh:demo_cql> CREATE TABLE purchase_ability(id int PRIMARY KEY, customer_id int, purchase_rating tuple<float, float, int, boolean>);

code=2200 [Invalid query] message="Non-frozen tuples are not supported, please use frozen<>"

cqlsh:demo_cql> CREATE TABLE purchase_ability(id int PRIMARY KEY, customer_id int, purchase_rating frozen<tuple<float, float, int, boolean>>);

You can specify a non-clustering column as static and this column stays static across all the rows with the same partition key (row key). This only applies to the columns not involved in compound key. The static column does not make sense in a setting where you do not have a compound key because in those cases, you have row key as the primary key and hence each row lives in a separate partition, so each column is essentially a static column. Here's an example:

# We wanted to have one university per ID
CREATE TABLE static_col (id int, city varchar, university varchar static, name varchar, PRIMARY KEY(id, city));

# Insert some data
INSERT INTO static_col (id, city, university, name ) VALUES ( 1, 'Seattle', 'Seattle University', 'Barry Kriple'),
INSERT INTO static_col (id, city, university, name ) VALUES ( 2, 'Washington', 'Washington University', 'Rose Byrne'),

INSERT INTO static_col (id, city, university, name ) VALUES ( 1, 'Washington', 'Seattle University', 'Nick Jonas'),

# So far so good
SELECT * FROM static_col;


 id | city       | university            | name

----+------------+-----------------------+--------------
  1 |    Seattle |    Seattle University | Barry Kriple
  1 | Washington |    Seattle University |   Nick Jonas
  2 | Washington | Washington University |   Rose Byrne

(3 rows)

# Just insert another record to id=1 with a different university name
INSERT INTO static_col (id, city, university, name ) VALUES ( 1, 'New York', 'Coursera', 'John Hacker Doe'),

# All the records in that row key are updated
cqlsh:demo_cql> SELECT * FROM static_col;



 id | city       | university            | name

----+------------+-----------------------+-----------------
  1 |   New York |              Coursera | John Hacker Doe
  1 |    Seattle |              Coursera |    Barry Kriple
  1 | Washington |              Coursera |      Nick Jonas
  2 | Washington | Washington University |      Rose Byrne

(4 rows)

The static column can be useful when you want to store a common global record of a column across all the records that share the same row key. It can be thought of as a static variable in Java, which is shared among all the instances of the class. An application of such a case could be a game where you are given initial credit coins, and you can use these coins to purchase things in the game. When you buy something, a purchase record is added with balance_coins as the static column. However, one might argue to use counter, which makes sense. Another example could be a static Set<text> to keep a set of ever changing tags of a product of a category available to all the products. For more rationale on static type, you can review the Jira ticket for this feature at https://issues.apache.org/jira/browse/CASSANDRA-6561.

CREATE TABLE tiny_tiny_table (
  tab_id uuid,
  tab_details text,
  PRIMARY KEY (tab_id)
) WITH bloom_filter_fp_chance = 0.001;

caching = { 'keys' : '{NONE|ALL}', 'rows_per_partition' : '{NONE|ALL|positive_int}' };

CREATE TABLE searched_pages (
  page_id uuid,
  page_text text,
  metadata text,
  advertisement_count int,
  PRIMARY KEY (page_id)
) WITH caching = { 'keys' : 'ALL', 'rows_per_partition' : '31415' };

CREATE TABLE searched_pages (
  page_id uuid,
  page_text text,
  metadata text,
  advertisement_count int,
  PRIMARY KEY (page_id)
) WITH caching = 'keys_only';

CREATE TABLE pigeon_hole (
  pigeon_id uuid,
  pigeon_offense text,
  pigeon_age int,
  is_it_a_fun_pigeon boolean,
  PRIMARY KEY(pigeon_id)
) WITH comment = 'Pigeon hole is a table for criminal pigeons. They cannot see or talk to each other but if they are fun, we may want to feed them once in a while';

CREATE TABLE compaction_demo (id uuid PRIMARY KEY, name varchar)
WITH
COMPACTION  = {
'class': 'SizeTieredCompactionStrategy',
'tombstone_compaction_interval':43200,
'tombstone_threshold': 0.1
};

CREATE TABLE speculative_retries_demo (id uuid PRIMARY KEY, name varchar)
WITH
speculative_retry = '99percentile';

Altering a table is similar to creating one. You can add, rename, change type, drop columns, and you can also change various table properties that we saw in the previous section. Let's take a simple table and play with it:

CREATE TABLE modify_me (id uuid PRIMARY KEY, name varchar);

ALTER TABLE table_name ADD new_column_name column_type;

ALTER TABLE modify_me ADD phone_numbers map <varchar, varchar>;

ALTER TABLE modify_me ADD count_sheep counter;

<ErrorMessage code=2300 [Query invalid because of configuration issue] message="Cannot add a counter column (count_sheep) in a non counter column family">

ALTER TABLE table_name RENAME old_name TO new_name;

# A table with a clustering column and index
CREATE TABLE type_changer (id int, name ascii, age ascii, primary key(id, age));

CREATE INDEX tc_idx ON type_changer (name);


# Alter the clustering column
ALTER TABLE type_changer ALTER age TYPE varchar;


# Insert some data
INSERT INTO type_changer (id, age, name) VALUES ( 1, '12', 'Mercedes'),


# Alter everything
ALTER TABLE type_changer ALTER age TYPE text;
ALTER TABLE type_changer ALTER name TYPE varchar;
ALTER TABLE type_changer ALTER id TYPE varint;

ALTER TABLE table_name DROP column_name;

ALTER TABLE table_name WITH property [ AND property …];

ALTER TABLE modify_me
WITH
default_time_to_live = 3600
AND
compaction = {
'class': 'LeveledCompactionStrategy',
'sstable_size_in_mb': 314
}
AND
speculative_retry = '75percentile'
AND
comment = 'New and improved formula';

Dropping a table is the simplest of all the commands. All you have to do is execute a query in the following pattern:

DROP TABLE IF EXISTS table_name;

Unlike relational databases, if you try to perform a search on a column that is not the primary key, it will not work. One may argue the feasibility of going through all the rows sequentially and finding out the records matching the criteria, but Cassandra is meant for large datasets and performing a linear search on hundreds of gigabytes of records is just impractical. Allowing so may cause malpractice. So, consider a simple table like this:

 SELECT * FROM searchable_tab;
 id | age | name         | phones
----+-----+--------------+----------------------------------
  1 |  21 |   Misa Amane | {'111-222-3333', '111-223-4444'
  2 |  26 | Light Yagami |                 {'111-277-0563'}
  3 | 666 |         Ryuk |                 {'000-000-0000'}

(3 rows)

Here, a simple query like this wouldn't work:

SELECT * FROM searchable_tab WHERE name = 'Light Yagami';

code=2200 [Invalid query] message="No indexed columns present in by-columns clause with Equal operator"

So, we create index on that to make this search work:

CREATE INDEX searchable_tab_name_idx ON searchable_tab (name);
SELECT * FROM searchable_tab WHERE name = 'Light Yagami';


 id | age | name         | phones
----+-----+--------------+------------------
  2 |  26 | Light Yagami | {'111-277-0563'}

(1 rows)

Indexes are of great help in Cassandra. So why not just go ahead and create indexes for all the columns? There are a couple of reasons to that. First, indexes are not free. Indexes are maintained in separate columns' families, and extra work is needed to be done every time one mutates a column that has an index on it. The second thing is cardinality, which is true to the database indexes in general. The way Cassandra stores indexes is by creating a reverse index in a separate table; let's call it an index table. Let's also define the table on which the index is created as a parent table. The index table can be assumed as a table with very large rows where each cell can have any arbitrary name. (Traditionally, this is a wide row column family.) The index table contains primary keys of the parent table as the cell names and the values of the column of the parent table as primary keys. Let's see an example of a table with the primary key as user_id and an indexed column, city:

user_id | city
-----------+---------------------------
1            | Cape Town
2            | Kabul
3            | Mogadishu
4            | Cape Town
5            | Baghdad
6            | Kabul

The index table will look like this:

Baghdad            |  5   |
–------------------+-----------------------------------------
Cape Town          |   1  |     4   |
–------------------+-----------------------------------------
Kabul              |   2  |     6   |
–------------------+-----------------------------------------
Mogadishu          |  3   |
–------------------+-----------------------------------------

So, indexes are basically wide-rows or dynamic rows with row keys as values in the column being indexed (remember wide rows from the previous section?). These indexes are stored locally, which means an index table on node X will have an index created only for the rows that exist on node X. This means, every search that includes an indexed column makes a query on the index table on all the nodes, but do not worry about it, it is fast. The other things that you should keep in mind are as follows:

After understanding the introduction , let's now see the index creation syntax:

CREATE [ CUSTOM ] INDEX [ IF NOT EXISTS ] [ index_name]
ON table_name ( { KEYS ( column_name ) | column_name } )
[ USING class_name ] [ WITH OPTIONS = json_object ];

As usual, IF NOT EXISTS is to avoid an error in case it already exists. Most commonly, the version is used as follows:

CREATE INDEX IF NOT EXISTS my_simple_index
ON my_table(my_column);

Note that from Cassandra 2.1 onward, you are able to create an index on collections as well. In case of map, by default, the index is created on map values. To create an index on map keys, you will have to use the KEYS keyword. Here are some examples:

CREATE TABLE index_demo (id int PRIMARY KEY, name text, age int, address map<text, text>, phones set <text>);
CREATE INDEX IF NOT EXISTS index_demo_name_idx ON index_demo (name);
CREATE INDEX index_demo_age_idx ON index_demo (age);
CREATE INDEX IF NOT EXISTS index_demo_phones_idx ON index_demo (phones);
CREATE INDEX IF NOT EXISTS index_demo_address_key_idx ON index_demo (KEYS(address));
CREATE INDEX IF NOT EXISTS index_demo_address_val_idx ON index_demo (address);
code=2200 [Invalid query] message="Cannot create index on address values, an index on address keys already exists and indexing a map on both keys and values at the same time is not currently supported"

Something worth noticing; you can create an index on map keys or map values, but not on both.

Also, the preceding example, with all the columns having indexes, is made up. The decision to create an index is usually a requirement-driven one; so you may decide to create an index later in the application development when you actually need it.

Cassandra allows users to create a custom indexing algorithm. You can create your own indexing mechanism, wrap it into a jar, and drop it into Cassandra's lib folder. You will have to mention the details of this new index when you are creating an index. The options can be used to pass parameters to the class. The CUSTOM, USING, and OPTIONS keywords are for the custom index. Here's an example:

CREATE CUSTOM INDEX index_name
ON
table_name (column_name)
USING 'fully.qualified.index.class.Name'
WITH OPTIONS = {
'param1': 'val1',
'param2': 'val2'
};

Dropping an index is simple:

DROP INDEX [ IF EXISTS ] index_name;

You may have noticed that you can create indexes without providing a name. For those cases, Cassandra assigned an automated name that has the following pattern:

{table_name}_{column_name}_idx;

So, for an unnamed index on the users table on the browsing_history column, you would use the following query:

DROP INDEX IF EXISTS users_browsing_history_idx;

Cassandra gives you freedom to compose your own data type using the basic types that Cassandra provides you with. Much like Struct in C, it gives you relief in cases where you have to denormalize data because Cassandra does not support the join queries. The most common example of this is putting multiple shipping addresses in a map within a users' table. In RDBMS, you would probably just create two tables: users and addresses. You'd make user_id a foreign key in the addresses table. Then, you use a join query to retrieve a User object that has Map<String, Address> that lists all the shipping address of the user. In Cassandra, we have two options to go about it. The first option is to create a custom data type, which is User Defined Type (UDT), for the address object, and save it as a part of row in the users table. This is denormalization using UDT.

The other option is to have a separate addresses table and store the primary keys of these addresses in the users table. When retrieving, the former just needs to fetch the data from the users table to get a user's data along with its addresses. In the latter case, however, you will need to pull the user's records and then use the primary keys of the addresses to execute another IN query on the addresses table to get addresses. It's a lot of work. We would go with the former, the custom data type with the denormalization approach.

Creating a type has the same syntax as creating a table without properties. Here's the query pattern:

CREATE TYPE [ IF NOT EXISTS ] type_name
( field_name field_type [, field_name field_type, ... ] );

Let's take a look at the following example:

# Create a UDT
CREATE TYPE address ( address text, country varchar, postal_code varchar, preference int );

# Use the UDT in a table in a map
CREATE TABLE users ( user_id int PRIMARY KEY, name varchar, age int, addresses map <text, frozen<address>> );

# Insert some records
INSERT INTO users (user_id, name, age, addresses ) VALUES ( 1, 'Willy Wonka', 42, {'office': {address: 'The Choco Factory', country: 'US', postal_code: '123', preference: 1}, 'home': {address: 'NA'}} );

SELECT * FROM users;

 user_id  | age | name        | addresses
----------+-----+-------------+--------------------------        1 |  42 | Willy Wonka | {
                                 'home': {
                                   address: 'NA',
                                   country: null,
                                   postal_code: null,
                                   preference: null
                                   },
                                  'office': {
                                    address: 'The Choco Factory',
                                    country: 'US',
                                    postal_code: '123',
                                    preference: 1
                                    }
                                   }


(1 rows)

A couple of things worth noticing:

You can have collections within a UDT. Also, obviously, creating a UDT of a type that is provided by Cassandra out of the box will throw an error. Ideally, avoid all reserved keywords as a rule of thumb.

You cannot drop an attribute but you can practically do everything that you can do while altering a table. You can change type name, change attributes within the type's type, change the attributes' name, and append a new attribute.

Here are the patterns followed by an example:

ALTER TYPE old_type_name RENAME TO new_type_name;

At the time of writing, the preceding code was failing in Cassandra 2.1.

# Rename a type attribute
ALTER TYPE type_name RENAME old_column_name TO new_column_name;
ALTER TYPE address RENAME postal_code TO zipcode;

# Change a type attribute's type
ALTER TYPE type_name ALTER column_name TYPE new_type;
ALTER TYPE address ALTER country TYPE blob;

# Add a new attribute to the type
ALTER TYPE type_name ADD new_column column_type;
ALTER TYPE address ADD default_address Boolean;

# Final type
DESC TYPE address
CREATE TYPE address (
  address text,
  country blob,
  zipcode text,
  preference int,
  default_address boolean
);

A custom type that is not used by any table can be dropped. The familiar drop syntax is as follows:

DROP TYPE [ IF EXISTS ] user_defined_type_name;

Cassandra provides a way to act upon when a mutation request is made on a table. It is called trigger. Triggers are Java classes that implement org.apache.cassandra.triggers.ITrigger. You are provided with full mutation details, and you may return a collection of mutation objects if you wanted to take some actions. However, you are not limited to returning mutation; you may decide to send a mail as a trigger. Remember to keep your logic fast because it may affect the latency.

To create a trigger class, you must implement the Itrigger interface. Compile it then place the .class file or if packaged, the .jar file in $CASSANDRA_HOME/lib along with any external libraries that the trigger class needed. You need to copy this file to all the nodes and then reboot the Cassandra service. Once Cassandra is up, you may go ahead and use that trigger.

Here's the skeleton of a trigger:

package com.mycompany.cassandra;

import java.nio.ByteBuffer;
import java.util.Collection;
import org.apache.cassandra.db.ColumnFamily;
import org.apache.cassandra.db.Mutation;
import org.apache.cassandra.triggers.ITrigger;

public class DummyTrigger implements ITrigger {

  /**
  * key: is the row key of incoming mutation
  * cf is map-like structure that holds column-name and mutation to applied
  * Collection<Mutation> is the collection of mutations that you want to execute. If there is no new mutation, return null
  **/
  public Collection<Mutation> augment(ByteBuffer key, ColumnFamily cf)

    /*
    * TRIGGER LOGIC GOES HERE
    */
    return null;
  }
}

To use this trigger, you need to follow this syntax:

# Pattern to create a trigger
CREATE TRIGGER trigger_name
ON
table_name
USING 'fully.qualified.trigger.Name';

# Create a trigger
CREATE TRIGGER dummy_trigger_on_users
ON
users
USING 'com.mycompany.cassandra.DummyTrigger';

Triggers are executed before the mutation request is performed. Triggers can be helpful in scenarios where you need to update another table based on mutations on a table, logging specific mutation or creating notification on certain events, or anything that is specific to your application.

There is an excellent example of trigger that generates an inverse index. The code can be found in Cassandra 2.1 source code at https://git-wip-us.apache.org/repos/asf?p=cassandra.git;a=blob_plain;f=examples/triggers/src/org/apache/cassandra/triggers/InvertedIndex.java;hb=HEAD.

A trigger can be dropped using the following pattern:

DROP TRIGGER trigger_name ON table_name;

Cassandra, by default, ships with authentication or authorization settings disabled. To be able to create a user, you need to first enable it. Here is what you need to do:

To be on the safe side, you may also want to change the Cassandra user's password. From now on, use the new superuser to perform tasks.

Here's how one uses cqlsh for the first login after setting up authentication.

$CASSANDRA_HOME/bin/cqlsh -u cassandra -p cassandra localhost 9042

We will see more authentication and authorization in Chapter 4, Deploying a Cluster. Coming back to query definition, the following is the pattern to create a user:

CREATE USER [ IF NOT EXISTS ] user_name
[ WITH PASSWORD 'password' ]
[ NOSUPERUSER | SUPERUSER ];

Let's take a look at the following example:

CREATE USER oracle WITH PASSWORD 'X10n' SUPERUSER;

A couple of things to notice here are as follows:

There are two things you can do with a user: alter its superuser status, and change its password. The syntax goes like this:

ALTER USER user_name
[ WITH PASSWORD 'new_password' ][ NOSUPERUSER | SUPERUSER ];

Let's take a look at the following example:

ALTER USER oracle WITH PASSWORD 'FollowTheWhileRabbitN30';

Dropping a user follows a simple known syntax:

DROP USER [ IF EXISTS ] user_name;

Let's take a look at the following example:

DROP USER IF EXISTS oracle;

The REVOKE permission basically undoes what GRANT did. There are a couple of gotchas that you should be aware of:

The syntax for REVOKE is as follows:

REVOKE { permission_type | REVOKE ALL PERMISSIONS }
ON
{ ALL KEYSPACES | KEYSPACE ks_name | TABLE ks_name.table_name }
FROM
user_name;

The details stay the same as GRANT.

With the platform created, now we are ready to do what we will be doing the most in an application: inserting and retrieving records. Inserting in Cassandra is similar to SQL with a couple of extra options. Here's the pattern:

INSERT INTO table_name ( column_name1, column_name1, ...)
VALUES
( column_value1, column_value2, ... )
[ IF NOT EXISTS ]
[ USING [ TTL ttl_in_sec ] [ [ AND ] TIMESTAMP ts_in_microsec ];

So the format is, after the INSERT INTO keyword, goes the table name, then a comma-separated list of columns, then the VALUES keyword, then a comma-separated list of values of appropriate types in the same order as the list of the columns. In case you want to avoid concurrently writing threads to overwrite a row, you should use IF NOT EXISTS. So, why not do this for all the insert queries? The answer is performance hit. We will come to see why this happens soon in this section. You can set a TTL for the row. TTL is a precondition that states the row will be deleted automatically after the specified time. You can also optionally set TIMESTAMP; this timestamp is used in conflict resolution. It is also worth noting that the INSERT statements are not applicable to counter tables. So, here are some examples:

# Create a simple table to insert data into
CREATE TABLE demo_insert (id int PRIMARY KEY, name text, phone set<text>);

# Insert a simple row
INSERT INTO demo_insert (id, name, phone) VALUES ( 1, 'Harry Potter', {'44-234-0495','44-234-9845'});

# Insert a row with the same primary key
# check for duplicates this time
INSERT INTO demo_insert (id, name, phone) VALUES ( 1, 'Draco malfoy', {'44-234-0495','44-234-9845'}) IF NOT EXISTS;

 [applied] | id | name         | phone

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

     False |  1 | Harry Potter | {'44-234-0495', '44-234-9845'}
# Insert the same row with a different primary key; set its life as 30 seconds
INSERT INTO demo_insert (id, name, phone) VALUES ( 2, 'Draco malfoy', {'44-234-4398'}) IF NOT EXISTS USING TTL 30;

 [applied]
-----------

      True

# Immediately after
SELECT * FROM demo_insert;


 id | name         | phone

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

  1 | Harry Potter | {'44-234-0495', '44-234-9845'}

  2 | Draco malfoy |                {'44-234-4398'}



(2 rows)



# 30 seconds later
SELECT * FROM demo_insert;


 id | name         | phone

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

  1 | Harry Potter | {'44-234-0495', '44-234-9845'}



(1 rows)
# Insert a record with a timestamp far into the future
INSERT INTO demo_insert (id, name) VALUES ( 3, 'Ginevra Weasley') USING TIMESTAMP 2147483647000000;

# Check the insertion timestamp
SELECT id, name, phone, WRITETIME(name) FROM demo_insert;



 id | name            | phone                          | writetime(name)

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

  1 |    Harry Potter | {'44-234-0495', '44-234-9845'} | 1411919467570135

  3 | Ginevra Weasley |                           null | 2147483647000000



(2 rows)

Note that you cannot use IF NOT EXISTS if you manually set TIMESTAMP.

[item1, item2, …]

# Create a table with a list of integer column
CREATE TABLE list_demo (id int PRIMARY KEY, list_col list <int>);

# Insert some values with duplicates in the list
INSERT INTO list_demo (id, list_col ) VALUES ( 1, [1, 2, 1]);
select * from list_demo;


 id | list_col

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

  1 | [1, 2, 1]



(1 rows)



# Updating value in at index 1
UPDATE list_demo set list_col[1] = 3 WHERE id = 1;

select * from list_demo;


 id | list_col

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

  1 | [1, 3, 1]



(1 rows)



# Appending and prepending a column
UPDATE list_demo SET list_col = list_col + [4,5] WHERE id = 1;
UPDATE list_demo SET list_col = [0] + list_col WHERE id = 1;
SELECT * FROM list_demo;


 id | list_col

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

  1 | [0, 1, 3, 1, 4, 5]



(1 rows)



# Delete an item by the index
delete list_col[2] from list_demo WHERE id = 1;

SELECT * FROM list_demo;



 id | list_col

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

  1 | [0, 1, 1, 4, 5]



(1 rows)



# Deleting an item in the list by value deletes all copies of it
UPDATE list_demo SET list_col = list_col - [1] WHERE id = 1;

SELECT * FROM list_demo;



 id | list_col

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

  1 | [0, 4, 5]



(1 rows)

{item1, item2, …}

# Create a simple table and insert a set with duplicates
CREATE TABLE set_demo (id int PRIMARY KEY, set_col set<text>);
INSERT INTO set_demo (id, set_col ) VALUES ( 1, {'head', 'tail', 'head'});
SELECT * FROM set_demo;


 id | set_col

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

  1 | {'head', 'tail'}



(1 rows)



# You can append, but cannot prepend, but order is not preserved. So, it does not matter.
UPDATE set_demo SET set_col = set_col + { 'right' }  WHERE id = 1;
SELECT * FROM set_demo;


 id | set_col
----+---------------------------

  1 | {'head', 'right', 'tail'}



(1 rows)



# Removing an element is similar to adding one
UPDATE set_demo SET set_col = set_col - { 'head' }  WHERE id = 1;
SELECT * FROM set_demo;


 id | set_col

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

  1 | {'right', 'tail'}



(1 rows)

{'key1': value1, 'key2': value2, …}

# Create a table and insert a map with duplicate keys
CREATE TABLE map_demo (id int PRIMARY KEY, map_col map<int, text> );
INSERT INTO map_demo (id, map_col ) VALUES ( 1, {1:'one', 2: 'two', 1: 'dupe'} );
SELECT * FROM map_demo;


 id | map_col

----+----------------------
  1 | {1: 'one', 2: 'two'}



(1 rows)



# Update the value of a key
UPDATE map_demo SET map_col[3] = 'three' WHERE id = 1;
SELECT * FROM map_demo;


 id | map_col

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

  1 | {1: 'one', 2: 'two', 3: 'three'}



(1 rows)



# Delete a key
DELETE map_col[2] FROM map_demo WHERE id = 1;
SELECT * FROM map_demo;


 id | map_col

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

  1 | {1: 'one', 3: 'three'}

(1 rows)

Support for transaction is introduced in Cassandra 2.0. In the previous version of this book, we discussed how cages and other libraries can be used to lock specific mutations and allow other entries only when the previous operations have been executed. It was too much work and the system was brittle. But Cassandra 2.0 and onwards supports lightweight transactions using the Paxos consensus protocol. It is a part of CQL, and much more simple to use. To learn more about Paxos, visit http://www.cs.utexas.edu/users/lorenzo/corsi/cs380d/past/03F/notes/paxos-simple.pdf.

Coming back to insert, we noted earlier that using IF NOT EXISTS has a negative impact on performance. To make sure the writes go linearly, Cassandra uses the Paxos consensus protocol. It basically make builds an agreement across the nodes before starting mutation. The whole process takes four round trips from all the replica nodes, which is concerning. This is the reason it is suggested to use light weight transaction, where it is a must.

One such example can be the case where users are registering with unique usernames. You will not want two users inserting the same name, because being unique it will act as an identifier, which is used to fetch user records or log them in.

Light weight transactions can be used during insert or update. Here's an example:

# Create an ordinary table
CREATE TABLE lwt_demo (id int PRIMARY KEY, name text, passwd text);

# Insert only iof the id column wasn't already taken
INSERT INTO lwt_demo (id, name, passwd) VALUES ( 1, 'Derek', 's3cr3t') IF NOT EXISTS;


 [applied]

-----------

      True



# If it is taken, the query returns the existing value with a hint that this query/mutation was not applied
INSERT INTO lwt_demo (id, name, passwd) VALUES ( 1, 'Maverick', '0p3ns3cr3t') IF NOT EXISTS;
 [applied] | id | name  | passwd

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

     False |  1 | Derek | s3cr3t



# In update you do not need to depend on the primary key. Any column can be validated in the IF block.
UPDATE lwt_demo SET name = 'Maverick', passwd = '0p3ns3cr3t' WHERE id = 1 IF name = 'Derek';


 [applied]

-----------

      True



# In case, the mutation failed, you are notified with the condition that failed
UPDATE lwt_demo SET name = 'Maverick', passwd = '0p3ns3cr3t' WHERE id = 1 IF name = 'Derek';



 [applied] | name

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

     False | Maverick

Update follows a very similar construct to SELECT, and under the hood, UPDATE and INSERT are the same thing. As we have seen in the previous section, the counter column family only gets updated and cannot be inserted. Here is the syntax:

UPDATE [ table_name ]

[ USING [ TTL ttl_in_sec ] [ [ AND ] TIMESTAMP ts_in_microsec ] ]
SET column_name = column_value_or_expression [, column_name = column_value_or_expression, ... ]

WHERE primary_key_name { = pk_value | IN ( pk_value1, pk_value2, ... ) }

IF column_name = col_val [ AND column_name = column_value ... ];

Most of the things in the previous expression follow the same rule as we have seen in the previous sections under Inserting data, Collections in CQL, and Lightweight transactions.

So, you can update to set TTL or TIMESTAMP of an individual or a set of columns. In simple cases like regular columns or collection columns with full replacement, you just go ahead and provide the value. But in cases like adding or removing collections, we use the + or - operator and sometime the DELETE query as we have seen in the Collections in CQL section previously.

Here are a couple of examples:

# Create a table and insert some data
CREATE TABLE update_demo ( id int PRIMARY KEY, name text, movies list <text> );
INSERT INTO update_demo (id, name, movies) VALUES ( 1, 'imdb', ['Beautiful Mind', 'Se7en', '1984'] );

INSERT INTO update_demo (id, name, movies) VALUES ( 10, 'rotten tomato', ['Star Wars', 'Donnie darko'] );

# Update a couple of rows
UPDATE update_demo SET name = 'Movie Website' WHERE id in (1,10);
SELECT * FROM update_demo;


 id | movies                              | name

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

 10 |       ['Star Wars', 'Donnie darko'] | Movie Website

  1 | ['Beautiful Mind', 'Se7en', '1984'] | Movie Website



(2 rows)



# Update a collection
UPDATE update_demo SET movies[2] = 'Amelie' WHERE id = 1;
SELECT * FROM update_demo;


 id | movies                                | name

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

 10 |         ['Star Wars', 'Donnie darko'] | Movie Website

  1 | ['Beautiful Mind', 'Se7en', 'Amelie'] | Movie Website



(2 rows)



# Lightweight transaction not applied as the condition did not match
UPDATE update_demo SET name  = 'Reviews' WHERE id = 1 IF movies[2] = '1984';


 [applied] | movies

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

     False | ['Beautiful Mind', 'Se7en', 'Amelie']

Deleting a row is a little more complicated than all the DROP statements that we have seen so far. All you have to do is specify the column or a member of a collection to delete, followed by table name and optionally, you can specify TIMESTAMP if you wanted it to be a check. And finally, you need to identify the row. Here's the scheme:

DELETE [ column_identifier ]
FROM table_name
[ USING TIMESTAMP ts_in_microsec ]
WHERE row_identifier
[ IF { EXISTS | condition [ AND condition [ AND condition ... ] ] } ];

Here are some examples, continuing from the previous section:

# Delete an element from a collection
DELETE movies[1] FROM update_demo WHERE id = 1;
SELECT * FROM update_demo;


 id | movies                        | name

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

 10 | ['Star Wars', 'Donnie darko'] |          null

  1 |  ['Beautiful Mind', 'Amelie'] | Movie Website



(2 rows)



# Delete using the lightweight transaction
DELETE name FROM update_demo WHERE id = 1 IF movies[1] = 'Amelie';



 [applied]

-----------

      True



SELECT * FROM update_demo;



 id | movies                        | name

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

 10 | ['Star Wars', 'Donnie darko'] | null
  1 |  ['Beautiful Mind', 'Amelie'] | null



(2 rows)



# Delete the whole row when columns are not specified
DELETE FROM update_demo WHERE id = 10;

SELECT * FROM update_demo;



 id | movies                       | name

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

  1 | ['Beautiful Mind', 'Amelie'] | null



(1 rows)

Cassandra provides batch processing which is remotely similar to stored procedures in SQL. You can have multiple mutation (INSERT, UPDATE, or DELETE) statements in a BATCH statement and execute them. Cassandra guarantees that batch operations will be atomic; that means that if any of the queries in the batch are executed, all the queries will be executed. However, it does not provide a transactional guarantee. For example, a client can see the effect of execution of the first query in the batch, while others are yet to be applied. Note that it does not alter lightweight transaction queries in the batch by any way, but those queries still stay transactional.

Here's the syntax for BATCH:

BEGIN [ UNLOGGED | COUNTER ]  BATCH

  [ USING TIMESTAMP timestamp ]

  query1;

  query2;
  ...

APPLY BATCH;

The BATCH statement is written to a system table before being applied to make sure that the batch completes, even in the event of death of the coordinator node. This record gets removed when the batch gets processed. This may cause some performance issues. If you wanted to skip this persistence of the batch, use the UNLOGGED keyword. The UNLOGGED keyword should also be used if the batch contains any query that updates the counter. In Cassandra 2.0.9 or earlier, however, you should use COUNTER instead of UNLOGGED. It is important to note that counters are not idempotent. This means that if the same query is run twice, the results will be different. For example, if a batch contains a statement that increases credit_balance of a user by 1,000 and if for some reason the update block failed and retry was attempted, credit_balance will be updated again. Now if the batch failed after the counter was applied, the user will have credit_balance as 2,000, which is undesired.

By default, all the mutations hold the same system timestamp. If you wanted to supply something different, you could provide it by specifying USING TIMESTAMP <timestamp_in_microseconds>. It should be noted that the batch level timestamp does not overwrite the query level timestamp. So, if a query has a different timestamp than the batch, that query timestamp will be applied on that mutation.

CQL provides some commands to quickly get an overview, obtain information, or perform operations.

USE keyspace_name

TRUNCATE table_name

cqlsh:demo_cql> LIST USERS;



 name      | super

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

    oracle | False

 cassandra |  True

  testuser | False



(3 rows)

LIST [ permission_name PERMISSION | ALL PERMISSIONS ]

[ ON { ALL KEYSPACES | KEYSPACE ks_name | TABLE ks_name.table_name} ]

[ OF user_name ]

[ NORECURSIVE ]

LIST ALL PERMISSIONS


 username | resource                    | permission

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

   oracle |         <keyspace demo_cql> |     SELECT

   oracle | <table demo_cql.grant_test> |     SELECT

 testuser |             <all keyspaces> |     SELECT

 testuser | <table demo_cql.grant_test> |     SELECT

 testuser |           <keyspace system> |     CREATE