Introducing CQL (SQL Skills Welcome)
This is an excerpt from the DataStax eBook Getting Started with NoSQL and Apache Cassandra™; which delves into how to migrate to Apache Cassandra™ and build the next generation of powerful applications. Download the full eBook.
We touched on CQL earlier—it’s a fast way to start working with Cassandra, especially when you’re familiar with SQL, so you can quickly start creating or altering keyspaces and tables, making changes to data, and performing queries.
If you’re familiar with SELECT, INSERT, UPDATE, and DELETE in SQL, you’re ready for CQL and can refer to our CQL Quick Reference Guide. We’ve also highlighted some of the key similarities and differences here.
Introducing CQL
| Operation | CQL Examples |
| SELECT | SELECT col_0, col_1 FROM my_table; Retrieving all rows in a table looks the same when comparing SQL and CQL SELECT col_0, col_1 FROM my_table WHERE ...; In SQL, any column can be included in the WHERE clause, though in CQL only columns that are strictly declared in the primary key can be used as a restricting column. Also, each query must have a partition key defined at a minimum. |
| INSERT UPDATE | INSERT INTO my_table (col_0, col_1) VALUES (val_0, val_1); UPDATE my_table SET col_0=val_0 WHERE ...; DSE and Cassandra are best-in-class at high throughput writes. Bear in mind that in CQL, both INSERT and UPDATE must include the partition key. |
| DELETE | DELETE FROM my_table WHERE ...; In SQL, there is only the option to remove the entire row(s) using the DELETE syntax. In CQL, you can delete specific columns using DELETE my_col FROM my_table WHERE ...; |
Did you know that in Cassandra, data isn’t deleted in the same way it is in an RDBMS? Apache Cassandra is designed for high write throughput and avoids reads-before-writes. It uses SSTables, which are immutable once written. So, a delete is an update and updates are actually inserts (into new SSTables).
Just like how you control permissions and resources of the entities in SQL, you can do the same in CQL:
| _CREATE ROLE | _RESTRICT |
| _ALTER ROLE | _UNRESTRICT |
| _DROP ROLE | _RESTRICT ROWS |
| _LIST ROLES | _UNRESTRICT ROWS |
| _GRANT | _LIST PERMISSIONS |
| _REVOKE |
Thanks for reading this excerpt from the DataStax eBook Getting Started with NoSQL and Apache Cassandra™; tune in next week when we'll release another excerpt or click here to download the full asset.
