Cassandra Query Language FAQs
What is CQL?
Cassandra CQL, a simple alternative to Structured Query Language (SQL), is a declarative language developed to provide abstraction in accessing Apache Cassandra.
Basic Cassandra (CQL) constructs include:
- Keyspace — Similar to an RDBMS database, a keyspace is a container for application data that must have a name and a set of associated attributes. Cassandra keyspace is a SQL database.
- Column Families/Tables — A keyspace consists of a number of Column Families/Tables. A Cassandra column family is a SQL table.
- Primary Key / Tables — A Primary Key consists of a Row/Partition Key and a Cluster Key, and functions to enable users to uniquely identify internal rows of data. A Row/Partition Key determines the node on which data is stored. A Cluster Key determines the sort order of data within a particular row.
Cassandra Query Language example:
CQL Create Keyspace — To create a keyspace called “fruit,” execute the statement below:
CREATE KEYSPACE fruit WITH REPLICATION = { 'class' : 'SimpleStrategy' , 'replication_factor' : 3 };
Execute the “DESC KEYSPACES” command once more to see if the “fruit” keyspace is created.
Cassandra Query Language vs SQL
CQL query language is a NoSQL interface that is intentionally similar to SQL, providing users who are comfortable with relational databases a familiar language that ultimately lowers the barrier of entry to Apache Cassandra.
Some characteristics that are associated with SQL but are not available in Cassandra (CQL) include:
- Arbitrary WHERE clauses – A predicate can only contain columns specified in the Primary Key.
- Arbitrary ORDER BY clauses – Order by can only be applied to a cluster column.
- GROUP BY – Identical data cannot be grouped in CQL.
- Cassandra CQL JOIN — Data across column families cannot be joined in CQL without third party tools.
- Complex transactions — Where SQL supports complex transactions, CQL supports simple transactions.
CQL is used in non-relational databases, is compatible with Apache Cassandra, uses SQL-like query syntax, allows the terms “table” and “columnfamily” to be interchangeable, and uses its own CQL binary protocol. CQL query can also return objects. Objects in a collection can be accessed via iteration through the collection field in the result object. Unlike highly structured SQL columns, result fields can be collections or objects themselves, rather than just data type values.
Read our educational page on SQL vs. NoSQL.
CQL Data Types
Apache Cassandra CQL supports a rich set of data types, including collection types, custom types, native types, tuple types, and user-defined types:
- collection types — intended to store and/or denormalize small amounts of data; supports Maps, Sets, and Lists collections
- custom types — a string that can be loaded by Cassandra and contains the name of Java class that extends the server side AbstractType class
- native types — includes Bigint, Blob, Boolean, Counter, Date, Decimal, Double, Duration, Float, Inet, Int, Smallint, Text, Time, Timestamp, Timeuuid, Tinyint, Uuid, Varchar, Varint
- tuple types — an ordered set of values that can functionally be considered anonymous UDT with anonymous fields, and can only be updated as a whole
- user-defined types — a named set consisting of named and typed fields that can be created, modified, and removed using the create_type_statement
CQL Documentation
CQL language documentation includes:
- Definitions for Conventions, Identifiers and Keywords, Constants, Terms, Comments, Statements, and Prepared Statements
- CQL Data Types
- Definitions of statements used to create, modify, and remove keyspaces and tables, such as Create Keyspace, Alter Keyspace, and Drop Keyspace; Create Table, Alter Table, and Drop Table; Truncate; and Describe.
- Data Manipulation statements, such as CQL Select, Insert, Update, Delete, and Batch
- Secondary Index statements such as Create Index and Drop Index
- Materialized Views statements such as Create Materialized View, Alter Materialized View, and Drop Materialized View
- Security definitions for Database Roles, Users, and Data Control
- Definitions for Scalar Functions and Aggregate Functions
- Definitions for the arithmetic operators that CQL supports — Number Arithmetic and Datetime Arithmetic
- JSON Support documentation
- Trigger definitions for Create Trigger and Drop Trigger
- Appendices for CQL Keywords, CQL Reserved Types, and Dropping Compact Storage
- Cassandra CQL updates from 3.0.1 to 3.4.5
Masterclass: Data Modeling for NoSQL Databases
Looking for extensive training on about data modeling for NoSQL Databases? Our experts offer a 3-hour masterclass that assists practitioners wanting to migrate from SQL to NoSQL or advance their understanding of NoSQL data modeling. This free, self-paced class covers techniques and best practices on NoSQL data modeling that will help you steer clear of mistakes that could inconvenience any engineering team.
You can access the complete course here.
Does ScyllaDB Offer Solutions for CQL?
ScyllaDB is a NoSQL database that supports CQL and ScyllaDB-specific extensions to CQL. ScyllaDB offers an open source NoSQL database that acts as a drop-in replacement for Apache Cassandra and powers higher performance and lower latency at a fraction of the cost of most NoSQL databases. ScyllaDB’s new approach to NoSQL data store design is optimized for modern hardware. ScyllaDB runs multiple engines, one per core, each with its own memory, CPU, and multi-queue NIC.
