File size: 4,092 Bytes
42c6c72
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
---
license: cc-by-nc-sa-4.0
language:
- en
library_name: transformers
tags:
- text-to-sql
- text2sql
- nlp2sql
- nlp-to-sql
- SQL
---
# Model Card for text2sql

<!-- Provide a quick summary of what the model is/does. -->

LLM instruction finetuned for Text-to-SQL task. 


## How to Get Started with the Model

Use the code below to get started with the model.

```
import torch
from transformers import AutoModelForCausalLM, AutoTokenizer

model = AutoModelForCausalLM.from_pretrained(
                                "dataeaze/dataeaze-text2sql-codellama_7b_instruct-clinton_text_to_sql_v1", 
                                torch_dtype=torch.bfloat16,
                                device_map='auto'
                                )

tokenizer = AutoTokenizer.from_pretrained("dataeaze/dataeaze-text2sql-codellama_7b_instruct-clinton_text_to_sql_v1")
# print("model device :", model.device)
tokenizer.pad_token = tokenizer.eos_token
model.eval()

prompt = """ Below are sql tables schemas paired with instruction that describes a task. 
Using valid SQLite, write a response that appropriately completes the request for the provided tables. 
### Instruction: How many transactions were made by a customer in a specific month? 
### Database: RewardsProgramDB61 
### Input: 
CREATE SCHEMA RewardsProgram;

CREATE TABLE Customer (
    CustomerID INT NOT NULL AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Phone VARCHAR(20) UNIQUE,
    DateOfBirth DATE,
    PRIMARY KEY (CustomerID)
);

CREATE TABLE Membership (
    MembershipID INT NOT NULL AUTO_INCREMENT,
    MembershipType VARCHAR(50) NOT NULL,
    DiscountPercentage DECIMAL(5, 2) NOT NULL,
    ValidFrom DATETIME,
    ValidTo DATETIME,
    CustomerID INT NOT NULL,
    PRIMARY KEY (MembershipID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE Transaction (
    TransactionID INT NOT NULL AUTO_INCREMENT,
    TransactionDate TIMESTAMP,
    TotalAmount DECIMAL(10, 2) NOT NULL,
    CustomerID INT NOT NULL,
    PRIMARY KEY (TransactionID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE TransactionDetail (
    TransactionDetailID INT NOT NULL AUTO_INCREMENT,
    TransactionID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (TransactionDetailID),
    FOREIGN KEY (TransactionID) REFERENCES Transaction(TransactionID),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

CREATE TABLE Product (
    ProductID INT NOT NULL AUTO_INCREMENT,
    ProductName VARCHAR(100) NOT NULL,
    UnitPrice DECIMAL(10, 2) NOT NULL,
    AvailableQuantity INT NOT NULL,
    CreatedDate DATETIME,
    PRIMARY KEY (ProductID)
);

ALTER TABLE Membership ADD CONSTRAINT FK_Membership_Customer FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID);

ALTER TABLE TransactionDetail ADD CONSTRAINT FK_TransactionDetail_Transaction FOREIGN KEY (TransactionID) REFERENCES Transaction(TransactionID);

ALTER TABLE TransactionDetail ADD CONSTRAINT FK_TransactionDetail_Product FOREIGN KEY (ProductID) REFERENCES Product(ProductID);"
"""

input_ids = tokenizer(prompt, padding=True, return_tensors='pt')
outputs = model.generate(
    input_ids=input_ids['input_ids'].to(model.device),
    attention_mask=input_ids['attention_mask'].to(model.device),
    max_new_tokens=3072,
)

generated_query = tokenizer.decode(outputs[0], skip_special_tokens=True)
print(generated_query)


```



### Results

```
model-index:
  - name: dataeaze/dataeaze-text2sql-codellama_7b_instruct-dzsql
    results:
    - task:
        type: text-to-sql
      dataset:
        name: SPIDER 1.0
        type: text-to-sql
      metrics:
        - name: Execution with Values
          type: Execution with Values
          value: 64.3
        - name: Exact Set Match without Values
          type: Exact Set Match without Values
          value: 29.6
      source:
        name: Spider 1.0 - Leaderboard
        url: https://yale-lily.github.io/spider
```