Introduction
As the evolvement of AI, we can earn more benefits from it in our work. Generating SQL from natrual language is one of the most important benefits among AI capability.
We can find several fine tuned LLM models to do such work, for example, codellama by Meta, sql-coder by defog.ai. These models allow you to add the table definition in the prompt, then generate SQL according to the user query.
However, Text-to-SQL may have two teasers:
- From the correctness and complexity perspective, the generated SQL is not so accuracy. If we don’t do any adjustment, the SQL can’t even run when in a complex database schema environment. Some illusion fields could be added in the SQL, which don’t exist in the table definitions.
- The length of prompt has limitation, we can’t just simple paste all the column definitions into prompt if we have thousands of tables.
In this implementation, I am trying to solve these two issues with following work arounds:
- Add a sample SQL in the schema text to ensure the correctness of the generated SQL.
- Detect the intent from user query, in order to add only the necessary tables into the prompt.
The implementation also includes:
- Execute the generated SQL on HANA Cloud to retrieve the data – verify the correctness of the SQL.
- Present the result on an Web page – be easy to test the implementation.
Basic Information
Data
All the sample data is from datasphere-content/SAP_Sample_Content.
This dataset is good to present a real business scenario that there’s lot of tables for different business modules, some may be cross modules (PRODUCTS in this case, it can be used for both FI and Sales modules).
Import Data on HANA Cloud
Database schema DATMOK is created to contain all the data.
LLM
As being lack of computing resource, I run everything on my MacBook, the LLM and my implementation are going to run locally.
Ollama would be the right option for this demo.
Download Ollama from https://ollama.com/, then pull the necessary models.
Here are the downloaded models.
The usage of the LLM is as below:
- Codellama – model to generate SQL.
- Llava – MultiModel to deal with the data in an image.
- Llama3/mistral/qwen – model to deal with the general chat.
Python Environment
The following packages are needed for the environment. Suggest to create an own environment and then install the following packages.
accelerate
transformers
tokenizers
bitsandbytes
einops
langchain
sentence_transformers
tiktoken
pandas
pypdf
faiss-cpu
langchain_experimental
text-generation
langchain-community
environs
beautifulsoup4
ollama
chromdb
langchain_chroma
flask
Implementation
Features
The chat bot should be able to provide below features:
- Detect the intent from the user question – Sales, FI and HR in this case. If no match, then it will interact with a general chat.
- An SQL is generated based on the detected intent, and post to HANA cloud to run.
- A special feature is to read data and convert to the necessary format if user uploads an image.
Schema file and sample SQL
Add the sample SQL along with table definition can ensure LLM to learn how to write a precise SQL.
Here is the sample SQL for sales schema.
One example of the SQL would be `SELECT so.SALESORDERID as "Sales Order",
bp.COMPANYNAME as "Business Partner",
so.CREATEDAT as "Sales Order Creation Date",
YEAR(so.CREATEDAT) as "Year of Sales Order Creation",
QUARTER(so.CREATEDAT) as "Quarter of Sales Order Creation",
so.CURRENCY as "Currency",
soi.SALESORDERITEM as "Sales Order Item",
prod.MEDIUM_DESCR as "Product",
TO_DECIMAL(ROUND(soi.GROSSAMOUNT, 2), 32, 2) as "Item Gross Amount",
TO_DECIMAL(ROUND(soi.NETAMOUNT, 2), 32, 2) as "Item Net Amount",
TO_DECIMAL(ROUND(soi.TAXAMOUNT, 2), 32, 2) as "Item Tax Amount"
FROM DATMOK.SALES_ORDERS as so
JOIN DATMOK.BUSINESS_PARTNERS as bp ON so.PARTNERID = bp.PARTNERID
JOIN DATMOK.SALES_ORDER_ITEMS soi ON so.SALESORDERID = soi.SALESORDERID
JOIN DATMOK.PRODUCT_TEXTS prod ON soi.PRODUCTID = prod.PRODUCTID;`
Here is the sample SQL for FI schema.
An example of the SQL would be `SELECT ft.TRANSACTIONID as "Transaction ID",
gl_acc.MEDIUM_DESCR as "GL Account name",
customer_t.MEDIUM_DESCR as "Customer Name",
customer.COUNTRY as "Customer Country",
ft.DATE as "Transaction Date",
YEAR(ft.DATE) as "Year of Transaction",
QUARTER(ft.DATE) as "Quarter of Transaction",
ft.VERSION as "Transaction Version",
prod.MEDIUM_DESCR as "Product Name",
prod_cat.SHORT_DESCR as "Product Category",
pc.LONG_DESCR as "Profit Center",
TO_DECIMAL(ROUND(ft.VALUE, 2), 32, 2) as "Value"
FROM DATMOK.FINANCIAL_TRANSACTIONS as ft
JOIN DATMOK.GL_ACCOUNT_TEXTS gl_acc ON ft.ACCOUNTID = gl_acc.ACCOUNTID
JOIN DATMOK.CUSTOMERS as customer ON ft.CUSTOMERID = customer.CUSTOMERID
JOIN DATMOK.CUSTOMER_TEXTS as customer_t ON ft.CUSTOMERID = customer_t.CUSTOMERID
JOIN DATMOK.PRODUCT_TEXTS prod ON ft.PRODUCTID = prod.PRODUCTID
JOIN DATMOK.PRODUCT_CATEGORY_TEXTS prod_cat ON ft.PRODUCTCATEGORYID = prod_cat.PRODCATEGORYID
JOIN DATMOK.PROFIT_CENTER_TEXTS pc ON ft.PROFITCENTERID = pc.PROFITCENTERID;`
Here is the sample SQL for HR schema.
An example of the SQL would be `SELECT DISTINCT hc.EMPLOYEEID,
division.SHORT_DESCRIPTION as "Division",
dep.SHORT_DESCRIPTION as "Department",
job.MEDIUM_DESCRIPTION as "Job",
job_cls.SHORT_DESCRIPTION as "Job Classification",
manager.FULLNAME as "Manager",
pd.FULLNAME as "Full Name",
pd.GENDER as "Gender",
pd.MARITALSTATUS as "Marital Status",
pd.ETHNICITY as "Ethnicity",
pd.YEAROFBIRTH as "Year of Birth",
loc.SHORT_DESCRIPTION as "Location",
hc.DATE as "Start Date",
CASE WHEN hc.EXITDATE < TO_VARCHAR(CURRENT_DATE, 'YYYYMMDD') THEN hc.EXITDATE ELSE '' END as "Last Date",
hc.EXITREASON as "Exit Reason",
hc.FTE as "FTE",
TO_DECIMAL(ROUND(hc.SALARY, 2), 32, 2) as "Salary",
perf.PERFORMANCE as "Performance",
perf.IMPACTOFLOSS as "Impact of Loss",
CASE WHEN perf.FUTURELEADER = '#' THEN '' ELSE perf.FUTURELEADER END as "Future Leader"
FROM DATMOK.EMPLOYEE_HEADCOUNT hc
JOIN DATMOK.EMPLOYEE_PERSONAL_DATA pd ON hc.EMPLOYEEID = pd.EMPLOYEEID
JOIN DATMOK.LOCATION_TEXTS loc ON pd.LOCATIONID = loc.LOCATIONID AND loc.LANGUAGE = 'EN'
JOIN DATMOK.DIVISION_TEXTS division ON hc.DIVISIONID = division.DIVISIONID AND division.LANGUAGE = 'EN'
JOIN DATMOK.DEPARTMENT_TEXTS dep ON hc.DEPARTMENTID = dep.DEPARTMENTID AND dep.LANGUAGE = 'EN'
JOIN DATMOK.JOB_TEXTS job ON hc.JOBID = job.JOBID AND job.LANGUAGE = 'EN'
JOIN DATMOK.JOB_CLASSIFICATION_TEXTS job_cls ON hc.JOBCLASSIFICATIONID = job_cls.JOBCLASSIFICATIONID AND job_cls.LANGUAGE = 'EN'
JOIN DATMOK.HR_MANAGER manager ON hc.MANAGERID = manager.MANAGERID
JOIN DATMOK.EMPLOYEE_PERFORMANCE perf ON hc.EMPLOYEEID = perf.EMPLOYEEID;`
When question comes like “Show me the number of employee's performance is "Satisfactory" per department.”
It generates a correct SQL (except mis-joining more tables into it) and run out the result.
Length of prompt
In a general way, the table definition should be added to the prompt to let LLM learn what to add into the SQL. This is not an issue when the number of tables is less, say 3 tables with dozens of fields. When the number of tables is getting large, this may lead the text of the table definition is getting long. The worst case is out of the limit of the LLM, e.g. 4096 tokens for codellama.
A solution is needed to identify what the question is related to, and only add the necessary table definitions to the prompt.
Below is the prompt that used for SQL generation. The variable {schema_columns} is the text which needs to be added by intent.
"""
### Instructions:
Your task is to convert a question into a SQL query, given a database schema.
### Input:
Generate a SQL query that answers the question `{question}`.
This query will run on a database whose schema is represented in this string:
{schema_columns}\n
### Important:
Note to use format 'YYYYMMDD' of date.
### Response:
Based on your instructions, here is the SQL query I have generated to answer the question `{question}`:
```sql
"""
Intent
There are two options could be used to determine the intent:
- Similarity by prompt – the similarity calculation between user question and predefined prompt, the max probability is the intent.
- Few shot examples – use a set of examples which contain question and intent pair to determine the intent of user question.
Similarity by prompt
Embedding the predefined prompts, and then do a cosine similarity calculation by user question. The final intent would be the max number of the result.
The source code is as below.
general_chat = """You are a smart robot to chat with all general topic.
Any other topics don't match will put under your category.
Here is a question:
{query}"""
sales_chat = """You are a very smart analytics expert on sales data. \
You are great at answering questions about sales orders in a concise and easy to understand manner. \
You are going to read the data from database to find the perfect answers by question. \
The sales order data should include business partners, sales orders and the line items in the sales orders. \
When you don't know the answer to a question you admit that you don't know.
Here is a question:
{query}"""
finance_chat = """You are a very smart analytics expert on financial data. \
You are great at answering questions about finance data in a concise and easy to understand manner. \
You are going to read the data from database to find the perfect answers by question. \
The finance data should include customers, GL account, profit center and products. \
The finance transaction is different to the sales order, which is linked to the end customer instead of business partners. \
Never mix the finance transaction with the sales orders. \
When you don't know the answer to a question you admit that you don't know.
Here is a question:
{query}"""
hr_chat = """You are a very smart analytics expert on HR data. \
You are great at answering questions about HR data in a concise and easy to understand manner. \
You are going to read the data from database to find the perfect answers by question. \
The finance data should include employee information, e.g. head count, personal, postion, job, division, department, HR manager. \
When you don't know the answer to a question you admit that you don't know.
Here is a question:
{query}"""
def topic_intent(question):
routers = pd.DataFrame()
routers['topic'] = ['General', 'Sales', 'FI', 'HR']
routers['schema_file'] = ['', 'sales_schema.txt', 'finance_schema.txt', 'hr_schema.txt']
routers['capabilities'] = [general_chat, sales_chat, finance_chat, hr_chat]
prompt_embeddings = MINILM_EMBEDDING.embed_documents(routers['capabilities'].values)
query_embedding = MINILM_EMBEDDING.embed_query(question)
similarity = cosine_similarity([query_embedding], prompt_embeddings)[0]
print(similarity)
most_similar = routers.iloc[similarity.argmax()]
return most_similar
The cosine_similarity will return an array like:
[0.08753332 0.24481206 0.16693463 0.14286233]
And index 1 – “Sales” – in the array is the intent.
Few shot examples
The common question/intent pairs are accumulated into one document, the most high similarity of the user question is the intent.
Here is the few_shot.csv prepared for this case.
question|intent
List top 10 sales orders in year 2018.|Sales
List the top 10 BP sales in sales organization "APJ".|Sales
Pull the SO details in year 2018 in "APJ".|Sales
List top 10 sales products per BP in year 2018.|Sales
List top 10 sales product category.|Sales
Find all the transactions where GL Account is 'Travel' in year 2018.|FI
List top 10 transaction customers in year 2018.|FI
List top 10 transaction products in year '2018'.|FI
Show me the transaction details in year '2018'.|FI
Show me the total transaction amount per profit center.|FI
List all employees in department 'Office of CEO'.|HR
Show me the employee performance per department.|HR
The code to determine the intent is as below.
def intent_fewshot(query):
df = pd.read_csv(os.path.join('./data/few-shot', 'prompt_intent.csv'), sep="|")
examples = []
for _, row in df.iterrows():
question, intent = row
examples.append({"question": question, "intent":intent})
#example_prompt = PromptTemplate(
# input_variables=["question", "intent"], template="Question: {question}\n{intent}"
#)
#print(prompt.invoke(input=question).to_string())
example_selector = SemanticSimilarityExampleSelector.from_examples(
# This is the list of examples available to select from.
examples,
# This is the embedding class used to produce embeddings which are used to measure semantic similarity.
MINILM_EMBEDDING,
# This is the VectorStore class that is used to store the embeddings and do a similarity search over.
Chroma,
# This is the number of examples to produce.
k=1,
)
# Select the most similar example to the input.
#question = "Show me the total transaction amount."
selected_examples = example_selector.select_examples({"question": query})
return selected_examples
Comparison of two options
Get the intent with the same questions by using these two functions.
questions = ["How many products we have?", "Pull me SO data of year 2018 with product name and total amount."]
result = []
for question in questions:
result.append({'question': question,
'topic_intent': topic_intent(question)['topic'],
'fewshot': intent_fewshot(question)[0]['intent']})
print(result)[{'question': 'How many products we have?', 'topic_intent': 'Sales', 'fewshot': 'Sales'}, {'question': 'Pull me SO data of year 2018 with product name and total amount.', 'topic_intent': 'Sales', 'fewshot': 'FI'}]
Few shot function determines the intent question “'Pull me SO data of year 2018 with product name and total amount.” is “FI”, which is incorrect. Seems that’s the issue of embedding, but this demo wouldn’t like to adjust/train embeddings.
“Similarity of prompt” is the selected option for this demo.
Chatbot
Put all things together now the ChatBot class could be implemented as below.
from langchain_core.output_parsers import StrOutputParser
from langchain_core.prompts import PromptTemplate
from langchain_community.llms import Ollama
import os
import ollama
from intent_helper import topic_intent
from db import db_query
from config import SCHEMA_FILE_PATH
class ChatBot:
def __init__(self, host:str = 'localhost'):
"""
Sample parameters for options could be as below:
"options": {
"num_keep": 5,
"seed": 42,
"num_predict": 100,
"top_k": 20,
"top_p": 0.9,
"tfs_z": 0.5,
"typical_p": 0.7,
"repeat_last_n": 33,
"temperature": 0.8,
"repeat_penalty": 1.2,
"presence_penalty": 1.5,
"frequency_penalty": 1.0,
"mirostat": 1,
"mirostat_tau": 0.8,
"mirostat_eta": 0.6,
"penalize_newline": true,
"stop": ["\n", "user:"],
"numa": false,
"num_ctx": 1024,
"num_batch": 2,
"num_gpu": 1,
"main_gpu": 0,
"low_vram": false,
"f16_kv": true,
"vocab_only": false,
"use_mmap": true,
"use_mlock": false,
"num_thread": 8
}
I only use temperature and top_p here to ensure the minimum diversity.
"""
self.host = host
self.base_url = "http://%s:11434"%host
#self.client = ollama.Client(host = self.host)
def chat(self, question):
intent = topic_intent(question=question)
if intent['topic'] == 'General':
return self.general_chat(question)
else:
return self.db_chat(intent=intent, question=question)
def general_chat(self, question, model = "mistral", temperature=0.8, top_p=0.9):
"""
Set temperature as 0.8 and top_p as 0.9 to increase the diversity of the result.
"""
seed_prompt = """
Answer the user question.
{question}
Detect the language from the question.
Response the answer in the same language detected from the question.
"""
prompt = PromptTemplate.from_template(seed_prompt)
llm = Ollama(model=model, base_url = self.base_url, temperature=temperature, top_p=top_p)
chain = (
prompt
| llm
| StrOutputParser()
)
return {"status": "Success", "type": "General", "msg": chain.invoke({"question": question})}
def db_chat(self, intent, question, model="codellama", temperature=0, top_p=0, output_format="raw"):
schema_columns = open(os.path.join(SCHEMA_FILE_PATH, intent['schema_file']), 'r').read()
seed_prompt = """
### Instructions:
Your task is to convert a question into a SQL query, given a database schema.
### Input:
Generate a SQL query that answers the question `{question}`.
This query will run on a database whose schema is represented in this string:
{schema_columns}\n
### Important:
Note to use format 'YYYYMMDD' of date.
### Response:
Based on your instructions, here is the SQL query I have generated to answer the question `{question}`:
```sql
"""
prompt = PromptTemplate.from_template(seed_prompt)
llm = Ollama(model=model, base_url = self.base_url, temperature = temperature, top_p = top_p)
sql_response = (
prompt
| llm.bind(stop=["[```]"])
| StrOutputParser()
)
ret = sql_response.invoke({'question': question, 'schema_columns': schema_columns})
sql = ret.split("```")[0].strip().split(";")[0] + ";"
try:
result = db_query(sql, output_format)
messages = {"status": "Success", "sql": sql, "msg": ret, "data" : result}
except Exception as e:
msg = str(e)
messages = {"status": "Error", "type": "DB", "sql": sql, "msg": msg}
return messages
def image_chat(self, question, images: list[str], temperature=0, top_p=0, model="llava"):
"""
The images parameter can contain a list of image paths or a image byte
"""
client = ollama.Client(host=self.host)
options = {"temperature": temperature, "top_p": top_p}
res = client.chat(
model=model,
messages=[
{
"role": "user",
"content": question,
"images": images
}
],
options=options
)
return {"status": "Success", "type": "image", "msg": res['message']['content']}
Source code
The source code could be found from github.
Bluesky
