Cassandra Data Manipulation (Insert, Update, Delete) using `cqlsh`

In previous section we learn creating user, keyspaces and managed table. Now we will learning how to insert, update, and deleting data in cassandra using cassandra.Operation DML ( Data Manipulation Language) similar to RDMS (Relational Database Management System). Using Keyword INSERT INTO for inserting data, keyword UPDATE table_name SET columns WHERE conditional for updating data, keyword DELETE FROM table_name WHERE conditional for deleting data.

Insert Data

Example Syntax

INSERT INTO keyspace_name.table_name
  ( identifier, column_name...)
  VALUES ( value, value ... ) IF NOT EXISTS
  USING option AND option

example insert data

cassandra@cqlsh:putracode> INSERT INTO customers(id, customer_addresss, customer_birthday, customer_deposit, customer_email, customer_grade, customer_name, customer_point) VALUES(uuid(),'Gatot Subroto Street 18','2015-06-18',1000000.8021,'nagita.wishly@putracode.com',21,'Nagita Wishly',2103.33);

cassandra@cqlsh:putracode> select customer_name,customer_birthday,customer_addresss from customers;
 customer_name | customer_birthday | customer_addresss
---------------+-------------------+-------------------------
 Nagita Wishly |        2015-06-18 | Gatot Subroto Street 18

Insert Data With Options (TTL and TIMESTAMP)

• TTL ( Time to Live) in seconds, data will deleted in seconds
• TIMESTAMP

Insert Data Using TTL

TTL input is in seconds. TTL column values are automatically marked as deleted (with a tombstone) after the requested amount of time has expired. TTL marks the inserted values, not the column itself, for expiration. Any subsequent update of the column resets the TTL to the TTL specified in the update. By default, values never expire. You cannot set data in a counter column to expire

cassandra@cqlsh:putracode> INSERT INTO customers(id,customer_name,customer_addresss) VALUES(uuid(),'Customer Will Be Delete in 10 Seconds','Gatot Subroto Strett') USING TTL 10;
cassandra@cqlsh:putracode> select customer_name,customer_birthday,customer_addresss from customers;

 customer_name                         | customer_birthday | customer_addresss
---------------------------------------+-------------------+-------------------------
 Customer Will Be Delete in 10 Seconds |              null |    Gatot Subroto Strett
                         Nagita Wishly |        2015-06-18 | Gatot Subroto Street 18

(2 rows)
cassandra@cqlsh:putracode>
cassandra@cqlsh:putracode> select customer_name,customer_birthday,customer_addresss from customers;
 customer_name | customer_birthday | customer_addresss
---------------+-------------------+-------------------------
 Nagita Wishly |        2015-06-18 | Gatot Subroto Street 18
(1 rows)

Insert Data Using spesific TIMESTAMP(writetime())

The TIMESTAMP input is in microseconds. If not specified, the time (in microseconds) that the write occurred to the column is used.
Example

cassandra@cqlsh:putracode> INSERT INTO customers(id, customer_addresss, customer_birthday, customer_deposit, customer_email, customer_grade, customer_name, customer_point) VALUES(uuid(),'Gatot Subroto Street 18','2015-06-18',1000000.8021,'nagita.wishly@putracode.com',21,'Nagita Wishly',2103.33);
cassandra@cqlsh:putracode> INSERT INTO customers(id,customer_addresss) values(uuid(),'check the write_time') using TIMESTAMP 1514954917 ;
cassandra@cqlsh:putracode> SELECT id,customer_addresss FROM customers;
 id                                   | customer_addresss
--------------------------------------+-------------------------
 51a33c78-b220-4cd5-b656-2d9ffba08525 | Gatot Subroto Street 18
 7f44278e-2035-4375-b7c5-c921d71b08f5 |    check the write_time
(2 rows)
cassandra@cqlsh:putracode>

Check the writetime

cassandra@cqlsh:putracode> SELECT WRITETIME(CUSTOMER_ADDRESSS), id,customer_addresss FROM customers;
 writetime(customer_addresss) | id                                   | customer_addresss
------------------------------+--------------------------------------+-------------------------
             1515041320143727 | 51a33c78-b220-4cd5-b656-2d9ffba08525 | Gatot Subroto Street 18
                   1514954917 | 7f44278e-2035-4375-b7c5-c921d71b08f5 |    check the write_time
(2 rows)

if we dont spesific TIMESTAMP cassandra will created it automaticly
example value 1515041320143727

Insert Data Collections

when we need insert data collection type, we using formating JSON
1. List
example [‘value’,’value’]
2. Map
example {‘key1′:’value’,’key2′:’value 2′}
3. Set
example {‘value’,’value2′}

Example code

INSERT INTO customers_with_collections
(customer_id,
customer_addresss,
customer_birthday,
customer_emails,
customer_products,
customer_score
)
values(
uuid(),
'Gatot Subroto',
'2015-06-18',
{'putracode@gmail.com','cassandra@gmail.com'},
['iphone','imac','iphone'],
{'event1':'Perpect','event2':'bad'}
);

Update Data

Update row in cassandra db using is a upsert operation, upsert is a change in the database that updates specified column in a row if the column exists or inserts the new column if it does not exists.

Synopsis syntax

UPDATE [keyspace_name.] table_name
[USING TTL time_value | USING TIMESTAMP timestamp_value]
SET assignment [, assignment] . . . 
WHERE row_specification
[IF EXISTS | IF condition [AND condition] . . .] ;

Example

UPDATE customers 
set customer_addresss='Gatot subroto 21' 
where id=51a33c78-b220-4cd5-b656-2d9ffba08525;

 id                          | customer_addresss    | customer_grade
-----------------------------+----------------------+----------------
 51a33c78-b220-4cd5-b656-... |     Gatot subroto 20 | 21
 7f44278e-2035-4375-b7c5-... | check the write_time | null

Update Collections type

1. List
   set list_column=['data','data2','data3'] //replace datas
set list_column=list_column+ ['data4'] // add new data
set list_column=list_column+ ['data4'] // delete in data in list
set list_column[3] =['new data'] // replace data using index
2. Map
   set map_column={'newKey':'newValue'} //replace data
set map_column=map_column + {'newKey':'newValue'} // add new value
3. Set
   set set_column ={} //remove all set datas
set set_column={'newDataSet'} // replace set data
set set_column=set_column + {'newData2'} // add new data in set
set set_column=set_column - {'newData2'} // remove spesific data

example code

UPDATE customers_with_collections
SET
customer_emails=customer_emails+{'newEmail@gmail.com'},
customer_products=['phone','computer','website'],
customer_score =customer_score+{'event3':'perfect'}
WHERE customer_id=3a3b40e0-94ca-46dd-889f-392c9ff366b5;

//select data first
cassandra@cqlsh:putracode> SELECT CUSTOMER_ID,customer_emails, customer_products, customer_score from customers_with_collections ;
 customer_id                          | customer_emails                                | customer_products            | customer_score
--------------------------------------+------------------------------------------------+------------------------------+----------------------------------------
 3a3b40e0-94ca-46dd-889f-392c9ff366b5 | {'cassandra@gmail.com', 'putracode@gmail.com'} | ['iphone', 'imac', 'iphone'] | {'event1': 'Perpect', 'event2': 'bad'}

(1 rows)
// RESULT
cassandra@cqlsh:putracode> SELECT CUSTOMER_ID,customer_emails, customer_products, customer_score from customers_with_collections ;
 customer_id                          | customer_emails                                                      | customer_products                | customer_score
--------------------------------------+----------------------------------------------------------------------+----------------------------------+-------------------------------------------------------------
 3a3b40e0-94ca-46dd-889f-392c9ff366b5 | {'cassandra@gmail.com', 'newEmail@gmail.com', 'putracode@gmail.com'} | ['phone', 'computer', 'website'] | {'event1': 'Perpect', 'event2': 'bad', 'event3': 'perfect'}

Deleting Data

Synopsys syntax

DELETE [column_name (term)][, ...]
FROM [keyspace_name.] table_name 
[USING TIMESTAMP timestamp_value]
WHERE PK_column_conditions 
[IF EXISTS | IF static_column_conditions]

example

// Deleting spesific data using filtering
DELETE FROM customers where id=7f44278e-2035-4375-b7c5-c921d71b08f5;
// Deleting all data using truncate
TRUNCATE TABLE customers;