This listing is powered by the REST API noted above. To obtain the list of Parquet files for any dataset, we can simply use the following endpoint.
```bash
https://datasets-server.huggingface.co/parquet?dataset=Before we query any Parquet files, let's confirm we can utilize the above API in ClickHouse using the `url` function. For consistency of the response, we request the output be rendered as JSON. ```sql SELECT json FROM url('https://datasets-server.huggingface.co/parquet?dataset=blog_authorship_corpus', 'JSONAsString') FORMAT Vertical Row 1: ────── json: json: {"parquet_files":[{"dataset":"blog_authorship_corpus","config":"blog_authorship_corpus","split":"train","url":"https://huggingface.co/datasets/blog_authorship_corpus/resolve/refs%2Fconvert%2Fparquet/blog_authorship_corpus/train/0000.parquet","filename":"0000.parquet","size":301216503},{"dataset":"blog_authorship_corpus","config":"blog_authorship_corpus","split":"train","url":"https://huggingface.co/datasets/blog_authorship_corpus/resolve/refs%2Fconvert%2Fparquet/blog_authorship_corpus/train/0001.parquet","filename":"0001.parquet","size":152312736},{"dataset":"blog_authorship_corpus","config":"blog_authorship_corpus","split":"validation","url":"https://huggingface.co/datasets/blog_authorship_corpus/resolve/refs%2Fconvert%2Fparquet/blog_authorship_corpus/validation/0000.parquet","filename":"0000.parquet","size":24997972}],"pending":[],"failed":[],"partial":false} 1 row in set. Elapsed: 1.220 sec. ``` Examining the urls closely, we can see files reside under the path [https://huggingface.co/datasets/](https://huggingface.co/datasets/). This seems to be consistent across datasets. ## Dataset For our sample dataset, we use [`maharshipandya/spotify-tracks-dataset`](https://huggingface.co/datasets/maharshipandya/spotify-tracks-dataset). This represents a Spotify tracks dataset, with each row containing information for a given track over a range of 125 different genres. Each track has audio features associated with it, such as its duration, genre and tempo. Using the above url function and [JSON_QUERY](https://clickhouse.com/docs/en/sql-reference/functions/json-functions#json_queryjson-path) function, we can extract a concise list of Parquet files available for this dataset. ```sql SELECT JSON_QUERY(json, '$.parquet_files[*].url') AS urls FROM url('https://datasets-server.huggingface.co/parquet?dataset=maharshipandya/spotify-tracks-dataset', 'JSONAsString') ┌─urls──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ ["https://huggingface.co/datasets/maharshipandya/spotify-tracks-dataset/resolve/refs%2Fconvert%2Fparquet/default/train/0000.parquet"] │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 1 row in set. Elapsed: 0.908 sec. ``` With only one file, this represents one of the simplest datasets to work with in Hugging Face. In this case, we can simply query the file directly - again using the url function. ```sql SELECT count() FROM url('https://huggingface.co/datasets/maharshipandya/spotify-tracks-dataset/resolve/refs%2Fconvert%2Fparquet/default/train/0000.parquet') SETTINGS enable_url_encoding = 0, max_http_get_redirects = 1 ┌─count()─┐ │ 114000 │ └─────────┘ 1 row in set. Elapsed: 1.280 sec. Processed 77.00 thousand rows, 8.81 MB (60.16 thousand rows/s., 6.88 MB/s.) ``` We are required to specify the parameter `max_http_get_redirects = 1`. This ensures we follow the redirect to the CDN path that is returned by the Parquet file’s url. The parameter `enable_url_encoding = 0`, introduced in 23.7, ensures no decoding is applied to the URL - the escape characters in the path are intentional and should be preserved. **For all subsequent queries assume these parameters are set in the session.**For all of our examples, we use the console mode of clickhouse-local. For Linux administrators or users wishing to incorporate clickhouse-local into scripts, any query can be passed through the
--queryparameter with the response provided by stdout.
To simplify subsequent requests, we can create a url table engine to abstract the url. This allows us to query the dataset with the table `spotify` in all subsequent queries. This table will exist for the lifetime of the `clickhouse-local` session. ```sql SET max_http_get_redirects = 1 SET enable_url_encoding = 0 CREATE TABLE spotify ENGINE=URL('https://huggingface.co/datasets/maharshipandya/spotify-tracks-dataset/resolve/refs%2Fconvert%2Fparquet/default/train/0000.parquet') SELECT count() FROM spotify ┌─count()─┐ │ 114000 │ └─────────┘ 1 row in set. Elapsed: 0.838 sec. Processed 39.00 thousand rows, 4.51 MB (46.52 thousand rows/s., 5.37 MB/s.) ``` **All queries below use the `spotify` table created above.** ### Exploring the dataset To identify the columns available, we can rely on ClickHouse’s type inference capabilities and issue a [DESCRIBE](https://clickhouse.com/docs/en/sql-reference/statements/describe-table) query. ```sql DESCRIBE TABLE spotify ┌─name─────────────┬─type──────────────┬ │ Unnamed: 0 │ Nullable(Int64) │ │ track_id │ Nullable(String) │ │ artists │ Nullable(String) │ │ album_name │ Nullable(String) │ │ track_name │ Nullable(String) │ │ popularity │ Nullable(Int64) │ │ duration_ms │ Nullable(Int64) │ │ explicit │ Nullable(Bool) │ │ danceability │ Nullable(Float64) │ │ energy │ Nullable(Float64) │ │ key │ Nullable(Int64) │ │ loudness │ Nullable(Float64) │ │ mode │ Nullable(Int64) │ │ speechiness │ Nullable(Float64) │ │ acousticness │ Nullable(Float64) │ │ instrumentalness │ Nullable(Float64) │ │ liveness │ Nullable(Float64) │ │ valence │ Nullable(Float64) │ │ tempo │ Nullable(Float64) │ │ time_signature │ Nullable(Int64) │ │ track_genre │ Nullable(String) │ └──────────────────┴───────────────────┴ 21 rows in set. Elapsed: 0.000 sec. ``` A full description of these columns is available [here](https://huggingface.co/datasets/maharshipandya/spotify-tracks-dataset) for those interested. We’ll provide descriptions when using a column below and its content is not obvious. ## Simple queries Before performing an analysis of a dataset in detail using more complex statistical functions, users often need to perform simple queries to understand the data. ### Aggregations Aggregations are helpful in understanding the common values in each column. Below, we identify popular artists from our dataset: ```sql SELECT count() AS c, artists FROM spotify GROUP BY artists ORDER BY c DESC LIMIT 20 ┌───c─┬─artists─────────┐ │ 279 │ The Beatles │ │ 271 │ George Jones │ │ 236 │ Stevie Wonder │ │ 224 │ Linkin Park │ │ 222 │ Ella Fitzgerald │ │ 217 │ Prateek Kuhad │ │ 202 │ Feid │ │ 190 │ Chuck Berry │ │ 183 │ Håkan Hellström │ │ 181 │ OneRepublic │ └─────┴─────────────────┘ 20 rows in set. Elapsed: 0.828 sec. Processed 114.00 thousand rows, 13.05 MB (137.73 thousand rows/s., 15.77 MB/s.) ``` Our later analysis will often look at differences across music genres. Ideally, this data is thus distributed evenly across genres, as indicated by a 0 variance, which should allow us to make some later statistical measurements with confidence. Confirming this with SQL is straightforward with the varPop function. ```sql SELECT count(), track_genre FROM spotify GROUP BY track_genre LIMIT 10 ┌─count()─┬─track_genre─┐ │ 1000 │ indie │ │ 1000 │ salsa │ │ 1000 │ new-age │ │ 1000 │ swedish │ │ 1000 │ j-dance │ │ 1000 │ garage │ │ 1000 │ latino │ │ 1000 │ malay │ │ 1000 │ rock │ │ 1000 │ sad │ └─────────┴─────────────┘ 10 rows in set. Elapsed: 0.848 sec. Processed 39.00 thousand rows, 4.48 MB (45.97 thousand rows/s., 5.28 MB/s.) SELECT uniqExact(track_genre) FROM spotify ┌─uniqExact(track_genre)─┐ │ 114 │ └────────────────────────┘ 1 row in set. Elapsed: 0.822 sec. Processed 114.00 thousand rows, 13.05 MB (138.62 thousand rows/s., 15.87 MB/s.) SELECT varPop(c) FROM ( SELECT count() AS c, track_genre FROM spotify GROUP BY track_genre ) ┌─varPop(c)─┐ │ 0 │ └───────────┘ 11 row in set. Elapsed: 0.881 sec. Processed 39.00 thousand rows, 4.51 MB (44.26 thousand rows/s., 5.11 MB/s.) ``` We can see that each genre has 1000 rows. A variance of 0 proves this is the same for all 114 genres in the dataset. ### Histograms Any early data analysis typically involves building histograms to show the distribution of our values and identify the likely probability distribution. For example, let's consider the column `danceability`:As well as requiring the addition of the parameter enable_url_encoding for this blog post, we also found reading of Hugging Face Parquet files to be slower than expected with ClickHouse. This was attributed to small row groups, with a separate HTTP request made for each row group. This was resolved in issue 53069.
Histograms can easily be constructed using the [bar function](https://clickhouse.com/docs/en/sql-reference/functions/other-functions#bar). Below, we group by danceability, rounded to 1 decimal place, and plot the count. This gives us the distribution of the values.Danceability considers how suitable a track is for dancing based on a combination of musical elements including tempo, rhythm stability, beat strength, and overall regularity. A value of 0.0 is least danceable and 1.0 is most danceable.
SELECT
round(danceability, 1) AS danceability,
bar(count(), 0, max(count()) OVER ()) AS dist
FROM spotify
GROUP BY danceability
ORDER BY danceability ASC
┌─danceability─┬─dist─────────────────────────────────────────────────────────────────────────────┐
│ 0 │ ▍ │
│ 0.1 │ ████▎ │
│ 0.2 │ █████████████▍ │
│ 0.3 │ ████████████████████████ │
│ 0.4 │ ████████████████████████████████████████████▋ │
│ 0.5 │ ████████████████████████████████████████████████████████████████████▊ │
│ 0.6 │ ████████████████████████████████████████████████████████████████████████████████ │
│ 0.7 │ ██████████████████████████████████████████████████████████████████████ │
│ 0.8 │ ██████████████████████████████████████████ │
│ 0.9 │ ██████████▋ │
│ 1 │ ▌ │
└──────────────┴──────────────────────────────────────────────────────────────────────────────────┘
11 rows in set. Elapsed: 0.839 sec. Processed 39.00 thousand rows, 4.48 MB (46.51 thousand rows/s., 5.34 MB/s.)
Energy is a measure from 0.0 to 1.0 and represents a perceptual measure of intensity and activity. Typically, energetic tracks feel fast, loud, and noisy. For example, death metal has high energy, while a Bach prelude scores low on the scale
┌─energy─┬─dist─────────────────────────────────────────────────────────────────────────────┐
│ 0 │ ███████▍ │
│ 0.1 │ ███████████████▎ │
│ 0.2 │ ████████████████████▌ │
│ 0.3 │ ███████████████████████████▉ │
│ 0.4 │ █████████████████████████████████████▌ │
│ 0.5 │ ███████████████████████████████████████████████▌ │
│ 0.6 │ █████████████████████████████████████████████████████████▎ │
│ 0.7 │ ███████████████████████████████████████████████████████████████████▌ │
│ 0.8 │ ██████████████████████████████████████████████████████████████████████▏ │
│ 0.9 │ ████████████████████████████████████████████████████████████████████████████████ │
│ 1 │ ███████████████████████████████████████▊ │
└────────┴──────────────────────────────────────────────────────────────────────────────────┘
It seems unlikely this will be normally distributed, with most music recorded in a studio - resulting in a left skew.Liveliness measures the presence of an audience in the recording. Higher liveness values represent an increased probability that the track was performed live. A value above 0.8 provides strong likelihood that the track is live
┌─liveness─┬─dist─────────────────────────────────────────────────────────────────────────────┐
│ 0 │ ███▍ │
│ 0.1 │ ████████████████████████████████████████████████████████████████████████████████ │
│ 0.2 │ ████████████████████████▎ │
│ 0.3 │ █████████████████▊ │
│ 0.4 │ █████████▌ │
│ 0.5 │ ██▉ │
│ 0.6 │ ██▌ │
│ 0.7 │ ██▉ │
│ 0.8 │ █▊ │
│ 0.9 │ ██▏ │
│ 1 │ █▍ │
└──────────┴──────────────────────────────────────────────────────────────────────────────────┘
Loudness is the overall loudness of a track in decibels (dB)
WITH (
SELECT histogram(20)(loudness)
FROM spotify
) AS hist
SELECT
round(arrayJoin(hist).1) AS lower,
round(arrayJoin(hist).2) AS upper,
bar(arrayJoin(hist).3, 0, max(arrayJoin(hist).3)) AS bar
ORDER BY arrayJoin(hist).1 ASC
┌─lower─┬─upper─┬─bar──────────────────────────────────────────────────────────────────────────────┐
│ -50 │ -48 │ │
│ -48 │ -45 │ │
│ -45 │ -41 │ │
│ -41 │ -38 │ │
│ -38 │ -35 │ ▎ │
│ -35 │ -32 │ ▍ │
│ -32 │ -30 │ ▊ │
│ -30 │ -27 │ █ │
│ -27 │ -25 │ █▋ │
│ -25 │ -21 │ ███▎ │
│ -21 │ -18 │ █████▋ │
│ -18 │ -15 │ ██████▉ │
│ -15 │ -13 │ ██████████▉ │
│ -13 │ -11 │ ██████████████████████ │
│ -11 │ -8 │ ████████████████████████████████████████████▏ │
│ -8 │ -6 │ ████████████████████████████████████████████████████████████████▉ │
│ -6 │ -3 │ ████████████████████████████████████████████████████████████████████████████████ │
│ -3 │ -1 │ ██████████████████▏ │
│ -1 │ 2 │ █ │
│ 2 │ 5 │ │
└───────┴───────┴──────────────────────────────────────────────────────────────────────────────────┘
20 rows in set. Elapsed: 0.883 sec. Processed 114.00 thousand rows, 13.05 MB (129.03 thousand rows/s., 14.77 MB/s.)
┌─ ────────────────┬─tempo──┬─danceability─┬─energy─┬─loudness─┬─speechiness─┬─acousticness─┬─instrumentalness─┬─liveness─┐
│ tempo │ 1 │ -0.05 │ 0.248 │ 0.212 │ 0.017 │ -0.208 │ -0.05 │ 0.001 │
│ danceability │ -0.05 │ 1 │ 0.134 │ 0.259 │ 0.109 │ -0.172 │ -0.186 │ -0.132 │
│ energy │ 0.248 │ 0.134 │ 1 │ 0.762 │ 0.143 │ -0.734 │ -0.182 │ 0.185 │
│ loudness │ 0.212 │ 0.259 │ 0.762 │ 1 │ 0.061 │ -0.59 │ -0.433 │ 0.077 │
│ speechiness │ 0.017 │ 0.109 │ 0.143 │ 0.061 │ 1 │ -0.002 │ -0.09 │ 0.205 │
│ acousticness │ -0.208 │ -0.172 │ -0.734 │ -0.59 │ -0.002 │ 1 │ 0.104 │ -0.021 │
│ instrumentalness │ -0.05 │ -0.186 │ -0.182 │ -0.433 │ -0.09 │ 0.104 │ 1 │ -0.08 │
│ liveness │ 0.001 │ -0.132 │ 0.185 │ 0.077 │ 0.205 │ -0.021 │ -0.08 │ 1 │
└──────────────────┴────────┴──────────────┴────────┴──────────┴─────────────┴──────────────┴──────────────────┴──────────┘
8 rows in set. Elapsed: 0.714 sec. Processed 2.00 thousand rows, 232.29 KB (2.80 thousand rows/s., 325.17 KB/s.)
This is also approximately normally distributed, but with differing variance:Valence describes the musical positiveness conveyed by a track.
┌─valence─┬─dist─────────────────────────────────────────────────────────────────────────────┐
│ 0 │ █████████████████████▌ │
│ 0.1 │ █████████████████████████████████████████████████████████▎ │
│ 0.2 │ ████████████████████████████████████████████████████████████████████████████▎ │
│ 0.3 │ █████████████████████████████████████████████████████████████████████████████▎ │
│ 0.4 │ ████████████████████████████████████████████████████████████████████████████████ │
│ 0.5 │ ████████████████████████████████████████████████████████████████████████████▌ │
│ 0.6 │ ████████████████████████████████████████████████████████████████████████▎ │
│ 0.7 │ ███████████████████████████████████████████████████████████████████▏ │
│ 0.8 │ ████████████████████████████████████████████████████████▉ │
│ 0.9 │ ████████████████████████████████████████████▍ │
│ 1 │ ███████████████▍ │
└─────────┴──────────────────────────────────────────────────────────────────────────────────┘
Below we compute Cramer's V for these variables against popularity: ```sql SELECT cramersV(popularity, explicit), cramersV(popularity, key), cramersV(popularity, track_genre), cramersV(popularity, artists) FROM spotify Row 1: ────── cramersV(popularity, explicit): 0.1111421067814236 cramersV(popularity, key): 0.049664681157575566 cramersV(popularity, track_genre): 0.16617136848279976 cramersV(popularity, artists): 0.6256530277850572 1 row in set. Elapsed: 0.843 sec. Processed 51.15 thousand rows, 5.87 MB (60.65 thousand rows/s., 6.96 MB/s.) ``` Cramer's V ranges from 0 to 1, where 0 indicates no association and 1 indicates a perfect association. Popularity clearly has a strong association with artists as you'd expect. We'd encourage users to explore other possible associations here. ClickHouse also supports a version of Cramer's V with bias correction (a common challenge of the algorithm if the data is unbalanced or small) - [cramersVBiasCorrected](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/cramersvbiascorrected). This suggests our associations aren't possibly as strong as we initially measured: ```sql SELECT cramersVBiasCorrected(popularity, explicit), cramersVBiasCorrected(popularity, key), cramersVBiasCorrected(popularity, track_genre), cramersVBiasCorrected(popularity, artists) FROM spotify FORMAT Vertical Row 1: ────── cramersVBiasCorrected(popularity, explicit): 0.10712361030835567 cramersVBiasCorrected(popularity, key): 0.03986895101010225 cramersVBiasCorrected(popularity, track_genre): 0.1632331461526432 cramersVBiasCorrected(popularity, artists): 0.34027056010204915 1 row in set. Elapsed: 0.857 sec. Processed 40.00 thousand rows, 4.53 MB (46.67 thousand rows/s., 5.29 MB/s.) ``` Theil's U is a measure of association that quantifies the amount of information one variable provides about another or more simply how much randomness in a dependent variable can be explained by an independent variable. Below, we use the [APPLY syntax](https://clickhouse.com/docs/en/sql-reference/statements/select#apply) to compute Theil's U for the dependent variable `artists` and other continuous variables to see if there are obvious associations. We make these continuous variables ordinal, as required by our current implementation of Theil's U:Categorical variables refer to variables that take on categories or groups as their values. These can either be nominal (no inherent order or ranking) or ordinal (a natural order or ranking associated with them). In our Spotify dataset, the columns
explicit(explicit lyrics (true = yes it does; false = no or unknown)),track_genre,artistsandkey(The key the track is in. Integers map to pitches using standard Pitch Class notation).popularity, with a value of 0 (not popular) to 100 (popular), andordinalis also an ordinal variable.
SELECT * EXCEPT (`Unnamed: 0`, album_name, track_id, artists, track_name, time_signature, track_genre) APPLY x -> theilsU(artists, round(x * 10))
FROM spotify
FORMAT Vertical
Row 1:
──────
theilsU(artists, round(multiply(popularity, 10))): -0.3001786653454836
theilsU(artists, round(multiply(duration_ms, 10))): -0.9276605586651611
theilsU(artists, round(multiply(explicit, 10))): -0.02444367883018377
theilsU(artists, round(multiply(danceability, 10))): -0.12620692012945478
theilsU(artists, round(multiply(energy, 10))): -0.15039240344091118
theilsU(artists, round(multiply(key, 10))): -0.14364542076020673
theilsU(artists, round(multiply(loudness, 10))): -0.377285103342597
theilsU(artists, round(multiply(mode, 10))): -0.03448619204892218
theilsU(artists, round(multiply(speechiness, 10))): -0.07546345974559064
theilsU(artists, round(multiply(acousticness, 10))): -0.14720970877815828
theilsU(artists, round(multiply(instrumentalness, 10))): -0.08060433390539239
theilsU(artists, round(multiply(liveness, 10))): -0.08880043710056783
theilsU(artists, round(multiply(valence, 10))): -0.14408203139969228
theilsU(artists, round(multiply(tempo, 10))): -0.524866311749112
1 row in set. Elapsed: 0.991 sec. Processed 114.00 thousand rows, 13.05 MB (115.01 thousand rows/s., 13.17 MB/s.)
When defining a table, an engine must be defined. Users have two main choices here - MergeTree or Memory. The former, while requiring the [definition of an ordering key](https://clickhouse.com/docs/en/optimize/sparse-primary-indexes), will provide the most optimal performance on most queries and will not be bounded by memory for larger datasets. Below we insert the Spotify dataset into a table and repeat our earlier simple query. ```sql –-allow_nullable_key allows us to use track_genre for key –- this creates the table and inserts the data in a single query CREATE TABLE spotify_merge ENGINE = MergeTree ORDER BY track_genre SETTINGS allow_nullable_key = 1 AS SELECT * FROM url(hf('maharshipandya/spotify-tracks-dataset')) 0 rows in set. Elapsed: 3.038 sec. Processed 114.00 thousand rows, 13.05 MB (37.52 thousand rows/s., 4.30 MB/s.) SELECT count() AS c, artists FROM spotify_merge GROUP BY artists ORDER BY c DESC LIMIT 5 ┌───c─┬─artists─────────┐ │ 279 │ The Beatles │ │ 271 │ George Jones │ │ 236 │ Stevie Wonder │ │ 224 │ Linkin Park │ │ 222 │ Ella Fitzgerald │ └─────┴─────────────────┘ 5 rows in set. Elapsed: 0.016 sec. ``` As well being almost 100 times faster than the url function, note how our schema is automatically inferred from the Parquet file when the table is created. With this usage pattern, users can exploit the full capabilities of the ClickHouse MergeTree which powers real-time analytical applications at PB scale.This table differs from the previous example powered by the url table engine. In this case we insert the data into ClickHouse’s own internal format. The previous example was still backed by the Parquet file.
Choosing the usage pattern here depends on how frequently users expect to query a Hugging Face dataset. For infrequent, ad-hoc queries we recommend the url function and UDF. If you expect to query frequently, creating a table and inserting the data will deliver faster queries and reduce iteration cycles as you explore the data. ## Conclusion In this blog post we’ve explored how clickhouse-local can be used to query Hugging Face datasets directly using the url function. We have performed some simple queries on a Spotify dataset and illustrated how a basic statistical analysis can be performed without writing any code and using just SQL. For larger datasets, users can leverage these statistical tests and still enjoy the performance of ClickHouse from their laptop or workstation. Finally, we’ve provided a simple UDF that readers can use to query a Hugging Face dataset by only its name and shown how datasets can be inserted into local tables for optimal performance.We use the ordering key
track_genreabove. Users looking for optimal performance on larger datasets, or needing to optimize for specific access patterns, should consider this key carefully. Further information here.