Drake's
0%

發表於 更新於 分類於 閱讀次數： Disqus：

上一篇解釋了如何透過Langchain SQL Chain建立資料問答系統。使用 LangChain 的 SQL Chain 可以將問題轉換為 SQL 查詢，這過程中會將不同的動作連結在一起，最後通過執行完整的 Chain 一步步地完成每個步驟，最終獲得結果。如果我們希望 LLM 能夠更主動地與環境互動並完成特定任務，就需要建立代理（Agent）。

代理（Agent）可以想像成一個有大腦的系統（LLM），在設定目標後，代理能在無需人為干預的情況下自主完成任務。由於代理需要在每次情境變化時進行思考（Thought）並執行最適當的動作（Action），因此我們需要給代理一個工具箱，讓它能從中選擇最合適的工具（Tool）。此外，代理具有記憶功能，會觀察（Observation）執行結果，並根據情境和環境的變化，不斷調整自己的行為，直到完成任務為止。

在使用Langchain建立SQL Agent前，先把資料庫和大型語言服務準備好。

準備資料庫

建立Chinook這個資料庫來當作範例：

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

使用langchain的SQLDatabase來建立資料庫的查詢引擎

1
2
3
4
5
6
from langchain_community.utilities import SQLDatabase
# 準備sqlite db
db = SQLDatabase.from_uri("sqlite:///Chinook.db")

print(db.dialect)
print(db.get_usable_table_names())
1
2
sqlite
[(1, 'AC/DC'), (2, 'Accept'), (3, 'Aerosmith'), (4, 'Alanis Morissette'), (5, 'Alice In Chains'), (6, 'Antônio Carlos Jobim'), (7, 'Apocalyptica'), (8, 'Audioslave'), (9, 'BackBeat'), (10, 'Billy Cobham')]

準備大型語言模型服務

再來準備一個大型語言模型服務，這裡使用Gemini當範例

1
2
3
4
5
6
7
8
9
10
import os
import getpass
import google.generativeai as genai
# https://python.langchain.com/docs/integrations/chat/google_generative_ai/

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

llm = genai.GenerativeModel('gemini-1.5-flash')

建立SQL Agent

1
2
3
4
from langchain_community.agent_toolkits import create_sql_agent
agent_executor = create_sql_agent(llm, db=db)

agent_executor.run("How many employees are there?")

建立SQL Agent並輸入問題後，就會得到和SQL Chain一樣的回答結果。

1
There are 8 employees.

SQL Agent執行過程

使用SQL Chain和SQL Agent的差別在於，SQL Chain會把整個問答過程的步驟先定義好後依序執行。

Agent是先定義出不同的工具（Tool），並由大語言模型（LLM）這個大腦動態地根據執行當下的情況來思考（Thought）應該執行什麼動作（Action）。此時，LLM會挑選出其中一種工具，決定工具的輸入（Action Input）並執行。接著，LLM會觀察（Observation）執行完的結果，然後進行下一輪的思考/動作/動作輸入/觀察，直到得到最終答案。

下面可以透過把verbose打開看到LLM的思考/動作/動作輸入/觀察的執行過程。

1
2
3
4
from langchain_community.agent_toolkits import create_sql_agent
agent_executor = create_sql_agent(llm, db=db, verbose=True)

agent_executor.run("How many employees are there?")

在Text-to-SQL任務中會先有一個起始的情境，也就是讓LLM要先到資料庫裡找到所有可以查詢的資料表，這時Agent選擇了要執行sql_db_list_tables這個動作，在輸入空值的情況下會得到所有的資料表清單。

1
2
3
4
5
> Entering new SQL Agent Executor chain...
Thought: I should look at the tables in the database to see what I can query.  Then I should query the schema of the most relevant tables.
Action: sql_db_list_tables
Action Input: 
Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track

Agent在取得所有資料表清單後，開始進行思考並決定要使用Employee這張資料，因為這張資料表和輸入問題最相關。於是Agent選擇執行sql_db_schema並輸入Employee查詢出這張資料表的schema還有三筆資料。

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
Thought: The table 'Employee' likely contains information about employees. I'll check its schema.
Action: sql_db_schema
Action Input: Employee
CREATE TABLE "Employee" (
	"EmployeeId" INTEGER NOT NULL, 
	"LastName" NVARCHAR(20) NOT NULL, 
	"FirstName" NVARCHAR(20) NOT NULL, 
	"Title" NVARCHAR(30), 
	"ReportsTo" INTEGER, 
	"BirthDate" DATETIME, 
	"HireDate" DATETIME, 
	"Address" NVARCHAR(70), 
	"City" NVARCHAR(40), 
	"State" NVARCHAR(40), 
	"Country" NVARCHAR(40), 
	"PostalCode" NVARCHAR(10), 
	"Phone" NVARCHAR(24), 
	"Fax" NVARCHAR(24), 
	"Email" NVARCHAR(60), 
	PRIMARY KEY ("EmployeeId"), 
	FOREIGN KEY("ReportsTo") REFERENCES "Employee" ("EmployeeId")
)

/*
3 rows from Employee table:
EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email
1	Adams	Andrew	General Manager	None	1962-02-18 00:00:00	2002-08-14 00:00:00	11120 Jasper Ave NW	Edmonton	AB	Canada	T5K 2N1	+1 (780) 428-9482	+1 (780) 428-3457	andrew@chinookcorp.com
2	Edwards	Nancy	Sales Manager	1	1958-12-08 00:00:00	2002-05-01 00:00:00	825 8 Ave SW	Calgary	AB	Canada	T2P 2T3	+1 (403) 262-3443	+1 (403) 262-3322	nancy@chinookcorp.com
3	Peacock	Jane	Sales Support Agent	2	1973-08-29 00:00:00	2002-04-01 00:00:00	1111 6 Ave SW	Calgary	AB	Canada	T2P 5M5	+1 (403) 262-3443	+1 (403) 262-6712	jane@chinookcorp.com

Agent接著在看完Employee的schema和範例資料後，思考完認為可以透過計算這張資料表的列數來回答問題，於是Agent執行sql_db_query並輸入SQL查詢得到查詢結果。

1
2
3
4
Thought: I can count the number of rows in the Employee table to answer the question.
Action: sql_db_query
Action Input: SELECT COUNT(*) FROM Employee;
[(8,)]

Agent在看完查詢結果後認為已經能回答最後答案了，於是回答出最終的答案。

1
2
3
4
5
Thought: I now know the final answer
Final Answer: There are 8 employees.

> Finished chain.
There are 8 employees.

Chain和Agent差異

從Agent在完成Text-to-SQL的任務過程中可以發現，透過Agent可以讓LLM作為大腦來依據不同的情境動態決定下一步該執行什麼動作，Chain和Agent的比較可以參考下表(The table is craeted by Copilot)

Chains Agents
目的 設計來執行一系列預定的步驟，按照特定順序進行。每個步驟處理輸入並將其傳遞到下一步。 設計來執行動態任務，步驟並非預定。在當前上下文和可用工具的基礎上即時做出決策。
結構 線性結構，每個步驟明確定義並按順序執行。 使用思考、行動和觀察的循環。根據當前上下文和結果調整步驟。
靈活性 靈活性較低，遵循預定路徑，不會根據新信息或環境變化動態調整行為。 高度靈活，能夠適應新信息和變化的條件。可以根據當前上下文從多種行動和工具中進行選擇。
使用案例 適合具有明確線性工作流程的任務，如數據處理管道、文本轉換序列和已知步驟的例行操作。 適用於複雜、不可預測的任務，如互動應用程序、實時決策系統和需要適應行為的任務。
總結 線性，預定步驟，靈活性較低，適合例行的線性工作流程。 動態，決策循環，高度靈活，適合複雜、適應性任務。

參考資料:
Q&A over SQL - Agents
Weng, Lilian. (Jun 2023). “LLM-powered Autonomous Agents”. Lil’Log.

發表於 更新於 分類於 閱讀次數： Disqus：

上一篇透過Langchain SQL Chain建立資料問答系統中使用Langchain的sql query chain能夠快速開發資料問答功能。雖然Langchain提供了方便的開發模組，但卻封裝了許多與大型語言互動的細節。我們可以嘗試的從原始碼來看幾個Langchain封裝起來的細節。

和LLM互動的prompt

在這篇使用大型語言模型(LLM)完成Text-to-SQL任務可以看到要把輸入的問題轉成SQL查詢，和LLM互動時會需要準備prompt，如果有自己準備prompt的話，就能在呼叫create_sql_query_chain帶進prompt參數。如果沒有的話，Langchain會預設使用sql_database的prompt。

1
2
from langchain.chains.sql_database.prompt import SQL_PROMPTS
SQL_PROMPTS[db.dialect].pretty_print() # sqlite

在呼叫create_sql_query_chain且沒有提供prompt的情況下，會從SQLDatabase取得dialect，再從Langchain的sql_database取出對應資料庫的prompt。目前Langchain提供11種database prompt，每一種資料庫的prompt都有所不同，特別是在current date的寫法會不同。

以下是以SQLite為例，這prompt中會有三個在過程中會被帶入的參數，分別為top_k、table_info和input。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
You are a SQLite expert. Given an input question, first create a syntactically correct SQLite query to run, then look at the results of the query and return the answer to the input question.
Unless the user specifies in the question a specific number of examples to obtain, query for at most {top_k} results using the LIMIT clause as per SQLite. You can order the results to return the most informative data in the database.
Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (") to denote them as delimited identifiers.
Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
Pay attention to use date('now') function to get the current date, if the question involves "today".

Use the following format:

Question: Question here
SQLQuery: SQL Query to run
SQLResult: Result of the SQLQuery
Answer: Final answer here

Only use the following tables:
{table_info}

Question: {input}

prompt內置換的變數

top_k
1
2
3
4
5
6
7
8
# langchain/langchain/chains/sql_database/query.py
def create_sql_query_chain(
    llm: BaseLanguageModel,
    db: SQLDatabase,
    prompt: Optional[BasePromptTemplate] = None,
    k: int = 5,
    
    # top_k = 5

top_k本身在create_sql_query_chain就是一個輸入參數，如果不指定的話預設值會是5。

input
1
2
3
4
5
6
7
8
9
10
11
# langchain/langchain/chains/sql_database/query.py
inputs = {
    "input": lambda x: x["question"] + "\nSQLQuery: ",
    "table_info": lambda x: db.get_table_info(
        table_names=x.get("table_names_to_use")
    ),
}
# chain.invoke({"question": "How many employees are there"})

# input = How Many employees are there
#         SQLQuery:

input會從參數dict取出question，就是對應到在invoke時帶入的問題，接著會在問題後接SQLQuery用來讓LLM往後生成SQL查詢。

table_info

table_info會呼叫SQLDatabase的get_table_info函式，這個函式的作用是查找出資料庫內的資料表schmea加三筆資料，而且這裡可以接受帶入一個List參數table_names_to_use正向表列要查的資料表。注意如果不指定資料表，get_table_info會帶出所有的資料表資訊。

1
db.get_table_info(table_names=["Employee"])

實際使用這個函式並帶入資料表名稱，就會取得以下的資訊。這裡的目的就是要讓LLM看懂資料表名稱、欄位清單，還有範例的資料，讓LLM可以更準確的將問題轉成SQL。

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
CREATE TABLE "Employee" (
	"EmployeeId" INTEGER NOT NULL, 
	"LastName" NVARCHAR(20) NOT NULL, 
	"FirstName" NVARCHAR(20) NOT NULL, 
	"Title" NVARCHAR(30), 
	"ReportsTo" INTEGER, 
	"BirthDate" DATETIME, 
	"HireDate" DATETIME, 
	"Address" NVARCHAR(70), 
	"City" NVARCHAR(40), 
	"State" NVARCHAR(40), 
	"Country" NVARCHAR(40), 
	"PostalCode" NVARCHAR(10), 
	"Phone" NVARCHAR(24), 
	"Fax" NVARCHAR(24), 
	"Email" NVARCHAR(60), 
	PRIMARY KEY ("EmployeeId"), 
	FOREIGN KEY("ReportsTo") REFERENCES "Employee" ("EmployeeId")
)

/*
3 rows from Employee table:
EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email
1	Adams	Andrew	General Manager	None	1962-02-18 00:00:00	2002-08-14 00:00:00	11120 Jasper Ave NW	Edmonton	AB	Canada	T5K 2N1	+1 (780) 428-9482	+1 (780) 428-3457	andrew@chinookcorp.com
2	Edwards	Nancy	Sales Manager	1	1958-12-08 00:00:00	2002-05-01 00:00:00	825 8 Ave SW	Calgary	AB	Canada	T2P 2T3	+1 (403) 262-3443	+1 (403) 262-3322	nancy@chinookcorp.com
3	Peacock	Jane	Sales Support Agent	2	1973-08-29 00:00:00	2002-04-01 00:00:00	1111 6 Ave SW	Calgary	AB	Canada	T2P 5M5	+1 (403) 262-3443	+1 (403) 262-6712	jane@chinookcorp.com
*/

完整的prompt

總結以上的prompt template加上top_k、table_info和input三個要代入的參數，最後完整丟給LLM的prompt如下：

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
35
36
37
38
39
40
41
42
43
44
You are a SQLite expert. Given an input question, first create a syntactically correct SQLite query to run, then look at the results of the query and return the answer to the input question.
Unless the user specifies in the question a specific number of examples to obtain, query for at most 5 results using the LIMIT clause as per SQLite. You can order the results to return the most informative data in the database.
Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (") to denote them as delimited identifiers.
Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
Pay attention to use date('now') function to get the current date, if the question involves "today".

Use the following format:

Question: Question here
SQLQuery: SQL Query to run
SQLResult: Result of the SQLQuery
Answer: Final answer here

Only use the following tables:
CREATE TABLE "Employee" (
	"EmployeeId" INTEGER NOT NULL, 
	"LastName" NVARCHAR(20) NOT NULL, 
	"FirstName" NVARCHAR(20) NOT NULL, 
	"Title" NVARCHAR(30), 
	"ReportsTo" INTEGER, 
	"BirthDate" DATETIME, 
	"HireDate" DATETIME, 
	"Address" NVARCHAR(70), 
	"City" NVARCHAR(40), 
	"State" NVARCHAR(40), 
	"Country" NVARCHAR(40), 
	"PostalCode" NVARCHAR(10), 
	"Phone" NVARCHAR(24), 
	"Fax" NVARCHAR(24), 
	"Email" NVARCHAR(60), 
	PRIMARY KEY ("EmployeeId"), 
	FOREIGN KEY("ReportsTo") REFERENCES "Employee" ("EmployeeId")
)

/*
3 rows from Employee table:
EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email
1	Adams	Andrew	General Manager	None	1962-02-18 00:00:00	2002-08-14 00:00:00	11120 Jasper Ave NW	Edmonton	AB	Canada	T5K 2N1	+1 (780) 428-9482	+1 (780) 428-3457	andrew@chinookcorp.com
2	Edwards	Nancy	Sales Manager	1	1958-12-08 00:00:00	2002-05-01 00:00:00	825 8 Ave SW	Calgary	AB	Canada	T2P 2T3	+1 (403) 262-3443	+1 (403) 262-3322	nancy@chinookcorp.com
3	Peacock	Jane	Sales Support Agent	2	1973-08-29 00:00:00	2002-04-01 00:00:00	1111 6 Ave SW	Calgary	AB	Canada	T2P 5M5	+1 (403) 262-3443	+1 (403) 262-6712	jane@chinookcorp.com
*/

Question: How Many employees are there
SQLQuery:

如果要把table_names_to_use帶入資料表清單，可以用RunnablePassthrough作assign，注意如果資料表不存在資料庫中會回傳table_names not found in database。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool
from langchain_core.runnables import RunnableLambda
from langchain_core.runnables import RunnablePassthrough

execute_query = QuerySQLDataBaseTool(db=db, verbose=True)
write_query = create_sql_query_chain(llm, db)

chain = (
    # assign table_names_to_use
    RunnablePassthrough.assign(table_names_to_use=RunnableLambda(lambda x: ['Employee'])) 
    | write_query
    | RunnableLambda(lambda x: parse_sql(x))
    | execute_query
)

chain.invoke({"question": "How many employees are there"})
1
[(8,)]

發表於 更新於 分類於 閱讀次數： Disqus：

延續上一篇使用大型語言模型(LLM)完成Text-to-SQL任務，在開發大型語言模型的應用程式時，可以採用像LangChain一樣的開源框架。LangChain目的在幫助開發者建立大型語言模型的應用程式，縮短開發時間並更容易的實現各種複雜的大型語言模型應用程式。

準備資料庫

一開始先建立Chinook這個資料庫來當作範例：

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

接著使用langchain的SQLDatabase來建立資料庫的查詢引擎

1
2
3
4
5
6
7
from langchain_community.utilities import SQLDatabase
# 準備sqlite db
db = SQLDatabase.from_uri("sqlite:///Chinook.db")

print(db.dialect)
print(db.get_usable_table_names())
db.run("SELECT * FROM Artist LIMIT 10;")
1
2
sqlite
[(1, 'AC/DC'), (2, 'Accept'), (3, 'Aerosmith'), (4, 'Alanis Morissette'), (5, 'Alice In Chains'), (6, 'Antônio Carlos Jobim'), (7, 'Apocalyptica'), (8, 'Audioslave'), (9, 'BackBeat'), (10, 'Billy Cobham')]

準備大型語言模型服務

再來準備一個大型語言模型服務，這裡使用Gemini當範例

1
2
3
4
5
6
7
8
9
10
import os
import getpass
import google.generativeai as genai
# https://python.langchain.com/docs/integrations/chat/google_generative_ai/

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

llm = genai.GenerativeModel('gemini-1.5-flash')

使用LangChain

在LangChain中「Chain」是代表一組有順序執行的步驟或是一系列連接在一起的組件，可以把Chain視為一個工作流程，其中每個步驟都由一個特定的組件負責。每一個組件可能是語言模型、資料來源或是API，這些組件經由連接後可以形成一個完整的應用程式，進而建構更複雜模型推論應用。

使用create_sql_query_chain

準備好資料庫和大型語言模型服務後，透過create_sql_query_chain建立一個SQL查詢的Chain，這個Chain能夠在給定的資料庫下生成SQL查詢。前面使用的SQLDatabase則可以在這裡用來查詢資料庫類型和調用get_table_info來取得欄位資訊。

1
2
3
4
5
from langchain.chains import create_sql_query_chain

chain = create_sql_query_chain(llm, db)
response = chain.invoke({"question": "How many employees are there"})
print(f"response: {response}")

這裡使用到的create_sql_query_chain會把使用者輸入的文字轉成以下的SQL查詢

1
response: SQLQuery: SELECT COUNT(*) FROM Employee

接著可以詠唱一段解析程式把SQL取出來後對資料庫下查詢。

1
2
3
4
5
6
7
8
9
10
11
12
# prompt: write a regular expression to parse all substring start from "SQLQuery: "
import re

def parse_sql(answer):
    match = re.search(r"SQLQuery: (.*)", answer)
    if match:
        return match.group(1)
    else:
        return None

sql_str = parse_sql(response)
db.run(sql_str)
1
[(8,)]
Chain起生成SQL到下查詢

再來可以把這些動作都Chain起來，步驟會是(1) 將輸入問題透過LLM生成出SQL查詢 (2) 從生成結果解析出SQL (3) 對資料庫下SQL查詢

1
2
3
4
5
6
7
8
from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool
from langchain_core.runnables import RunnableLambda

execute_query = QuerySQLDataBaseTool(db=db, verbose=True)
write_query = create_sql_query_chain(llm, db)
# 生成SQL查詢 -> 解析出SQL -> 下查詢
chain = write_query | RunnableLambda(lambda x: parse_sql(x)) | execute_query
chain.invoke({"question": "How many employees are there"})
1
[(8,)]
把查詢結果轉換成人話

到這麼已經可以自動化的從輸入問題到生成結果並查詢資料庫，這時如果不想讓Agent只有回覆查詢數字，就可以把資料庫的查詢結果再丟給LLM生成出像人一樣的回答。

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
from operator import itemgetter

from langchain_core.output_parsers import StrOutputParser
from langchain_core.prompts import PromptTemplate
from langchain_core.runnables import RunnablePassthrough

answer_prompt = PromptTemplate.from_template(
    """Given the following user question, corresponding SQL query, and SQL result, answer the user question.
Question: {question}
SQL Query: {query}
SQL Result: {result}
Answer: """
)

answer = answer_prompt | llm | StrOutputParser()
chain = (
    RunnablePassthrough
        .assign(query=write_query) # answer_prompt中的query參數 = SQLQuery: SELECT COUNT(*) FROM Employee
        .assign(result=itemgetter("query") | RunnableLambda(lambda x: parse_sql(x)) | execute_query # answer_prompt中的result參數 = [(8,)]
    )
    # 到這裡會把放進answer_prompt裡面的dict準備好
    # {'question': 'How many employees are there',
    # 'query': 'SQLQuery: SELECT COUNT(*) FROM "Employee"',
    # 'result': '[(8,)]'}
    | answer 
)

chain.invoke({"question": "How many employees are there"}) # answer_prompt中的question = How many employees are there

從answer_prompt可以看到把question、query、result組成prompt後，就可以請LLM生成一個答案。所以完整的步驟會有四個 (1) 將輸入問題透過LLM生成出SQL查詢 (2) 從生成結果解析出SQL (3) 對資料庫下SQL查詢 (4) 將查詢結果透過LLM生成自然語言回答

1
There are 8 employees.

LangChain提供了開發大型語言模型所需要的組件，透過上面的範例可以快速的建立Text-to-SQL核心功能，加速開發大型語言應用程式。

參考資料: Build a Question/Answering system over SQL data

發表於 更新於 分類於 閱讀次數： Disqus：

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語法。

發表於 更新於 分類於 閱讀次數： Disqus：

當專案已經準備要導入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資料表，這種方式好處在於可以直接對準在原本的資料模型作轉移，一步一步從實體資料表、資料表關聯還有結合應用層的查詢條件將專案作轉移。

發表於 更新於 分類於 閱讀次數： Disqus：

關聯式資料庫的使用者在使用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

發表於 更新於 分類於 閱讀次數： Disqus：

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?

發表於 更新於 分類於 閱讀次數： Disqus：

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

發表於 更新於 分類於 閱讀次數： Disqus：

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

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

發表於 更新於 分類於 閱讀次數： Disqus：

上一篇有提到真的有重要的功能不支援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