DuckDB and Hugging Face: Querying Datasets Directly

Hugging Face is where much of the machine learning community publishes and finds its datasets, while DuckDB is the in-process analytical database that queries files like CSV and Parquet directly, with no server or warehouse to install or run.

Did you know that, since DuckDB v0.10.3 (released on May 22, 2024), you can point a SELECT at a dataset on the Hugging Face Hub, using the DuckDB hf:// protocol, and query it, without downloading it first? This post covers how that integration works and the use cases it fits.

Background

On the Hugging Face Hub, each dataset is a git repository holding its data as plain files, usually CSV, JSONL, or Parquet. The cais/mmlu benchmark and the datasets-examples/doc-formats-csv-1 repository used later in this post are two such examples: you can browse their files and commit history in the browser, the same way you would any git repository.

Before the Hugging Face integration, getting at that data from DuckDB meant downloading the files first, or loading them with the Hugging Face datasets library, before they could be read and analyzed. Either way, the data had to be copied out of the Hugging Face Hub before you could query it.

DuckDB was already able to read remote files over HTTP through its httpfs extension, so reading a URL directly was not new. Hugging Face datasets are also increasingly published as Parquet, the columnar format DuckDB reads natively and can scan without materializing everything in memory.

DuckDB and Hugging Face worked together to add the hf:// path scheme on top of httpfs, announced in May 2024 with DuckDB v0.10.3. As a result, DuckDB can resolve a dataset repository to the files inside it, so that a query can read and analyze them exactly where they are located, instead of via a downloaded copy.

Reading Hugging Face Datasets Directly

The examples below cover the common cases. DuckDB's Hugging Face docs are the full reference. The scheme maps a Hugging Face dataset repository onto a path DuckDB can read:

hf://datasets/my_username/my_dataset/path_to_file

Reading a file is then just a query. This reads the CSV file from the datasets-examples/doc-formats-csv-1 repository:

SELECT*FROM'hf://datasets/datasets-examples/doc-formats-csv-1/data.csv';

Here datasets-examples is the user or organization, doc-formats-csv-1 is the dataset repository, and data.csv is the file inside it. The same example data is published in the doc-formats-jsonl-1 and doc-formats-parquet-1 repositories, so these queries return the same four rows:

SELECT*FROM'hf://datasets/datasets-examples/doc-formats-jsonl-1/data.jsonl';

SELECT*FROM'hf://datasets/datasets-examples/doc-formats-parquet-1/data/train-00000-of-00001.parquet';

DuckDB infers the format from the file and reads only the columns that the query actually needs. And, nothing is downloaded to a local copy first.

Querying Many Files at Once

Datasets are often split across many files. A glob pattern lets you treat a whole directory as one table. The cais/mmlu benchmark stores its astronomy task across three Parquet files (dev, test, and validation), and this counts the rows across all of them:

SELECTcount(*)AScountFROM'hf://datasets/cais/mmlu/astronomy/*.parquet';

Because DuckDB reads Parquet column by column, you can filter across all those files without pulling every row into memory:

SELECTcount(*)AScountFROM'hf://datasets/cais/mmlu/astronomy/*.parquet'WHEREquestionLIKE'%planet%';

Versions and the ~parquet Branch

Each Hugging Face dataset is a git repository, so it has branches and revisions. You can pin a query to a specific one with an @ suffix:

SELECT*FROM'hf://datasets/datasets-examples/doc-formats-csv-1@~parquet/**/*.parquet';

Hugging Face automatically converts every dataset into Parquet on this special ~parquet branch to make it efficient to scan. So even a dataset published as CSV or JSONL usually has a columnar version ready, which is what DuckDB reads fastest.

Saving a Local Copy

If you are going to query the same data repeatedly, materialize it once so you are not hitting the remote endpoint each time:

CREATETABLEdataASSELECT*FROM'hf://datasets/datasets-examples/doc-formats-csv-1/data.csv';

After that, the data lives in the local table and queries no longer touch the Hugging Face Hub:

SELECT*FROMdata;

Private and Gated Datasets

Public datasets need no setup. For private or gated ones, store a Hugging Face token in DuckDB's Secrets Manager. You can pass the token directly:

CREATESECREThf_token(TYPEhuggingface,TOKEN'your_hf_token');

Or let DuckDB pick it up from ~/.cache/huggingface/token, where the Hugging Face tooling stores it:

CREATESECREThf_token(TYPEhuggingface,PROVIDERcredential_chain);

Typical Use Cases

The integration is a good fit whenever you want to look at data on the Hugging Face Hub without committing to a download or a pipeline.

SELECT
    count(*) AS questions,
    count(DISTINCT question) AS distinct_questions,
    avg(len(choices)) AS avg_choices
FROM 'hf://datasets/cais/mmlu/astronomy/*.parquet';

COPY (
    SELECT question, choices, answer
    FROM 'hf://datasets/cais/mmlu/astronomy/*.parquet'
    WHERE question LIKE '%planet%'
) TO 'astronomy_planets.parquet';

SELECT subject, count(*) AS questions
FROM read_parquet([
    'hf://datasets/cais/mmlu/astronomy/test-00000-of-00001.parquet',
    'hf://datasets/cais/mmlu/anatomy/test-00000-of-00001.parquet'
])
GROUP BY subject
ORDER BY subject;

SELECT l.letter AS correct_choice, count(*) AS n
FROM 'hf://datasets/cais/mmlu/astronomy/*.parquet' AS m
JOIN (VALUES (0, 'A'), (1, 'B'), (2, 'C'), (3, 'D')) AS l(idx, letter)
  ON m.answer = l.idx
GROUP BY l.letter
ORDER BY l.letter;

SELECT count(*) AS count
FROM 'hf://datasets/cais/mmlu@c30699e8356da336a370243923dbaf21066bb9fe/astronomy/*.parquet';

• Exploring a dataset before you use it. Before training or finetuning a dataset, you usually want to know what is in it: the row count, how many rows are unique, and what the columns look like. A single query against an hf:// path answers that, reading only the columns you ask for:
• Filtering and sampling for training. Large datasets often need to be narrowed to a subset, say a single language or the rows above some quality threshold, before they are useful. Express that as a WHERE clause and write the result straight to a local Parquet file with COPY: This turns a remote dataset into a focused local file, here the 21 astronomy questions that mention a planet.
• Working with benchmarks and evaluation sets. Benchmarks like MMLU ship as many small files grouped by task. You can read several tasks as one table and compute per-task statistics:
• Joining Hugging Face Hub data with your own. Because an hf:// path behaves like any other table source, you can join a public dataset against your own tables. Here a small lookup table maps each numeric answer to a choice letter:
• Reproducible analysis. Pinning a query to a specific commit means it reads the same data every time it runs, which matters for anything you need to reproduce later. Add the revision with an @ suffix:

Conclusion

The hf:// protocol lets you query a dataset on the Hugging Face Hub by putting its path in a SELECT, with no download step and no server to run.

If you work with datasets on the Hugging Face Hub, that covers a lot of day-to-day tasks, from inspecting a new dataset to creating a training subset out of a larger one.

For further reading, see the original announcement post, DuckDB's Hugging Face docs, and Hugging Face's own DuckDB guide.