В ноябре 2021 года YouTube убрал публичный счётчик дизлайков у всех своих видео. Хотя авторы по-прежнему могут видеть количество дизлайков, зрители теперь видят только количество лайков, которые получило видео.
Ссылки
Набор данных содержит более 4,55 миллиарда записей, поэтому не спешите просто копировать и выполнять приведённые ниже команды, если ваши ресурсы не способны обработать такой объём данных. Команды ниже были выполнены на Production-инстансе ClickHouse Cloud.
Данные представлены в формате JSON и могут быть загружены с archive.org. Мы также разместили эти данные в S3, чтобы их можно было более эффективно загружать в инстанс ClickHouse Cloud.
Ниже приведены шаги для создания таблицы в ClickHouse Cloud и загрузки данных.
Примечание
Приведённые ниже шаги также без проблем подойдут для локальной установки ClickHouse. Единственное изменение — использовать функцию s3 вместо s3cluster (если только у вас не настроен кластер — в этом случае замените default на имя вашего кластера).
Следующая команда в потоковом режиме загружает записи из файлов 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 устанавливает его в пустую строку.
Откройте новую вкладку в SQL Console ClickHouse Cloud (или новое окно clickhouse-client) и следите за увеличением счётчика.
Вставка 4,56 млрд строк займёт некоторое время в зависимости от ресурсов сервера. (Без настройки параметров это занимает около 4,5 часов.)
SELECT formatReadableQuantity(count())
FROM youtube
Запрос выполняется так быстро, потому что мы выбрали uploader в качестве первого столбца первичного ключа — поэтому потребовалось обработать только 237 тыс. строк.
Рассмотрим лайки и дизлайки видеороликов 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.)
Вот пример запроса, ищущего видео с ClickHouse в полях title или 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;
Этот запрос должен обработать каждую строку и дополнительно распарсить два столбца строк. Даже при этом мы получаем достойную производительность — 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.)
Если отключить комментарии, уменьшится ли вероятность того, что кто-то поставит лайк или дизлайк?
Когда комментарии отключены, станут ли люди чаще ставить лайки или дизлайки, чтобы выразить своё отношение к видео?
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;
Включение комментариев, как правило, коррелирует с более высоким уровнем вовлечённости.
Как со временем меняется количество видео — какие при этом можно выделить события?
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;
Больше субтитров со временем: когда это произошло
С развитием технологий распознавания речи создавать субтитры для видео стало проще, чем когда-либо: 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;
Данные показывают всплеск в 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;
Как меняется соотношение лайков по мере увеличения числа просмотров?
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
);
