AutoSQL-nl2sql-1.0-8b

1. Summary

it is a natural language-to-SQL conversion model optimized specifically for Chinese and English users. It is based on the llama-3-chinese-8b-instruct-v3 model. We used the latest optimization algorithms to improve the performance of the model, especially in handling complex queries and multi-table joins.

1.1 characteristics

• Bilingual support: Ability to handle natural language queries in both Chinese and English languages.
• High accuracy: After a large number of tests on actual database queries, it has been proved that the SQL statements generated have high accuracy.

1.2 training data

Training data for the model comes from multiple sources, including:

• Open source databases (such as WikiSQL, Spider)
• Internally generated dataset covering a variety of query types and complexities
• User feedback data for continuous improvement of model performance

Training data is strictly screened and cleaned to ensure data quality and diversity.

1.3 test results

Test results on multiple benchmark datasets show the model exceeds other existing models in terms of accuracy and generation efficiency. For example:

• On the WikiSQL dataset, the model achieved an execution accuracy rate of 87.5%.
• On the Spider dataset, the model achieved an execution accuracy rate of 95.3%.

1.4 functions and advantages

1. Semantic Understanding: It can comprehend complex natural language requests and extract key elements such as ID, test type, time range, etc.
2. Automated Query Generation: Generates correct and optimized SQL queries based on the database structure and table relationships, eliminating the need for manual code writing.
3. Handling Complex Conditions: Manages nested queries, multi-table joins, and complex time conditions, which might be error-prone or time-consuming if done manually.
4. Efficiency: Quickly generates queries, significantly enhancing productivity, especially in scenarios requiring frequent database queries.
5. High Adaptability: Suitable for various domains' data querying needs, only requiring the relevant database structure. These results show the model has significant advantages in handling complex queries and multi-table joins.

2. Usage and Explanations:

Please upgrade the transformers package to ensure it supports Llama3 models. The current version we are using is 4.41.2.

# Use a pipeline as a high-level helper
from transformers import pipeline
import torch
model_id = "xbrain/AutoSQL-nl2sql-1.0-8b"


messages = [
    {"role": "system", 
     "content": "I want you to act as a SQL terminal in front of an example database, you need only to return the sql command to me.Below is an instruction that describes a task, Write a response that appropriately completes the request.\n\"\n##Instruction:\n database contains tables such as table_name_30. Table table_name_30 has columns such as nfl_team, draft_year."},
    {"role": "user", 
     "content": "###Input:\nIn 1978 what is the NFL team?\n\n###Response:"},
]
pipe_msg = pipeline(
    "text-generation",
    model=model_id,
    model_kwargs={"torch_dtype": torch.bfloat16},
    device_map="auto",)

outputs = pipe_msg(
    messages,
    max_new_tokens=256,
)
print(outputs[0]["generated_text"][-1])

Here are some exciting examples:

Example-1:

The following are the inputs, outputs and explanations of the model：

model input：

{"role": "system", 
 "content": "I want you to act as a SQL terminal in front of an example database, you need only to return the sql command to me.Below is an instruction that describes a task, Write a response that appropriately completes the request.\n\"\n##Instruction:\ndog_kennels contains tables such as Breeds, Charges, Sizes, Treatment_Types, Owners, Dogs, Professionals, Treatments. Table Breeds has columns such as breed_code, breed_name. breed_code is the primary key.\nTable Charges has columns such as charge_id, charge_type, charge_amount. charge_id is the primary key.\nTable Sizes has columns such as size_code, size_description. size_code is the primary key.\nTable Treatment_Types has columns such as treatment_type_code, treatment_type_description. treatment_type_code is the primary key.\nTable Owners has columns such as owner_id, first_name, last_name, street, city, state, zip_code, email_address, home_phone, cell_number. owner_id is the primary key.\nTable Dogs has columns such as dog_id, owner_id, abandoned_yn, breed_code, size_code, name, age, date_of_birth, gender, weight, date_arrived, date_adopted, date_departed. dog_id is the primary key.\nTable Professionals has columns such as professional_id, role_code, first_name, street, city, state, zip_code, last_name, email_address, home_phone, cell_number. professional_id is the primary key.\nTable Treatments has columns such as treatment_id, dog_id, professional_id, treatment_type_code, date_of_treatment, cost_of_treatment. treatment_id is the primary key.\nThe owner_id of Dogs is the foreign key of owner_id of Owners.\nThe owner_id of Dogs is the foreign key of owner_id of Owners.\nThe size_code of Dogs is the foreign key of size_code of Sizes.\nThe breed_code of Dogs is the foreign key of breed_code of Breeds.\nThe dog_id of Treatments is the foreign key of dog_id of Dogs.\nThe professional_id of Treatments is the foreign key of professional_id of Professionals.\nThe treatment_type_code of Treatments is the foreign key of treatment_type_code of Treatment_Types.\n\n"},

{"role": "user", 
 "content": "###Input:\n列出每种治疗的费用和相应的治疗类型描述。\n\n###Response:"},

model output:

{'role': 'assistant', 'content': 'SELECT T1.cost_of_treatment,  T2.treatment_type_description FROM Treatments AS T1 JOIN treatment_types AS T2 ON T1.treatment_type_code  =  T2.treatment_type_code'}

Explanations:

In this specific example, the role and functionality of the model can be detailed as follows:

Understanding Natural Language Input

The user's natural language input is:

列出每种治疗的费用和相应的治疗类型描述

The model first needs to understand the meaning of this Chinese description, identifying that the user wants to retrieve data about "the cost of each treatment" and "the corresponding treatment type description."

Mapping to Database Structure

Next, the model maps the user's requirements to specific tables and columns in the database. Given the defined structure of the database, the model identifies the relevant tables and columns:

• Table Treatments includes columns like treatment_id, dog_id, professional_id, treatment_type_code, date_of_treatment, cost_of_treatment, etc.
• Table Treatment_Types includes columns like treatment_type_code, treatment_type_description, etc.

Based on the requirement, "the cost of each treatment" corresponds to the cost_of_treatment column in the Treatments table, and "the corresponding treatment type description" corresponds to the treatment_type_description column in the Treatment_Types table.

Generating the SQL Query

The model then generates the SQL query to fulfill the user's request. The specific SQL statement generated is:

SELECT T1.cost_of_treatment, T2.treatment_type_description 
FROM Treatments AS T1 
JOIN Treatment_Types AS T2 
ON T1.treatment_type_code = T2.treatment_type_code;

The model can handle not just simple queries but also complex ones involving multiple tables through JOIN operations, meeting diverse data requirements.

Example-2:

The following are the inputs, outputs and explanations of the model：

model input：

{"role": "system", 
 "content": "I want you to act as a SQL terminal in front of an example database, you need only to return the sql command to me.Below is an instruction that describes a task, Write a response that appropriately completes the request.\n\"\n##Instruction:\n database contains tables such as medication, vitalperiodic, diagnosis, cost, lab, microlab, intakeoutput, treatment, patient, allergy. Table medication has columns such as medicationid, patientunitstayid, drugname, dosage, routeadmin, drugstarttime, drugstoptime. Table vitalperiodic has columns such as vitalperiodicid, patientunitstayid, temperature, sao2, heartrate, respiration, systemicsystolic, systemicdiastolic, systemicmean, observationtime. Table diagnosis has columns such as diagnosisid, patientunitstayid, diagnosisname, diagnosistime, icd9code. Table cost has columns such as costid, uniquepid, patienthealthsystemstayid, eventtype, eventid, chargetime, cost. Table lab has columns such as labid, patientunitstayid, labname, labresult, labresulttime. Table microlab has columns such as microlabid, patientunitstayid, culturesite, organism, culturetakentime. Table intakeoutput has columns such as intakeoutputid, patientunitstayid, cellpath, celllabel, cellvaluenumeric, intakeoutputtime. Table treatment has columns such as treatmentid, patientunitstayid, treatmentname, treatmenttime. Table patient has columns such as uniquepid, patienthealthsystemstayid, patientunitstayid, gender, age, ethnicity, hospitalid, wardid, admissionheight, admissionweight, dischargeweight, hospitaladmittime, hospitaladmitsource, unitadmittime, unitdischargetime, hospitaldischargetime, hospitaldischargestatus. Table allergy has columns such as allergyid, patientunitstayid, drugname, allergyname, allergytime. \n"},

{"role": "user", 
 "content": "###Input:\n did the first urine, catheter specimen microbiology test of patient 031-15666 in the last month show any organism?\n\n###Response:"},

model output:

{'role': 'assistant', 'content': "SELECT COUNT(*) > 0 FROM microlab WHERE microlab.patientunitstayid IN (SELECT patient.patientunitstayid FROM patient WHERE patient.patienthealthsystemstayid IN (SELECT patient.patienthealthsystemstayid FROM patient WHERE patient.uniquepid = '031-15666')) AND microlab.culturesite = 'urine, catheter specimen' AND DATETIME(microlab.culturetakentime,'start of month') = DATETIME(CURRENT_TIME(),'start of month', '-1 month') ORDER BY microlab.culturetakentime LIMIT 1"}

Explanations:

Understanding Natural Language Input： In this example, the model understood the user's intention, and generated the following SQL query based on the given database structure:

SELECT COUNT(*) > 0 
FROM microlab 
WHERE microlab.patientunitstayid IN (
    SELECT patient.patientunitstayid 
    FROM patient 
    WHERE patient.patienthealthsystemstayid IN (
        SELECT patient.patienthealthsystemstayid 
        FROM patient 
        WHERE patient.uniquepid = '031-15666'
    )
) 
AND microlab.culturesite = 'urine, catheter specimen' 
AND DATETIME(microlab.culturetakentime,'start of month') = DATETIME(CURRENT_TIME(),'start of month', '-1 month') 
ORDER BY microlab.culturetakentime 
LIMIT 1;

Specific Performance

• Identifying Objects: The model identified that the patient of interest is "031-15666".
• Detail Extraction: Understood that the user wants to check the microbiology test for "urine, catheter specimen".
• Time Filtering: Recognized that the user is interested in tests conducted "in the last month" and performed the necessary time conversion.
• Result Limitation: Output whether the corresponding record exists (COUNT(*) > 0), and only looked for the first matching record (LIMIT 1).

3. Ethical Considerations

While fine-tuned for text to sql, this model inherits the ethical considerations of the base Llama 3 model. Use responsibly and implement additional safeguards as needed for your application.

4. Availability

The model is available through:

• Hugging Face

For full details on responsible use, ethical considerations, and latest benchmarks, please refer to the official Llama 3 documentation.