--- title: "Cassandra CQL Examples" --- # Cassandra CQL Examples :::caution[What the listener serves today] HeliosDB Full opens listeners for PostgreSQL, MySQL, Oracle, MongoDB, Cassandra, Redis and SQL Server plus an HTTP gateway; MongoDB, Cassandra and SQL Server clients can connect and authenticate, and reading and writing HeliosDB tables through them is planned. Standard Cassandra drivers, including the DataStax drivers and cqlsh, cannot connect yet; driver compatibility for the CQL listener is planned. The features on this page describe the planned surface. ::: Practical CQL examples and usage patterns for HeliosDB's Cassandra compatibility. ## Connection Examples ### Python (cassandra-driver) ```python from cassandra.cluster import Cluster from cassandra.auth import PlainTextAuthProvider # Basic connection cluster = Cluster(['localhost']) session = cluster.connect() # With authentication auth = PlainTextAuthProvider(username='admin', password='password') cluster = Cluster(['localhost'], auth_provider=auth) session = cluster.connect('my_keyspace') ``` ### Java (DataStax Driver) ```java import com.datastax.oss.driver.api.core.CqlSession; CqlSession session = CqlSession.builder() .addContactPoint(new InetSocketAddress("localhost", 9042)) .withLocalDatacenter("datacenter1") .withKeyspace("my_keyspace") .build(); ``` ### Node.js ```javascript const cassandra = require('cassandra-driver'); const client = new cassandra.Client({ contactPoints: ['localhost'], localDataCenter: 'datacenter1', keyspace: 'my_keyspace' }); await client.connect(); ``` ### cqlsh ```bash # Connect cqlsh localhost 9042 # Connect with credentials cqlsh localhost 9042 -u admin -p password # Execute file cqlsh -f schema.cql ``` ## Keyspace Management ### Create Keyspace ```cql -- Simple replication CREATE KEYSPACE my_keyspace WITH REPLICATION = { 'class': 'SimpleStrategy', 'replication_factor': 3 }; -- Network topology CREATE KEYSPACE production WITH REPLICATION = { 'class': 'NetworkTopologyStrategy', 'dc1': 3, 'dc2': 2 }; -- With durable writes CREATE KEYSPACE my_keyspace WITH REPLICATION = { 'class': 'SimpleStrategy', 'replication_factor': 1 } AND DURABLE_WRITES = true; ``` ### Modify Keyspace ```cql -- Alter replication ALTER KEYSPACE my_keyspace WITH REPLICATION = { 'class': 'SimpleStrategy', 'replication_factor': 5 }; -- Drop keyspace DROP KEYSPACE IF EXISTS my_keyspace; ``` ## Table Management ### Create Tables ```cql -- Basic table CREATE TABLE users ( user_id UUID PRIMARY KEY, username TEXT, email TEXT, created_at TIMESTAMP ); -- With clustering columns CREATE TABLE user_events ( user_id UUID, event_time TIMESTAMP, event_type TEXT, event_data MAP, PRIMARY KEY ((user_id), event_time) ) WITH CLUSTERING ORDER BY (event_time DESC); -- Compound partition key CREATE TABLE sensor_data ( sensor_id UUID, day DATE, reading_time TIMESTAMP, value DOUBLE, PRIMARY KEY ((sensor_id, day), reading_time) ); -- With TTL CREATE TABLE session_data ( session_id UUID PRIMARY KEY, user_id UUID, data BLOB ) WITH default_time_to_live = 3600; -- 1 hour -- With collections CREATE TABLE user_profiles ( user_id UUID PRIMARY KEY, name TEXT, tags SET, preferences MAP, addresses LIST> ); ``` ### User-Defined Types ```cql -- Create type CREATE TYPE address_type ( street TEXT, city TEXT, zip TEXT, country TEXT ); -- Use in table CREATE TABLE contacts ( id UUID PRIMARY KEY, name TEXT, home_address FROZEN, work_address FROZEN ); ``` ### Indexes ```cql -- Secondary index CREATE INDEX ON users (email); -- Custom index name CREATE INDEX users_by_username ON users (username); -- Drop index DROP INDEX users_by_username; ``` ## CRUD Operations ### INSERT ```cql -- Basic insert INSERT INTO users (user_id, username, email, created_at) VALUES (uuid(), 'alice', 'alice@example.com', toTimestamp(now())); -- With TTL INSERT INTO users (user_id, username, email) VALUES (uuid(), 'bob', 'bob@example.com') USING TTL 86400; -- With timestamp INSERT INTO users (user_id, username, email) VALUES (uuid(), 'charlie', 'charlie@example.com') USING TIMESTAMP 1640000000000000; -- If not exists (LWT) INSERT INTO users (user_id, username, email) VALUES (123e4567-e89b-12d3-a456-426614174000, 'unique', 'unique@example.com') IF NOT EXISTS; -- Insert JSON INSERT INTO users JSON '{"user_id": "550e8400-e29b-41d4-a716-446655440000", "username": "json_user", "email": "json@example.com"}'; ``` ### SELECT ```cql -- Basic select SELECT * FROM users WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; -- Select specific columns SELECT username, email FROM users WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; -- With ordering (clustering) SELECT * FROM user_events WHERE user_id = 550e8400-e29b-41d4-a716-446655440000 ORDER BY event_time DESC LIMIT 10; -- Time range SELECT * FROM user_events WHERE user_id = 550e8400-e29b-41d4-a716-446655440000 AND event_time >= '2024-01-01' AND event_time < '2024-02-01'; -- IN clause SELECT * FROM users WHERE user_id IN ( 550e8400-e29b-41d4-a716-446655440000, 550e8400-e29b-41d4-a716-446655440001 ); -- Token range scan SELECT * FROM users WHERE token(user_id) > token(550e8400-e29b-41d4-a716-446655440000) LIMIT 100; -- Allow filtering SELECT * FROM users WHERE email = 'alice@example.com' ALLOW FILTERING; -- As JSON SELECT JSON * FROM users WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; -- Functions SELECT user_id, TTL(username), WRITETIME(username) FROM users; ``` ### UPDATE ```cql -- Basic update UPDATE users SET email = 'new@example.com' WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; -- Multiple columns UPDATE users SET email = 'new@example.com', username = 'new_name' WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; -- With TTL UPDATE users USING TTL 86400 SET email = 'temp@example.com' WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; -- Conditional update (LWT) UPDATE users SET email = 'verified@example.com' WHERE user_id = 550e8400-e29b-41d4-a716-446655440000 IF email = 'unverified@example.com'; -- Update collections UPDATE user_profiles SET tags = tags + {'premium'} WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; UPDATE user_profiles SET preferences['theme'] = 'dark' WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; ``` ### DELETE ```cql -- Delete row DELETE FROM users WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; -- Delete column DELETE email FROM users WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; -- Conditional delete (LWT) DELETE FROM users WHERE user_id = 550e8400-e29b-41d4-a716-446655440000 IF EXISTS; -- Delete with condition DELETE FROM users WHERE user_id = 550e8400-e29b-41d4-a716-446655440000 IF email = 'delete@example.com'; -- Delete from collection UPDATE user_profiles SET tags = tags - {'old_tag'} WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; ``` ## Batch Operations ```cql -- Logged batch (atomic) BEGIN BATCH INSERT INTO users (user_id, username, email) VALUES (uuid(), 'user1', 'user1@example.com'); INSERT INTO user_events (user_id, event_time, event_type) VALUES (550e8400-e29b-41d4-a716-446655440000, toTimestamp(now()), 'signup'); APPLY BATCH; -- Unlogged batch BEGIN UNLOGGED BATCH UPDATE users SET email = 'new1@example.com' WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; UPDATE users SET email = 'new2@example.com' WHERE user_id = 550e8400-e29b-41d4-a716-446655440001; APPLY BATCH; -- Counter batch BEGIN COUNTER BATCH UPDATE page_views SET count = count + 1 WHERE page_id = 'home'; UPDATE page_views SET count = count + 1 WHERE page_id = 'about'; APPLY BATCH; ``` ## Prepared Statements ### Python ```python # Prepare statement prepared = session.prepare( "INSERT INTO users (user_id, username, email) VALUES (?, ?, ?)" ) # Execute with parameters session.execute(prepared, [uuid.uuid4(), 'alice', 'alice@example.com']) # Batch with prepared from cassandra.query import BatchStatement, BatchType batch = BatchStatement(batch_type=BatchType.LOGGED) for user in users: batch.add(prepared, [user.id, user.name, user.email]) session.execute(batch) ``` ### Java ```java PreparedStatement prepared = session.prepare( "INSERT INTO users (user_id, username, email) VALUES (?, ?, ?)" ); BoundStatement bound = prepared.bind( UUID.randomUUID(), "alice", "alice@example.com" ); session.execute(bound); ``` ## Aggregations ```cql -- Count SELECT COUNT(*) FROM users; -- Sum and average SELECT SUM(amount), AVG(amount) FROM transactions WHERE account_id = 550e8400-e29b-41d4-a716-446655440000; -- Min and max SELECT MIN(created_at), MAX(created_at) FROM events WHERE user_id = 550e8400-e29b-41d4-a716-446655440000; ``` ## Time Series Examples ### Create Time Series Table ```cql CREATE TABLE sensor_readings ( sensor_id UUID, bucket_date DATE, reading_time TIMESTAMP, temperature DOUBLE, humidity DOUBLE, PRIMARY KEY ((sensor_id, bucket_date), reading_time) ) WITH CLUSTERING ORDER BY (reading_time DESC); ``` ### Insert Readings ```cql INSERT INTO sensor_readings (sensor_id, bucket_date, reading_time, temperature, humidity) VALUES ( 550e8400-e29b-41d4-a716-446655440000, toDate(now()), toTimestamp(now()), 23.5, 65.2 ); ``` ### Query Patterns ```cql -- Latest readings SELECT * FROM sensor_readings WHERE sensor_id = 550e8400-e29b-41d4-a716-446655440000 AND bucket_date = '2024-12-01' LIMIT 100; -- Time range SELECT * FROM sensor_readings WHERE sensor_id = 550e8400-e29b-41d4-a716-446655440000 AND bucket_date = '2024-12-01' AND reading_time >= '2024-12-01 00:00:00' AND reading_time < '2024-12-01 12:00:00'; -- Aggregations SELECT AVG(temperature), MAX(temperature), MIN(temperature) FROM sensor_readings WHERE sensor_id = 550e8400-e29b-41d4-a716-446655440000 AND bucket_date = '2024-12-01'; ``` ## Materialized Views ```cql -- Create view CREATE MATERIALIZED VIEW users_by_email AS SELECT * FROM users WHERE email IS NOT NULL AND user_id IS NOT NULL PRIMARY KEY (email, user_id); -- Query view SELECT * FROM users_by_email WHERE email = 'alice@example.com'; ``` ## Migration from Cassandra ### Schema Export ```bash # Export schema from Cassandra cqlsh -e "DESCRIBE KEYSPACE my_keyspace" > schema.cql # Import to HeliosDB cqlsh heliosdb-host -f schema.cql ``` ### Data Migration ```python from cassandra.cluster import Cluster # Source (Cassandra) source = Cluster(['cassandra-host']).connect('my_keyspace') # Target (HeliosDB) target = Cluster(['heliosdb-host']).connect('my_keyspace') # Migrate data rows = source.execute("SELECT * FROM users") prepared = target.prepare( "INSERT INTO users (user_id, username, email, created_at) VALUES (?, ?, ?, ?)" ) for row in rows: target.execute(prepared, [row.user_id, row.username, row.email, row.created_at]) ``` --- **Related**: [README.md](https://www.heliosdb.com/docs/full/reference/protocols/cassandra/readme/) | [CONFIGURATION.md](https://www.heliosdb.com/docs/full/reference/protocols/cassandra/configuration/) | [COMPATIBILITY.md](https://www.heliosdb.com/docs/full/reference/protocols/cassandra/compatibility/)