CQL(Cassandra Query Language) Reference

Hi,
I think now I'm eligible to publish a blog post regd CQL. I wish this blog to be a tutorial rather a Syntax provider for various CQL queries

Points to remember
1. CQL doesn't support Composite Types[likely to chage]
2. CQL doesn't support Super columns
3. Counter values are returned in native format [atleast in php/using cqlsh]

Why should I prefer CQL?
1. Readability
2. Ease of use
3. SQL like
4. Stable

Support:
PHP => My Posts using PHPCassa
Python => Refer Here and Download it here
Java => JDBC Driver and Example is here
Ruby => This might help

Creating a Keyspace:

cqlsh> CREATE KEYSPACE sample WITH strategy_class = 'org.apache.cassandra.locator.SimpleStrategy' 
    ... AND strategy_options:replication_factor = 2;
 

Note:
: => Option Specifier

Use a Keyspace:

cqlsh> USE sample;
 

Note:
Don't forget to USE before use
'Sample' and 'sample' are different.

Create Column Family:

cqlsh> CREATE COLUMNFAMILY Test (date ascii PRIMARY KEY, name ascii, age int) WITH default_validation=ascii
    ... AND comparator=ascii
    ... AND replicate_on_write=true;
 

Insert Row:

cqlsh> INSERT INTO Test ('date', 'name', 'age') VALUES ('123', 'tamil', 12)
    ... USING CONSISTENCY ONE
    ... AND TTL 20;
 

Update Counter Row:

cqlsh> UPDATE COUNTERTEST SET 'samplecounter' = 'samplecounter'+5 WHERE KEY='testkey';
 

Update Standard Row:

cqlsh> UPDATE Test SET 'name'='Tamil' WHERE 'date'='123'
 

Note:
We have named row key as 'date'
If you specify KEY instead 'date' your terminal might knock with
Bad Request: Expected key 'KEY' to be present in WHERE clause for 'Test'


Select:

cqlsh> SELECT * FROM Test WHERE 'date'='123';
date | age | dept | desc |  name | rollno |
 123 |  12 |  yyy |  zzz | tamil |    xxx |

cqlsh> SELECT 'a'..'f' FROM Test WHERE 'date'='123';
age | dept | desc |
 12 |  yyy |  zzz |

cqlsh> SELECT FIRST 2 'a'..'z' FROM Test WHERE 'date'='123';
age | dept |
 12 |  yyy |
 
cqlsh> SELECT REVERSED 'z'..'a' FROM Test WHERE 'date'='123';
rollno |  name | desc | dept | age |
   xxx | tamil |  zzz |  yyy |  12 |
 
cqlsh> SELECT FIRST 2 REVERSED 'z'..'a' FROM Test WHERE 'date'='123';
rollno |  name |
   xxx | tamil |

cqlsh> SELECT * FROM Test;
date | age |  name |
 123 |  12 |  yyy |  zzz | tamil |    xxx |
 345 |  12 | pamil |
 234 |  12 | camil |
 
cqlsh> SELECT * FROM Test LIMIT 2; 
date | age |  name |
 123 |  12 |  yyy |  zzz | tamil |    xxx |
 345 |  12 | pamil |

--After some random insertions
cqlsh> SELECT * FROM Test where 'date' in ('123', '124', '125');
date | age |  name |
 123 |  12 | tamil |
 124 |  12 | tamil |
 125 |  12 | tamil |
 

Truncate:

cqlsh> TRUNCATE Test;
 

Drop:

cqlsh> DROP COLUMNFAMILY Test;
cqlsh> DROP KEYSPACE sample;
 

Using Secondary Indexes we can query columns based on values rather by their names.
Creating Index

cqlsh> CREATE INDEX name_test_key ON Test (name);

Using the index

cqlsh> SELECT * FROM Test where name = 'tamil';
 date | age |  name |
  127 |  13 | tamil |
  123 |  12 | tamil |
  124 |  12 | tamil |

cqlsh> SELECT * FROM Test where name = 'tamil' and age > 12;
 date | age |  name |
  127 |  13 | tamil |

Note: I'm able to query based on age's value too as there is an index existing on name
So, I'm done :) Just refer this guy's blog for all constants, arguments and options