{"course":{"slug":"hugging-face","title":"Hugging Face, End to End","url":"https://addaly.com/learn/hugging-face","level":"intermediate","track":"create"},"lesson":{"slug":"the-viewer-and-sql","title":"Query a dataset with SQL before downloading it","url":"https://addaly.com/learn/hugging-face/the-viewer-and-sql","position":42,"of":75,"minutes":8,"answer":"The Hub auto-converts public datasets to parquet, so DuckDB can run SQL against them over HTTP and answer class balance, length distribution, duplication and train–test overlap in seconds without downloading anything.","keyIdea":"The Hub auto-converts public datasets to parquet, so DuckDB can run SQL against them over HTTP and answer class balance, length distribution, duplication and train–test overlap in seconds without downloading anything."},"body":{"format":"markdown","text":"## The conversion nobody mentions\n\nWhen a dataset is published publicly on the Hub, the platform converts it to **Parquet** automatically and serves those files. The dataset viewer on the page is reading them. So is anything else that can read parquet over HTTP.\n\nThat gives you a capability most people never use: running real SQL against a dataset you have not downloaded, on a laptop, in seconds.\n\n## DuckDB, which is free and is one pip install\n\n```bash\npip install duckdb\n```\n\n```python\nimport duckdb\n\nq = \"\"\"\nSELECT label, COUNT(*) AS n\nFROM 'hf://datasets/imdb/plain_text/train-*.parquet'\nGROUP BY label\n\"\"\"\nprint(duckdb.sql(q).df())\n```\n\nDuckDB reads parquet metadata first, works out which row groups it needs, and fetches only those byte ranges. A count over a 50 GB dataset can transfer a few megabytes, because column statistics live in the footer and a `GROUP BY` on one column never touches the others.\n\n> Figure: What a GROUP BY over HTTP actually fetches. Steps: 1. Read the footer (Column statistics); 2. Pick row groups (Skip all the rest); 3. Range requests (One column only); 4. Aggregate locally (A few megabytes). A count over a fifty-gigabyte corpus can transfer a few megabytes, because parquet keeps its column statistics in the file footer and a query on one column never touches the others. This is why class balance is a question you can answer on a phone before downloading anything.\n\nThe file paths are discoverable from the API:\n\n```python\nimport requests\nr = requests.get(\"https://datasets-server.huggingface.co/parquet?dataset=imdb\").json()\nfor f in r[\"parquet_files\"][:3]:\n    print(f[\"split\"], f[\"url\"], f[\"size\"])\n```\n\n## The five questions worth asking before you download\n\n1. **How big is each class?** A dataset described as balanced frequently is not.\n\n   ```sql\n   SELECT label, COUNT(*) FROM 'hf://datasets/.../train-*.parquet' GROUP BY label\n   ```\n\n2. **How long are the texts?** This decides your `max_length` and therefore your compute bill.\n\n   ```sql\n   SELECT MIN(len), MAX(len), MEDIAN(len),\n          QUANTILE_CONT(len, 0.95) AS p95\n   FROM (SELECT LENGTH(text) AS len FROM '...')\n   ```\n\n3. **Are there duplicates?**\n\n   ```sql\n   SELECT COUNT(*) - COUNT(DISTINCT text) AS dupes FROM '...'\n   ```\n\n4. **Does the test split overlap the train split?** The leakage check, and the one that invalidates published results:\n\n   ```sql\n   SELECT COUNT(*) FROM 'hf://datasets/.../test-*.parquet' t\n   JOIN 'hf://datasets/.../train-*.parquet' r ON t.text = r.text\n   ```\n\n5. **What does the rare class actually look like?** Pull twenty rows and read them. Ten minutes of reading beats an hour of aggregate statistics, every time.\n\n## The viewer itself does more than you think\n\nOn the dataset page: full-text search across rows, sorting by column, filtering, and a per-column statistics panel showing distributions, null counts and distinct values. For a first look this is faster than writing anything, and it costs no download.\n\nIt also honestly shows what many cards do not: that a \"multilingual\" corpus is 94% English, or that a column described as clean text is 3% null.\n\n## Turning a query into a view of the whole corpus\n\nOne more pattern is worth having. Aggregates answer what is in a dataset; a sample answers what it is like. Combine them:\n\n```sql\nSELECT label, text\nFROM 'hf://datasets/.../train-*.parquet'\nUSING SAMPLE 20 ROWS\n```\n\nDuckDB's `USING SAMPLE` pulls a random subset without reading everything, so you can read twenty real rows of a terabyte corpus on a phone tethering connection. Reading twenty rows tells you about formatting, boilerplate, truncation and encoding problems that no aggregate will ever surface.\n\n## The limits\n\n- **Public datasets only** by default. Gated and private ones need a token, and very large ones may be only partially converted, which the viewer states.\n- **Some datasets have no viewer at all**: a custom loading script, an unsupported format, or an explicit opt-out. Absence is not a red flag by itself, but it does mean you are back to reading files by hand.\n- **The parquet copy can lag** a very recent update by a short period.\n\n## Querying your own files the same way\n\nDuckDB is not a Hub feature; it is a local database that happens to read remote parquet. Everything above works on a folder of your own files:\n\n```python\nduckdb.sql(\"SELECT COUNT(*) FROM 'data/*.parquet' WHERE lang = 'hi'\")\n```\n\nAnd it goes the other way, straight into the library:\n\n```python\nfrom datasets import Dataset\ndf = duckdb.sql(\"SELECT text, label FROM '...' WHERE LENGTH(text) BETWEEN 50 AND 2000\").df()\nds = Dataset.from_pandas(df)\n```\n\nSQL for selection and aggregation, `datasets` for the training path. That combination handles almost everything without Spark, without a cluster, and on hardware you already own.\n\n## Do this now\n\nPick any public dataset you have considered using and run the five queries above against it without downloading it. Most people find at least one thing the card did not say: usually the class balance or the length distribution, and occasionally the train–test overlap."},"check":{"question":"Why can a `GROUP BY` over a 50 GB Hub dataset transfer only a few megabytes?","options":[{"text":"The Hub pre-computes aggregate statistics for every column and DuckDB reads those instead of the rows.","correct":false,"tempting":"The viewer does show statistics, but an arbitrary SQL query is computed from data rather than from a precomputed summary."},{"text":"Parquet is columnar, so only the needed column's byte ranges are fetched.","correct":true},{"text":"DuckDB samples a subset of row groups and extrapolates, which is accurate enough for grouped counts.","correct":false,"tempting":"Invents sampling; the counts returned are exact."},{"text":"The query runs on the Hub's servers and only the result set crosses the network.","correct":false,"tempting":"Assumes server-side execution; DuckDB runs locally and pulls the bytes it needs."}]},"prev":{"slug":"map-filter-and-batching","title":"Transforming a million rows without waiting an hour","url":"https://addaly.com/learn/hugging-face/map-filter-and-batching"},"next":{"slug":"building-your-own-dataset","title":"Turning your files into a dataset","url":"https://addaly.com/learn/hugging-face/building-your-own-dataset"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Query a dataset with SQL before downloading it', in Hugging Face, End to End. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/hugging-face/the-viewer-and-sql (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Query a dataset with SQL before downloading it","courseTitle":"Hugging Face, End to End","module":{"index":5,"slug":"datasets-and-the-work-before-the-model","title":"Datasets, and the work that happens before any model"},"answer":null,"keyIdea":"The Hub auto-converts public datasets to parquet, so DuckDB can run SQL against them over HTTP and answer class balance, length distribution, duplication and train–test overlap in seconds without downloading anything.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"flow","steps":[{"label":"Read the footer","detail":"Column statistics"},{"label":"Pick row groups","detail":"Skip all the rest"},{"label":"Range requests","detail":"One column only"},{"label":"Aggregate locally","detail":"A few megabytes"}],"title":"What a GROUP BY over HTTP actually fetches","caption":"A count over a fifty-gigabyte corpus can transfer a few megabytes, because parquet keeps its column statistics in the file footer and a query on one column never touches the others. This is why class balance is a question you can answer on a phone before downloading anything."},"alt":"Figure: What a GROUP BY over HTTP actually fetches. Steps: 1. Read the footer (Column statistics); 2. Pick row groups (Skip all the rest); 3. Range requests (One column only); 4. Aggregate locally (A few megabytes). A count over a fifty-gigabyte corpus can transfer a few megabytes, because parquet keeps its column statistics in the file footer and a query on one column never touches the others. This is why class balance is a question you can answer on a phone before downloading anything."}},"blocks":[{"t":"h2","id":"the-conversion-nobody-mentions","runs":[{"text":"The conversion nobody mentions"}]},{"t":"p","runs":[{"text":"When a dataset is published publicly on the Hub, the platform converts it to "},{"text":"Parquet","b":true},{"text":" automatically and serves those files. The dataset viewer on the page is reading them. So is anything else that can read parquet over HTTP."}]},{"t":"p","runs":[{"text":"That gives you a capability most people never use: running real SQL against a dataset you have not downloaded, on a laptop, in seconds."}]},{"t":"h2","id":"duckdb-which-is-free-and-is-one-pip-install","runs":[{"text":"DuckDB, which is free and is one pip install"}]},{"t":"code","lang":"bash","label":"Bash","text":"pip install duckdb"},{"t":"code","lang":"python","label":"Python","text":"import duckdb\n\nq = \"\"\"\nSELECT label, COUNT(*) AS n\nFROM 'hf://datasets/imdb/plain_text/train-*.parquet'\nGROUP BY label\n\"\"\"\nprint(duckdb.sql(q).df())"},{"t":"p","runs":[{"text":"DuckDB reads parquet metadata first, works out which row groups it needs, and fetches only those byte ranges. A count over a 50 GB dataset can transfer a few megabytes, because column statistics live in the footer and a "},{"text":"GROUP BY","code":true},{"text":" on one column never touches the others."}]},{"t":"p","runs":[{"text":"The file paths are discoverable from the API:"}]},{"t":"code","lang":"python","label":"Python","text":"import requests\nr = requests.get(\"https://datasets-server.huggingface.co/parquet?dataset=imdb\").json()\nfor f in r[\"parquet_files\"][:3]:\n    print(f[\"split\"], f[\"url\"], f[\"size\"])"},{"t":"h2","id":"the-five-questions-worth-asking-before-you-download","runs":[{"text":"The five questions worth asking before you download"}]},{"t":"ol","start":1,"items":[[{"text":"How big is each class?","b":true},{"text":" A dataset described as balanced frequently is not."}]]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT label, COUNT(*) FROM 'hf://datasets/.../train-*.parquet' GROUP BY label"},{"t":"ol","start":2,"items":[[{"text":"How long are the texts?","b":true},{"text":" This decides your "},{"text":"max_length","code":true},{"text":" and therefore your compute bill."}]]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT MIN(len), MAX(len), MEDIAN(len),\n       QUANTILE_CONT(len, 0.95) AS p95\nFROM (SELECT LENGTH(text) AS len FROM '...')"},{"t":"ol","start":3,"items":[[{"text":"Are there duplicates?","b":true}]]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT COUNT(*) - COUNT(DISTINCT text) AS dupes FROM '...'"},{"t":"ol","start":4,"items":[[{"text":"Does the test split overlap the train split?","b":true},{"text":" The leakage check, and the one that invalidates published results:"}]]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT COUNT(*) FROM 'hf://datasets/.../test-*.parquet' t\nJOIN 'hf://datasets/.../train-*.parquet' r ON t.text = r.text"},{"t":"ol","start":5,"items":[[{"text":"What does the rare class actually look like?","b":true},{"text":" Pull twenty rows and read them. Ten minutes of reading beats an hour of aggregate statistics, every time."}]]},{"t":"h2","id":"the-viewer-itself-does-more-than-you-think","runs":[{"text":"The viewer itself does more than you think"}]},{"t":"p","runs":[{"text":"On the dataset page: full-text search across rows, sorting by column, filtering, and a per-column statistics panel showing distributions, null counts and distinct values. For a first look this is faster than writing anything, and it costs no download."}]},{"t":"p","runs":[{"text":"It also honestly shows what many cards do not: that a “multilingual” corpus is 94% English, or that a column described as clean text is 3% null."}]},{"t":"h2","id":"turning-a-query-into-a-view-of-the-whole-corpus","runs":[{"text":"Turning a query into a view of the whole corpus"}]},{"t":"p","runs":[{"text":"One more pattern is worth having. Aggregates answer what is in a dataset; a sample answers what it is like. Combine them:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT label, text\nFROM 'hf://datasets/.../train-*.parquet'\nUSING SAMPLE 20 ROWS"},{"t":"p","runs":[{"text":"DuckDB’s "},{"text":"USING SAMPLE","code":true},{"text":" pulls a random subset without reading everything, so you can read twenty real rows of a terabyte corpus on a phone tethering connection. Reading twenty rows tells you about formatting, boilerplate, truncation and encoding problems that no aggregate will ever surface."}]},{"t":"h2","id":"the-limits","runs":[{"text":"The limits"}],"role":"limit"},{"t":"ul","items":[[{"text":"Public datasets only","b":true},{"text":" by default. Gated and private ones need a token, and very large ones may be only partially converted, which the viewer states."}],[{"text":"Some datasets have no viewer at all","b":true},{"text":": a custom loading script, an unsupported format, or an explicit opt-out. Absence is not a red flag by itself, but it does mean you are back to reading files by hand."}],[{"text":"The parquet copy can lag","b":true},{"text":" a very recent update by a short period."}]]},{"t":"h2","id":"querying-your-own-files-the-same-way","runs":[{"text":"Querying your own files the same way"}]},{"t":"p","runs":[{"text":"DuckDB is not a Hub feature; it is a local database that happens to read remote parquet. Everything above works on a folder of your own files:"}]},{"t":"code","lang":"python","label":"Python","text":"duckdb.sql(\"SELECT COUNT(*) FROM 'data/*.parquet' WHERE lang = 'hi'\")"},{"t":"p","runs":[{"text":"And it goes the other way, straight into the library:"}]},{"t":"code","lang":"python","label":"Python","text":"from datasets import Dataset\ndf = duckdb.sql(\"SELECT text, label FROM '...' WHERE LENGTH(text) BETWEEN 50 AND 2000\").df()\nds = Dataset.from_pandas(df)"},{"t":"p","runs":[{"text":"SQL for selection and aggregation, "},{"text":"datasets","code":true},{"text":" for the training path. That combination handles almost everything without Spark, without a cluster, and on hardware you already own."}]},{"t":"h2","id":"do-this-now","runs":[{"text":"Do this now"}],"role":"try"},{"t":"p","runs":[{"text":"Pick any public dataset you have considered using and run the five queries above against it without downloading it. Most people find at least one thing the card did not say: usually the class balance or the length distribution, and occasionally the train–test overlap."}]}],"check":{"question":[{"text":"Why can a "},{"text":"GROUP BY","code":true},{"text":" over a 50 GB Hub dataset transfer only a few megabytes?"}],"options":[{"text":[{"text":"The Hub pre-computes aggregate statistics for every column and DuckDB reads those instead of the rows."}],"correct":false,"tempting":[{"text":"The viewer does show statistics, but an arbitrary SQL query is computed from data rather than from a precomputed summary."}]},{"text":[{"text":"Parquet is columnar, so only the needed column’s byte ranges are fetched."}],"correct":true,"tempting":null},{"text":[{"text":"DuckDB samples a subset of row groups and extrapolates, which is accurate enough for grouped counts."}],"correct":false,"tempting":[{"text":"Invents sampling; the counts returned are exact."}]},{"text":[{"text":"The query runs on the Hub’s servers and only the result set crosses the network."}],"correct":false,"tempting":[{"text":"Assumes server-side execution; DuckDB runs locally and pulls the bytes it needs."}]}],"order":[2,0,3,1],"why":null},"prev":{"slug":"map-filter-and-batching","title":"Transforming a million rows without waiting an hour","minutes":9},"next":{"slug":"building-your-own-dataset","title":"Turning your files into a dataset","minutes":9}}}