duckdb-nsql:7b-q4_1

Provided this schema:

CREATE TABLE taxi (
    VendorID bigint,
    tpep_pickup_datetime timestamp,
    tpep_dropoff_datetime timestamp,
    passenger_count double,
    trip_distance double,
    fare_amount double,
    extra double,
    tip_amount double,
    tolls_amount double,
    improvement_surcharge double,
    total_amount double,
);

Give me taxis with more than 2 passengers

示例输出

SELECT * FROM taxi WHERE passenger_count > 2

设置系统提示

此模型需要系统提示中输入的模式作为输入。

/set system """Here is the database schema that the SQL query will run on:
CREATE TABLE taxi (
    VendorID bigint,
    tpep_pickup_datetime timestamp,
    tpep_dropoff_datetime timestamp,
    passenger_count double,
    trip_distance double,
    fare_amount double,
    extra double,
    tip_amount double,
    tolls_amount double,
    improvement_surcharge double,
    total_amount double,
);"""

在系统提示中提供模式后，模型将在后续响应中使用它。

对于以下提示

get all columns ending with _amount from taxi table

模型将输出类似这样的内容

SELECT COLUMNS('.*_amount') FROM taxi;

API示例

$ curl https://127.0.0.1:11434/api/generate -d '{
    "model": "duckdb-nsql:7b-q4_0",
    "system": "Here is the database schema that the SQL query will run on: CREATE TABLE taxi (VendorID bigint, tpep_pickup_datetime timestamp, tpep_dropoff_datetime timestamp, passenger_count double, trip_distance double, fare_amount double, extra double, tip_amount double, tolls_amount double, improvement_surcharge double, total_amount double,);",
    "prompt": "get all columns ending with _amount from taxi table"
}'

Python库示例

pip install ollama

import ollama

r = ollama.generate(
    model='duckdb-nsql:7b-q4_0',
    system='''Here is the database schema that the SQL query will run on:
CREATE TABLE taxi (
    VendorID bigint,
    tpep_pickup_datetime timestamp,
    tpep_dropoff_datetime timestamp,
    passenger_count double,
    trip_distance double,
    fare_amount double,
    extra double,
    tip_amount double,
    tolls_amount double,
    improvement_surcharge double,
    total_amount double,
);''',
    prompt='get all columns ending with _amount from taxi table',
)

print(r['response'])

训练数据

20万个DuckDB文本转SQL对，使用Mixtral-8x7B-Instruct-v0.1合成生成，并以DuckDB v0.9.2文档为指导。以及来自NSText2SQL的文本转SQL对，这些对使用sqlglot转换为DuckDB SQL。

训练过程

DuckDB-NSQL使用交叉熵损失进行训练，以最大化顺序输入的可能性。对于文本转SQL对的微调，我们只计算对中SQL部分的损失。该模型使用80GB A100进行训练，利用数据和模型并行化。我们进行了10个epoch的微调。

预期用途和局限性

该模型设计用于根据给定的表模式和自然语言提示生成文本转SQL。该模型最适合下面定义的提示格式和输出。与现有的文本转SQL模型相比，SQL生成不受限于SELECT语句，而是可以生成任何有效的DuckDB SQL语句，包括官方DuckDB扩展的语句。

参考文献

Hugging Face

![duckdb-nsql model](https://github.com/ollama/ollama/assets/3325447/b9217c78-0803-45fe-90cf-00bd76705a37)

DuckDB-NSQL is a 7 billion parameter text-to-SQL model designed specifically for SQL generation tasks.

This model is based on Meta's original Llama-2 7B model and further pre-trained on a dataset of general SQL queries and then fine-tuned on a dataset composed of DuckDB text-to-SQL pairs.

## Usage

### Example Prompt

```
Provided this schema:

CREATE TABLE taxi (
    VendorID bigint,
    tpep_pickup_datetime timestamp,
    tpep_dropoff_datetime timestamp,
    passenger_count double,
    trip_distance double,
    fare_amount double,
    extra double,
    tip_amount double,
    tolls_amount double,
    improvement_surcharge double,
    total_amount double,
);

Give me taxis with more than 2 passengers
```

### Example output

```
SELECT * FROM taxi WHERE passenger_count > 2
```

## Setting the system prompt

This model expects the schema in the system prompt as input:

```
/set system """Here is the database schema that the SQL query will run on:
CREATE TABLE taxi (
    VendorID bigint,
    tpep_pickup_datetime timestamp,
    tpep_dropoff_datetime timestamp,
    passenger_count double,
    trip_distance double,
    fare_amount double,
    extra double,
    tip_amount double,
    tolls_amount double,
    improvement_surcharge double,
    total_amount double,
);"""

```

Once the schema is provided in the system prompt, the model will use it in subsequent responses.

For the following prompt:

```
get all columns ending with _amount from taxi table
```

The model will output something like this:

```
SELECT COLUMNS('.*_amount') FROM taxi;
```

## API example

```
$ curl https://127.0.0.1:11434/api/generate -d '{
    "model": "duckdb-nsql:7b-q4_0",
    "system": "Here is the database schema that the SQL query will run on: CREATE TABLE taxi (VendorID bigint, tpep_pickup_datetime timestamp, tpep_dropoff_datetime timestamp, passenger_count double, trip_distance double, fare_amount double, extra double, tip_amount double, tolls_amount double, improvement_surcharge double, total_amount double,);",
    "prompt": "get all columns ending with _amount from taxi table"
}'
```

## Python library example

```
pip install ollama
```

```
import ollama

r = ollama.generate(
    model='duckdb-nsql:7b-q4_0',
    system='''Here is the database schema that the SQL query will run on:
CREATE TABLE taxi (
    VendorID bigint,
    tpep_pickup_datetime timestamp,
    tpep_dropoff_datetime timestamp,
    passenger_count double,
    trip_distance double,
    fare_amount double,
    extra double,
    tip_amount double,
    tolls_amount double,
    improvement_surcharge double,
    total_amount double,
);''',
    prompt='get all columns ending with _amount from taxi table',
)

print(r['response'])
```

## Training Data

200k DuckDB text-to-SQL pairs, synthetically generated using Mixtral-8x7B-Instruct-v0.1, guided by the DuckDB v0.9.2 documentation. And text-to-SQL pairs from NSText2SQL that were transpiled to DuckDB SQL using sqlglot.

## Training Procedure

DuckDB-NSQL was trained using cross-entropy loss to maximize the likelihood of sequential inputs. For finetuning on text-to-SQL pairs, we only compute the loss over the SQL portion of the pair. The model is trained using 80GB A100s, leveraging data and model parallelism. We fine-tuned for 10 epochs.

## Intended Use and Limitations

The model was designed for text-to-SQL generation tasks from given table schema and natural language prompts. The model works best with the prompt format defined below and outputs. In contrast to existing text-to-SQL models, the SQL generation is not contrained to SELECT statements, but can generate any valid DuckDB SQL statement, including statements for official DuckDB extensions.

## References

[Hugging Face](https://hugging-face.cn/motherduckdb/DuckDB-NSQL-7B-v0.1)

粘贴、拖放或点击上传图像（.png、.jpeg、.jpg、.svg、.gif）