Getting started with CQL

To get started with CQL, we will create a simple keyspace and table. We will insert a record into the table and read it back. Let's create a simple table which stores some personal information of a social network user.

Creating a keyspace

A keyspace is a collection of related tables equivalent to a database in a relational system. To create keyspace, issue the following statement in the CQL shell:

cqlsh> CREATE KEYSPACE "users"
WITH REPLICATION = {
  'class': 'SimpleStrategy', 'replication_factor': 1
};

Here, we created a keyspace called users. When we create a keyspace, we have to specify replication options. Cassandra provides several strategies for managing replication of data; SimpleStrategy is the best strategy as long as your Cassandra deployment does not span across multiple data centers. The replication_factor value tells Cassandra how many copies of each piece of data are to be kept in the cluster; since we are only running a single instance of Cassandra, there is no point in keeping more than one copy of the data. In a production deployment, you would certainly want a higher replication factor; three is a good place to start.

A few things at this point are worth noting about CQL's syntax:

• It's syntactically very similar to SQL; as we further explore CQL, the impression of similarity will not diminish.
• Double quotes are used for identifiers such as keyspace, table, and column names. As in SQL, quoting identifier names is usually optional, unless the identifier is a keyword or contains a space or another character that will trip up the parser.
• Single quotes are used for string literals; the key value structure we use for replication is a map literal, which is syntactically similar to an object literal in JSON.

Selecting a keyspace

Once you've created a keyspace, you would want to use it. In order to do this, employ the USE command:

cqlsh> USE "users";

This tells Cassandra that all future commands will implicitly refer to tables inside the users keyspace. If you close the CQL shell and reopen it, you'll need to reissue this command.

Creating a table

Let's create a table within our users keyspace to store personal information. You can create a table by issuing the following command:

CREATE TABLE "personal_info" (id int PRIMARY KEY, name text, dob text);

I will be omitting cqlsh> in the text from now on. You should always run the commands after entering cqlsh.

So we created the table personal_info with three columns: id , which is a unique integer identifier for a user which also happens to be the primary key for this table, name, and dob (date of birth) columns which are text values (strings).

Inserting and reading data

To insert data into the table, run the following command:

INSERT INTO personal_info (id, name, dob) VALUES ( 1 , 'Alice' , '02-25-1954' );

This will insert a record for a user named Alice whose date of birth is 02-25-1954 and has been assigned the id 1. To read the data from the table, run the following query:

SELECT * FROM personal_info WHERE id = 1;

You should get an output that looks like this:

Voila! You have created your first keyspace and table, inserted a record, and queried the record back.