Cloud Learing

2014年8月19日星期二

cassandra 2.x cql 5 語法學習(六)

Using collections (set、list、map)

學習如何使用 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' 會自動消失

cassandra 2.x cql 5 語法學習(五)

學習 cql 進階的 query 語法
1. create table use clustering column

cqlsh:demo>  


CREATE TABLE timeline (
  day text,
  hour int,
  min int,
  sec int,
  value text,
  PRIMARY KEY (day, hour, min, sec)
);

2. insert example data

cqlsh:demo>  

cqlsh:demo>
INSERT INTO timeline (day, hour , min , sec , value ) VALUES ('12 Jan 2014', 3, 43, 12, 'event1');

cqlsh:demo>
INSERT INTO timeline (day, hour , min , sec , value ) VALUES ('12 Jan 2014', 3, 52, 58, 'event2');

cqlsh:demo>
INSERT INTO timeline (day, hour , min , sec , value ) VALUES ('12 Jan 2014', 4, 37, 01, 'event3');

cqlsh:demo>
INSERT INTO timeline (day, hour , min , sec , value ) VALUES ('12 Jan 2014', 4, 37, 01, 'event3');

cqlsh:demo>
INSERT INTO timeline (day, hour , min , sec , value ) VALUES ('12 Jan 2014', 6, 00, 34, 'event4');

cqlsh:demo>
SELECT * FROM timeline;


 day         | hour | min | sec | value
-------------+------+-----+-----+--------
 12 Jan 2014 |    3 |  43 |  12 | event1
 12 Jan 2014 |    3 |  52 |  58 | event2
 12 Jan 2014 |    4 |  37 |   1 | event3
 12 Jan 2014 |    6 |   0 |  34 | event4

(4 rows)

3. Slicing over partition rows

cqlsh:demo> 

SELECT * FROM timeline WHERE day='12 Jan 2014'
AND (hour, min) >= (3, 50)
AND (hour, min, sec) <= (4, 37, 30);

 day         | hour | min | sec | value
-------------+------+-----+-----+--------
 12 Jan 2014 |    3 |  52 |  58 | event2
 12 Jan 2014 |    4 |  37 |   1 | event3

(2 rows)

4. 查詢該筆資料的寫入的時間

cqlsh:demo>

select writetime(value) from timeline where hour = 6 allow filtering;

 writetime_value
------------------
 1408501327276126

(1 rows)

cassandra 2.x cql 5 語法學習(四)

學習如何在 table 上index的使用及新增 column

1. create table

cqlsh:demo>  

CREATE TABLE playlists (
  id uuid,
  song_order int,
  song_id uuid,
  title text,
  album text,
  artist text,
  PRIMARY KEY (id, song_order ) );

2. insert data to playlists

cqlsh:demo>  

INSERT INTO playlists (id, song_order, song_id, title, artist, album)
VALUES (
62c36092-82a1-3a00-93d1-46196ee77204, 4, 7db1a490-5878-11e2-bcfd-0800200c9a66,
'Ojo Rojo', 'Fu Manchu', 'No One Rides for Free');

cqlsh:demo> 

INSERT INTO playlists (id, song_order, song_id, title, artist, album)
VALUES (
88c36092-82a1-3a00-93d1-46196ee77204, 7, 7db1a490-5878-11e2-bcfd-0800200c9a88,
'測試', '未知', 'No One Rides for Free');

cqlsh:demo> 

select * from playlists;


 id                                   | song_order | album                 | artist    | song_id                              | title
--------------------------------------+------------+-----------------------+-----------+--------------------------------------+----------
 62c36092-82a1-3a00-93d1-46196ee77204 |          4 | No One Rides for Free | Fu Manchu | 7db1a490-5878-11e2-bcfd-0800200c9a66 | Ojo Rojo
 88c36092-82a1-3a00-93d1-46196ee77204 |          7 | No One Rides for Free |      未知 | 7db1a490-5878-11e2-bcfd-0800200c9a88 |     測試

(2 rows)

3. create index on artist

cqlsh:demo> 

CREATE INDEX ON playlists(artist );

cqlsh:demo>

SELECT * FROM playlists WHERE artist = 'Fu Manchu';

 id                                   | song_order | album                 | artist    | song_id                              | title
--------------------------------------+------------+-----------------------+-----------+--------------------------------------+----------
 62c36092-82a1-3a00-93d1-46196ee77204 |          4 | No One Rides for Free | Fu Manchu | 7db1a490-5878-11e2-bcfd-0800200c9a66 | Ojo Rojo

若沒有 create index 的話，無法直接使用 where 的條件做 query 可以 drop index 看看再做query, 看看結果會如何？

cqlsh:demo> 

DROP INDEX playlists_artist_idx ;

cqlsh:demo>
SELECT * FROM playlists WHERE artist = 'Fu Manchu';

code=2200 [Invalid query] message="No indexed columns present in by-columns clause with Equal operator"

會產生 code=2200 error, 所以記得要加上 index 才可做進階的query

cqlsh:demo> 
CREATE INDEX ON playlists(artist );

4. 基本上 order by 及 limit 都跟 sql 語法雷同, 不在此多做說明

cqlsh:demo> 

SELECT * FROM playlists WHERE id = 62c36092-82a1-3a00-93d1-46196ee77204
ORDER BY song_order DESC LIMIT 2;


 id                                   | song_order | album                 | artist    | song_id                              | title
--------------------------------------+------------+-----------------------+-----------+--------------------------------------+----------
 62c36092-82a1-3a00-93d1-46196ee77204 |          4 | No One Rides for Free | Fu Manchu | 7db1a490-5878-11e2-bcfd-0800200c9a66 | Ojo Rojo

(1 rows)

5. 在 table 中新增 Collection columns

cqlsh:demo> 

ALTER TABLE playlists ADD tags set<text>;

此時可以用 desc 來檢測一下 table 的 schema 是否有跟著改變

cqlsh:demo> 

desc TABLE playlists 

cqlsh:demo> 

CREATE TABLE demo.playlists (
    id uuid,
    song_order int,
    album text,
    artist text,
    song_id uuid,
    tags setset<text>,
    title text,
    PRIMARY KEY (id, song_order)
.
.
.

從 schema 中可以看出，table playlists 多新增了一個 colume tags
6. 更新新增欄位的 value 語法

cqlsh:demo> 

UPDATE playlists  set tags = tags + {'2013'} where id = 62c36092-82a1-3a00-93d1-46196ee77204 and song_order = 4;
UPDATE playlists  set tags = tags + {'covers'} where id = 62c36092-82a1-3a00-93d1-46196ee77204 and song_order = 4;

select * from playlists ;

 id                                   | song_order | album                 | artist    | song_id                              | tags               | title
--------------------------------------+------------+-----------------------+-----------+--------------------------------------+--------------------+----------
 62c36092-82a1-3a00-93d1-46196ee77204 |          4 | No One Rides for Free | Fu Manchu | 7db1a490-5878-11e2-bcfd-0800200c9a66 | {'2013', 'covers'} | Ojo Rojo
 88c36092-82a1-3a00-93d1-46196ee77204 |          7 | No One Rides for Free |      未知 | 7db1a490-5878-11e2-bcfd-0800200c9a88 |               null |     測試

(2 rows)

可以看到欄位多了一個 tabs, 新增的 data 也有 show 出來。

7. 更新欄位名稱語法

cqlsh:demo> 

ALTER TABLE playlists RENAME id TO uid;

cqlsh:demo>

desc TABLE playlists ;

CREATE TABLE demo.playlists (
    uid uuid,
    song_order int,
    album text,
    artist text,
    song_id uuid,
    tags set,
    title text,
    PRIMARY KEY (uid, song_order)
.
.
.

2014年8月17日星期日

cassandra 2.x cql 5 語法學習(三)

1.TTL(time to live) 的使用方式

cqlsh:demo>  

CREATE TABLE clicks (
    userid uuid,
    url text,
    date timestamp,
    name text,
    PRIMARY KEY (userid, url)
);

新增一筆　data 並設定該筆資料有效時間為　8（4800s）分鐘

cqlsh:demo>  

INSERT INTO clicks (userid, url, date, name)
    VALUES (
        3715e600-2eb0-11e2-81c1-0800200c9a66,
        'http://abc.org',
        '2013-10-09', 
        'Mary'
    )
USING TTL 4800;

檢示該筆 data 的時效性

cqlsh:demo>  

SELECT TTL (name) from clicks WHERE url = 'http://abc.org' ALLOW FILTERING;

2. Output data to csv

cqlsh:demo>  

COPY clicks (userid, url, date, name) TO 'temp.csv'

cassandra 2.x cql 5 語法學習(二)

如何利用 create type insert multi json object

example json data:

{ 
    username: 'user', 
    userage: 20,
    userprofile: {
        mail: {'user@abc.eamil.com', 'user1@abc.email.com'},
        phone: {1234567, 9876543},
        address: {      
                  street : 'Wu fu Rd.',
                  city : 'KAOHSIUNG CITY'
        }
    }    
}

1. create type address

cqlsh:demo>
CREATE TYPE address2 (
      street text,
      city text
  );

2. create type profile

cqlsh:demo>
CREATE TYPE profile (
      mail set<text>,
      phone set<int>,
      address address2 
  );

3. create type user_data for json

cqlsh:demo>
CREATE TYPE user_data (
      username text,
      userage int,
      userprofile profile
  );

4. create table user_profiles2

cqlsh:demo>  
  CREATE TABLE user_profiles2 (
      id int PRIMARY KEY,
      data user_data
  );

5. insert json data

cqlsh:demo>  
  INSERT INTO user_profiles2(id, data)
  VALUES (1,
         { 
            username: 'user', 
            userage: 20,
            userprofile: {
                mail: {'user@abc.eamil.com', 'user1@abc.email.com'},
                phone: {1234567, 9876543},
                address: {      
                    street : 'Wu fu Rd.',
                    city : 'KAOHSIUNG CITY'
                }    
            }
         }
  );

6. select data

cqlsh:demo>  
select * from user_profiles2;


 id | data
----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | {username: 'user', userage: 20, userprofile: {mail: {'user1@abc.email.com', 'user@abc.eamil.com'}, phone: {1234567, 9876543}, address: {street: 'Wu fu Rd.', city: 'KAOHSIUNG CITY'}}}

cqlsh:demo>

or only grab address

cqlsh:demo>  
select data.userprofile.address from user_profiles2 where id = 1;


 data_userprofile_address
-----------------------------------------------
 {street: 'Wu fu Rd.', city: 'KAOHSIUNG CITY'}

cqlsh:demo>

cassandra 2.x cql 5 語法學習(一)

無法查看此摘要。請按這裡查看文章。

2013年10月9日星期三

Eclipse安裝WindowBuilder可支援java 的GUI

eclipse update sites Installation 的方式安裝，步驟可參考 Google Web Toolkit 的
Update Site Installation into Eclipse 3.7 章節

照網站內的介紹即可

或者可以參考此BLOG

基本上安裝 eclipse 內建 Update Site 的版本即可，有需要開發 eclipse RPC 插件需求就安裝 Project 版本，有 GWT 開發需求，或者想嘗試最多功能者，可選擇安裝 Google 版本，或者把缺漏的套件一一補齊，不過對於一般使用者而言，內建版本應該就可以符合需求。

訂閱：文章 (Atom)

2014年8月19日 星期二