SQL Reference

Cassandra queries are written using a variation of the standard SQL SELECT statement. When connecting to Cassandra, Aqua Data Studio supports a subset of the SQL-92 standard. This document details Aqua Data Studio's CQL syntax and functions.

Contents

Cassandra Query Language : CQL syntax

The Cassandra Query Language (CQL) is designed to allow the use of SQL-like statements to query the tables of Cassandra. 

All Cassandra queries are SELECT statements of this form:

SELECT <select-clause>
   [FROM table_name1|(subselect1)] [[AS] tablealias1]
   [WHERE condition]
   [ORDER BY field1|alias1, field2|alias2, ...]
   [LIMIT <INTEGER>]
   [ALLOW FILTERING]
GO

Keywords are not case-sensitive. In this document, keywords such as SELECT are capitalized for illustration purposes.

Back to top

DML Syntax

Data manipulation language (DML) is a family of syntax elements used to insert, delete and update data in a database. The DML below is specific to Cassandra in Aqua Data Studio.

SELECT statement

SELECT expr1 [AS alias1], expr2 [AS alias2], ...

The SELECT clause indicates the set of values to be returned by a query. Expressions (expr1, etc.) in the SELECT clause can be field names, literals or functional expressions that operate on fields or literals. Expressions must be comma-separated.

The SELECT clause supports an AS section, which defines an alias for the field, literal, or functional expression.

We support:

SELECT * FROM jenny.testArray

where the database name is jenny and the collection name is testArray

This is similar to MySQL syntax where you can specify:

SELECT * FROM db.tableName

Example

SELECT city AS htowns
   FROM zips WHERE city
   LIKE 'H%' 

Back to top

DDL Syntax

Data Definition Language (DDL) is a syntax used to define data structures, especially database schemas. The DDL below is specific to Cassandra in Aqua Data Studio. 

INSERT clause

INSERT INTO table_name ( identifier, identifier … )
VALUES ( value, value … ) USING option AND option

An INSERT writes one or more columns to a record in a Cassandra table atomically and in isolation. No results are returned. If a column exists it is updated.

Examples

INSERT INTO zips 
   VALUES ('San Jose', -86.51599, 6055, 'CA', 99999) 

Examples of using a collection set or map

INSERT INTO users (user_id, task) 
   VALUES ('Crowton', { '2012-10-2 12:10' : 'todo' } )

Back to top

UPDATE clause

UPDATE table_name USING TTL seconds SET assignment , assignment, ...
WHERE condition

assignment is one of column_name = value

set_or_list_item + | - set | list

map_name + map

collection_column_name [ term ] = value

counter_column_name = counter_column_name + | - integer

set is : { item1, item2, ...}

list is : [ name, value ]

map is : { name : value, name : value ..}

term is : [ list_index_position | [ list_value] ]

row_specification is: primary key name = key value

 An update writes one or more column values to existing columns in a Cassandra table. No results are returned. A statement begins with the UPDATE keyword followed by a table name. The SET clause specifies the column name-value pairs to update.

Example

UPDATE zips SET pop='626' 
   WHERE city='CANKTON' 
   AND state='LA'

Example using a collection set

UPDATE password SET emails = emails + {'architect@aquafold.com'} WHERE user_id = 'tariq'

To remove an element from a set, use the subtraction (-) operator.

UPDATE password SET emails = emails - {'architect@aquafold.com'} WHERE user_id = 'tariq' 

Example using a collection map

UPDATE password SET task = { '2013-01-01' : 'Enter task', '2013-29-01 12.00' : 'Enter amount' } WHERE user_id = 'tariq'

Example using a collection list

UPDATE password SET rank = [ 'Arun' , 'Bijas' ] WHERE user_id = 'tariq'

Back to top

DELETE clause

DELETE column_name, ...| collection_colname [ term ] FROM table_name USING TIMESTAMP integer [WHERE row_specification]

Term is [ list_index_position | [ list_value ] ] and row_specification is primary_key_name = key_value

Example

DELETE username, password FROM users USING TIMESTAMP 1318452291034 
   WHERE username ='tariq rahiman'

Back to top

TRUNCATE clause

TRUNCATE table_name

A TRUNCATE statement results in the removal of all data in the named table

Example

TRUNCATE user_history

Back to top

CREATE KEYSPACE

CREATE KEYSPACE | SCHEMA keyspace_name WITH REPLICATION = map AND DURABLE_WRITES = true | false

   map is a map collection with { property : value, property, value : property, value ...}

CREATE KEYSPACE creates a top-level namespace and sets the keyspace name, replica replacement strategy class, replication options, and durable_writes options for the keyspace. Keyspace names are 32 or fewer alpha-numeric characters and underscores, the first of which is an alpha character. Keyspace names are case-sensitive. To make a name case-sensitive, enclose it in double quotation marks.

To set the replica placement strategy, construct a map of properties and values:

Example of setting a KEYSPACE with the SimpleStrategy class

CREATE KEYSPACE moviestore WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 3 } 

Example of setting a KEYSPACE with the NetworkTopologyStrategy class

CREATE KEYSPACE musicstore WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy', 'dcl' : 1 } 

CREATE KEYSPACE gamestore WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy', 'dcl' : 1 } AND durable_writes = false

DROP KEYSPACE

DROP KEYSPACE | SCHEMA keyspace_name

DROP KEYSPACE removes the keyspace and all of its Tables and data contained in the keyspace. You can also use the alias DROP SCHEMA.

Example

DROP KEYSPACE moviestore

Back to top

CREATE TABLE

CREATE TABLE table_name ( column_definition, column_definition, ...) WITH property AND property ...

COLUMN_DEFINITION is: 

column_name cql_type
 |  column_name cql_type PRIMARY KEY
 |  PRIMARY KEY ( partition_key )
 |  column_name collection_type

PARTITION_KEY is:

column_name
 |  ( column_name1, column_name2, column_name3 ...)
 |  ((column_name1, column_name2), column_name3, column_namne4 ...)

COLLECTION_TYPE is: 

LIST <cql_type>column_name cql_type
 |  SET <cql_type>column_name cql_type PRIMARY KEY
 |  MAP <cql_type, cql_type>

PROPERTY is one of the CQL table storage options or a directive. A directive is either: COMPACT STORAGE or CLUSTERING ORDER followed by the clustering order specification

You can also use the alias CREATE COLUMNFAMILY. The only schema information that must be defined for a table is the primary key and its associated data type. You can use the WITH clause to specify the attributes of tables listed in CQL 3 table storage properties.

Example of defining a primary key column

CREATE TABLE products (product_id varchar PRIMARY KEY,
                       product_name bigint )

Example of using composite partition keys

CREATE TABLE genre (genre_id uuid,
                    genre_name bigint, color text, store varint, location text,
                    PRIMARY KEY ((genre_id, color), store, location))

Example of TABLE OPTIONS settings WITH clause

CREATE TABLE employees (employee_id uuid,
                        employee_name bigint, color text, store varint, location text,
                        PRIMARY KEY ((employee_id, color), store, location)
)
WITH comment='Employee Data' 
AND read_repair_chance = 1.0 AND replicate_on_write = 'true'

Example of Compact Storage

CREATE TABLE time (employee_id uuid,
                   employee_name bigint, color text, store varint, location text,
                   PRIMARY KEY ((employee_id, color), store, location)
)
WITH COMPACT STORAGE

Example of Clustering order

CREATE TABLE series (series_id uuid,
                   employee_name bigint, color text, store varint, series_time timestamp, 
                   PRIMARY KEY ((series_id, color), store, series_time)
)
WITH CLUSTERING ORDER BY (series_time DESC)

Table properties and format: In CQL commands, such as CREATE TABLE, you format properties in either the name-value pair or collection map format. CQL supports Cassandra table properties listed below

Back to top

DROP TABLE

DROP TABLE table_name

DROP TABLE removes a given Table and all of its contents. You can also use the alias DROP COLUMNFAMILY.

Example

DROP TABLE products

Back to top

ALTER TABLE

ALTER TABLE modifies the column metadata of a table.

Back to top

ALTER TABLE ADD COLUMN

ALTER TABLE table_name
ADD FIELD Data_type

This alters a table by adding a column to an existing table.

Example

ALTER TABLE products 
   ADD widget text

Back to top

ALTER TABLE DROP COLUMN

ALTER TABLE table_name
DROP column_name

This feature is not ready in Cassandra 1.2 but will be available in subsequent versions.

Example

ALTER TABLE products 
   DROP store

Back to top

ALTER TABLE RENAME COLUMN

ALTER TABLE table_name
RENAME column_name TO column_name

The main purpose of the the RENAME clause is to change the names of the CQL-3 generated primary key and column names.

Example

ALTER TABLE products 
   RENAME name TO PRODUCT_NAME

Back to top

CREATE INDEX

CREATE INDEX index_name
ON table_name( column_name )

CREATE INDEX creates an Index for a specified Table. If data already exists for the column, Cassandra indexes the data during the execution of this statement. After the index is created, Cassandra indexes new data for the column automatically when new data is inserted. Cassandra does not support secondary indexes on collections. Also you cannot create a secondary index on primary key.

Example

CREATE INDEX myIndex ON series(store)

Back to top

DROP INDEX

DROP INDEX index_name

DROP INDEX removes the named secondary index.

Example

DROP INDEX myIndex

Back to top

CREATE USER

CREATE USER user_name WITH PASSWORD 'password' NOSUPERUSER | SUPERUSER

CREATE USER defines a new database user account. By default users accounts do not have superuser status. Only a superuser can issue CREATE USER requests. User accounts are required for logging in under internal authentication and authorization.

Example

CREATE USER tariq 
   WITH PASSWORD '1337@55H@X0|2'

Back to top

DROP USER

DROP USER user_name

DROP USER removes an existing user from the Cassandra Server. You have to be logged in as a superuser to issue a DROP USER statement. A user cannot drop themselves. Enclose the username in single quotation marks only if it contains non-alphanumeric characters.

Example

DROP USER tariq

Back to top

ALTER USER

ALTER USER user_name WITH PASSWORD 'password' NOSUPERUSER | SUPERUSER

ALTER USER alter existing user options. Superusers can change a user's password or superuser status. Ordinary users can change only their own password. To prevent disabling all superusers, superusers cannot change their own superuser status.

Example

ALTER USER tariq WITH PASSWORD 'bestQuaterBack'

Back to top

LIST PERMISSIONS

LIST permission_name PERMISSION | LIST ALL PERMISSIONS ON resource OF user_name NORECURSIVE

Lists permissions granted to USER.

Example

LIST ALL PERMISSIONS OF tariq;

Back to top

LIST USERS

LIST USERS 

List existing users and their superuser status.

Example

LIST USERS

Back to top

GRANT

GRANT permission_name PERMISSION | GRANT ALL PERMISSIONS ON resource TO user_name

permission_name is one of these: ALTER | AUTHORIZE | CREATE | DROP | MODIFY | SELECT and resource is one of : ALL KEYSPACES | KEYSPACE keyspace_name | TABLE tablename

Example

GRANT MODIFY ON KEYSPACE DEMO TO tariq 

Back to top

REVOKE

REVOKE permission_name PERMISSION | REVOKE ALL PERMISSIONS ON resource FROM user_name

permission_name is one of these: ALTER | AUTHORIZE | CREATE | DROP | MODIFY | SELECT and resource is one of : ALL KEYSPACES | KEYSPACE keyspace_name | TABLE tablename

Example

REVOKE SELECT ON songs FROM tariq

Back to top

BATCH

BEGIN BATCH
 | BEGIN UNLOGGED
 | BEGIN COUNTER
USING TIMESTAMP timestamp;
dml_statement
dml_statement
...
APPLY BATCH;

Writes multiple DML statement and sets a client-supplied timestamp for all columns written by the statements in the batch. dml_statement is INSERT | UPDATE | DELETE

Back to top

Supported Functions

A limited set of Cassandra' functions are available .

Back to top

Supported Commands

CONSISTENCY

CONSISTENCY

This command returns the current consistency level.

Example

CONSISTENCY

Back to top

CONSISTENCY <level>

CONSISTENCY <level>

This command sets the consistency level of the current connection.

<level> is one of these: ONE, QUORUM, LOCAL_QUORUM, EACH_QUORUM, ALL, ANY, TWO, THREE

Example

CONSISTENCY QUORUM

Back to top