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