Text2SQL with Hugging Face Dataset Viewer API and DuckDB-NSQL-7B

Hugging Face has demonstrated a workflow for implementing Text-to-SQL capabilities using the DuckDB-NSQL-7B model in conjunction with the Hugging Face Dataset Viewer API. This integration allows users to query over 120,000 open datasets using natural language, bypassing the need for manual SQL coding.

DuckDB-NSQL-7B Model Overview

DuckDB-NSQL-7B is a state-of-the-art Large Language Model (LLM) specifically designed for DuckDB SQL. Developed by MotherDuck and Numbers Station, the model is fine-tuned from Meta's Llama-2-7b.

Training and Capabilities

  • Fine-tuning Process: The model was initially fine-tuned on a broad dataset of general SQL queries and subsequently refined with DuckDB-specific text-to-SQL pairs.
  • Versatility: Beyond standard SELECT statements, DuckDB-NSQL-7B can generate a wide range of valid DuckDB SQL statements, including those utilizing official documentation and extensions.

Integrating with Hugging Face Dataset Viewer API

The Hugging Face Dataset Viewer API provides the infrastructure necessary to feed the LLM the correct context for query generation. It allows the system to programmatically access metadata and data formats for more than 120,000 datasets.

Key API Functionalities

  • Schema Retrieval: The API provides dataset splits, column names, and data types.
  • Data Access: It enables the download of rows at any index and provides access to datasets as auto-converted Parquet files.
  • Metadata: The API serves dataset size (rows/bytes), full-text search, and filtering options.

Technical Implementation Workflow

The Text-to-SQL pipeline follows a specific sequence to translate a natural language question into a data result:

1. Schema Extraction

To provide the model with the necessary context, the system retrieves the dataset schema. For example, using the world-cities-geo dataset, the system fetches the first Parquet file via the API and uses DuckDB to simulate a table creation from the first row to extract the Data Definition Language (DDL) CREATE statement.

2. Prompt Construction

The model requires a structured prompt containing the instruction, the database schema, and the user's question. The prompt format is as follows:

### Instruction:
Your task is to generate valid duckdb SQL to answer the following question.
### Input:
Here is the database schema that the SQL query will run on:
{ddl_create}
### Question:
{query_input}
### Response (use duckdb shorthand if possible):

3. SQL Generation and Execution

  • Model Inference: The prompt is sent to the DuckDB-NSQL-7B model (which can be run via transformers pipeline, AutoModelForCausalLM, or llama.cpp for GGUF quantized versions).
  • Query Modification: Because the model generates queries referencing a generic data table, the output is modified to replace FROM data with the actual URL of the Parquet file hosted on the Hugging Face viewer.
  • Execution: The final SQL query is executed using DuckDB, which can query Parquet files directly via URL, returning the filtered data as a dataframe.

Deployment Options

The DuckDB-NSQL-7B model can be deployed using several methods depending on hardware and performance requirements:

  • Hugging Face Transformers: Standard pipeline or tokenizer/model loading.
  • llama.cpp: Used for local or cloud inference with minimal setup and high performance, specifically utilizing the GGUF quantized version (DuckDB-NSQL-7B-v0.1-q8_0.gguf).

Sources