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

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.

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 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.

# 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.

# 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.

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.

# 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 exposes all Python API features through a CLI using uv run. This enables rapid data exploration without writing scripts.

# 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}