# Querying and Transforming Hugging Face Datasets with DuckDB and hf://: A Complete Guide

> Query Hugging Face datasets with DuckDB using the hf:// protocol. Transform and export data seamlessly without downloading files. A complete guide.

- Repository: [Hugging Face/skills](https://github.com/huggingface/skills)
- Tags: how-to-guide
- Published: 2026-03-08

---

**You can execute SQL directly on remote Hugging Face datasets using DuckDB's `hf://` protocol and the `HFDatasetSQL` wrapper, eliminating the need to download entire archives while enabling seamless export back to the Hub.**

The `huggingface/skills` repository provides a production-ready solution for dataset exploration through the `hugging-face-datasets` skill. By leveraging DuckDB's ability to query Parquet files over the `hf://` virtual filesystem, the `HFDatasetSQL` class streams data on-demand and supports complex transformations without local storage constraints.

## How the hf:// Protocol Works in DuckDB

DuckDB understands `hf://` as a virtual filesystem that maps to the Hugging Face Hub's Parquet-backed datasets. When you instantiate `HFDatasetSQL`, the internal `_build_hf_path` method constructs URIs matching the pattern `hf://datasets/{dataset_id}/*.parquet`, targeting the `~parquet` revision by default.

The wrapper intercepts SQL queries containing the placeholder table name `data` and substitutes it with the generated `hf://` URI. If your query already contains explicit `hf://` paths, the engine passes it through unchanged. This architecture allows DuckDB to perform predicate pushdown and column pruning directly on remote files, transferring only the rows and columns you actually need.

## Setting Up the Connection

Initialize the manager by calling `HFDatasetSQL.__init__`, which creates an in-process DuckDB connection. If the environment variable `HF_TOKEN` is present, the constructor registers it as a secret using `CREATE SECRET hf_token`, enabling access to private datasets and gated repositories.

```python
from sql_manager import HFDatasetSQL

# Initialize with automatic token detection

sql = HFDatasetSQL()

```

Always invoke `sql.close()` when finished to release the DuckDB connection and associated resources.

## Core Querying Methods

The `query` method in [`skills/hugging-face-datasets/scripts/sql_manager.py`](https://github.com/huggingface/skills/blob/main/skills/hugging-face-datasets/scripts/sql_manager.py) serves as the foundation for all data retrieval. It accepts a `dataset_id` and SQL string, replaces the `data` placeholder with the proper `hf://` URI, and returns results in your choice of format: Python dictionaries, pandas DataFrames, PyArrow tables, or raw tuples.

```python

# Return results as a list of dictionaries

rows = sql.query(
    dataset_id="cais/mmlu",
    sql="SELECT * FROM data WHERE subject='nutrition' LIMIT 5",
    return_type="dict"
)

```

For schema inspection, use `describe(dataset_id)`, which queries the Parquet metadata and returns column definitions with types. To fetch a reproducible random sample, call `sample(dataset_id, n=10, seed=42)`, which uses DuckDB's `ORDER BY random()` with a fixed seed for deterministic results.

## Filtering, Aggregation, and Analysis

The skill provides high-level helpers that construct SQL behind the scenes:

- **`count(dataset_id, where)`** – Executes `SELECT COUNT(*)` with optional predicates.
- **`unique_values(dataset_id, column)`** – Returns distinct values for categorical analysis.
- **`histogram(dataset_id, column, bins)`** – Generates frequency distributions using DuckDB's `histogram` function.
- **`filter_and_transform(dataset_id, select, where, group_by)`** – A flexible builder for complex aggregations.

```python

# Count rows matching a condition

nutrition_count = sql.count(
    dataset_id="cais/mmlu",
    where="subject='nutrition'"
)

# Generate a distribution of subjects

histogram_data = sql.histogram(
    dataset_id="cais/mmlu",
    column="subject",
    bins=20
)

```

## Joining Multiple Datasets

The `join_datasets` method constructs multi-source SQL statements using separate `hf://` URIs for the left and right tables. It automatically handles split specifications and generates the appropriate `JOIN` clause.

```python
joined = sql.join_datasets(
    left_dataset="cais/mmlu",
    right_dataset="cais/grade-school-math",
    on="left_table.id = right_table.id",
    select="left_table.subject, right_table.question",
    left_split="train",
    right_split="train",
    limit=20
)

```

This operation streams both datasets simultaneously without downloading them to disk, performing the join inside DuckDB's in-memory engine.

## Exporting and Publishing Results

Once you have transformed your data, export it using `export_to_parquet` or `export_to_jsonl` for local persistence. To publish results back to the Hub, use `push_to_hub`, which writes the query results to a temporary Parquet file and uploads it via the `datasets` library.

```python

# Export locally

sql.export_to_parquet(
    dataset_id="cais/mmlu",
    output_path="nutrition_subset.parquet",
    sql="SELECT * FROM data WHERE subject='nutrition'"
)

# Push to a new private repository

url = sql.push_to_hub(
    dataset_id="cais/mmlu",
    target_repo="myusername/nutrition-subset",
    sql="SELECT subject, choices[answer] AS correct_answer FROM data WHERE subject='nutrition'",
    private=True
)

```

The `push_to_hub` method handles authentication, repository creation, and file upload in a single call.

## Command-Line Interface

The `main()` function in [`sql_manager.py`](https://github.com/huggingface/skills/blob/main/sql_manager.py) exposes all Python API features through a CLI using `uv run`. This enables rapid data exploration without writing scripts.

```bash

# Inspect schema

uv run scripts/sql_manager.py describe --dataset "cais/mmlu"

# Sample with seed

uv run scripts/sql_manager.py sample --dataset "cais/mmlu" --n 5 --seed 42

# Complex transformation and upload

uv run scripts/sql_manager.py transform \
    --dataset "cais/mmlu" \
    --select "subject, COUNT(*) AS cnt" \
    --group-by "subject" \
    --order-by "cnt DESC" \
    --limit 10 \
    --push-to "myusername/mmlu-subject-counts" --private

```

## Summary

- The `HFDatasetSQL` class in [`skills/hugging-face-datasets/scripts/sql_manager.py`](https://github.com/huggingface/skills/blob/main/skills/hugging-face-datasets/scripts/sql_manager.py) wraps DuckDB to query Hugging Face datasets via the `hf://` protocol.
- The `_build_hf_path` method generates virtual filesystem URIs pointing to Parquet files, enabling streaming without full downloads.
- All operations occur in-process; you can return data as dictionaries, DataFrames, or Arrow tables, then export locally or push back to the Hub using `export_to_parquet` or `push_to_hub`.
- The CLI entry point in `main()` provides immediate access to sampling, filtering, histograms, and transformations.

## Frequently Asked Questions

### What is the hf:// protocol in DuckDB?

The `hf://` protocol is a virtual filesystem implementation recognized by DuckDB that maps URI paths to the Hugging Face Hub's dataset files. When you use `hf://datasets/{dataset_id}/*.parquet`, DuckDB streams the Parquet files directly from the Hub, applying SQL predicates remotely to minimize data transfer.

### Do I need to download entire datasets to query them?

No. Because DuckDB supports direct Parquet querying over HTTP via the `hf://` protocol, only the necessary row groups and columns are fetched over the network. The `HFDatasetSQL` class handles URI construction and secret management, allowing you to run SQL on multi-gigabyte datasets using minimal local memory.

### How do I push transformed data back to Hugging Face?

Use the `push_to_hub` method, which takes your SQL query results, writes them to a temporary Parquet file, and uploads the dataset using the `datasets` library. You can specify `private=True` to create a private repository, and the method returns the URL of the newly created dataset.

### Can I join two different Hugging Face datasets together?

Yes. The `join_datasets` method constructs a SQL query referencing two separate `hf://` URIs and executes a JOIN operation inside DuckDB. You specify the join condition, column selection, and splits for each dataset, and DuckDB streams both sources simultaneously to produce the result.