Cassandra MLB 狀況劇 (1) 新增欄位和索引

接續上一篇今天要做的事情還是把下面的表格選幾個欄位轉存起來，不過這次多了些額外的操作，完整資料來自https://www.fangraphs.com/teams/redsox)：

shell 操作流程：

1. 建立一個名為 redsox 的 keyspace(class='SimpleStrategy', replication_factor=1)
2. 建立一個名為 pitchers 的 table，table 包含 Name, G, GS 和 IP 等欄位。
3. 我們把名字設成主鍵(假設名字沒有重複)
4. 匯入資料
5. 新增一個 Age 欄位
6. 更新 Age 欄位的資料
7. 新增 secondary key
8. 查看資料寫入的時間
9. 刪除 table
10. 刪除 keyspace

1. Creating keyspace:

新增 keyspace:

CREATE KEYSPACE redsox WITH replication = {
  'class': 'SimpleStrategy', 
  'replication_factor': 1 
};

看一下 keyspace 屬性：

DESCRIBE KEYSPACE redsox;

使用 redsox:

USE redsox ;

2. Creating table:

建立個表單，並查看一下欄位：

CREATE TABLE pitchers ( 
  Name text, 
  G tinyint, 
  GS tinyint, 
  IP float, 
  PRIMARY KEY(Name)
);

SELECT * FROM pitchers ;

 name | g | gs | ip
------+---+----+----

照慣例確認一下表單屬性：

DESCRIBE pitchers ;

3. Inserting data:

插入幾筆資料：

INSERT INTO pitchers (name, g, gs, ip) 
VALUES ('Chris Sale', 23, 23, 146.0);

INSERT INTO pitchers (name, g, gs, ip) 
VALUES ('Rick Porcello', 26, 26, 158.2);

SELECT * FROM redsox.pitchers ;

 name          | g  | gs | ip
---------------+----+----+-------
 Rick Porcello | 26 | 26 | 158.2
    Chris Sale | 23 | 23 |   146

4. Adding column:

oops 我們忘記加入 age 欄位。修改 pitchers，新增一個 age 欄位類別是 tinyint：

ALTER TABLE pitchers ADD age tinyint ;

SELECT * FROM pitchers ;

 name          | age  | g  | gs | ip
---------------+------+----+----+-------
 Rick Porcello | null | 26 | 26 | 158.2
    Chris Sale | null | 23 | 23 |   146

5. Update age:

來更新我們的 age 欄位啦。

UPDATE pitchers SET age = 29 WHERE name = 'Chris Sale' ;
UPDATE pitchers SET age = 29 WHERE name = 'Rick Porcello' ;

SELECT * FROM pitchers ;

 name          | age | g  | gs | ip
---------------+-----+----+----+-------
 Rick Porcello |  29 | 26 | 26 | 158.2
    Chris Sale |  29 | 23 | 23 |   146

6. Create index:

Secondary key 和 Primary key 一樣是索引的一種，只有設了 key 的 column 才能被列為查找的條件。

SELECT name, ip FROM pitchers WHERE name = 'Rick Porcello' ;

 name          | ip
---------------+-------
 Rick Porcello | 158.2

上面的查找行為是允許的，因為 name 是 key。

如果我們試圖列出局數超過 150 局的投手資料是不被允許的，因為 ip 不是 key(index)。

SELECT name, ip FROM pitchers WHERE ip > 150 ;

幫 ip 建立索引會將 ip 變成可查找的欄，但不要試圖幫所有欄位都加上索引，那樣會增加資料寫入的時間。

CREATE INDEX on pitchers(ip) ;

再來查詢一次，句尾加上 ALLOW FILTERING 代表我們允許 ip > 150 這個過濾。 對大型資料庫而言這種過濾等於是把資料庫裡的 data 都問一遍，所以程式預設會禁止這種問法。

SELECT name, ip FROM pitchers WHERE ip > 150 ALLOW FILTERING ;

7. Timestamp:

前面我們用 select *，只會列出我們定義的欄位，事實上我們有些隱藏欄位沒有顯示。

writetime() 用於取得資料的寫入時間，下面是查找寫入時間的方式。

SELECT name, writetime(ip), writetime(age) FROM pitchers ;

 name          | writetime(ip)    | writetime(age)
---------------+------------------+------------------
 Rick Porcello | 1534902336231691 | 1534903293027955
    Chris Sale | 1534902277776277 | 1534903258692364