These are instructions on how exactly we build Cassandra SE packages.

Getting into build environment

See How_to_access_buildbot_VMs page on the internal wiki. The build VM to use is

Get into the VM and continue to next section.

Set up Thrift

Get the bzr checkout

• Create another SSH connection to terrier, run the script suggested by motd.

• Press (C-a C-c) to create another window

• Copy the base bazaar repository into the VM:

Then, get back to the window with VM, and run in VM:

Compile

This should end with:

Free up some disk space:

Patch the tarball to include Thrift

Verify that mysqld was built with Cassandra SE:

This should point to libthrift-0.8.0.so.

Copy the data out of VM

In the second window (the one that's on terrier, but not in VM), run:

References

_{This page is licensed: CC BY-SA / Gnu FDL}

This page documents status variables related to the Cassandra storage engine. See Server Status Variables for a complete list of status variables that can be viewed with SHOW STATUS.

Cassandra_multiget_keys_scanned

• Description: Number of keys we've made lookups for.

• Scope: Global, Session

• Data Type: numeric

Cassandra_multiget_reads

• Description: Number of read operations.

• Scope: Global, Session

• Data Type: numeric

Cassandra_multiget_rows_read

• Description: Number of rows actually read.

• Scope: Global, Session

• Data Type: numeric

Cassandra_network_exceptions

• Description: Number of network exceptions.

• Scope: Global, Session

• Data Type: numeric

• Introduced:

Cassandra_row_insert_batches

• Description: Number of insert batches performed.

• Scope: Global, Session

• Data Type: numeric

Cassandra_row_inserts

• Description: Number of rows inserted.

• Scope: Global, Session

• Data Type: numeric

Cassandra_timeout_exceptions

• Description: Number of Timeout exceptions we got from Cassandra.

• Scope: Global, Session

• Data Type: numeric

Cassandra_unavailable_exceptions

• Description: Number of Unavailable exceptions we got from Cassandra.

• Scope: Global, Session

• Data Type: numeric

_{This page is licensed: CC BY-SA / Gnu FDL}

This page lists difficulties and peculiarities of Cassandra Storage Engine. I'm not putting them into bug tracker because it is not clear whether these properties should be considered bugs.

No way to get E(#rows in column family)

There seems to be no way to get even a rough estimate of how many different keys are present in a column family. I'm using an arbitrary value of 1000 now, which causes

• EXPLAIN will always show rows=1000 for full table scans. In the future, this may cause poor query plans.

• DELETE FROM table always prints "1000 rows affected", with no regards how many records were actually there in the table.

We could use the new feature to get some data statistics.

_{This page is licensed: CC BY-SA / Gnu FDL}

Joins with data stored in a Cassandra database are only possible on the MariaDB side. That is, if we want to compute a join between two tables, we will:

1. Read the relevant data for the first table.

2. Based on data we got in #1, read the matching records from the second table.

Either of the tables can be an InnoDB table, or a Cassandra table. In case the second table is a Cassandra table, the Cassandra Storage Engine allows to read matching records in an efficient way.

Some general info

All this is targeted at running joins which touch small fraction of the tables. The expected typical use-case looks like this:

• The primary data is stored in MariaDB (ie. in InnoDB)

• There is also some extra data stored in Cassandra (e.g. hit counters)

• The user accesses data in MariaDB (think of a website and a query like:

Cassandra SE allows to grab some Cassandra data, as well. One can write things like this:

which is much easier to do than to use Thrift API.

If the user wants to run huge joins that touch a big fraction of table's data, for example:

"What are top 10 countries that my website had visitors from in the last month"?

or

"Go through last month's orders and give me top 10 selling items"

then Cassandra Storage engine is not a good answer. Queries like this are answered in two ways:

1. Design their schema in Cassandra in such a way that allows to get this data in one small select. No kidding. This is what Cassandra is targeted at, they explicitly recommend that Cassandra schema design starts with the queries.

2. If the query doesn't match Cassandra's schema, they need to run Hive (or Pig), which have some kind of distributed join support. Hive/Pig compile queries to Map/reduce job which are ran across the whole cluster, so they will certainly beat Cassandra Storage Engine which runs on one mysqld node (you can have multiple mysqld nodes of course, but they will not cooperate with one another).

It is possible to run Hive/Pig on Cassandra.

_{This page is licensed: CC BY-SA / Gnu FDL}

This page describes how to build the .

Getting the source code

The code is in bazaar branch at .

Alternatively, you can download a tarball from

This page is a short demo of what using looks like.

First, a keyspace and column family must be created in Cassandra:

Now, let's try to connect an SQL table to it:

We've used a wrong datatype. Let's try again:

Ok. Let's insert some data:

Let's select it back:

Now, let's check if it can be seen in Cassandra:

Or, in cassandra-cli:

_{This page is licensed: CC BY-SA / Gnu FDL}

Julien Duponchelle has made a virtual machine available for testing the Cassandra storage engine. Find out more at .

The virtual machine is based on:

• Ubuntu 12.04

• DataStax Cassandra

• + Cassandra

These are possible future directions for Cassandra Storage Engine. This is mostly brainstorming, nobody has committed to implementing this.

Unlike MySQL/MariaDB, Cassandra is not suitable for transaction processing. (the only limited scenario that they handle well is append-only databases)

Instead, they focus on ability to deliver real-time analytics. This is achieved via the following combination:

1. Insert/update operations are reduced to inserting a newer version, their implementation (SSTree) allows to make lots of updates at a low cost

2. The data model is targeted at denormalized data, cassandra docs and user stories all mention the practice of creating/populating a dedicated column family (=table) for each type of query you're going to run.

In other words, Cassandra encourages creation/use of materialized VIEWs. Having lots of materialized VIEWs makes updates expensive, but their low cost and non-conflicting nature should offset that.

How does one use Cassandra together with an SQL database? I can think of these use cases:

use case 1

1. The "OLTP as in SQL" is kept in the SQL database.

2. Data that's too big for SQL database (such as web views, or clicks) is stored in Cassandra, but can also be accessed from SQL.

As an example, one can think of a web shop, which provides real-time peeks into analytics data, like amazon's "people who looked at this item, also looked at ..." , or "today's best sellers in this category are ...", etc

Generally, CQL (Cassandra Query Language) allows to query Cassandra's data in an SQL-like fashion.

Access through a storage engine will additionally allow:

1. to get all of the data from one point, instead of rwo

2. joins betwen SQL and Cassandra's data might be more efficent due to Batched Key Access (this remains to be seen)

3. ??

use case 2

Suppose, all of the system's data is actually stored in an OLTP SQL database.

Cassandra is only used as an accelerator for analytical queries. Cassandra won't allow arbitrary, ad-hoc dss-type queries, it will require the DBA to create and maintain appropriate column families (however, it is supposed to give a nearly-instant answers to analytics-type questions).

Tasks that currently have no apparent solutions:

1. There is no way to replicate data from MySQL/MariaDB into Cassandra. It would be nice if one could update data in MySQL and that would cause appropriate inserts made into all relevant column families in Cassandra.

2. ...

_{This page is licensed: CC BY-SA / Gnu FDL}

This page documents system variables related to the . See for a complete list of system variables and instructions on setting them.

cassandra_default_thrift_host

• Description: Host to connect to, if not specified on per-table basis.

Installing

If using the YUM repositories on Fedora, Red Hat, or CentOS, first install the Cassandra storage engine package with:

If using the Debian or Ubuntu repositories, the Cassandra plugin is in the main MariaDB server package.

To install/activate the storage engine into MariaDB, issue the following command:

You can also activate the storage engine by using the --plugin-load command on server startup.

Introduction

The Cassandra Storage Engine allows access to data in a Cassandra cluster from MariaDB. The overall architecture is shown in the picture below and is similar to that of the NDB cluster storage engine.

You can access the same Cassandra cluster from multiple MariaDB instances, provided each of them runs the Cassandra Storage Engine:

The primary goal of Cassandra SE (Storage Engine) is data integration between the SQL and NoSQL worlds. Have you ever needed to:

• grab some of Cassandra's data from your web frontend, or SQL query?

• insert a few records into Cassandra from some part of your app?

Now, this is easily possible. Cassandra SE makes Cassandra's column family appear as a table in MariaDB that you can insert to, update, and select from. You can write joins against this table; it is possible to join data that's stored in MariaDB with data that's stored in Cassandra.

Versions in MariaDB

What about CQL?

The Cassandra Query Language (CQL) is the best way to work with Cassandra. It resembles SQL on first glance; however, the resemblance is very shallow. CQL queries are tightly bound to the way Cassandra accesses its data internally. For example, you can't have even the smallest join. In fact, adding a mere... AND non_indexed_column=1 into a WHERE clause is already invalid CQL.

Our goal is to let one work in SQL instead of having to move between CQL and SQL all the time.

Does this make Cassandra an SQL database?

No. Cassandra SE is not suitable for running analytics-type queries that sift through huge amounts of data in a Cassandra cluster. That task is better handled by Hadoop-based tools like Apache Pig or Apache Hive. Cassandra SE is rather a "window" from an SQL environment into NoSQL.

Data mapping

Let's get specific. In order to access Cassandra's data from MariaDB, one needs to create a table with engine=cassandra. The table will represent a view of a Column Family in Cassandra and its definition will look like so:

The name of the table can be arbitrary. However, primary key, column names, and types must "match" those of Cassandra.

Cassandra's rowkey

The table must define a column that corresponds to the Column Family's rowkey.

• If Cassandra's rowkey has an alias (or name), then MariaDB's column must have the same name.

  • Otherwise, it must be named "rowkey".

• The type of MariaDB's column must match the validation_class of Cassandra's rowkey (datatype matching is covered in more detail below).

Note: Multi-column primary keys are currently not supported. Support may be added in a future version, depending on whether there is a demand for it.

Cassandra's static columns

Cassandra allows one to define a "static column family", where column metadata is defined in the Column Family header and is obeyed by all records.

These "static" columns can be mapped to regular columns in MariaDB. A static column named 'foo' in Cassandra should have a counterpart named 'foo' in MariaDB. The types must also match; they are covered below.

Cassandra's dynamic columns

Cassandra also allows individual rows to have their own sets of columns. In other words, each row can have its own unique columns.

These columns can be accessed through MariaDB's feature. To do so, one must define a column:

• with an arbitrary name

• of type blob

• with the DYNAMIC_COLUMN_STORAGE=yes attribute

Here is an example:

Once define, one can access individual columns with the new variant of the Dynamic Column functions, which now support string names (they used to support integers only).

Super columns

Cassandra's SuperColumns are not supported, there are currently no plans to support them.

Datatypes

There is no direct 1-to-1 mapping between Cassandra's datatypes and MySQL/MariaDB datatypes. Also, Cassandra's size limitations are often more relaxed than MySQL/MariaDB's. For example, Cassandra's limit on rowkey length is about 2G, while MySQL limits unique key length to about 1.5Kb.

The types must be mapped as follows:

For types like "VARBINARY(n)", n should be chosen sufficiently large to accommodate all the data that is encountered in the table.

Command mapping

INSERT

Cassandra doesn't provide any practical way to make INSERT different from UPDATE. Therefore, INSERT works as INSERT-or-UPDATE, it will overwrite the data, if necessary.

INSERT ... SELECT and multi-line INSERT will try to write data in batches. Batch size is controlled by the system variable, which specifies the max. batch size in columns.

The status variables and allow one to see whether inserts are actually batched.

UPDATE

UPDATE works like one would expect SQL's UPDATE command to work (i.e. changing a primary key value will result in the old record being deleted and a new record being inserted)

DELETE

• DELETE FROM cassandra_table maps to the truncate(column_family) call.

• The DELETE with WHERE clause will do per-row deletions.

SELECT

Generally, all SELECT statements work like one expects SQL to work. Conditions in the form primary_key=... allow the server to construct query plans which access Cassandra's rows with key lookups.

Full table scan

Full table scans are performed in a memory-efficient way. Cassandra SE performs a full table scan as a series of batches, each of which reads not more than records.

Batched Key Access support

Cassandra supports Batched Key Access in no-association mode. This means that it requires the SQL layer to do hashing, which means the following settings are required:

• optimizer_switch='join_cache_hashed=on'

• join_cache_level=7|8

Cassandra SE is currently unable to make use of space in the join buffer (the one whose size is controlled by ). Instead, it will limit read batches to reading not more than at a time, and memory are allocated on the heap.

Note that the buffer is still needed by the SQL layer, so its value should still be increased if you want to read in big batches.

It is possible to track the number of read batches, how many keys were looked-up, and how many results were produced with these status variables:

System and status variables

The following are available:

The following are available:

A note about Cassandra 1.2

Cassandra 1.2 has slightly changed its data model, as described at . This has caused some of Thrift-based clients to no longer work (for example, here's a problem experienced by Pig:).

Currently, Cassandra SE is only able to access Cassandra 1.2's column families that were defined WITH COMPACT STORAGE attribute.

See also

• Slides from talk at Percona Live 2013 -

• - JIRA task for Cassandra SE work

_{This page is licensed: CC BY-SA / Gnu FDL}

A storage engine interface to Cassandra. Read the original announcement information about CassandraSE.