Любая деятельность генерирует данные. Чем бы вы ни занимались, у вас наверняка на руках кладезь необработаной полезной информации, ну или хотя бы доступ к его источнику.
Сегодня побеждает тот, кто принимает решения, основываясь на объективных данных. Навыки аналитика как никогда актуальны, а наличие под рукой необходимых для этого инструментов позволяет всегда быть на шаг впереди. Это и есть подспорьем появления данной статьи.
У вас есть свой бизнес? Или может… хотя, не важно. Сам процесс добычи данных бесконечен и увлекателен. И даже просто хорошо покопавшись в интернете можно найти себе поле для деятельности.
Вот, что мы имеем сегодня – Неофициальная XML-база раздач сайта RuTracker.ORG. База обновляется раз в полгода и содержит в себе информацию о всех раздачах за историю существования данного торрент-трекера.
Что она может рассказать владельцам рутрекера? А непосредственным пособникам пиратства в интернете? Или обычному юзеру, увлекающемуся аниме, например?
Понимаете о чем я?
Я не поддерживаю пиратство в интернете и против него. Прибегаю к использованию торрентов только в случае скачивания open source продуктов.
Выбор данной темы вызван исключительно интересом к аналитике и big data.
Стэк – R, Clickhouse, Dataiku
Любая аналитика проходит несколько основных этапов: извлечение данных, их подготовка и изучение данных (визуализация). Для каждого этапа — свой инструмент. Потому сегодняшний стэк:
- R. Да, непопулярный и уступает Python. Но до того же чистый и приятный со своим dplyr и ggplot2. Он рожден для аналитики и не пользоваться этим – преступление.
- Clickhouse. Колоночная аналитическая СУБД. Наверняка слышали: “clickhouse не тормозит” или “скорость на грани фантастики”. Народ не врет, и мы в этом убедимся. В ответе за моментальность.
- Dataiku. Платформа для обработки, визуализации и прогнозного анализа бизнес-данных.
Ревью: Dataiku работает на линуксе и маке. Доступна бесплатная версия с ограничением пользователей до 3 человек. Документация тут.
Удивительно, но на русскоязычных ресурсах и даже на Хабре, до сих пор нет ажиотажа или хайпа, если хотите, на тему неотразимости данной платформы. Возьмусь исправить сие недоразумение и прошу поздравить dataiku с почином.
Big Data – big problems
На руках сжатый xml–файл весом 5 Гб. Внутри – база всех раздач сайта rutracker.org, с самого начала его существования (2005 г.) и до ноября 2019 г. А это 15 лет!
Загрузить такой обьем в R Studio – ха! Не вариант. Мы люди простые, ресурсы ограничены.
Значит нужна БД, дабы подключаться и делать запросы через R. Поскольку имеем дело с Big Data выбираем Clickhouse и … не так быстро, у нас все еще xml–файл. Надо распарсить. И опять упираемся в ресурсы.
Тут на сцену выходит наш сегодняшний дебютант. Импортировать и подготовить такой обьем в Dataiku DSS не проблема. Но у нас будет ограничение на отображаемый семпл – 10 000 строк. Просмотреть аналитику также можно только в рамках семпла. Но для парсинга нам достаточно, вполне. Лимит на семпл можно и поднять, документация для корректной работы советует не больше 200 000 строк.
Создаем проект, импортируем дату. Пару минут и сырые данные готовы к предобработке.
Получили данные разных форматов. Самые интересные: колонка content — с описанием каждого торрента в разметке форумного движка и несколько колонок в формате массива json.
Удаляем пока колонку content, для сквозного анализа она будет нам в тягость. Но к ней мы еще вернемся – там есть где закопаться.
Создаем recipe — правила предобработки. Из соответствующих колонок достаем информацию о торренте, загружаемом файле и форуме к которому он относится. Благо датайку позволяет нам парсить json массивы.
Форматируем дату регистрации торрента. Отмечу, ни строчки кода еще не написано, и это огромный + для dataiku.
Запускаем наш recipe, ждем пол часа — на выходе все красиво.
Забираем csv с чистой датой и импортируем в Clickhouse.
Простота и фантастическая скорость
Давайте протестируем Clickhouse и охватим наконец все 15 лет существования rutracker-a.
Сколько же торрентов в нашей базе?
SELECT ROUND(uniq(torrent_id) / 1000000, 2) AS Count_M
FROM rutracker
┌─Count_M─┐
│ 1.46 │
└─────────┘
1 rows in set. Elapsed: 0.247 sec. Processed 25.51 million rows, 204.06 MB (103.47 million rows/s., 827.77 MB/s.)Итого 1.5 млн торрентов и 25 млн строк. За 0.3 с! Попробуем запрос посложнее и понаблюдаем за скоростью.
Посмотрим, к примеру, сколько книжек нам доступно для скачивания.
SELECT COUNT(*) AS Count
FROM rutracker
WHERE (file_ext = 'epub') OR (file_ext = 'fb2') OR (file_ext = 'mobi')
┌──Count─┐
│ 333654 │
└────────┘
1 rows in set. Elapsed: 0.435 sec. Processed 25.51 million rows, 308.79 MB (58.64 million rows/s., 709.86 MB/s.)300 тыс — читать не перечитать! Но согласитесь, там есть дубли. Раз уж на то пошло узнаем их суммарный вес.
SELECT ROUND(SUM(file_size) / 1000000000, 2) AS Total_size_GB
FROM rutracker
WHERE (file_ext = 'epub') OR (file_ext = 'fb2') OR (file_ext = 'mobi')
┌─Total_size_GB─┐
│ 625.75 │
└───────────────┘
1 rows in set. Elapsed: 0.296 sec. Processed 25.51 million rows, 344.32 MB (86.24 million rows/s., 1.16 GB/s.)Итог – мы охватили 25 млн строк менее чем за пол секунды. Приятно, не правда ли?
Добыча данных в R
Продолжим добывать данные уже в R. Подключим библиотеки, в часности DBI (для работы с БД). И установим соединение с Clickhouse.
library(DBI) # Для работы с БД, в.т.ч. Clickhouse
library(dplyr) # Для пайпов %>%
# Визуализация
library(ggplot2)
library(ggrepel)
library(cowplot)
library(scales)
library(ggrepel)
# Подключимся к localhost:9000
connection <- dbConnect(RClickhouse::clickhouse(), host="localhost", port = 9000)Все, можно делать запросы, и сразу же визуализировать. А благодаря dplyr можем легко обойтись и без переменных.
Так умирают ли торренты? Давайте посмотрим статистику их количества на rutracker.org по годам.
years_stat <- dbGetQuery(connection,
"SELECT
round(COUNT(*)/1000000, 2) AS Files,
round(uniq(torrent_id)/1000, 2) AS Torrents,
toYear(torrent_registred_at) AS Year
FROM rutracker
GROUP BY Year")
ggplot(years_stat, aes(as.factor(Year), as.double(Files))) +
geom_bar(stat = 'identity', fill = "darkblue", alpha = 0.8)+
theme_minimal() +
labs(title = "Сколько файлов было загружено на RuTracker", subtitle = "за 2005 - 2019n")+
theme(axis.text.x = element_text(angle=90, vjust = 0.5),
axis.text.y = element_text(),
axis.title.y = element_blank(),
axis.title.x = element_blank(),
panel.grid.major.x = element_blank(),
panel.grid.major.y = element_line(size = 0.9),
panel.grid.minor.y = element_line(size = 0.4),
plot.title = element_text(vjust = 3, hjust = 0, family = "sans", size = 16, color = "#101010", face = "bold"),
plot.caption = element_text(vjust = 3, hjust = 0, family = "sans", size = 12, color = "#101010", face = "bold"),
plot.margin = unit(c(1,0.5,1,0.5), "cm"))+
scale_y_continuous(labels = number_format(accuracy = 1, suffix = " млн"))
ggplot(years_stat, aes(as.factor(Year), as.integer(Torrents))) +
geom_bar(stat = 'identity', fill = "#008b8b", alpha = 0.8)+
theme_minimal() +
labs(title = "Сколько торрентов было добавлено на RuTracker", subtitle = "за 2005 - 2019n", caption = "*Количество уникальных торрентов")+
theme(axis.text.x = element_text(angle=90, vjust = 0.5),
axis.text.y = element_text(),
axis.title.y = element_blank(),
axis.title.x = element_blank(),
panel.grid.major.x = element_blank(),
panel.grid.major.y = element_line(size = 0.9),
panel.grid.minor.y = element_line(size = 0.4),
plot.title = element_text(vjust = 3, hjust = 0, family = "sans", size = 16, color = "#101010", face = "bold"),
plot.caption = element_text(vjust = -3, hjust = 1, family = "sans", size = 9, color = "grey60", face = "plain"),
plot.margin = unit(c(1,0.5,1,0.5), "cm")) +
scale_y_continuous(labels = number_format(accuracy = 1, suffix = " тыс"))
На каждом из графиков заметно просел 2016 год. Важно отметить, что в январе 2016 официально вступило в силу решение Роскомнадзора о блокировке rutracker.org для российских пользователей. Тогда, в СМИ сообщалось о незначительном снижении посещаемости сайта, что коррелирует с нашей картиной.
Количество файлов последние года очевидно возрастает, при том что количество торрентов остается практически на одном уровне. Это значит что на один торрент приходится все больше возможных расширений.
Пролить свет на данную картину нам поможет статистика ТОПа расширений за весь период.
extention_stat <- dbGetQuery(connection,
"SELECT toYear(torrent_registred_at) AS Year,
COUNT(tracker_id)/1000 AS Count,
ROUND(SUM(file_size)/1000000000000, 2) AS Total_Size_TB,
file_ext
FROM rutracker
GROUP BY Year, file_ext
ORDER BY Year, Count")
# Функция получения ТОПа расширений для каждого года
TopExt <- function(x, n) {
res_tab <- NULL
#Упустим 2005 и 2006, т.к. там мало торрентов
for (i in (3:15)) {
res_tab <-bind_rows(list(res_tab,
extention_stat %>% filter(Year == x[i]) %>%
arrange(desc(Count), desc(Total_Size_TB)) %>%
head(n)
))
}
return(res_tab)
}
years_list <- unique(extention_stat$Year)
ext_data <- TopExt(years_list, 5)
ggplot(ext_data, aes(as.factor(Year), as.integer(Count), fill = file_ext)) +
geom_bar(stat = "identity",position="dodge2", alpha =0.8, width = 1)+
theme_minimal() +
labs(title = "Динамика ТОПа расширений файлов на RuTracker",
subtitle = "за 2005 - 2019n",
caption = "*взято ТОП-5 за каждый год", fill = "") +
theme(axis.text.x = element_text(angle=90, vjust = 0.5),
axis.text.y = element_text(),
axis.title.y = element_blank(),
axis.title.x = element_blank(),
panel.grid.major.x = element_blank(),
panel.grid.major.y = element_line(size = 0.9),
panel.grid.minor.y = element_line(size = 0.4),
legend.title = element_text(vjust = 1, hjust = -1, family = "sans", size = 9, color = "#101010", face = "plain"),
legend.position = "top",
plot.title = element_text(vjust = 3, hjust = 0, family = "sans", size = 16, color = "#101010", face = "bold"),
plot.caption = element_text(vjust = -4, hjust = 1, family = "sans", size = 9, color = "grey60", face = "plain"),
plot.margin = unit(c(1,0.5,1,0.5), "cm")) +
scale_y_continuous(labels = number_format(accuracy = 0.5, scale = (1/1000), suffix = " млн"))+guides(fill=guide_legend(nrow=1))
И вот ответ. Очень существенно возросло количество картинок в торрентах. Они и влияют на рост количества файлов.
Давайте погуляем по разделам rutracker-a. Узнаем их суммарный вес и количество торрентов внутри.
chapter_stat <- dbGetQuery(connection,
"SELECT
substring(forum_name, 1, position(forum_name, ' -')) Chapter,
uniq(torrent_id) AS Count,
ROUND(median(file_size)/1000000, 2) AS Median_Size_MB,
ROUND(max(file_size)/1000000000) AS Max_Size_GB,
ROUND(SUM(file_size)/1000000000000) AS Total_Size_TB
FROM rutracker WHERE Chapter NOT LIKE('"%')
GROUP BY Chapter
ORDER BY Count DESC")
chapter_stat$Count <- as.integer(chapter_stat$Count)
# Функция для агрегации по разделам
AggChapter2 <- function(Chapter){
var_ch <- str(Chapter)
res = NULL
for(i in (1:22)){
select_str <-paste0(
"SELECT
toYear(torrent_registred_at) AS Year,
substring(forum_name, 1, position(forum_name, ' -')) Chapter,
uniq(torrent_id)/1000 AS Count,
ROUND(median(file_size)/1000000, 2) AS Median_Size_MB,
ROUND(max(file_size)/1000000000,2) AS Max_Size_GB,
ROUND(SUM(file_size)/1000000000000,2) AS Total_Size_TB
FROM rutracker
WHERE Chapter LIKE('", Chapter[i], "%')
GROUP BY Year, Chapter
ORDER BY Year")
res <-bind_rows(list(res, dbGetQuery(connection, select_str)))
}
return(res)
}
chapters_data <- AggChapter2(chapter_stat$Chapter)
chapters_data$Chapter <- as.factor(chapters_data$Chapter)
chapters_data$Count <- as.numeric(chapters_data$Count)
chapters_data %>% group_by(Chapter)%>%
ggplot(mapping = aes(x = reorder(Chapter, Total_Size_TB), y = Total_Size_TB))+
geom_bar(stat = "identity", fill="darkblue", alpha =0.8)+
theme(panel.grid.major.x = element_line(colour="grey60", linetype="dashed"))+
xlab('Разделn') + theme_minimal() +
labs(title = "Cуммарный вес разделов RuTracker-а",
subtitle = "на ноябрь 2019n")+
theme(axis.text.x = element_text(),
axis.text.y = element_text(family = "sans", size = 9, color = "#101010", hjust = 1, vjust = 0.5),
axis.title.y = element_text(vjust = 2.5, hjust = 0, family = "sans", size = 9, color = "grey40", face = "plain"),
axis.title.x = element_blank(),
axis.line.x = element_line(color = "grey60", size = 0.1, linetype = "solid"),
panel.grid.major.y = element_blank(),
panel.grid.major.x = element_line(size = 0.7, linetype = "solid"),
panel.grid.minor.x = element_line(size = 0.4, linetype = "solid"),
plot.title = element_text(vjust = 3, hjust = 1, family = "sans", size = 16, color = "#101010", face = "bold"),
plot.subtitle = element_text(vjust = 2, hjust = 1, family = "sans", size = 12, color = "#101010", face = "plain"),
plot.caption = element_text(vjust = -3, hjust = 1, family = "sans", size = 9, color = "grey60", face = "plain"),
plot.margin = unit(c(1,0.5,1,0.5), "cm"))+
scale_y_continuous(labels = number_format(accuracy = 1, suffix = " ТБ"))+
coord_flip()
Топ увесистых разделов вполне понятен и логичен. А вот антилидеры — Мобильные устройства и Иностранные языки — вероятно на торрентах умирают. Взглянув на распределение количества торрентов, мы в этом убедимся. Тут же, рядом расположился и раздел с Apple.
chapters_data %>% group_by(Chapter)%>%
ggplot(mapping = aes(x = reorder(Chapter, Count), y = Count))+
geom_bar(stat = "identity", fill="#008b8b", alpha =0.8)+
theme(panel.grid.major.x = element_line(colour="grey60", linetype="dashed"))+
xlab('Раздел') + theme_minimal() +
labs(title = "Распределение торрентов по разделам RuTracker-а",
subtitle = "на ноябрь 2019n")+
theme(axis.text.x = element_text(),
axis.text.y = element_text(family = "sans", size = 9, color = "#101010", hjust = 1, vjust = 0.5),
axis.title.y = element_text(vjust = 3.5, hjust = 0, family = "sans", size = 9, color = "grey40", face = "plain"),
axis.title.x = element_blank(),
axis.line.x = element_line(color = "grey60", size = 0.1, linetype = "solid"),
panel.grid.major.y = element_blank(),
panel.grid.major.x = element_line(size = 0.7, linetype = "solid"),
panel.grid.minor.x = element_line(size = 0.4, linetype = "solid"),
plot.title = element_text(vjust = 3, hjust = 1, family = "sans", size = 16, color = "#101010", face = "bold"),
plot.subtitle = element_text(vjust = 2, hjust = 1, family = "sans", size = 12, color = "#101010", face = "plain"),
plot.caption = element_text(vjust = -3, hjust = 1, family = "sans", size = 9, color = "grey60", face = "plain"),
plot.margin = unit(c(1,0.5,1,0.5), "cm"))+
scale_y_continuous(limits = c(0, 300), labels = number_format(accuracy = 1, suffix = " тыс"))+
coord_flip()
Уяснив ранее, что торренты с годами не умирают, у вас вероятно возник вопрос: а как же тогда время влияет на понятие торрент-трекера.
Тут мы можем использовать агрегацию по разделам и просмотреть тенденции за ~15 лет.
library("RColorBrewer")
getPalette = colorRampPalette(brewer.pal(19, "Spectral"))
chapters_data %>% #filter(Chapter %in% chapter_stat$Chapter[c(4,6,7,9:20)])%>%
filter(!Chapter %in% chapter_stat$Chapter[c(16, 21, 22)])%>%
filter(Year>=2007)%>%
ggplot(mapping = aes(x = Year, y = Count, fill = as.factor(Chapter)))+
geom_area(alpha =0.8, position = "fill")+
theme_minimal() +
labs(title = "Как изменяется характер торрент-трекера",
subtitle = "за ~15 лет", fill = "Раздел")+
theme(axis.text.x = element_text(vjust = 0.5),
axis.text.y = element_blank(),
axis.title.y = element_blank(),
axis.title.x = element_blank(),
panel.grid.major.x = element_blank(),
panel.grid.major.y = element_line(size = 0.9),
panel.grid.minor.y = element_line(size = 0.4),
plot.title = element_text(vjust = 3, hjust = 0, family = "sans", size = 16, color = "#101010", face = "bold"),
plot.caption = element_text(vjust = -3, hjust = 1, family = "sans", size = 9, color = "grey60", face = "plain"),
plot.margin = unit(c(1,1,1,1), "cm")) +
scale_x_continuous(breaks = c(2008, 2010, 2012, 2014, 2016, 2018),expand=c(0,0)) +
scale_fill_manual(values = getPalette(19))
Кино-пиратство на торрентах умирает — это факт. С ним за руку — Apple и мобильные устройства, которых почти и не видно.
При этом, в последнее время явно возрастает количество игр и сериалов. Вероятно эта тенденция будет сохранятся.
Отойдя немного в сторону, и взглянув на данные под новым углом, можно обнаружить еще пару скелетов Rutracker-a. Посмотрим-ка на тепловую карту ежедневного появления торрентов на rutracker.org.
unique_torr_per_day <- dbGetQuery(connection,
"SELECT toDate(torrent_registred_at) AS date,
uniq(torrent_id) AS count
FROM rutracker
GROUP BY date
ORDER BY date")
unique_torr_per_day %>%
ggplot(aes(format(date, "%Y"), format(date, "%j"), fill = as.numeric(count)))+
geom_tile() +
theme_minimal() +
labs(title = "Тепловая карта пополняемости RuTracker-a",
subtitle = "за ~15 летnn",
fill = "К-во уникальных торрентов n")+
theme(axis.text.x = element_text(vjust = 0.5),
axis.text.y = element_text(),
axis.title.y = element_blank(),
axis.title.x = element_blank(),
panel.grid.major.y = element_blank(),
panel.grid.major.x = element_line(size = 0.9),
panel.grid.minor.x = element_line(size = 0.4),
legend.title = element_text(vjust = 0.7, hjust = -1, family = "sans", size = 10, color = "#101010", face = "plain"),
legend.position = c(0.88, 1.30),
legend.direction = "horizontal",
plot.title = element_text(vjust = 3, hjust = 0, family = "sans", size = 16, color = "#101010", face = "bold"),
plot.caption = element_text(vjust = -3, hjust = 1, family = "sans", size = 9, color = "grey60", face = "plain"),
plot.margin = unit(c(1,1,1,1), "cm"))+ coord_flip(clip = "off") +
scale_y_discrete(breaks = c(format(as.Date("2007-01-15"), "%j"),
format(as.Date("2007-02-15"), "%j"),
format(as.Date("2007-03-15"), "%j"),
format(as.Date("2007-04-15"), "%j"),
format(as.Date("2007-05-15"), "%j"),
format(as.Date("2007-06-15"), "%j"),
format(as.Date("2007-07-15"), "%j"),
format(as.Date("2007-08-15"), "%j"),
format(as.Date("2007-09-15"), "%j"),
format(as.Date("2007-10-15"), "%j"),
format(as.Date("2007-11-15"), "%j"),
format(as.Date("2007-12-15"), "%j")),
labels = c("янв", "фев", "мар", "апр", "май", "июн","июл", "авг", "сен", "окт","ноя","дек"), position = 'right') +
scale_fill_gradientn(colours = c("#155220", "#c6e48b")) +
annotate(geom = "curve", x = 16.5, y = 119, xend = 13, yend = 135,
curvature = .3, color = "grey15", arrow = arrow(length = unit(2, "mm"))) +
annotate(geom = "text", x = 16, y = 45,
label = "Релиз приложения для борьбы с «замедлителем торрентов» Роскомнадзораn",
hjust = "left", vjust = -0.75, color = "grey25") +
guides(x.sec = guide_axis_label_trans(~.x)) +
annotate("rect", xmin = 11.5, xmax = 12.5, ymin = 1, ymax = 366,
alpha = .0, colour = "white", size = 0.1) +
geom_segment(aes(x = 11.5, y = 25, xend = 12.5, yend = 25, colour = "segment"),
show.legend = FALSE)
Сразу бросается в глаза всплеск активности в 2017 году. (ред. В мае того года на GitHub было выложено приложение для борьбы с попытками российских властей замедлять скорость скачивания файлов). А вот блокировка сайта в 2016 году отнюдь не очевидна, т.к существенно не повлияла на активность добавления торрентов.
Закопаться можно и хочется в любую из найденых выше закономерностей. Добывать данные можно до бесконечности. А писать и читать статью – нет.
Давайте еще немного поиграем, вернем весьма информативную колонку content и посмотрим что нам расскажут данные, к примеру, об аниме за последние 15 лет.
Её величество Dataiku
Создаем новую ветку, оставляем все видео файлы касательно аниме и парсим колонку с описанием торрентов: вытягиваем режиссера, страну, жанр, продолжительность и год выхода анимешки.
Отфильтруем картинки, субтитры и инфо-файлы. Также поднимем лимит отображаемого семпла. Пару кликов – все красиво.
Предлагаю взглянуть на года выхода наших анимешек и в тоже время потрогать удобнейшую функцию датайку – внутриколоночную аналитику.
Резюмирую: на rutracker.org доступно для скачивания аниме снятое за последние пол века, если быть точнее, уникальных годов выпуска — 60. При этом наиболее продуктивными оказались 2009 — 2014 года.
Платформа также позволяет моментально визуализировать данные. И при этом, напомню, никакого кода. Просто выбираем нужные фильтры.
К примеру, агрегируем Японию и возьмем топ самых продуктивных режиссеров. Получаем тепловую карту их активности простым перетаскиванием переменных.
К чему я веду, dataiku — отличный инструмент для аналитика любого уровня. Импорт, подготовка, анализ и визуализация данных реализуется как кодом (R, Python), так и кликаньем мышки. Но это уже совсем другая история и отличная тема для следующей статьи.
А пока, возвращаясь к RuTracker, констатируем: торренты не умирают, даже в условиях блокировок. Сама же база раздач невероятно емкая и может ответить еще не на один вопрос. Могу пообещать сделать больше аналитики, при проявленном интересе. Предлагайте свои гипотезы в комментариях.
Автор: m-pilipenko
