cassandra data model, CQL/Thrift

John Sanda jsanda at redhat.com
Fri Apr 19 17:15:49 UTC 2013


For the new Cassandra-based metrics backend, we are defining schema with CQL. CQL provides abstractions over the underlying physical storage model and data types used. This makes working with Cassandra a lot easier and also lowers the initial learning curve; however, there is a danger. If someone starts learning Cassandra and only learns CQL it could very easy to start thinking in terms of a relational model and designing schema, queries, etc. accordingly. Cassandra is a key/value store. For those getting started in the feature/cassandra-backend branch and anyone getting started with Cassandra, I encourage you to explore things from both the command line tools cqlsh and cassandra-cli. The former is all CQL and the latter uses Thrift APIs. I will provide examples below that help illustrate key things. I will indicate before each command whether it is CQL or CLI. The examples below assume you have installed Cassandra via the storage installer script (or through the rhqctl script) in the feature/cassandra-backend branch. If you don't want to build the branch in order to use those scripts, I can provide you with the necessary steps for configuring a stock Cassandra install.

# (CQL) First log into cqlsh and create the keyspace and then switch over to using it.
$ cqlsh -u cassandra -p cassandra
> CREATE KEYSPACE test WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};
> use test;

# (CQL) now let's create a table/column family for storing metrics. The table 
# schema will allow for "wide rows" meaning it can have variable number of columns.
> CREATE TABLE metrics (
     schedule_id int,
     time timestamp,
     type int,
     value double,
     PRIMARY KEY (schedule_id, time, type)
   ) WITH COMPACT STORAGE;

The above table definition looks very similar to SQL, but what it does is very different. A table or column family in Cassandra consists of rows and columns. Each row has a unique key. A column consists of a name and a value (along with some meta data such as a timestamp that Cassandra uses for conflict resolution). The first field in a primary key defines the row key. The additional columns in the primary key define a composite column name that is essentially used for grouping. A primary key with multiple fields is how you create "wide rows" with CQL, or put another way, rows with variable numbers of columns. The WITH COMPACT STORAGE clause  will result in the table looking just as it would as if we defined it via Thrift from the CLI.

Now we will insert some data so that we can see how things look.
# (CQL) insert data from cqlsh
> insert into metrics (schedule_id, time, type, value) values (1, '2013-04-19', 0, 1.1);
> insert into metrics (schedule_id, time, type, value) values (1, '2013-04-19', 1, 1.2);
> insert into metrics (schedule_id, time, type, value) values (1, '2013-04-19', 2, 1.3);
> insert into metrics (schedule_id, time, type, value) values (2, '2013-04-19', 0, 2.1);
> select * from metrics;

 schedule_id | time                     | type | value
-------------+--------------------------+------+-------
           1 | 2013-04-19 00:00:00-0400 |    0 |   1.1
           1 | 2013-04-19 00:00:00-0400 |    1 |   1.2
           1 | 2013-04-19 00:00:00-0400 |    2 |   1.3
           2 | 2013-04-19 00:00:00-0400 |    0 |   2.1

The above output looks just like it would with SQL. It returns 4 rows; however, there are actually only two rows in the metrics table. Now let's explore things from cassandra-cli to get a more detailed picture of what is happening.

# (CLI) log into cassandra-cli and switch over to use the test keyspace.
$ cassandra-cli -u cassandra -pw cassandra
> use test;

# (CLI) The list command is analogous to select *
> list metrics;

Using default limit of 100
Using default column limit of 100
-------------------
RowKey: 1
=> (column=2013-04-19 00\:00\:00-0400:0, value=1.1, timestamp=1366388467678000)
=> (column=2013-04-19 00\:00\:00-0400:1, value=1.2, timestamp=1366388474316000)
=> (column=2013-04-19 00\:00\:00-0400:2, value=1.3, timestamp=1366388486612000)
-------------------
RowKey: 2
=> (column=2013-04-19 00\:00\:00-0400:0, value=2.1, timestamp=1366388765600000)


Here we can clearly see that there are two and not four rows. The column name is the part following "Column=" and the value is the part following "value=". The column names for each column consist of a date and an integer (which identifies the type of metric). The timestamp at the end is meta data. The CLI output here reflects that actual physical storage model. Now we will insert some data from the CLI.

# (CLI) insert a couple columns
> set metrics[1]['2013-04-19:1'] = double('2.14');
> set metrics[2]['2013-04-19:1'] = double('3.14');
> list metrics;

Using default limit of 100
Using default column limit of 100
-------------------
RowKey: 1
=> (column=2013-04-19 00\:00\:00-0400:0, value=1.1, timestamp=1366388467678000)
=> (column=2013-04-19 00\:00\:00-0400:1, value=1.2, timestamp=1366388474316000)
=> (column=2013-04-19 00\:00\:00-0400:2, value=1.3, timestamp=1366388486612000)
-------------------
RowKey: 2
=> (column=2013-04-19 00\:00\:00-0400:0, value=2.1, timestamp=1366388765600000)
=> (column=2013-04-19 00\:00\:00-0400:1, value=2.14, timestamp=1366390824274000)
=> (column=2013-04-19 00\:00\:00-0400:2, value=3.14, timestamp=1366390817649000)


Let's go back to cqlsh and run a query that filters on the schedule id.
# (CQL)
> select * from metrics where schedule_id = 1;

schedule_id | time                     | type | value
-------------+--------------------------+------+-------
           1 | 2013-04-19 00:00:00-0400 |    0 |   1.1
           1 | 2013-04-19 00:00:00-0400 |    1 |   1.2
           1 | 2013-04-19 00:00:00-0400 |    2 |   1.3


The query is filtering on the row key which means we only querying against a single row. Queries typically should be designed to read a single row (or a subset of the row).

- John


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.fedorahosted.org/pipermail/rhq-devel/attachments/20130419/a2ca291e/attachment.html>


More information about the rhq-devel mailing list