Jeu de données YouTube sur les Je n’aime pas

Une collection de Je n’aime pas sur des vidéos YouTube.

En novembre 2021, YouTube a supprimé le décompte public des Je n’aime pas sur toutes ses vidéos. Si les créateurs peuvent toujours voir le nombre de Je n’aime pas, les internautes, eux, ne peuvent voir que le nombre de J’aime reçus par une vidéo.

Le jeu de données contient plus de 4,55 milliards d’enregistrements. Soyez donc prudent avant de simplement copier-coller les commandes ci-dessous, à moins que vos ressources ne puissent gérer un tel volume. Les commandes ci-dessous ont été exécutées sur une instance de production ClickHouse Cloud.

Les données sont au format JSON et peuvent être téléchargées depuis archive.org. Nous avons également mis ces données à disposition dans S3 afin qu’elles puissent être chargées plus efficacement dans une instance ClickHouse Cloud.

Voici les étapes à suivre pour créer une table dans ClickHouse Cloud et y insérer les données.

Les étapes ci-dessous fonctionneront tout aussi bien sur une installation locale de ClickHouse. La seule modification consiste à utiliser la fonction s3 au lieu de s3cluster (sauf si vous avez un cluster configuré — dans ce cas, remplacez default par le nom de votre cluster).

​

Instructions pas à pas

1. ​

   Exploration des données

   Voyons à quoi ressemblent les données. La fonction de table s3cluster renvoie une table ; nous pouvons donc DESCRIBE le résultat :

   DESCRIBE s3(
       'https://clickhouse-public-datasets.s3.amazonaws.com/youtube/original/files/*.zst',
       'JSONLines'
   );
   

   ClickHouse déduit le schéma suivant du fichier JSON :

   ┌─name────────────────┬─type───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
   │ id                  │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
   │ fetch_date          │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
   │ upload_date         │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
   │ title               │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
   │ uploader_id         │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
   │ uploader            │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
   │ uploader_sub_count  │ Nullable(Int64)                                                                                                                        │              │                    │         │                  │                │
   │ is_age_limit        │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
   │ view_count          │ Nullable(Int64)                                                                                                                        │              │                    │         │                  │                │
   │ like_count          │ Nullable(Int64)                                                                                                                        │              │                    │         │                  │                │
   │ dislike_count       │ Nullable(Int64)                                                                                                                        │              │                    │         │                  │                │
   │ is_crawlable        │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
   │ is_live_content     │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
   │ has_subtitles       │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
   │ is_ads_enabled      │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
   │ is_comments_enabled │ Nullable(Bool)                                                                                                                         │              │                    │         │                  │                │
   │ description         │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
   │ rich_metadata       │ Array(Tuple(call Nullable(String), content Nullable(String), subtitle Nullable(String), title Nullable(String), url Nullable(String))) │              │                    │         │                  │                │
   │ super_titles        │ Array(Tuple(text Nullable(String), url Nullable(String)))                                                                              │              │                    │         │                  │                │
   │ uploader_badges     │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
   │ video_badges        │ Nullable(String)                                                                                                                       │              │                    │         │                  │                │
   └─────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
   
2. ​

   Créer la table

   À partir du schéma inféré, nous avons simplifié les types de données et ajouté une clé primaire. Définissez la table suivante :

   CREATE TABLE youtube
   (
       `id` String,
       `fetch_date` DateTime,
       `upload_date_str` String,
       `upload_date` Date,
       `title` String,
       `uploader_id` String,
       `uploader` String,
       `uploader_sub_count` Int64,
       `is_age_limit` Bool,
       `view_count` Int64,
       `like_count` Int64,
       `dislike_count` Int64,
       `is_crawlable` Bool,
       `has_subtitles` Bool,
       `is_ads_enabled` Bool,
       `is_comments_enabled` Bool,
       `description` String,
       `rich_metadata` Array(Tuple(call String, content String, subtitle String, title String, url String)),
       `super_titles` Array(Tuple(text String, url String)),
       `uploader_badges` String,
       `video_badges` String
   )
   ENGINE = MergeTree
   ORDER BY (uploader, upload_date)
   
3. ​

   Insérer des données

   La commande suivante ingère en flux les enregistrements des fichiers S3 dans la table youtube.

   Cela insère beaucoup de données - 4,65 milliards de lignes. Si vous ne voulez pas l’ensemble des données, ajoutez simplement une clause LIMIT avec le nombre de lignes souhaité.

   INSERT INTO youtube
   SETTINGS input_format_null_as_default = 1
   SELECT
       id,
       parseDateTimeBestEffortUSOrZero(toString(fetch_date)) AS fetch_date,
       upload_date AS upload_date_str,
       toDate(parseDateTimeBestEffortUSOrZero(upload_date::String)) AS upload_date,
       ifNull(title, '') AS title,
       uploader_id,
       ifNull(uploader, '') AS uploader,
       uploader_sub_count,
       is_age_limit,
       view_count,
       like_count,
       dislike_count,
       is_crawlable,
       has_subtitles,
       is_ads_enabled,
       is_comments_enabled,
       ifNull(description, '') AS description,
       rich_metadata,
       super_titles,
       ifNull(uploader_badges, '') AS uploader_badges,
       ifNull(video_badges, '') AS video_badges
   FROM s3(
       'https://clickhouse-public-datasets.s3.amazonaws.com/youtube/original/files/*.zst',
       'JSONLines'
   )
   

   Quelques remarques sur notre commande INSERT :

   • La fonction parseDateTimeBestEffortUSOrZero est pratique lorsque les champs de date entrants risquent de ne pas être au bon format. Si fetch_date n’est pas correctement interprété, il sera défini sur 0
   • La colonne upload_date contient des dates valides, mais aussi des chaînes comme “4 hours ago” — ce qui n’est évidemment pas une date valide. Nous avons choisi de stocker la valeur d’origine dans upload_date_str et de tenter de l’interpréter avec toDate(parseDateTimeBestEffortUSOrZero(upload_date::String)). Si l’analyse échoue, nous obtenons simplement 0
   • Nous avons utilisé ifNull pour éviter d’avoir des valeurs NULL dans notre table. Si une valeur entrante est NULL, la fonction ifNull la remplace par une chaîne vide
4. ​

   Compter le nombre de lignes

   Ouvrez un nouvel onglet dans la SQL Console de ClickHouse Cloud (ou une nouvelle fenêtre clickhouse-client) et regardez le total augmenter. L’insertion de 4,56 milliards de lignes prendra un certain temps, en fonction des ressources de votre serveur. (Sans aucun ajustement des paramètres, cela prend environ 4,5 heures.)

   SELECT formatReadableQuantity(count())
   FROM youtube
   

   ┌─formatReadableQuantity(count())─┐
   │ 4.56 billion                    │
   └─────────────────────────────────┘
   
5. ​

   Explorer les données

   Une fois les données insérées, comptez le nombre de « Je n’aime pas » sur vos vidéos ou chaînes préférées. Voyons combien de vidéos ClickHouse a mises en ligne :

   SELECT count()
   FROM youtube
   WHERE uploader = 'ClickHouse';
   

   ┌─count()─┐
   │      84 │
   └─────────┘
   
   1 row in set. Elapsed: 0.570 sec. Processed 237.57 thousand rows, 5.77 MB (416.54 thousand rows/s., 10.12 MB/s.)
   

   La requête ci-dessus s’exécute aussi rapidement parce que nous avons choisi uploader comme première colonne de la clé primaire : elle n’a donc eu à traiter que 237k lignes.

   Examinons les J’aime et les Je n’aime pas des vidéos ClickHouse :

   SELECT
       title,
       like_count,
       dislike_count
   FROM youtube
   WHERE uploader = 'ClickHouse'
   ORDER BY dislike_count DESC;
   

   La réponse se présente ainsi :

   ┌─title────────────────────────────────────────────────────────────────────────────────────────────────┬─like_count─┬─dislike_count─┐
   │ ClickHouse v21.11 Release Webinar                                                                    │         52 │             3 │
   │ ClickHouse Introduction                                                                              │         97 │             3 │
   │ Casa Modelo Algarve                                                                                  │        180 │             3 │
   │ Профайлер запросов:  трудный путь                                                                    │         33 │             3 │
   │ ClickHouse в Курсометре                                                                              │          4 │             2 │
   │ 10 Good Reasons to Use ClickHouse                                                                    │         27 │             2 │
   ...
   
   84 rows in set. Elapsed: 0.013 sec. Processed 155.65 thousand rows, 16.94 MB (11.96 million rows/s., 1.30 GB/s.)
   

   Voici une recherche de vidéos contenant ClickHouse dans les champs title ou description :

   SELECT
       view_count,
       like_count,
       dislike_count,
       concat('https://youtu.be/', id) AS url,
       title
   FROM youtube
   WHERE (title ILIKE '%ClickHouse%') OR (description ILIKE '%ClickHouse%')
   ORDER BY
       like_count DESC,
       view_count DESC;
   

   Cette requête doit traiter chaque ligne et analyser également deux colonnes de chaînes de caractères. Malgré cela, nous obtenons des performances correctes, à 4,15 M lignes/seconde :

   1174 rows in set. Elapsed: 1099.368 sec. Processed 4.56 billion rows, 1.98 TB (4.15 million rows/s., 1.80 GB/s.)
   

   Les résultats se présentent ainsi :

   ┌─view_count─┬─like_count─┬─dislike_count─┬─url──────────────────────────┬─title──────────────────────────────────────────────────────────────────────────────────────────────────┐
   │       1919 │         63 │             1 │ https://youtu.be/b9MeoOtAivQ │ ClickHouse v21.10 Release Webinar                                                                      │
   │       8710 │         62 │             4 │ https://youtu.be/PeV1mC2z--M │ What is JDBC DriverManager? | JDBC                                                                     │
   │       3534 │         62 │             1 │ https://youtu.be/8nWRhK9gw10 │ CLICKHOUSE - Arquitetura Modular                                                                       │
   

​

Questions

​

Si quelqu’un désactive les commentaires, cela réduit-il les chances que quelqu’un clique sur J’aime ou Je n’aime pas ?

Lorsque les commentaires sont désactivés, les gens sont-ils plus enclins à cliquer sur J’aime ou Je n’aime pas pour exprimer ce qu’ils ressentent face à une vidéo ?

SELECT
    concat('< ', formatReadableQuantity(view_range)) AS views,
    is_comments_enabled,
    total_clicks / num_views AS prob_like_dislike
FROM
(
    SELECT
        is_comments_enabled,
        power(10, CEILING(log10(view_count + 1))) AS view_range,
        sum(like_count + dislike_count) AS total_clicks,
        sum(view_count) AS num_views
    FROM youtube
    GROUP BY
        view_range,
        is_comments_enabled
) WHERE view_range > 1
ORDER BY
    is_comments_enabled ASC,
    num_views ASC;

┌─views─────────────┬─is_comments_enabled─┬────prob_like_dislike─┐
│ < 10.00           │ false               │  0.08224180712685371 │
│ < 100.00          │ false               │  0.06346337759167248 │
│ < 1.00 thousand   │ false               │  0.03201883652987105 │
│ < 10.00 thousand  │ false               │  0.01716073540410903 │
│ < 10.00 billion   │ false               │ 0.004555639481829971 │
│ < 100.00 thousand │ false               │  0.01293351460515323 │
│ < 1.00 billion    │ false               │ 0.004761811192464957 │
│ < 1.00 million    │ false               │ 0.010472604018980551 │
│ < 10.00 million   │ false               │  0.00788902538420125 │
│ < 100.00 million  │ false               │  0.00579152804250582 │
│ < 10.00           │ true                │  0.09819517478134059 │
│ < 100.00          │ true                │  0.07403784478585775 │
│ < 1.00 thousand   │ true                │  0.03846294910067627 │
│ < 10.00 billion   │ true                │ 0.005615217329358215 │
│ < 10.00 thousand  │ true                │  0.02505881391701455 │
│ < 1.00 billion    │ true                │ 0.007434998802482997 │
│ < 100.00 thousand │ true                │ 0.022694648130822004 │
│ < 100.00 million  │ true                │ 0.011761563746575625 │
│ < 1.00 million    │ true                │ 0.020776022304589435 │
│ < 10.00 million   │ true                │ 0.016917095718089584 │
└───────────────────┴─────────────────────┴──────────────────────┘

22 rows in set. Elapsed: 8.460 sec. Processed 4.56 billion rows, 77.48 GB (538.73 million rows/s., 9.16 GB/s.)

L’activation des commentaires semble corrélée à un taux d’engagement plus élevé.

​

Comment le nombre de vidéos évolue-t-il au fil du temps, et quels sont les événements notables ?

SELECT
    toStartOfMonth(toDateTime(upload_date)) AS month,
    uniq(uploader_id) AS uploaders,
    count() AS num_videos,
    sum(view_count) AS view_count
FROM youtube
GROUP BY month
ORDER BY month ASC;

┌──────month─┬─uploaders─┬─num_videos─┬───view_count─┐
│ 2005-04-01 │         5 │          6 │    213597737 │
│ 2005-05-01 │         6 │          9 │      2944005 │
│ 2005-06-01 │       165 │        351 │     18624981 │
│ 2005-07-01 │       395 │       1168 │     94164872 │
│ 2005-08-01 │      1171 │       3128 │    124540774 │
│ 2005-09-01 │      2418 │       5206 │    475536249 │
│ 2005-10-01 │      6750 │      13747 │    737593613 │
│ 2005-11-01 │     13706 │      28078 │   1896116976 │
│ 2005-12-01 │     24756 │      49885 │   2478418930 │
│ 2006-01-01 │     49992 │     100447 │   4532656581 │
│ 2006-02-01 │     67882 │     138485 │   5677516317 │
│ 2006-03-01 │    103358 │     212237 │   8430301366 │
│ 2006-04-01 │    114615 │     234174 │   9980760440 │
│ 2006-05-01 │    152682 │     332076 │  14129117212 │
│ 2006-06-01 │    193962 │     429538 │  17014143263 │
│ 2006-07-01 │    234401 │     530311 │  18721143410 │
│ 2006-08-01 │    281280 │     614128 │  20473502342 │
│ 2006-09-01 │    312434 │     679906 │  23158422265 │
│ 2006-10-01 │    404873 │     897590 │  27357846117 │

Un pic du nombre de comptes publiant des vidéos autour du Covid est notable.

​

Plus de sous-titres au fil du temps, et à partir de quand

Grâce aux progrès de la reconnaissance vocale, il est plus facile que jamais de créer des sous-titres pour les vidéos. YouTube ayant ajouté le sous-titrage automatique fin 2009, est-ce à ce moment-là que le bond s’est produit ?

SELECT
    toStartOfMonth(upload_date) AS month,
    countIf(has_subtitles) / count() AS percent_subtitles,
    percent_subtitles - any(percent_subtitles) OVER (
        ORDER BY month ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
    ) AS previous
FROM youtube
GROUP BY month
ORDER BY month ASC;

┌──────month─┬───percent_subtitles─┬────────────────previous─┐
│ 2015-01-01 │  0.2652653881082824 │      0.2652653881082824 │
│ 2015-02-01 │  0.3147556050309162 │    0.049490216922633834 │
│ 2015-03-01 │ 0.32460464492371877 │    0.009849039892802558 │
│ 2015-04-01 │ 0.33471963051468445 │    0.010114985590965686 │
│ 2015-05-01 │  0.3168087575501062 │   -0.017910872964578273 │
│ 2015-06-01 │  0.3162609788438222 │  -0.0005477787062839745 │
│ 2015-07-01 │ 0.31828767677518033 │   0.0020266979313581235 │
│ 2015-08-01 │  0.3045551564286859 │   -0.013732520346494415 │
│ 2015-09-01 │   0.311221133995152 │    0.006665977566466086 │
│ 2015-10-01 │ 0.30574870926812175 │   -0.005472424727030245 │
│ 2015-11-01 │ 0.31125409712077234 │   0.0055053878526505895 │
│ 2015-12-01 │  0.3190967954651779 │    0.007842698344405541 │
│ 2016-01-01 │ 0.32636021432496176 │    0.007263418859783877 │

Les résultats montrent un pic en 2009. Apparemment, à cette époque, YouTube supprimait sa fonctionnalité de sous-titres communautaires, qui permettait de téléverser des sous-titres pour la vidéo d’autres personnes. Cela a déclenché une campagne très fructueuse visant à inciter les créateurs à ajouter des sous-titres à leurs vidéos pour les spectateurs malentendants et sourds.

​

Principaux comptes publiant des vidéos au fil du temps

WITH uploaders AS
    (
        SELECT uploader
        FROM youtube
        GROUP BY uploader
        ORDER BY sum(view_count) DESC
        LIMIT 10
    )
SELECT
    month,
    uploader,
    sum(view_count) AS total_views,
    avg(dislike_count / like_count) AS like_to_dislike_ratio
FROM youtube
WHERE uploader IN (uploaders)
GROUP BY
    toStartOfMonth(upload_date) AS month,
    uploader
ORDER BY
    month ASC,
    total_views DESC;

┌──────month─┬─uploader───────────────────┬─total_views─┬─like_to_dislike_ratio─┐
│ 1970-01-01 │ T-Series                   │    10957099 │  0.022784656361208206 │
│ 1970-01-01 │ Ryan's World               │           0 │  0.003035559410234172 │
│ 1970-01-01 │ SET India                  │           0 │                   nan │
│ 2006-09-01 │ Cocomelon - Nursery Rhymes │   256406497 │    0.7005566715978622 │
│ 2007-06-01 │ Cocomelon - Nursery Rhymes │    33641320 │    0.7088650914344298 │
│ 2008-02-01 │ WWE                        │    43733469 │   0.07198856488734842 │
│ 2008-03-01 │ WWE                        │    16514541 │    0.1230603715431997 │
│ 2008-04-01 │ WWE                        │     5907295 │    0.2089399470159618 │
│ 2008-05-01 │ WWE                        │     7779627 │   0.09101676560436774 │
│ 2008-06-01 │ WWE                        │     7018780 │    0.0974184753155297 │
│ 2008-07-01 │ WWE                        │     4686447 │    0.1263845422065158 │
│ 2008-08-01 │ WWE                        │     4514312 │   0.08384574274791441 │
│ 2008-09-01 │ WWE                        │     3717092 │   0.07872802579349912 │

​

Comment le ratio de J’aime évolue-t-il à mesure que le nombre de vues augmente ?

SELECT
    concat('< ', formatReadableQuantity(view_range)) AS view_range,
    is_comments_enabled,
    round(like_ratio, 2) AS like_ratio
FROM
(
SELECT
    power(10, CEILING(log10(view_count + 1))) AS view_range,
    is_comments_enabled,
    avg(like_count / dislike_count) AS like_ratio
FROM youtube WHERE dislike_count > 0
GROUP BY
    view_range,
    is_comments_enabled HAVING view_range > 1
ORDER BY
    view_range ASC,
    is_comments_enabled ASC
);

┌─view_range────────┬─is_comments_enabled─┬─like_ratio─┐
│ < 10.00           │ false               │       0.66 │
│ < 10.00           │ true                │       0.66 │
│ < 100.00          │ false               │          3 │
│ < 100.00          │ true                │       3.95 │
│ < 1.00 thousand   │ false               │       8.45 │
│ < 1.00 thousand   │ true                │      13.07 │
│ < 10.00 thousand  │ false               │      18.57 │
│ < 10.00 thousand  │ true                │      30.92 │
│ < 100.00 thousand │ false               │      23.55 │
│ < 100.00 thousand │ true                │      42.13 │
│ < 1.00 million    │ false               │      19.23 │
│ < 1.00 million    │ true                │      37.86 │
│ < 10.00 million   │ false               │      12.13 │
│ < 10.00 million   │ true                │      30.72 │
│ < 100.00 million  │ false               │       6.67 │
│ < 100.00 million  │ true                │      23.32 │
│ < 1.00 billion    │ false               │       3.08 │
│ < 1.00 billion    │ true                │      20.69 │
│ < 10.00 billion   │ false               │       1.77 │
│ < 10.00 billion   │ true                │       19.5 │
└───────────────────┴─────────────────────┴────────────┘

​

Comment les vues sont-elles réparties ?

SELECT
    labels AS percentile,
    round(quantiles) AS views
FROM
(
    SELECT
        quantiles(0.999, 0.99, 0.95, 0.9, 0.8, 0.7, 0.6, 0.5, 0.4, 0.3, 0.2, 0.1)(view_count) AS quantiles,
        ['99.9th', '99th', '95th', '90th', '80th', '70th','60th', '50th', '40th', '30th', '20th', '10th'] AS labels
    FROM youtube
)
ARRAY JOIN
    quantiles,
    labels;

┌─percentile─┬───views─┐
│ 99.9th     │ 1216624 │
│ 99th       │  143519 │
│ 95th       │   13542 │
│ 90th       │    4054 │
│ 80th       │     950 │
│ 70th       │     363 │
│ 60th       │     177 │
│ 50th       │      97 │
│ 40th       │      57 │
│ 30th       │      32 │
│ 20th       │      16 │
│ 10th       │       6 │
└────────────┴─────────┘