Drake's

上一篇說明Langchain的SQL Agent範例如何透過ReAct Prompting實作，而目前LLM其實都已經具有Function Calling的能力，比起要解析LLM回傳的Action和Action Input再另外執行，直接透過Function Calling的效果其實更好。甚至目前的LLM能力已經可以在不強迫產生Thought的推論動作下模型就能順利解決問題。

Function Calling可以允許LLM使用外部的API或Function，以克服無法取得最新知識或是外部資料的限制。只要將prompt和可以允許執行的Function提供給LLM，模型就能在處理prompt時選擇能夠處理當下任務的Function並得到回傳資訊。在取得Function所提供的外部回傳資訊後，可以再提供給模型作最後的回答。

下面使用Langchain的SQLDatabase提供執行的Function，與Gemini的Function Calling完成Text-to-SQL任務。

首先建立Chinook資料庫，並使用langchain的SQLDatabase建立資料庫的查詢引擎：

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

1
2
3

from langchain_community.utilities import SQLDatabase
# 準備sqlite db
db = SQLDatabase.from_uri("sqlite:///Chinook.db")

Gemini會使用function name, docstring, parameters和parameter type annotations來決定是否要使用特定的函式來回答問題。這裡借用Langchain的SQLDatabase的三個Function並寫好docstring和parameter。

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

def sql_db_list_tables():
    """Tool for getting tables names.

    Args:
        Input is an empty string
    """
    return ", ".join(db.get_usable_table_names());

def sql_db_schema(table_names: str):
    """Tool for getting metadata about a SQL database. Get the schema and sample rows for the specified SQL tables.

    Args:
        table_names: A comma-separated list of the table names for which to return the schema.
    """
    return db.get_table_info_no_throw(
            [t.strip() for t in table_names.split(",")]
        )

def sql_db_query(query: str):
    """Tool for querying a SQL database. Execute a SQL query against the database and get back the result..

    Args:
        query: A detailed and correct SQL query.
    """
    return db.run_no_throw(query)

接著設定function calling config，這裡設定成ANY會強迫讓模型使用提供的Function，並提供以上三個Function。

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

from google.generativeai.types import content_types
from collections.abc import Iterable

def tool_config_from_mode(mode: str, fns: Iterable[str] = ()):
    """Create a tool config with the specified function calling mode."""
    return content_types.to_tool_config(
        {"function_calling_config": {"mode": mode, "allowed_function_names": fns}}
    )

sql_tool_dict = {
    "sql_db_query": sql_db_query,
    "sql_db_schema": sql_db_schema,
    "sql_db_list_tables": sql_db_list_tables
}

tool_config = tool_config_from_mode(mode="any", fns=list(sql_tool_dict.keys()))

以下參考Langchain的prompt內容並擷取prefix當作instruction還有輸入的問題為input。

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

instruction = """
You are an agent designed to interact with a SQL database.
Given an input question, create a syntactically correct sqlite query to run, then look at the results of the query and return the answer.
Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most 10 results.
You can order the results by a relevant column to return the most interesting examples in the database.
Never query for all the columns from a specific table, only ask for the relevant columns given the question.
You have access to tools for interacting with the database.
Only use the below tools. Only use the information returned by the below tools to construct your final answer.
You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.

DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.

If the question does not seem related to the database, just return "I don't know" as the answer.

First, You 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.
"""

text2sql_input = """
How many employees are there?
"""

如果使用generate_content會需要手動管理與模型的互動，所以這裡會把將instruction、prompt和tools提供給模型並與模型建立chat session，其中將enable_automatic_function_calling設成True時會允許模型自己呼叫我們所提供的Function。

1
2
3
4
5
6
7
8
9
10

# Configure Gemini
genai.configure(api_key=GEMINI_API_KEY)
model = genai.GenerativeModel(model_name='gemini-1.5-flash', \
                system_instruction=instruction, \
                tools=list(sql_tool_dict.values()))

chat = model.start_chat(enable_automatic_function_calling=True)

response = chat.send_message(text2sql_input)
response.text

最後可以從模型得到正確回答「There are 8 employees.」。

1

There are 8 employees.

可以從以下的chat history看到模型會從input的內容開始，先呼叫查詢所有的資料表後，接著選出Employee資料表查詢schema資訊，最後下一段模型生成出來的SQL查詢後回答最終的答案。

1
2
3

for content in chat.history:
    print(content.role, "->", [type(part).to_dict(part) for part in content.parts])
    print("-" * 80)

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

user -> [{'text': '\nHow many employees are there?\n'}]
--------------------------------------------------------------------------------
model -> [{'function_call': {'name': 'sql_db_list_tables', 'args': {}, 'id': ''}}]
--------------------------------------------------------------------------------
user -> [{'function_response': {'name': 'sql_db_list_tables', 'response': {'result': 'Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track'}, 'id': ''}}]
--------------------------------------------------------------------------------
model -> [{'function_call': {'name': 'sql_db_schema', 'args': {'table_names': 'Employee'}, 'id': ''}}]
--------------------------------------------------------------------------------
user -> [{'function_response': {'name': 'sql_db_schema', 'response': {'result': '\nCREATE TABLE "Employee" (\n\t"EmployeeId" INTEGER NOT NULL, \n\t"LastName" NVARCHAR(20) NOT NULL, \n\t"FirstName" NVARCHAR(20) NOT NULL, \n\t"Title" NVARCHAR(30), \n\t"ReportsTo" INTEGER, \n\t"BirthDate" DATETIME, \n\t"HireDate" DATETIME, \n\t"Address" NVARCHAR(70), \n\t"City" NVARCHAR(40), \n\t"State" NVARCHAR(40), \n\t"Country" NVARCHAR(40), \n\t"PostalCode" NVARCHAR(10), \n\t"Phone" NVARCHAR(24), \n\t"Fax" NVARCHAR(24), \n\t"Email" NVARCHAR(60), \n\tPRIMARY KEY ("EmployeeId"), \n\tFOREIGN KEY("ReportsTo") REFERENCES "Employee" ("EmployeeId")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/'}, 'id': ''}}]
--------------------------------------------------------------------------------
model -> [{'function_call': {'name': 'sql_db_query', 'args': {'query': 'SELECT COUNT(*) FROM Employee'}, 'id': ''}}]
--------------------------------------------------------------------------------
user -> [{'function_response': {'name': 'sql_db_query', 'response': {'result': '[(8,)]'}, 'id': ''}}]
--------------------------------------------------------------------------------
model -> [{'text': 'There are 8 employees.'}]
--------------------------------------------------------------------------------

參考資料:
Generative-AI function-calling
Gemini cookbook Function_Calling
Gemini cookbook Function_Calling_config

上一篇透過Langchain SQL Agent建立資料問答系統中使用了Langchain的create_sql_agent來建立SQL Agent。Langchain在實作這個SQL Agent背後的使用了create_react_agent，這是基於2022年的paper “ReAct: Synergizing Reasoning and Acting in Language Models”的實作，雖然這個版本是比較早期的實作現在並不適合拿來使用在production環境，但仍可以透過瞭解SQL Agent是怎麼基於ReAct prompting技巧運作的。

ReAct Prompting

ReAct Prompting包含了兩個部份，分別是Reasoning和Acting。主要是讓LLM能夠透過交錯的方式產生推理的過程(Reasoning traces)和執行特定任務的動作(task-specific actions)。在產生推理時可以讓LLM針對推理的結果，更新並產生新的執行任務的動作，同時允許LLM在執行任務時可以和外部的工具互動，以取得更具信賴且真實的資訊。

Hotspot QA

上面是論文中使用的問題「Aside from the Apple Remote, what other devices can control the program Apple Remote was originally designed to interact with?」讓LLM透過ReAct解決問題的推論與執行動作過程。可以看到在找到答案前，每次都會進行思考(Though)、行動(Action)和觀察(Observation)。所以模型會在每一次作完動作得到的觀察結果(例如搜尋結果)後，思考下一步該作什麼動作(例如搜尋某個資訊)。

AlfWorld

第二個例子是應用在決策任務中，在決策任務中會包含了更複雜的互動環境，因此更需要透過有效的推論過程和執行動作，讓模型最後能完成正確的任務。在解決AlfWorld的問題「You are in the middle of a room. Looking quickly around you, you see a cabinet 6, a cabinet 1, a coffee machine 1, a countertop 3, a stove burner 1, and a toaster 1. Your task is to: Put some pepper shaker on a drawer.」可以看到在中間加入思考推論後，能讓模型正確的把最後的目標拆解更小的目標來完成最終的任務。

ReAct Prompting in SQL Agent

接著我們來看一下在Langchain裡面SQL Agent用到的ReAct prompting預設是怎麼設計的。在prompt的template組成總共會有四個部份，分別為database prompt，tools，ReAct instructions和Begin and Scratchpad。

Database Prompt

第一個部份是整個prompt的prefix，和SQL Chain一樣由Database Prompt組成，會需要置換的參數為dialect、top_k，這裡和SQL Chain一樣。

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

You are an agent designed to interact with a SQL database.
Given an input question, create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.
Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most {top_k} results.
You can order the results by a relevant column to return the most interesting examples in the database.
Never query for all the columns from a specific table, only ask for the relevant columns given the question.
You have access to tools for interacting with the database.
Only use the below tools. Only use the information returned by the below tools to construct your final answer.
You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.

DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.

If the question does not seem related to the database, just return "I don't know" as the answer.

Tools

第二個部份為所有提供給模型使用的tool與描述，這裡總共提供四種tool，分別為執行SQL(sql_db_query)、查詢Schema(sql_db_schema)、查詢資料表(sql_db_list_tables)和驗證SQL查詢(sql_db_query_checker)。模型會在推論中決定下一個要使用哪一個tool來執行動作。

1
2
3
4

sql_db_query - Input to this tool is a detailed and correct SQL query, output is a result from the database. If the query is not correct, an error message will be returned. If an error is returned, rewrite the query, check the query, and try again. If you encounter an issue with Unknown column 'xxxx' in 'field list', use sql_db_schema to query the correct table fields.
sql_db_schema - Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables. Be sure that the tables actually exist by calling sql_db_list_tables first! Example Input: table1, table2, table3
sql_db_list_tables - Input is an empty string, output is a comma-separated list of tables in the database.
sql_db_query_checker - Use this tool to double check if your query is correct before executing it. Always use this tool before executing a query with sql_db_query!

ReAct Instruction

第三個部份為ReAct prompting主要的格式，會告訴模型須要進行Thought-Action-Action Input-Observation的循環，直到找到最終的答案。這裡的tool_names參數即為第二部份所有的tool名稱集合。

模型會根據推論(Though)決定執行的行動(Action)，並產生行動輸入(Action Input)。在這裡執行的行動即為使用其中一個tool，而行動輸入就是tool的輸入參數。在執行完行動後的結果就是會產生觀察(Observation)，而模型根據觀察再進行下一步思考推論(Though)，並反覆直到完成任務。

1
2
3
4
5
6
7
8
9
10

Use the following format:

Question: the input question you must answer
Thought: you should always think about what to do
Action: the action to take, should be one of [{tool_names}]
Action Input: the input to the action
Observation: the result of the action
... (this Thought/Action/Action Input/Observation can repeat N times)
Thought: I now know the final answer
Final Answer: the final answer to the original input question

Begin and Scratchpad

第四個部份為Begin與Scratchpad，在這裡會有任務起始的資訊，包含了要回答的問題、最一開始的起始推論，還有儲存推論和行動的暫存器用來紀錄縱跡歷程。這裡的參數共有兩個，第一個input為輸入的問題，第二個agent_scratchpad會紀錄每次的Question-Thought-Action-Action Input-Observation紀錄。

1
2
3
4
5

Begin!

Question: {input}
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.
{agent_scratchpad}

完整的SQL Agent Prompting

以下是使用sqlite範例的完整prompt，其中input會在invoke的時候帶入，而scratchpad會在模型每一次的推論與動作執行紀錄下來。

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

You are an agent designed to interact with a SQL database.
Given an input question, create a syntactically correct sqlite query to run, then look at the results of the query and return the answer.
Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most 10 results.
You can order the results by a relevant column to return the most interesting examples in the database.
Never query for all the columns from a specific table, only ask for the relevant columns given the question.
You have access to tools for interacting with the database.
Only use the below tools. Only use the information returned by the below tools to construct your final answer.
You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.

DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.

If the question does not seem related to the database, just return "I don't know" as the answer.

You have access to the following tools:

sql_db_query - Input to this tool is a detailed and correct SQL query, output is a result from the database. If the query is not correct, an error message will be returned. If an error is returned, rewrite the query, check the query, and try again. If you encounter an issue with Unknown column 'xxxx' in 'field list', use sql_db_schema to query the correct table fields.
sql_db_schema - Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables. Be sure that the tables actually exist by calling sql_db_list_tables first! Example Input: table1, table2, table3
sql_db_list_tables - Input is an empty string, output is a comma-separated list of tables in the database.
sql_db_query_checker - Use this tool to double check if your query is correct before executing it. Always use this tool before executing a query with sql_db_query!

Use the following format:

Question: the input question you must answer
Thought: you should always think about what to do
Action: the action to take, should be one of [sql_db_query, sql_db_schema, sql_db_list_tables, sql_db_query_checker]
Action Input: the input to the action
Observation: the result of the action
... (this Thought/Action/Action Input/Observation can repeat N times)
Thought: I now know the final answer
Final Answer: the final answer to the original input question

Begin!

Question: {input}
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.
{agent_scratchpad}

Scratchpad的過程說明可以參考SQL Agent執行過程，完整的Scratchpad如下。

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

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: 
Observation: Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
Thought: Thought: The `Employee` table likely contains information about employees. I'll check its schema.
Action: sql_db_schema
Action Input: Employee
Observation: 
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
*/
Thought: Thought: I can count the number of rows in the Employee table to find the number of employees.
Action: sql_db_query
Action Input: SELECT COUNT(*) FROM Employee;
Observation: [(8,)]
Thought: I now know the final answer\nFinal Answer: There are 8 employees. 

參考資料:
ReAct: Synergizing Reasoning and Acting in Language Models
ReAct Prompting
create_react_agent

上一篇解釋了如何透過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.

SQLAgent執行過程

使用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)

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

上一篇透過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(#2)、table_info(#15)和input(#17)。

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,)]

延續上一篇使用大型語言模型(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

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

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