Набор данных YouTube о дизлайках

Коллекция дизлайков видео на YouTube.

В ноябре 2021 года YouTube убрал публичный счетчик дизлайков у всех видео. Хотя авторы по-прежнему могут видеть число дизлайков, зрителям теперь видно только, сколько лайков получило видео.

Набор данных содержит более 4,55 миллиарда записей, поэтому не копируйте бездумно приведенные ниже команды, если ваши ресурсы не рассчитаны на такой объем. Приведенные ниже команды были выполнены на инстансе Production в ClickHouse Cloud.

Данные представлены в формате JSON, их можно скачать с archive.org. Мы также разместили эти данные в S3, чтобы их можно было быстрее загрузить в инстанс ClickHouse Cloud.

Ниже описаны шаги по созданию таблицы в ClickHouse Cloud и вставке данных.

Приведенные ниже шаги также легко подойдут для локальной установки ClickHouse. Единственное отличие — нужно использовать функцию s3 вместо s3cluster (если только у вас не настроен cluster — в этом случае замените default на имя вашего cluster).

​

Пошаговые инструкции

1. ​

   Исследование данных

   Посмотрим, как выглядят данные. Табличная функция s3cluster возвращает таблицу, поэтому мы можем выполнить DESCRIBE для результата:

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

   ClickHouse определяет следующую схему на основе 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. ​

   Создайте таблицу

   На основе автоматически определённой схемы мы уточнили типы данных и добавили первичный ключ. Создайте следующую таблицу:

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

   Вставка данных

   Следующая команда загружает записи из файлов S3 в таблицу youtube.

   Будет вставлен большой объём данных — 4,65 миллиарда строк. Если вам не нужен весь набор данных, просто добавьте предложение LIMIT с нужным количеством строк.

   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'
   )
   

   Несколько замечаний о нашей команде INSERT:

   • Функция parseDateTimeBestEffortUSOrZero удобна, когда входящие поля даты могут быть в неправильном формате. Если fetch_date не удастся корректно разобрать, ему будет присвоено значение 0
   • Столбец upload_date содержит корректные даты, но в нём также встречаются строки вроде “4 hours ago”, которые, конечно, не являются корректной датой. Мы решили сохранять исходное значение в upload_date_str и пытаться разобрать его с помощью toDate(parseDateTimeBestEffortUSOrZero(upload_date::String)). Если разбор не удастся, мы просто получим 0
   • Мы использовали ifNull, чтобы избежать значений NULL в нашей таблице. Если входящее значение равно NULL, функция ifNull заменяет его на пустую строку
4. ​

   Подсчитайте количество строк

   Откройте новую вкладку в SQL Console ClickHouse Cloud (или новое окно clickhouse-client) и наблюдайте, как растет счетчик. Вставка 4,56 млрд строк займет некоторое время — в зависимости от ресурсов вашего сервера. (Без какой-либо дополнительной настройки параметров это занимает около 4,5 часов.)

   SELECT formatReadableQuantity(count())
   FROM youtube
   

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

   Изучение данных

   После вставки данных подсчитайте, сколько дизлайков у ваших любимых видео или каналов. Давайте посмотрим, сколько видео загрузил ClickHouse:

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

   Приведённый выше запрос выполняется так быстро, потому что мы выбрали uploader в качестве первого столбца первичного ключа, — поэтому ему пришлось обработать всего 237k строк.

   Давайте посмотрим на лайки и дизлайки видео в ClickHouse:

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

   Ответ выглядит следующим образом:

   ┌─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.)
   

   Вот поиск видео, в полях title или description которых есть ClickHouse:

   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;
   

   Этот запрос должен обработать каждую строку, а также разобрать данные в двух строковых столбцах. Даже в этом случае мы получаем неплохую производительность — 4,15 млн строк/с:

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

   Результаты будут выглядеть так:

   ┌─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                                                                       │
   

​

Вопросы

​

Если кто-то отключает комментарии, уменьшает ли это вероятность того, что люди действительно будут нажимать «Нравится» или «Не нравится»?

Когда комментарии отключены, чаще ли люди ставят «Нравится» или «Не нравится», чтобы выразить своё отношение к видео?

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

Похоже, что включение комментариев связано с более высоким уровнем вовлеченности.

​

Как менялось количество видео с течением времени — примечательные события?

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 │

Заметен всплеск числа авторов, загружающих видео, примерно в период COVID-19.

​

Больше субтитров с течением времени и когда именно

С развитием технологий распознавания речи создавать субтитры для видео стало проще, чем когда-либо; когда YouTube добавил автоматические субтитры в конце 2009 года, произошёл ли тогда скачок?

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 │

Результаты показывают всплеск в 2009 году. По-видимому, в то время YouTube убирал функцию субтитров от сообщества, которая позволяла загружать субтитры для чужих видео. Это вызвало очень успешную кампанию, побудившую авторов добавлять субтитры к своим видео для слабослышащих и глухих зрителей.

​

Лидеры по загрузкам в динамике

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 │

​

Как меняется доля лайков с ростом числа просмотров?

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 │
└───────────────────┴─────────────────────┴────────────┘

​

Как распределяются просмотры?

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 │
└────────────┴─────────┘