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
- 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
- Map
set map_column={'newKey':'newValue'} //replace data
set map_column=map_column + {'newKey':'newValue'} // add new value
- 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;