RazorSQL Cassandra SQL Syntax Guide
RazorSQL provides support for interacting with Apache Cassandra databases. Cassandra provides a database query language called CQL (Cassandra Query Language) that is similar to SQL, but does not support the full set of SQL features supported by most relational database systems. Listed below are examples for how to write select, insert, update, and delete statements. SQL / CQL statements executed in RazorSQL that attempt to use features not supported by CQL will throw an error.
CQL supports selecting all columns and certain columns from tables. It also supports where clauses with some limitations compared to ANSI SQL. Joins are not supported. Listed below are some examples:
select * from employee
select first_name, last_name from employee
Where clauses must contain either a key or an indexed column. For indexed columns, the "=" operator is the only operator supported. For tables without a composite key, the "=" operator and the "in" operator are supported. For tables with composite keys where one key acts as the partition key and the second key acts as the primary key, the "=", "in", "<", ">", "<=", and ">=" operators are supported.
Sample query against an indexed column:
select * from employee where first_name = 'John'
Sample queries against a single key table:
select * from employee where id = 1
select * from employee where id in (1,2)
Sample query against a composite key table - querying the primary key column
select * from employee where dept_id < 10 allow filtering
The "allow filtering" clause is necessary in the above. If not included, an error will be thrown by the driver indicating the performance unpredictability of the query.
Insert Statements When executing insert statements, the column list including all column names must be included. For example:
insert into employee (id, dept_id, first_name, last_name) values (1, 100, 'John', 'Smith')
When executing update statements, the where clause must include the key, or an in clause with multiple keys. If the table has a composite key, each part of the key must be included in the where clause. For example:
update employee set first_name = 'John' where id = 1
update employee set first_naem = 'John' where id in (1,2)
update contact set address = '101 Main St' where contact_id = 1 and dept_id = 2;
The same rules apply for delete where clauses as apply for update where clauses. Here is an example:
delete from employee where id = 4