在採用Cassandra前你必須要知道的事-CQL怎麼ORDERBY

SQL開發者在查詢完資料後，往往會使用ORDER BY語法對查詢結果作排序調整。Cassandra的排序是對partition中的資料作排序，而且這個排序必須在建立資料表時就先定義好，並不能在查詢時使用任意的欄位改變排序。

Cassandra在建立資料表時定義的partition key決定資料存放的位置，並在每份partion中使用clustering key決定資料的排序，而且資料在寫入Cassandra時就會依據clustering key定義的欄位順序將資料排序好。

以下用員工表範例來作說明：

-- create
CREATE TABLE CASSANDRA.EMPLOYEE (
  deptName text,
  jobgrade text,
  empId text,
  name text,
  gender text,
  primary key(deptName, jobgrade, gender, empId)
) WITH CLUSTERING ORDER BY (jobgrade ASC, gender ASC, empId ASC);

-- 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

在建立資料表時決定資料排序

從以上的範例可以看到定義的clustering key包含了jobgrade、gender和empId，也可以發現資料查詢出來預設就會依照這三個欄位排序。因為Cassandra在執行資料寫入時，就已經將排序完成了，所以查詢出來就是排序後的結果。
像是HR部門的資料，雖然有一筆jobgrade為1的資料後來才寫入，但仍然會被排序在jobgrade為3的資料前面。

我們可以嘗試再寫入一筆測試看看：

INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('Design', '2', '007', 'Grace', 'Female');

SELECT * FROM CASSANDRA.EMPLOYEE WHERE deptname = 'Design';

 deptname | jobgrade | gender | empid | name
----------+----------+--------+-------+-------
   Design |        1 | Female |   004 | Alice
   Design |        2 | Female |   007 | Grace
   Design |        2 |   Male |   003 |  John
   Design |        3 |   Male |   006 | Henry

從上面新增的資料和查詢結果，可以發現這筆資料先依jobgrade被排序在1和3中間，再依gender排序在Male前面。

ORDER BY的使用限制

也因為Cassandra的特性，在下CQL查詢時使用ORDER BY時會有以下幾個限制:

ORDER BY只能支援partition key有給定的情況
ORDER BY的欄位必須被定義在clustering key中
ORDER BY的欄位順序必須符合clustering key定義的順序
ORDER BY的欄位正/逆排方向必須和clustering key定義的欄位正/逆排方向一致

-- 對HR部門的職等作逆排序
SELECT *
  FROM CASSANDRA.EMPLOYEE
 WHERE deptname = 'HR' -- 符合限制1
 ORDER BY jobgrade DESC; -- 符合限制2

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

必須依照clustering key順序

Cassandra可以將原本定義好的排序在查詢時轉向，因此會將資料依jobgrade改成從大到小排序。又因為資料已經依clustering key的欄位順序與方向關係儲存下來了，所以gender和empid會連帶隨jobgrade的排序轉向而一起轉向。

接著再來看下面這支CQL，如果不依clustering key定義的欄位順序排序會發生什麼事：

-- 對HR部門的員工編號與性別逆排序
SELECT *
  FROM CASSANDRA.EMPLOYEE
 WHERE deptname = 'HR' -- 符合限制1
 ORDER BY empid DESC, gender DESC; -- 違反限制2&3 (跳過jobgrade且欄位順序不符)

InvalidRequest: Error from server: code=2200 [Invalid query] message="Order by currently only supports the ordering of columns following their declared order in the PRIMARY KEY"

SELECT *
  FROM CASSANDRA.EMPLOYEE
 WHERE deptname = 'HR' -- 符合限制1
   AND jobgrade = '1' 
ORDER BY gender DESC, empid DESC;  -- 符合限制2&3 (已綁定jobgrade且剩下欄位順序正確)

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

如果想對員工編號與性別作逆排序，這時會違反clustering key的順序，就會看到Cassandra跳出Order by只支援PRIMARY KEY宣告的欄位順序的錯誤訊息。因為Cassandra在建立資料表時就已經決定好資料儲存的順序，除非對前面順序的clustering key下filter否則不能跳過，並且排序的欄位順序須符合clustering key的定義。

必須符合clustering key排序方向

接著再來看下面這支CQL，如果欄位正/逆排方向和clustering key定義的欄位正/逆排方向不一致會發生什麼事：

SELECT *
  FROM CASSANDRA.EMPLOYEE
 WHERE deptname = 'HR' -- 符合限制1
ORDER BY jobgrade DESC, gender ASC, empid DESC;  -- 符合限制2&3但違反限制4

InvalidRequest: Error from server: code=2200 [Invalid query] message="Unsupported order by relation"

SELECT *
  FROM CASSANDRA.EMPLOYEE
 WHERE deptname = 'HR' -- 符合限制1
ORDER BY jobgrade DESC, gender DESC, empid DESC;  -- 符合限制2&3&4

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

因為建資料表時已經決定每個欄位的排序方向組合，因為在查詢時如果要作排序轉向，就必須要滿足每個欄位的方向都作轉向。以這個資料表為例，三個欄位宣告為ASC，在排序轉向時就必須都要是DESC，否則Cassandra是無法把順序排出來的。

如果排序結果又沒辦法透過CQL達成的話，只能重建新的資料表，或是將資料表讀進程式裡面再作排序了。

為了讓資料查詢的速度可以提升，Cassandra嚴格限制資料排序都必須要預先設計與定義，即查詢出來就是排序好的結果，而不是在查詢時才作排序。正因為Cassandra的data modeling是query-driven的，必須先分析未來資料查詢的需求包含查詢欄位、排序順序，這可能會和RDBMS的使用經驗不同，也是在使用Cassandra前必須要有的思維轉換。

參考資料:

Cassandra Data Modeling - Sorting is a design decision

DataStax document - Using compound primary keys and sorting results

Dear DataStax Episode 18: How can you order by any field in Cassandra?