Drake's

Text-to-SQL（Text2SQL）是一種自然語言處理（NLP）技術，旨在將自然語言文本自動轉換為SQL查詢語句。這項技術的核心在於將用戶輸入的自然語言描述轉換為結構化的SQL查詢，使這些查詢可以在關聯式資料庫中執行。

在現代化的數據平台中，通常會提供自助服務環境，讓使用者在獲得資料存取權限後，可以自行進行數據分析。然而，有時使用者所面臨的問題並不需要將資料匯入Power BI或Tableau等BI工具進行可視化分析，而只是希望在資料中透過查詢、篩選、聚合運算找到答案。

在這種情境下，對於不熟悉SQL的使用者來說，Text-to-SQL服務是一個很好的解決方案，因為它可以幫助使用者輕鬆地將自然語言轉換為SQL查詢，從而快速獲得所需的數據。

拜大型語言模型(LLM)的普及，Text-to-SQL可以很容易的透過LLM完成。以下透過建立Chinook這個SQLite範例資料庫來當作Text-to-SQL任務中查詢用的關聯式資料庫：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

import sqlite3
import requests

# 下載 SQL 腳本
url = "https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sql"
response = requests.get(url)

# 儲存 SQL 腳本到本地檔案
with open('Chinook_Sqlite.sql', 'w') as f:
    f.write(response.text)

# 連接到 SQLite 資料庫 (會自動建立資料庫檔案)
conn = sqlite3.connect('Chinook.db')
cursor = conn.cursor()

# 執行 SQL 腳本來建立資料庫
with open('Chinook_Sqlite.sql', 'r') as f:
    cursor.executescript(f.read())

# 提交變更並關閉連接
conn.commit()
conn.close()

查詢資料表清單可以看到這個範例資料庫裡面包含了總共有11張資料表

1
2
3
4

con = sqlite3.connect("Chinook.db")
cursorObj = con.cursor()
cursorObj.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursorObj.fetchall())

1

>>> [('Album',), ('Artist',), ('Customer',), ('Employee',), ('Genre',), ('Invoice',), ('InvoiceLine',), ('MediaType',), ('Playlist',), ('PlaylistTrack',), ('Track',)]

以Employee這張資料表為例子，可以將資料表名稱和欄位清單提供給Gemini，並請Gemini將問題轉成SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

import os
import getpass
import google.generativeai as genai

prompt = """
Please convert question into SQL query by following table and column information.

table: Employee
column: EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate, Address, City, State, Country, PostalCode, Phone, Fax, Email

Question: How many employees come from Canada?
"""

# Configure Gemini
GEMINI_API_KEY = getpass.getpass() # input your gemini api key
genai.configure(api_key=GEMINI_API_KEY)

model = genai.GenerativeModel('gemini-1.5-flash')
response = model.generate_content(prompt)

print(response.text)

可以看到Gemini依據prompt生成了一段SQL，直接讀這段SQL也有符合前面給的問題。

1
2
3
4

>>>
SELECT COUNT(*) AS "Number of Canadian Employees"
FROM Employee
WHERE Country = 'Canada';

直接把這段SQL拿去查詢資料庫也能得到正確答案，所以在提供資料表和欄位清單的情況下，LLM確實可以生成出能執行的SQL。

1
2
3
4
5

cursorObj.execute("""
SELECT COUNT(*) AS "Number of Canadian Employees"
FROM Employee
WHERE Country = 'Canada';""")
print(cursorObj.fetchall())

1

>>> [(8,)]

到這裡可以發現，如果要作出一個簡單的Text-to-SQL服務，只要依據使用者的問題找到正確的資料表，接著將提示詞、資料表資訊與問題提供給LLM就可以詠唱出一段「可能」可以執行的SQL語法。

當專案已經準備要導入Cassandra時，可能是專案已經遇到某些困難與瓶頸，並已經考慮採用Cassandra是一個正確的使用情境。以下是使用Cassandra: The Definitive Guide, Third Edition其中第十五章的部份段落來說明如何將資料表轉移到Cassandra上。

要將關聯式資料庫轉到Cassandra上，第一個遇到的問題就是如何將原本的資料表關係轉移到Cassandra上。一種方式是將既有的資料模型作直接轉譯(Direct Translation)，以下會舉旅館預約系統來說明如何對資料模型作直接轉譯。

在將關聯式資料庫的資料表直接轉譯到Cassandra上，會分成二個部份：

1. 轉譯實體(Translation Entities)
2. 轉譯關聯(Translation Relationships)

轉譯實體(Translation Entities)

1. 根據主鍵設定建立相似鍵值的Cassandra資料表
   首先找到原本關聯式資料模型中的實體。比如說Hotel這張資料表會被預約系統查詢使用，這時就可以在Cassandra也建立一個具有相似鍵值的資料表，即為上圖的hotels資料表，其中參照原本的設定將hotel_id設定成主鍵(在這裡亦為partition key)。
2. 建立反正規化資料表滿足不同查詢組合
   因為並不是所有的查詢都是透過hotel_id查詢，假設要滿足使用name來查詢hotel資料的行為，那就需要建立另一張以name欄位為partition key的資料表叫hotels_by_name。這裡的主鍵為name和hotel_id組合，其中hotel_id為clustering key。
3. 使用UDT(User-defined Type)描述複雜的型別資料
   在資料表中的address可以是一個單純的字串表示地址，也可以是一個自定義的型別。因為在Cassandra中可以透過自定義型別來描述複雜的型別資料，以自定義型別Address來說，其中可以包含street、city等多個欄位的屬性。自定義型別並不是必須的，可以依系統的設計需求來考慮是否採用。

轉譯關聯(Translation Relationships)

將實體的資料表轉譯到Cassandra的資料表後，接著就可以開始看這些實體資料表間的關聯性。以RoomToAmenity來說，這張為其實是描述Room和Amenity之間的關聯，而這類的關聯資料表常常沒有自己的屬性，可以看到RoomID和AmenityID這兩個欄位組成的主鍵，同時也分別是連接到Room和Amenity的外部鍵。

1. 將實體資料表間的關聯性建立Cassandra資料表
   這些被用來表達實體間關聯的資料表，時常伴隨著實體資料表作使用。以amenities_by_room為例，這張資料表的主鍵會拿實體資料表中的鍵值作為查詢用的partition key(hotel_id, room_number)。因此在查詢時就可以從hotel_id查詢某一間room_number提供的amenities。
2. 使用UDT描述從屬資料
   以右下角rooms_by_hotel資料表為例，這張表是透過hotel_id查詢所有的room資料，這時可以設計一個amenity自定義型態，並設定amenities欄位是list of amenity型態。這種設計的好處在於可以透過一個查詢就一併查出room相關的amenities資料，不需要分多次查詢。當然UDT的使用也是依系統的設計需求來考慮是否採用。

以上就是採用直接轉譯的方式將原本關聯式資料表轉成Cassandra資料表，這種方式好處在於可以直接對準在原本的資料模型作轉移，一步一步從實體資料表、資料表關聯還有結合應用層的查詢條件將專案作轉移。

關聯式資料庫的使用者在使用Cassandra設計資料模型時，通常第一個會遇到的問題就是不能使用join。Cassandra明確說明不支援join，建議的方式為建立一個反正規化(Denormalization)的資料表。

什麼叫反正規化(Denormalization)

有別於關聯式資料庫的正規化(Normalization)設計，透過減少資料庫內的資料冗餘(Data Redundancy)和去除相依性來增進資料的一致性。這種方式的缺點在當資料被拆成多個資料表後，依不同使用情境下將資料join起來查詢時，會導致效能不佳。

而反正規化則是相反，反正規化會增加資料冗餘或是對資料進行分組，來得到最佳化的讀取效能。所以在反正規化的實例中，會把預先join完的資料建成一張資料表，這時就會有同一份資料被複製成多張資料表的情況。

怎麼將反正規化應用在資料設計

關聯式資料庫正規化設計

舉DataStax的課程DS220.08範例來說明。這個範例為電影資料庫被設計成videos、users和comments三張資料表。

如果想要依電影標題查詢評論時，可以使用videos的id和comments的video_id將兩張表join起來，這時就可以使用videos的title欄位查詢出對應的comment資訊。

如果要依會員帳號查詢評論時，則是使用users的id和comments的user_id將兩張表join起來，接著就可以使用users的login欄位查詢出該會員帳號留過的評論。

Cassandra反正規化設計

在Cassandra的反正規化資料模型設計會直接建立成兩張資料表，並透過不同的primary key來提供兩種查詢方式。第一張comments_by_video會將video_title設定成partition key，第二張comments_by_user會將user_login設計成partition key。

因為Cassandra會使用partition key決定存放資料的位置，因為在作Cassandra的資料模型設計時，要查詢的欄位都必須被設計成partition key才能達到最佳的查詢效能。以這個例子來說，這兩張表的建立資料表指令如下，可以看的出來兩張資料表幾乎有共通的欄位，只差在partition key的設計不同。

這就是反正規化中對資料作pre-join並且會將資料複製成多張資料表的作法。而目的就是為了要讓查詢的效能可以最佳化，不需要在查詢時面對可能不可控的join結果，或是複雜的join行為帶來的查詢效能低落。

Cassandra Data Modeling - No Join

DS220.08 Denormalization

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- 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的資料前面。

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

1
2
3
4
5
6
7
8
9
10
11

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時會有以下幾個限制:

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

1
2
3
4
5
6
7
8
9
10
11

-- 對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定義的欄位順序排序會發生什麼事：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- 對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定義的欄位正/逆排方向不一致會發生什麼事：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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?

SQL開發者絕對少不了會使用聚合函式對資料作加總、平均、計數和取大小值，而Cassandra也支援了SUM、AVG、COUNT、和MIN/MAX的聚合函式。以下用員工表範例來作說明。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- 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裡用以上建立的資料表，舉幾種查詢情境如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- 依部門計數
SELECT deptname, count(*) 
  FROM EMPLOYEE
 GROUP BY deptname

  deptname | count
----------+-------
       HR |     3
   Design |     3

-- 依職等計數
SELECT jobgrade, count(*) 
  FROM EMPLOYEE
 GROUP BY jobgrade

 jobgrade | count
----------+-------
        1 |     3
        2 |     1
        2 |     2

-- 依部門, 職等計數
SELECT deptname, jobgrade, count(*) 
  FROM EMPLOYEE
 GROUP BY deptname, jobgrade

 deptname | jobgrade | count
----------+----------+-------
       HR |        1 |     2
       HR |        3 |     1
   Design |        1 |     1
   Design |        2 |     1
   Design |        3 |     1

只能用在primary key

如同在Cassandra查詢資料一樣，Cassandra對於使用聚合函式也是有所限制。在使用聚合函式時，只能針對被設定為primary key的欄位使用GROUP BY，也就是聚合函式只能用在被定義為partition key或是clustering key的欄位，而且嚴格限制GROUP BY的順序。以下我們看在Cassandra執行第一支CQL的結果：

1
2
3
4
5
6
7
8
9
10
11
12

-- 依部門計數
SELECT deptname, count(*) 
  FROM CASSANDRA.EMPLOYEE
 GROUP BY deptname

  deptname | count
----------+-------
       HR |     3
   Design |     3

Warnings :
Aggregation query used without partition key

因為deptname在這張表是partition key因此這支CQL可以被執行，但同時Cassandra會附帶警告訊息，表示這個聚合查詢沒有使用partition key。原因在於Cassandra會使用partition key在cluster中存放資料，如果沒有在CQL中使用partition key當作查詢條件，CQL在執行時便會在Cassandra cluster中遍尋(Full Scan)所有的資料，這會造成效能降低與資源浪費。

1
2
3
4
5
6
7
8
9

-- 依部門計數
SELECT deptname, count(*) 
  FROM CASSANDRA.EMPLOYEE
 WHERE deptname = 'HR'
 GROUP BY deptname

 deptname | count
----------+-------
       HR |     3

如果加上deptname當作查詢條件就不會出現警告訊息，這時Cassandra會直接從存放資料的node取出deptname=HR的資料作計數，而不需要遍尋整座Cassandra cluster。

必須依照primary key順序

接著我們看第二支CQL，第二支CQL在執行時會出現以下的錯誤：

1
2
3
4
5
6

-- 依職等計數
SELECT jobgrade, count(*) 
  FROM CASSANDRA.EMPLOYEE
 GROUP BY jobgrade

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

當在Cassandra中執行GROUP BY時，必須要依照primary key的順序，也就是deptname、jobgrade和gender這個順序，跳過deptname直接使用jobgrade或是gender作GROUP BY是不允許的。

其中原因在於Cassandra使用partition key決定資料存放的位置，並在每份partion中使用clustering key決定資料的排序順序。因此如果要對沒有任何索引的非primary key欄位作分組計算，就會需要遍尋Cassandra cluster。

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

1
2
3
4
5
6
7
8
9
10
11
12

-- 依部門, 職等計數
SELECT deptname, jobgrade, count(*) 
  FROM CASSANDRA.EMPLOYEE
 GROUP BY deptname, jobgrade

 deptname | jobgrade | count
----------+----------+-------
       HR |        1 |     2
       HR |        3 |     1
   Design |        1 |     1
   Design |        2 |     1
   Design |        3 |     1

從以上查詢範例可以發現Cassandra在使用聚合函式時會受限於資料表primary key設計，並不是所有的欄位都能使用。因此如果預期會對資料欄位作聚合函式的計算，就必須預先在建立資料表就放進primary key中。

過去兩年的時間投入了數據平台的建置，希望可以打造一個所謂的現代化數據平台(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的情況。

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- 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裡用以上建立的資料表，舉幾種查詢情境如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- 查詢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會得到以下的錯誤：

1
2
3
4
5

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執行則會得到以下的錯誤：

1
2
3
4
5
6

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限制所以可以成功執行。

1
2
3
4
5
6
7
8
9

 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的語法，就覺得可以很容易的上手使用！

上一篇有提到真的有重要的功能不支援web的情況下，可以採用conditional import的方式，在不同平台運行不同的程式片段，避免使用過多的平台判斷邏輯造成程式碼過長，或是在compile階段出現error。

假設今天要實作一個platform判斷邏輯，因為我們已經知道web平台不支援dart:io，且其他的平台(ios, android等)都可以用dart:io.Platform。所以設計的邏輯為，當dart:io能被使用時採用dart:io.Platform判斷運行平台，否則即判定為web。

首先可以先作一個簡單的列舉項目PlatformType，用來比較platform判斷結果。接著設計一個PlatformCheck類別，並實作類似dart:io的isPlatform檢查。而checkPlatform函式會實作在拆出去的platform_web或platform_io兩個dart裡面。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

// platform_type.dart
import 'platform_web.dart' if (dart.library.io) 'platform_io.dart';

enum PlatformType {
  WEB,
  ANDROID,
  IOS
}
class PlatformCheck {
  static bool isWeb() => checkPlatform() == PlatformType.WEB;
  static bool isAndroid() => checkPlatform() == PlatformType.ANDROID;
  static bool isIos() => checkPlatform() == PlatformType.IOS;
  // ...other platform
}

回頭看開頭的conditional import，我們想到達成的效果為，如果可以使用dart.library.io的話就import platform_io.dart，不行的話則import platform_web.dart。

所以只要分別在platform_io.dart實作非web平台的checkPlatform函式，接著在platform_web.dart實作web平台的checkPlatform函式即可。
如果今天app是運行在可以使用dart:io的非web平台上，platform_io.dart就會被import進來，接著就可以在checkPlatform裡面實作使用dart:io.Platform的平台判斷邏輯。

1
2
3
4
5
6
7
8
9
10

// platform_io.dart
import 'dart:io' show Platform;
import 'package:webapp/platform_type.dart';

PlatformType checkPlatform() {
  if (Platform.isIOS) return PlatformType.IOS;
  if (Platform.isAndroid) return PlatformType.ANDROID;
  
  return PlatformType.IOS;
}

如果app是運行在不能使用dart:io的web平台上，會變成import platform_web.dart，因為目前只有web平台無法使用dart:io，所以如果是web情況下checkPlatform就不作其他的邏輯判斷，直接回傳是web。

到這裡可以再往回看一次，如果是運行在web平台上，在程式compile時就完全不會import到platform_io.dart，可以避免在web平台上import dart:io造成的compile錯誤。

1
2
3
4
5
6

// platform_web.dart
import 'package:webapp/platform_type.dart';

PlatformType checkPlatform() {
  return PlatformType.WEB;
}

最後只要import自己寫的platform_type.dart，就可以用以下的語法達成跨平台的platform檢查。如果今天app是運行在web之下，就會回傳web。

1
2
3
4
5
6

String currPlatform = "";
if (PlatformCheck.isWeb()) currPlatform = 'Web';
else if (PlatformCheck.isAndroid()) currPlatform = 'Android';
else if (PlatformCheck.isIos()) currPlatform = 'iOS';

>>> WEB

如果專案在考量到web平台下，勢必會遇一個功能在不同的平台上要運行不同的package或是程式片段的跨平台問題。比起使用多個if-else的platform判斷邏輯，再把所有程式碼都寫進每個if-else中，使用conditaional import的方式可以有效的規劃程式碼，也可以避免import到平台不支援的package造成的compile錯誤。

參考資料:
Conditional imports across Flutter and Web

打開Flutter web開發的方法並不困難，但在將手機app轉換(migration)到web app時會遇到許多轉換問題。在Flutter Engage的發佈會上官方有特別展示web migration要注意的地方。而使用Flutter開發跨平台應用程式時，第一個要考量到的就是package的跨平台兼容性。

使用pub.dev確認package兼容性

為了確保使用到的package在所有的平台都能正常的執行，專案在評估採用package時就必須確認是否兼容所有要發佈的平台，或是未來可能會發佈的平台。

剛起頭的專案在pub.dev尋找可採用的package時，就可以邊從資訊頁上方找到平台支援資訊。如果是從mobile轉移到web，就建議要掃過所有使用到的package，當然也可以直接把web app在debug模式跑起來就知道哪些不能用了。

dart:io不支援web的解決方法

dart:io可以協助處理File、Socket、HTTP與其他I/O任務，官方的Web FAQ和dart:io api都有特別寫到dart:io是不支援web app的。自己的經驗有遇過以下兩種情況有使用到dart:io須要特別注意：

1. 使用http處理http request

有些專案會使用dart:io的HttpClient處理跟Http server之間的request與response。但因為dart:io不支援web平台，所以官方建議使用http進行跨平台的http處理，在開發時也可以參考官方的Networking Cookbook有詳細的介紹。

2. 使用universal_platform檢查運行平台

一般跨平台專案會使用dart:io.Platform判斷當前的運行平台，但dart:io.Platform並不支援web，所以在運行於web環境時會出現Unsupported operation: Platfor._operationSystem錯誤。

1
2
3
4
5
6
7
8
9

import 'dart:io' show Platform;

if (Platform.isAndroid) {
  // Do something on Android
} else if (Platform.isIOS) {
  // Do something on iOS
} else {
  // I want do something on web, but I only get Unsupported operation: Platform._operatingSystem
}

考量到web的情境下，另一個方法可以使用kIsWeb再搭配Theme.of與TargetPlatform就可以達到避開使用dart:io.Platform又能夠在建立widget時判斷當下的運行平台。

1
2
3
4
5
6
7
8
9
10

import 'package:flutter/foundation.dart' show kIsWeb, TargetPlatform;

final platform = Theme.of(context).platform;
if (kIsWeb) {
  // Do something on Web
} else if (platform == TargetPlatform.android) {
  // Do something on Android
} else if (platform == TargetPlatform.iOS) {
  // Do something on iOS
}

但比較適合的方法為採用conditional import來解決。當dart:io能被import時採用dart:io.Platform判斷運行平台，無法被import的情況下判定為web。這種方式因為已經有被實作過了(universal_platform)，所以可以考慮不用再自己實作。

使用conditional import在不同平台上運行不同程式片段

如果真的有一個重要的功能，但又不支援web該怎麼辦？像是上面提到只在mobile使用dart:io判斷運行平台，或是須要在mobile上用dart:io的HttpClient處理proxy問題，然後在web單純使用http處理與server的溝通。

若無法確保package跨平台兼容，相同feature在不同平台上要執行不同程式片段時，較簡單的方法是使用上述提到的平台判斷。例如使用universal_platform判斷當下運行的平台後，再執行於不同平台要執行的函式或是widget。

1
2
3
4
5
6
7
8
9
10
11

import 'package:universal_platform/universal_platform.dart';
import 'dart:js' // web platform need this, but it will cause Not Found Error on Android/iOS


if (UniversalPlatform.isWeb) {
  // Do something or render widget on Web
} else if (UniversalPlatform.isAndroid) {
  // Do something or render widget on Android
} else if (UniversalPlatform.isIOS) {
  // Do something or render widget on iOS
}

但如果功能複雜度高且程式碼又很長的話，程式片段會變很長易讀性也會變差。另外若使用到只有web能用的package，也會造成在其他平台compile階段出現錯誤。這時會建議將不同平台要執行的程式片段拆成不同dart，並使用conditional import在開頭就決定要import哪個dart進來使用。

import “web_support_func.dart” if (dart.library.io) “non_web_support_func.dart”

以上是進行web migration馬上會遇到的跨平台兼容問題，除了專案本身使用到的第三方package可能不支援web平台外，要特別注意因為dart:io不支援web引發的http request還有platform判斷問題。如果真的有feature要在不同平台執行不同程式片段時，建議使用conditional import來設計程式碼。

在使用CanvasKit渲染器開發Flutter web app時，會在app啟動時從CDN服務下載CanvasKit，因此在採用CanvasKit渲染器的情況下預設是需要有連網的環境，否則app在啟動時會因為無法下載CanvasKit出現Failed to load resource: net::ERR_INTERNET_DISCONNECTED的錯誤訊息。

但如果是在有連網限制的開發環境下，雖然可以在開發環境使用html渲染器進行開發，並在佈署環境使用CanvasKit渲染器來達到最佳的操作效果，但因為兩種渲染器方法不同，所以渲染出來的效果還是有所差異，這會造成開發和佈署的版面不一致。

雖然預設需要連網才能使用CanvasKit，但目前版本(2.2.2)可以透過指定CanvasKit URL的方式置換掉預設的URL，而且也支援將CanvasKit與web檔案bundle在一起佈署，達到在離線或是有連網限制的環境下執行。但這個方法目前只支援profile與release模式，不支援debug模式。

下載CanvasKit放入web資料夾

下載canvaskit.js與canvaskit.wasm並放進web資料夾，舉例來說目前版本使用的CanvasKit為0.25.1，可以先從CDN下載canvaskit.js和canvaskit.wasm。如果要使用profile模式就將兩個檔案在進/web/profiling/目錄內，要使用release模式的話直接放在/web/目錄。

修改url指到CanvasKit位置

透過--dart-define覆寫FLUTTER_WEB_CANVASKIT_URL環境變數，並指到下載的CanvasKit檔案位置。因為這裡的預設根目錄會是web/，所以如果用上述的檔案放法的話只要給/就行了。這樣在profile模式下會自動在/web/profiling/讀取CanvasKit，在release模式會自動在根目錄(預設輸出的local位置為/web/，實際佈署會存在於根目錄)讀取CanvasKit。

使用profile模式：

flutter run -d chrome --profile --dart-define=FLUTTER_WEB_CANVASKIT_URL=/

使用release模式：

flutter build web --dart-define=FLUTTER_WEB_CANVASKIT_URL=/

使用profile模式也可以透過在VS Code設定launch.json檔中的flutterMode與args參數，接著直接在VS Code執行啟動偵錯就可以進入profile模式。如果是使用release模式也可以透過dhttpd在本地端啟動web server測試。

1
2
3
4
5
6
7
8
9
10
11

// launch.json
"configurations": [
    {
        "name": "Flutter",
        "type": "dart",
        "request": "launch",
        "program": "lib/main.dart",
        "flutterMode": "profile",
        "args": ["--dart-define=FLUTTER_WEB_CANVASKIT_URL=/"]
    }
]

在專案中引用Roboto預設字型

如果app內Text相關widget沒有指定字型(fontFamily)，或是雖然有指定字型但沒有將字型檔(.ttf/.otf)放進專案並在pubspec宣告，使用CanvasKit渲染器時會預設從fonts.google.com下載Roboto-Regular使用。

因此要先將Roboto放在專案並宣告在pubspec，在web啟動時就會自動使用專案內的字型檔而不會連到google下載字型。如果有使用到其它字型，可以參考官方的字型引用方法。

從google下載下來會有Rotobo全部的ttf檔，只要把Roboto-Regular放進來即可

接著在pubspec宣告引用

1
2
3
4
5
6

flutter:
  uses-material-design: true
  fonts:
    - family: Roboto
      fonts:
        - asset: assets/fonts/Roboto-Regular.ttf

預設app啟動就會自動使用Roboto而不會連到google下載字型。

在Flutter官方提供更簡易的支援前，可以使用上述的三個步驟來達成在連網限制的環境上使用CanvasKit渲染器，在部署時只要將CanvasKit檔案一起部署即可。

參考資料:
Flutter issues - Can’t build web apps with CanvasKit without internet
Flutter issues - support bundling CanvasKit instead of CDN
Flutter issues - support specifying CanvasKit URL in debug builds

在Flutter正式將web支援放進stable channel後，現在只要從官網下載最新的stable版sdk，就可以直接開發與佈建web應用程式。這篇會紀錄Flutter web開發從前置準備到發佈前的環節，提供給有Flutter經驗而且要嘗試打開web支援的開發者。如果需要從頭開始建立Flutter的開發環境，可以依不同開發平台參考flutter.dev的說明。

前置準備

以Windows開發為例，在完成Flutter開發環境與IDE設定後，就可以執行flutter doctor來協助診斷是否設定完全。其中可以看到開發web需要使用chrome所以會有一項檢查chrome是否有安裝。

flutter doctor

1
2
3
4
5
6
7
8
9

Running "flutter pub get" in flutter_tools...                       8.4s
Doctor summary (to see all details, run flutter doctor -v):
[√] Flutter (Channel stable, 2.2.2, on Microsoft Windows [Version 10.0.19041.1052], locale zh-TW)
[√] Android toolchain - develop for Android devices (Android SDK version 30.0.2)
[√] Chrome - develop for the web
[√] Android Studio (version 4.0)
[√] VS Code (version 1.57.1)
[√] Connected device (2 available)
• No issues found!

其實Flutter也可以使用Edge，所以使用flutter devices來確定有連接到的device，如果有安裝Edge也會被偵測到。

flutter devices

1
2
3
4

2 connected devices:

Chrome (web) • chrome • web-javascript • Google Chrome 91.0.4472.106
Edge (web)   • edge   • web-javascript • Microsoft Edge 91.0.864.54

建立Flutter專案與打開web支援

如果是要從頭建立一個專案，可以直接使用flutter create [project name]，建立的新專案就會帶有web資料夾，裡面會包含所有web需要的檔案像是index、manifest與icons。如果是已經存在的專案但要打開web支援，則可以使用flutter create [project path]就會自動產生web目錄。

flutter create [project name]

設定IDE(VSCode)

打開VSCode點擊右下角設定，就能夠選擇現在可使用的emulator，為了開發web所以這裡選擇chrome。如果不先選擇emulator在執行run debug時也會跳出來請你選擇。

如果需要在debug的時候帶進參數，可以在launch.json檔裡面寫入args。例如Flutter web預設使用的渲染器是Auto模式，即在桌上裝置使用CanvasKit，在行動裝置使用html。如果要指定渲染器都使用html的話，就可以寫進args。

運行debug模式

再來可以在IDE啟動偵錯就會開始debug模式，除了使用IDE啟動偵錯外也可以透過指令flutter run來運行debug模式。因為Flutter會運行dartdevc並對app進行compile將dart轉成javascript，所以第一次啟動debug模式的時間會比較長。web開發可以使用hot restart，Flutter只會對有更動到的部份recompile成javascript，而不需要從頭compile整個app。在IDE可以透過restart debug功能觸發hot restart，如果是使用command line可以按R觸發。

flutter run -d chrome

1
2
3
4
5
6
7
8
9

Launching lib\main.dart on Chrome in debug mode...
Waiting for connection from debug service on Chrome...             22.7s
This app is linked to the debug service: ws://127.0.0.1:50526/2kZvThD-K8w=/ws
Debug service listening on ws://127.0.0.1:50526/2kZvThD-K8w=/ws

 Running with sound null safety

  To hot restart changes while running, press "r" or "R".
For a more detailed help message, press "h". To quit, press "q".

建構web發佈版本

在開發完準備要發佈後，可以使用flutter build web指令來建構發佈版本。預設發佈時使用的渲染器是auto模式(即在桌上裝置使用canvaskit，在行動裝置使用html)，如果要指定使用特定的渲染器，可以使用--web-renderer。

flutter build web --web-renderer [auto/html/canvaskit]

執行指令後Flutter會使用dart2js將app compile成一個單一的javascript檔案main.dart.js，發佈完後會在build目錄內產生web目錄，這整包資料會包含所有必要的檔案並且需要同時佈署。

使用dhttpd於本地端運行

最後在實際將整包web佈署到server上之前，可以使用dhttpd嘗試在本地端先運行app，檢視要發佈的app的功能與asset是否都正常。dhttpd安裝方法可以參考dhttpd在pub.dev的安裝方法，並在運行時指定build/web/目錄就可以在本地端運行要發佈的app，在本地端運行app檢查沒問題後就可以準備將web佈署到正式的server了。

dart pub global run dhttpd –path build/web/

1

Server started on port 8080

Flutter的web支援目前可以很容易的將自己開發好的app轉成web上線，而且設定到發佈其實也不困難，可以達成快速的支援跨平台的需求。但實際在使用時還是會遇到像是將app轉成web app所會遇到的migration問題，或是渲染器選擇使用跟canvaskit預設不支援offline模式問題，而且發佈完成的app本身會是一個PWA(Progressive Web Apps)需要設定manifest。這些問題會在實際實作的過程會遇到，有些自己踩過的坑也會再持續紀錄下來。

參考資料:
Fluter docs - Web