В ноябре 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
После вставки данных можно подсчитать количество дизлайков для ваших любимых видео или каналов. Посмотрим, сколько видео было загружено каналом ClickHouse:
SELECT count()
FROM youtube
WHERE uploader = 'ClickHouse';
┌─count()─┐
│ 84 │
└─────────┘
Получена 1 строка. Прошло: 0.570 сек. Обработано 237.57 тыс. строк, 5.77 МБ (416.54 тыс. строк/сек., 10.12 МБ/сек.)
Примечание
Запрос выполняется так быстро, потому что мы выбрали 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 строки. Затрачено: 0,013 сек. Обработано 155,65 тыс. строк, 16,94 МБ (11,96 млн строк/с., 1,30 ГБ/с.)
Вот пример поиска видео с упоминанием 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 строк. Затрачено: 1099,368 сек. Обработано 4,56 млрд строк, 1,98 ТБ (4,15 млн строк/с., 1,80 ГБ/с.)
Если отключить комментарии, уменьшится ли вероятность того, что кто-то поставит лайк или дизлайк?
Когда комментарии отключены, станут ли люди чаще ставить лайки или дизлайки, чтобы выразить своё отношение к видео?
SELECT
concat('< ', formatReadableQuantity(view_range)) AS просмотры,
is_comments_enabled,
total_clicks / num_views AS вероятность_реакции
FROM
(
SELECT
is_comments_enabled,
power(10, CEILING(log10(view_count + 1))) AS view_range,
sum(like_count + dislike_count) AS всего_реакций,
sum(view_count) AS число_просмотров
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
);
