在採用Cassandra前你必須要知道的事-CQL不是SQL

過去兩年的時間投入了數據平台的建置，希望可以打造一個所謂的現代化數據平台(Modern Data Platform)，這中間可是比想像中困難多了。但兩年過去了團隊成員從也是從小貓幾隻，一路成長到十多人的團隊。講到現代化的數據平台，NoSQL的儲存方案肯定是會被考量到的一項技術，在一陣研究和討論後，我們決定採用Cassandra當作在數據平台服務中，擔任NoSQL的主要儲存技術。

在導入的過程中當然沒有這麼順利，特別是對於只用過Oracle或是SQL Server等RDBMS技術，完全沒有碰過NoSQL的開發團隊，大家對於使用Cassandra總是有錯誤的想像，或是在不熟悉與不合適的使用情境下，最後造成大家與Cassandra「不歡而散」。

這篇想開始談談實際使用Cassandra後會因為Cassandra的設計限制遇到的問題，希望大家在一頭熱投入Cassandra的研究前，可以評估使用情境是否適合。我特別推薦在準備DataStax的Cassandra Developer Certification時上到的一堂課，這堂DS220提到許多Cassandra在實在作data modeling會需要知道的技巧和特性。

回到這次想談的，CQL不是SQL。

在初期投入Cassandra技術評估，看到Cassandra支援CQL(Cassandra Query Language)時，肯定會讓RDBMS開發者眼睛一亮，這看起來多麼的熟悉，忍不住在比較NoSQL方案時在Cassandra蓋上一個優勝的章。當然我不能否認當初我們也這麼認為，一直到大家實際使用時，才會發現CQL和SQL有根本上的不同。

不是任意欄位都能FILTER

簡單來說，比起RDBMS的SQL查詢，Cassandra因為本身的設計，所以CQL本來就只能作到有限度的查詢。CQL不能像SQL一樣可以將任意欄位放在where條件執行查詢，而是只能針對設定成partition key和clustering key的欄位進行查詢，甚至對組成clustering key的欄位，還有查詢順序的限制。當然這裡我們就先不提建立secondary index或是materialize view的情況。

拿下面這個簡單的員工資料表來說明。

deptname	jobgrade	gender	emplid	name
HR	1	Male	001	Tom
HR	3	Female	002	Amy
Design	2	Male	003	John
Design	1	Female	004	Alice
HR	1	Male	005	Bob
Design	3	Male	006	Henry

在Cassandra的員工表作了以下設計，如果不說是Cassandra的話，看起來其實就和一般的RDBMS沒有差別。但在Cassandra中下面建立的員工表其實隱含了將deptName設定為partition key，clustering key設定為jobgrade、gender和emplid的意思。以下的語法可以把這個範例建起來，DataStax有提供線上的serverless database可以使用。

-- create
CREATE TABLE CASSANDRA.EMPLOYEE (
  deptName text,
  jobgrade text,
  empId text,
  name text,
  gender text,
  primary key(deptName, jobgrade, gender, empId)
);

-- insert
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('HR', '1', '001', 'Tom', 'Male');
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('HR', '3', '002', 'Amy', 'Female');
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('Design', '2', '003', 'John', 'Male');
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('Design', '1', '004', 'Alice', 'Female');
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('HR', '1', '005', 'Mary', 'Female');
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('Design', '3', '006', 'Henry', 'Male');

-- select
SELECT * FROM CASSANDRA.EMPLOYEE;

deptname | jobgrade | gender | empid | name
----------+----------+--------+-------+-------
       HR |        1 | Female |   005 |  Mary
       HR |        1 |   Male |   001 |   Tom
       HR |        3 | Female |   002 |   Amy
   Design |        1 | Female |   004 | Alice
   Design |        2 |   Male |   003 |  John
   Design |        3 |   Male |   006 | Henry

假設在RDBMS裡用以上建立的資料表，舉幾種查詢情境如下:

-- 查詢jobgrade為1的員工資料
SELECT * 
  FROM EMPLOYEE
 WHERE jobgrade = '1'

deptname | jobgrade | gender | empid | name
----------+----------+--------+-------+-------
       HR |        1 |   Male |   001 |   Tom
       HR |        1 | Female |   005 |  Mary
   Design |        1 | Female |   004 | Alice

-- 查詢deptname為HR, gender為Male的名字
SELECT name 
  FROM EMPLOYEE
 WHERE deptname = 'HR'
   AND gender = 'Male'

 name
------
  Tom

 --查詢deptname為HR, jobgrade為1的員工資料 
 SELECT *
  FROM EMPLOYEE
 WHERE deptname = 'HR'
   AND jobgrade = '1'

 deptname | jobgrade | gender | empid | name
----------+----------+--------+-------+------
       HR |        1 | Female |   005 | Mary
       HR |        1 |   Male |   001 |  Tom

以上看起來很直覺的查詢，如果是在Cassandra的世界裡，執行第一支CQL會得到以下的錯誤：

SELECT * 
  FROM CASSANDRA.EMPLOYEE
 WHERE jobgrade = '1'

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

Cassandra會將資料依partitiony key分散放到不同的partition存放，所以在CQL查詢中的第一個條件必須要帶partition key(也就是deptname)，Cassandra才能直接到指定的partition取回資料，用來加速資料查詢的速度。因此在不給定partition key條件下直接用jobgrade作filter是不允許的。

第二支CQL執行則會得到以下的錯誤：

SELECT name 
  FROM CASSANDRA.EMPLOYEE
 WHERE deptname = 'HR'
   AND gender = 'Male'

InvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY column "gender" cannot be restricted as preceding column "jobgrade" is not restricted"

因為Cassandra的世界裡，會將一個partition內部的資料依據clustering key作排序。以員工資料表為例，會以deptname切partition後，再依序用jobgrade、gender和emplid排序。

Casssandra透過預先排序的方式，在查詢資料時就可以得到排序結果，和SQL查詢完才作order by不同。因此Cassandra嚴格限制clustering key的欄位必須要依順序作filter，所以跳過jobgrade先查詢gender是不允許的。

第三支CQL則因為符合partition和clustering限制所以可以成功執行。

 SELECT *
  FROM CASSANDRA.EMPLOYEE
 WHERE deptname = 'HR'
   AND jobgrade = '1'

 deptname | jobgrade | gender | empid | name
----------+----------+--------+-------+------
       HR |        1 | Female |   005 | Mary
       HR |        1 |   Male |   001 |  Tom

由以上可以發現，CQL雖然和SQL語法非常相似，但本質上Cassandra就是NoSQL資料庫，也有本身的設計限制。因此對於SQL使用者來說，千萬不要因為看到很像SQL的語法，就覺得可以很容易的上手使用！