Cassandra 2.0

Cassandra

Create KEYSPACE or DATABASE

  • DESCRIBE KEYSPACES;
    • Creating a keyspace is the CQL counterpart to creating an SQL database, but a little different. The Cassandra keyspace is a namespace that defines how data is replicated on nodes. Typically, a cluster has one keyspace per application. Replication is controlled on a per-keyspace basis, so data that has different replication requirements typically resides in different keyspaces.
  • DESCRIBE TABLES : lists all tables if inside a key-space else lists all keycaps & tables under it.
  • EXPAND ON: used for showing select output vertically.
  • CREATE KEYSPACE user WITH replication = {‘class’: ‘SimpleStrategy’, ‘replication_factor’: 1 };
  • USE user ;

Create & INSERT

  • CREATE TABLE users ( user_id timeuuid, date_joined timestamp, user_name text, department text, team text, manager_id timeuuid, primary key (user_id))  ;
  • CREATE TABLE playlists ( id uuid , song_order int , song_id uuid , title text , album text , artist text, PRIMARY KEY (id, song_order));
  • DESCRIBE TABLE user;
  • INSERT INTO playlists (id, song_order, song_id, title, artist, album) VALUES (62c36092-82a1-3a00-93d1-46196ee77204, 4, 7db1a490-5878-11e2-bcfd-0800200c9a66, ‘Ojo Rojo’, ‘Fu Manchu’, ‘No One Rides for Free’);
  • INSERT INTO users (user_id, date_joined, department, manager_id, team, user_name) VALUES (d2177dd0-eaa2-11de-b572-001b779c76e3, ‘2011-02-03’, ‘SSPT’, d2177dd0-eaa2-11de-a572-001b779c76e3, ‘DCCONFIG’, ‘Rosh’);

ALTER table , ADD collection, UPDATE row

  • ALTER TABLE playlists ADD tags set<text>;
  • ALTER TABLE playlists ADD reviews list<text>; 
  • ALTER TABLE playlists ADD venue map<timestamp, text>;
  • UPDATE playlists SET tags = tags + {‘2007’} WHERE id = 62c36092-82a1-3a00-93d1-46196ee77204 AND song_order = 2;
  • ALTER TABLE users RENAME userid to user_id; 

INDEXING for Select & Search

  • Individual Column Index
    • CREATE INDEX ON playlists( artist ); : Allows for searching(Select) by artist value
    • Select * from playlists WHERE artist = ‘BAD’;
  • Collection Index
    • CREATE INDEX ON playlists (tags);
    • CREATE INDEX mymapvalues ON playlists (venue); (Specifying a name for the index, such as mymapindex, is optional.)
  • Index on map collection keys (Indexes on the keys and values of a map cannot co-exist.)
    • DROP INDEX mymapvalues; 
    • CREATE INDEX mymapkeys ON playlists (KEYS(venue));
  • Do not use an index in these situations:
    • On high-cardinality columns because you then query a huge volume of records for a small number of results. See Problems using a high-cardinality column index below.
    • In tables that use a counter column
    • On a frequently updated or deleted column. See Problems using an index on a frequently updated or deleted column below.
    • To look for a row in a large partition unless narrowly queried. See Problems using an index to look for a row in a large partition unless narrowly queried below.

SELECT on Collection

  • SELECT album, tags FROM playlists WHERE tags CONTAINS ‘blues’; (LIST)
  • SELECT artist, venue FROM playlists WHERE venue CONTAINS ‘The Fillmore’; (MAP Values)
  • SELECT album, venue FROM playlists WHERE venue CONTAINS KEY ‘2013-09-22 22:00:00-0700’;(MAP KEYS)

SELECT

  • CREATE INDEX album_name ON playlists ( album );
  • CREATE INDEX title_name ON playlists ( title );
  • SELECT * FROM playlists WHERE album = ‘Roll Away’ AND title = ‘Outside Woman Blues’ ALLOW FILTERING ;