Перейти к основному содержимому
Перейти к основному содержимому

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

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

к сведению

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

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

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

примечание

Шаги ниже также легко работают на локальной установке ClickHouse. Единственное изменение - использовать функцию s3 вместо s3cluster (если у вас не настроен кластер, в противном случае измените default на имя вашего кластера).

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

  1. Давайте посмотрим, как выглядят данные. Функция таблицы s3cluster возвращает таблицу, поэтому мы можем выполнить DESCRIBE результата:

ClickHouse выводит следующую схему из файла JSON:

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

Это вставляет много данных - 4,65 миллиарда строк. Если вы не хотите весь набор данных, просто добавьте условие LIMIT с желаемым числом строк.

Некоторые комментарии касательно нашей команды INSERT:

  • Функция parseDateTimeBestEffortUSOrZero удобна, когда входные даты могут быть в неправильном формате. Если fetch_date не удается правильно распарсить, он будет установлен в 0
  • Столбец upload_date содержит действительные даты, но также содержит строки, такие как "4 часа назад" - что определенно не является действительной датой. Мы решили сохранить оригинальное значение в upload_date_str и попытаться разобрать его с помощью toDate(parseDateTimeBestEffortUSOrZero(upload_date::String)). Если разбор не удается, мы просто получаем 0
  • Мы использовали функцию ifNull, чтобы избежать получения значений NULL в нашей таблице. Если входящее значение NULL, функция ifNull устанавливает значение в пустую строку
  1. Откройте новую вкладку в консоли SQL ClickHouse Cloud (или новое окно clickhouse-client) и наблюдайте за увеличением счетчика. Вставка 4,56B строк займет некоторое время, в зависимости от ваших серверных ресурсов. (Без каких-либо настроек это занимает около 4,5 часов.)
  1. Как только данные вставлены, вы можете подсчитать количество дизлайков ваших любимых видеороликов или каналов. Давайте посмотрим, сколько видеороликов было загружено ClickHouse:
примечание

Запрос выше выполняется так быстро, потому что мы выбрали uploader в качестве первого столбца первичного ключа - поэтому ему нужно было обработать только 237k строк.

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

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

  1. Вот поиск видео с ClickHouse в полях title или description:

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

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

Вопросы​

Если кто-то отключает комментарии, понижает ли это вероятность того, что кто-то действительно нажмет лайк или дизлайк?​

Когда комментарии отключены, более склонны ли люди ставить лайки или дизлайки, чтобы выразить свои чувства о видео?

Включение комментариев, похоже, коррелирует с более высокой степенью вовлеченности.

Как изменяется количество видео с течением времени - заметные события?​

Пик количества загрузчиков вокруг коронавируса заметен.

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

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

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

Топ-загрузчики с течением времени​

Как изменяется соотношение лайков по мере увеличения просмотров?​

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