April 15, 2017

CQL (Cassandra Query Language)

CQL (Cassandra Query Language) 
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 Articles: List of NoSQL Databases  Cassandra nodetool  cassandra-cli Commands


2 comments:

  1. Appreciation to my father who told me concerning this web site,
    this web site is really remarkable.

    ReplyDelete
  2. Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking.Hadoop Administration Online Training

    ReplyDelete