學習如何使用 cassandra 的內鍵型態
限制：
．The maximum size of an item in a collection is 64K.
．Keep collections small to prevent delays during querying because Cassandra reads a collection in its entirety. The collection is not paged internally.
．Never insert more than 64K items in a collection.
If you insert more than 64K items into a collection, only 64K of them will be queryable, resulting in data loss.
1. create table with set
cqlsh:demo> CREATE TABLE users ( user_id text PRIMARY KEY, first_name text, last_name text, emails set<text> );
2. insert data
cqlsh:demo>
INSERT INTO users (user_id, first_name, last_name, emails)
VALUES('frodo', 'Frodo', 'Baggins', {'f@baggins.com',
'baggins@gmail.com'});
cqlsh:demo> select * from users;
user_id | emails | first_name | last_name
---------+----------------------------------------+------------+-----------
frodo | {'baggins@gmail.com', 'f@baggins.com'} | Frodo | Baggins
(1 rows)
3. 使用 '+' 新增 email (set) 欄位的 valeus
cqlsh:demo>
UPDATE users
SET emails = emails + {'fb@friendsofmordor.org'} WHERE user_id = 'frodo';
cqlsh:demo> select * from users;
user_id | emails | first_name | last_name
---------+------------------------------------------------------------------+------------+-----------
frodo | {'baggins@gmail.com', 'f@baggins.com', 'fb@friendsofmordor.org'} | Frodo | Baggins
(1 rows)
4. 使用 '-' 刪除 email (set) 欄位的 valeus
cqlsh:demo>
UPDATE users
SET emails = emails - {'fb@friendsofmordor.org'} WHERE user_id = 'frodo';
cqlsh:demo> select * from users;
user_id | emails | first_name | last_name
---------+----------------------------------------+------------+-----------
frodo | {'baggins@gmail.com', 'f@baggins.com'} | Frodo | Baggins
(1 rows)
若要清空 email (set) 中的資料，可使用以下方法
cqlsh:demo>
UPDATE users SET emails = {} WHERE user_id = 'frodo';
cqlsh:demo>
DELETE emails FROM users WHERE user_id = 'frodo';
// 以上兩種方法都會有相同的結果
cqlsh:demo> select * from users;
user_id | emails | first_name | last_name
---------+--------+------------+-----------
frodo | null | Frodo | Baggins
(1 rows)
5. 新增 top_places (list) 欄位
cqlsh:demo> ALTER TABLE users ADD top_places list<text>;
6. 在 top_places (list) 新增data
cqlsh:demo> UPDATE users SET top_places = [ 'rivendell', 'rohan' ] WHERE user_id = 'frodo'; cqlsh:demo> select * from users; user_id | emails | first_name | last_name | top_places ---------+--------+------------+-----------+------------------------ frodo | null | Frodo | Baggins | ['rivendell', 'rohan'] (1 rows)
在 top_places (list) 中, the head of list 插入新的資料
cqlsh:demo> UPDATE users SET top_places = [ 'the shire' ] + top_places WHERE user_id = 'frodo'; cqlsh:demo> select * from users; user_id | emails | first_name | last_name | top_places ---------+--------+------------+-----------+------------------------------------- frodo | null | Frodo | Baggins | ['the shire', 'rivendell', 'rohan'] (1 rows)
在 top_places (list) 中, the tail of list 插入新的資料
cqlsh:demo> UPDATE users SET top_places = top_places + [ 'mordor' ] WHERE user_id = 'frodo'; cqlsh:demo> select * from users; user_id | emails | first_name | last_name | top_places ---------+--------+------------+-----------+----------------------------------------------- frodo | null | Frodo | Baggins | ['the shire', 'rivendell', 'rohan', 'mordor'] (1 rows)
更新 list 中的 value
cqlsh:demo> UPDATE users SET top_places[2] = 'riddermark' WHERE user_id = 'frodo'; cqlsh:demo> select * from users; user_id | emails | first_name | last_name | top_places ---------+--------+------------+-----------+---------------------------------------------------- frodo | null | Frodo | Baggins | ['the shire', 'rivendell', 'riddermark', 'mordor'] (1 rows)
刪除 list 中的 value
cqlsh:demo> DELETE top_places[3] FROM users WHERE user_id = 'frodo'; cqlsh:demo> UPDATE users SET top_places = top_places - ['riddermark'] WHERE user_id = 'frodo'; // 以上兩種方法都可刪除掉 list element cqlsh:demo> select * from users; user_id | emails | first_name | last_name | top_places ---------+--------+------------+-----------+---------------------------- frodo | null | Frodo | Baggins | ['the shire', 'rivendell'] (1 rows)
刪除 list 中 all element
cqlsh:demo> DELETE top_places FROM users WHERE user_id = 'frodo'; cqlsh:demo> UPDATE users SET top_places = [] WHERE user_id = 'frodo'; // 以上兩種方法都可刪除掉 list all element cqlsh:demo> select * from users; user_id | emails | first_name | last_name | top_places ---------+--------+------------+-----------+------------ frodo | null | Frodo | Baggins | null (1 rows)
7. 新增 todo (map) 欄位
cqlsh:demo> ALTER TABLE users ADD todo map<timestamp, text>;
8. 在 todo (map) 新增data
cqlsh:demo>
UPDATE users
SET todo =
{ '2012-9-24' : 'enter mordor',
'2012-10-2 12:00' : 'throw ring into mount doom' }
WHERE user_id = 'frodo';
cqlsh:demo> select * FROM users;
user_id | emails | first_name | last_name | todo | top_places
---------+--------+------------+-----------+--------------------------------------------------------------------------------------------------------+------------
frodo | null | Frodo | Baggins | {'2012-09-24 00:00:00+0800': 'enter mordor', '2012-10-02 12:00:00+0800': 'throw ring into mount doom'} | null
(1 rows)
cqlsh:demo>
INSERT INTO users (user_id, todo) VALUES ( 'New Yord', { '2013-9-22 12:01' : 'birthday wishes to Bilbo', '2013-10-1 18:00' : 'Check into Inn of Prancing Pony' });
cqlsh:demo> select * FROM users;
user_id | emails | first_name | last_name | todo | top_places
----------+--------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------+------------
New Yord | null | null | null | {'2013-09-22 12:01:00+0800': 'birthday wishes to Bilbo', '2013-10-01 18:00:00+0800': 'Check into Inn of Prancing Pony'} | null
frodo | null | Frodo | Baggins | {'2012-09-24 00:00:00+0800': 'enter mordor', '2012-10-02 12:00:00+0800': 'throw ring into mount doom'} | null
(2 rows)
更新 map 中 key 為 '2012-10-2 12:00' 的 value
cqlsh:demo>
UPDATE users SET todo['2012-10-2 12:00'] = 'throw my precious into mount doom'
WHERE user_id = 'frodo';
cqlsh:demo> select * FROM users where user_id = 'frodo';
user_id | emails | first_name | last_name | todo | top_places
---------+--------+------------+-----------+---------------------------------------------------------------------------------------------------------------+------------
frodo | null | Frodo | Baggins | {'2012-09-24 00:00:00+0800': 'enter mordor', '2012-10-02 12:00:00+0800': 'throw my precious into mount doom'} | null
(1 rows)
刪除 map 中 key 為 '2012-9-24' element
cqlsh:demo>
DELETE todo['2012-9-24'] FROM users WHERE user_id = 'frodo';
cqlsh:demo> select * FROM users where user_id = 'frodo';
user_id | emails | first_name | last_name | todo | top_places
---------+--------+------------+-----------+-------------------------------------------------------------------+------------
frodo | null | Frodo | Baggins | {'2012-10-02 12:00:00+0800': 'throw my precious into mount doom'} | null
(1 rows)
新增一個有時效性的 map value
cqlsh:demo>
UPDATE users USING TTL 80 SET todo['2012-10-1'] = 'find water' WHERE user_id = 'frodo';
cqlsh:demo> select * FROM users where user_id = 'frodo';
user_id | emails | first_name | last_name | todo | top_places
---------+--------+------------+-----------+-------------------------------------------------------------------------------------------------------------+------------
frodo | null | Frodo | Baggins | {'2012-10-01 00:00:00+0800': 'find water', '2012-10-02 12:00:00+0800': 'throw my precious into mount doom'} | null
(1 rows)
// 當 80 秒過後 該筆 data '2012-10-01 00:00:00+0800': 'find water' 會自動消失
沒有留言:
張貼留言