CQL (Cassandra Query Language)

CQL (Cassandra Query Language) 

CQL was introduced, an SQL-like alternative to the traditional RPC interface.
Language drivers are available for Java (JDBC), Python (DBAPI2) and Node.JS (Helenus).

Aggregation, joins, and subqueries are not supported in CQL.

CREATE KEYSPACE <NAME> WITH AND strategy_class = <STRATEGY>
AND strategy_options.<OPTION> = <VALUE> [AND strategy_options.  <OPTION> = <VALUE>];
CREATE KEYSPACE test with strategy_class = 'SimpleStrategy' and strategy_options:replication_factor=1;
CREATE KEYSPACE twissandra WITH strategy_class = 'NetworkTopologyStrategy' AND strategy_options:DC1 = 3;

USE <KEYSPACE>;
USE test;

CREATE COLUMNFAMILY <COLUMN FAMILY> (KEY <type> PRIMARY KEY [, name1 type, name2 type, ...]);
CREATE COLUMNFAMILY <COLUMN FAMILY> (KEY <type> PRIMARY KEY [, name1 type, name2 type, ...])
[WITH keyword1 = arg1 [AND keyword2 = arg2 [AND ...]]];
CREATE COLUMNFAMILY users (
   ...     key varchar PRIMARY KEY,
   ...     full_name varchar,
   ...     birth_date int,
   ...   birth_year bigint,
   ...     state varchar
   ... );
create column family sblocks with comparator = 'UUIDType'
and default_validation_class = 'BytesType'
and key_validation_class = 'UUIDType'

DROP <KEYSPACE|COLUMNFAMILY> namespace;
DROP COLUMNFAMILY users;
DROP KEYSPACE twissandra;

TRUNCATE <COLUMN FAMILY>

CREATE TABLE Fish (KEY blob PRIMARY KEY);

CREATE INDEX [index_name] ON <column_family> (column_name);
CREATE INDEX ON users (birth_date);
CREATE INDEX surnameidx ON users (surname);

SELECT [FIRST N] [REVERSED] <SELECT EXPR> FROM <COLUMN FAMILY>  [USING <CONSISTENCY>] [WHERE <CLAUSE>] [LIMIT N];
SELECT * from People;
SELECT key, state FROM users;
SELECT * FROM users WHERE state='UT' AND birth_date > 1980;
SELECT id, given FROM users WHERE surname = thirumani;
SELECT id, given FROM users WHERE surname = thirumani LIMIT 1000;
SELECT '2013-01-01'..'2013-03-28' FROM News WHERE topic = cassandra;
SELECT FIRST 10 REVERSED * FROM News WHERE topic = cassandra;
SELECT surname, given FROM users WHERE id = tsatya;

INSERT INTO (KEY, , , ...) VALUES (, , , ...) [USING <CONSISTENCY>];
INSERT INTO users (key, full_name, birth_date, state) VALUES ('bsanderson', 'Brandon Sanderson', 1975, 'UT');
INSERT INTO NerdMovies (KEY, 11924) VALUES ('fde66cdc-d857-4e90-b1e5-df09a3d40dd6', 'john') USING CONSISTENCY LOCAL_QUORUM AND TTL 86400;
INSERT INTO users (id, given, surname) VALUES (12345, Satya, Thirumani);

UPDATE <COLUMN FAMILY> [USING <CONSISTENCY>] SET name1 = value1, name2 = value2 WHERE KEY = keyname;
UPDATE users SET given = Satya, surname = Thirumani WHERE id = tsatya;
UPDATE inventory SET apples = apples + 1 WHERE id = fruit;
UPDATE inventory SET carrots = carrots - 1 WHERE id = vegetable;

DELETE [COLUMNS] FROM <COLUMN FAMILY> [USING <CONSISTENCY>] WHERE KEY = keyname1
DELETE [COLUMNS] FROM <COLUMN FAMILY> [USING <CONSISTENCY>] WHERE KEY IN (keyname1, keyname2);
DELETE session_token FROM users where KEY = 'thirumani';
DELETE FROM users where KEY = 'thirumani';

BEGIN BATCH [USING CONSISTENCY ]
INSERT or UPDATE or DELETE statements separated by semicolon or "end of line"
APPLY BATCH

BEGIN BATCH
INSERT INTO msgs (owner, subject, body) VALUES(jericevans, 'Hi', 'Hiya');
UPDATE subjects SET subject = now WHERE owner = jericevans
APPLY BATCH

CQL 2.0
types made more consistent w/ SQL
count() returns rows, not columns

CQL 3.0
• Column names are strictly UTF-8
• Column names are case-insensitive (unless quoted)
• Old slice notation is gone (<start>..<end>)
• Static column families are actually static (schema-enforced)

Related Cassandra Articles: List of NoSQL Databases  Cassandra nodetool  cassandra-cli Commands