Самые популярные книги по версии Stack Overflow — разработчик рассказал о создании удобного сервиса и поделился месячным отчётом
Комментарии
В закладки
7419
Пользователь ресурса freeCodeCamp Влад Ветцель решил выяснить, как найти «свою» книгу по программированию.
Свободное время разработчика ограничено, а для чтения его нужно много. Поэтому очень важно выбрать хорошую книгу, после прочтения которой не возникнет ощущения потерянного времени.
К счастью, Stack Exchange (родительская компания Stack Overflow) опубликовала дамп своей базы данных, которым и воспользовался Ветцель. Он запустил сервис dev-books.com, который позволяет изучить все собранные и отсортированные им данные о книгах, когда-либо упомянутых на Stack Overflow. Сайт уже посетило более 100 000 человек.
Кроме того, Влад поделился историей создания этого сервиса. Передаём ему слово.
Рассказывает Влад Ветцель
На данный момент этот блок не поддерживается, но мы не забыли о нём!Наша команда уже занята его разработкой, он будет доступен в ближайшее время.
С самого начала было ясно, что нельзя выложить XML-файл размером 48 ГБ в новую базу данных (PostgreSQL), используя популярные методы, такие как myxml := pg_read_file('path/to/my_file.xml'), потому что на моем сервере не было 48 ГБ ОЗУ. Поэтому я решил использовать парсер SAX.
Все значения хранились в тегах <row>, так что для парсинга я использовал скрипт на Python:
def startElement(self, name, attributes):
if name == 'row':
self.cur.execute("INSERT INTO posts (Id, Post_Type_Id, Parent_Id, Accepted_Answer_Id, Creation_Date, Score, View_Count, Body, Owner_User_Id, Last_Editor_User_Id, Last_Editor_Display_Name, Last_Edit_Date, Last_Activity_Date, Community_Owned_Date, Closed_Date, Title, Tags, Answer_Count, Comment_Count, Favorite_Count) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
(
(attributes['Id'] if 'Id' in attributes else None),
(attributes['PostTypeId'] if 'PostTypeId' in attributes else None),
(attributes['ParentID'] if 'ParentID' in attributes else None),
(attributes['AcceptedAnswerId'] if 'AcceptedAnswerId' in attributes else None),
(attributes['CreationDate'] if 'CreationDate' in attributes else None),
(attributes['Score'] if 'Score' in attributes else None),
(attributes['ViewCount'] if 'ViewCount' in attributes else None),
(attributes['Body'] if 'Body' in attributes else None),
(attributes['OwnerUserId'] if 'OwnerUserId' in attributes else None),
(attributes['LastEditorUserId'] if 'LastEditorUserId' in attributes else None),
(attributes['LastEditorDisplayName'] if 'LastEditorDisplayName' in attributes else None),
(attributes['LastEditDate'] if 'LastEditDate' in attributes else None),
(attributes['LastActivityDate'] if 'LastActivityDate' in attributes else None),
(attributes['CommunityOwnedDate'] if 'CommunityOwnedDate' in attributes else None),
(attributes['ClosedDate'] if 'ClosedDate' in attributes else None),
(attributes['Title'] if 'Title' in attributes else None),
(attributes['Tags'] if 'Tags' in attributes else None),
(attributes['AnswerCount'] if 'AnswerCount' in attributes else None),
(attributes['CommentCount'] if 'CommentCount' in attributes else None),
(attributes['FavoriteCount'] if 'FavoriteCount' in attributes else None)
)
);
После трех дней загрузки (за это время загрузилась почти половина XML), я понял, что допустил ошибку: атрибут ParentID на самом деле должен был быть задан как ParentId.
Ждать еще неделю мне не хотелось, и я перешел с AMD E-350 (2×1.35GHz) на Intel G2020 (2×2.90GHz). Но и это не ускорило процесс.
Следующим решением стала пакетная вставка:
class docHandler(xml.sax.ContentHandler):
def __init__(self, cusor):
self.cusor = cusor;
self.queue = 0;
self.output = StringIO();
def startElement(self, name, attributes):
if name == 'row':
self.output.write(
attributes['Id'] + '\t` +
(attributes['PostTypeId'] if 'PostTypeId' in attributes else '\\N') + '\t' +
(attributes['ParentId'] if 'ParentId' in attributes else '\\N') + '\t' +
(attributes['AcceptedAnswerId'] if 'AcceptedAnswerId' in attributes else '\\N') + '\t' +
(attributes['CreationDate'] if 'CreationDate' in attributes else '\\N') + '\t' +
(attributes['Score'] if 'Score' in attributes else '\\N') + '\t' +
(attributes['ViewCount'] if 'ViewCount' in attributes else '\\N') + '\t' +
(attributes['Body'].replace('\\', '\\\\').replace('\n', '\\\n').replace('\r', '\\\r').replace('\t', '\\\t') if 'Body' in attributes else '\\N') + '\t' +
(attributes['OwnerUserId'] if 'OwnerUserId' in attributes else '\\N') + '\t' +
(attributes['LastEditorUserId'] if 'LastEditorUserId' in attributes else '\\N') + '\t' +
(attributes['LastEditorDisplayName'].replace('\n', '\\n') if 'LastEditorDisplayName' in attributes else '\\N') + '\t' +
(attributes['LastEditDate'] if 'LastEditDate' in attributes else '\\N') + '\t' +
(attributes['LastActivityDate'] if 'LastActivityDate' in attributes else '\\N') + '\t' +
(attributes['CommunityOwnedDate'] if 'CommunityOwnedDate' in attributes else '\\N') + '\t' +
(attributes['ClosedDate'] if 'ClosedDate' in attributes else '\\N') + '\t' +
(attributes['Title'].replace('\\', '\\\\').replace('\n', '\\\n').replace('\r', '\\\r').replace('\t', '\\\t') if 'Title' in attributes else '\\N') + '\t' +
(attributes['Tags'].replace('\n', '\\n') if 'Tags' in attributes else '\\N') + '\t' +
(attributes['AnswerCount'] if 'AnswerCount' in attributes else '\\N') + '\t' +
(attributes['CommentCount'] if 'CommentCount' in attributes else '\\N') + '\t' +
(attributes['FavoriteCount'] if 'FavoriteCount' in attributes else '\\N') + '\n'
);
self.queue += 1;
if (self.queue >= 100000):
self.queue = 0;
self.flush();
def flush(self):
self.output.seek(0);
self.cusor.copy_from(self.output, 'posts')
self.output.close();
self.output = StringIO();
StringIO позволяет использовать переменную вместо файла для обработки функции copy_from, которая использует COPY. Таким образом, весь процесс импорта данных занял всего одну ночь.
После этого я занялся созданием индексов. Обычно индексы GiST медленнее, чем GIN, но они занимают меньше места. Поэтому я решил использовать GiST. На следующий день у меня был индекс объёмом 70 ГБ.
Когда я запустил пару тестовых запросов, я понял, что для их обработки нужно слишком много времени. Причина была в чтении с диска, и тут меня выручил новый SSD на 120 ГБ.
Я создал новый кластер PostgreSQL:
initdb -D /media/ssd/postgresql/data
Затем я позаботился о том, чтобы скорректировать файл конфигурации (я использовал Manjaro OS):
vim /usr/lib/systemd/system/postgresql.service
Environment=PGROOT=/media/ssd/postgresql
PIDFile=/media/ssd/postgresql/data/postmaster.pid
На этот раз для импорта потребовалась пара часов, но я использовал GIN. Индексы заняли 20 ГБ пространства на SSD, а выполнение простых запросов занимало меньше минуты.
Извлечение книг из базы данных
Когда мои данные, наконец, были импортированы, я начал искать сообщения, в которых упоминались книги, а затем скопировал их в отдельную SQL-таблицу:
CREATE TABLE books_posts AS SELECT * FROM posts WHERE body LIKE '%book%'";
Следующим шагом нужно было найти все гиперссылки:
CREATE TABLE http_books AS SELECT * posts WHERE body LIKE '%http%'";
Тут я понял, что StackOverflow проксирует все ссылки так: rads.stackowerflow.com/[$isbn]/
Я создал еще одну таблицу со всеми постами, содержащими ссылки:
CREATE TABLE rads_posts AS SELECT * FROM posts WHERE body LIKE '%http://rads.stackowerflow.com%'";
Все номера ISBN я извлёк при помощи регулярного выражения. Я поместил теги Stack Overflow в другую таблицу через regexp_split_to_table.
Как только самые популярные теги были извлечены и подсчитаны, топ-20 книг по всем тегам почти совпадал. Поэтому я решил улучшить систему рейтинга.
Идея заключалась в том, чтобы брать 20 самых популярных книг для каждого тега и исключать книги, которые уже были обработаны.
Поскольку это была «разовая» работа, я решил использовать массивы PostgreSQL. Примерный план создания запроса:
SELECT *
, ARRAY(SELECT UNNEST(isbns) EXCEPT SELECT UNNEST(to_exclude ))
, ARRAY_UPPER(ARRAY(SELECT UNNEST(isbns) EXCEPT SELECT UNNEST(to_exclude )), 1)
FROM (
SELECT *
, ARRAY['isbn1', 'isbn2', 'isbn3'] AS to_exclude
FROM (
SELECT
tag
, ARRAY_AGG(DISTINCT isbn) AS isbns
, COUNT(DISTINCT isbn)
FROM (
SELECT *
FROM (
SELECT
it.*
, t.popularity
FROM isbn_tags AS it
LEFT OUTER JOIN isbns AS i on i.isbn = it.isbn
LEFT OUTER JOIN tags AS t on t.tag = it.tag
WHERE it.tag in (
SELECT tag
FROM tags
ORDER BY popularity DESC
LIMIT 1 OFFSET 0
)
ORDER BY post_count DESC LIMIT 20
) AS t1
UNION ALL
SELECT *
FROM (
SELECT
it.*
, t.popularity
FROM isbn_tags AS it
LEFT OUTER JOIN isbns AS i on i.isbn = it.isbn
LEFT OUTER JOIN tags AS t on t.tag = it.tag
WHERE it.tag in (
SELECT tag
FROM tags
ORDER BY popularity DESC
LIMIT 1 OFFSET 1
)
ORDER BY post_count
DESC LIMIT 20
) AS t2
UNION ALL
SELECT *
FROM (
SELECT
it.*
, t.popularity
FROM isbn_tags AS it
LEFT OUTER JOIN isbns AS i on i.isbn = it.isbn
LEFT OUTER JOIN tags AS t on t.tag = it.tag
WHERE it.tag in (
SELECT tag
FROM tags
ORDER BY popularity DESC
LIMIT 1 OFFSET 2
)
ORDER BY post_count DESC
LIMIT 20
) AS t3
...
UNION ALL
SELECT *
FROM (
SELECT
it.*
, t.popularity
FROM isbn_tags AS it
LEFT OUTER JOIN isbns AS i on i.isbn = it.isbn
LEFT OUTER JOIN tags AS t on t.tag = it.tag
WHERE it.tag in (
SELECT tag
FROM tags
ORDER BY popularity DESC
LIMIT 1 OFFSET 78
)
ORDER BY post_count DESC
LIMIT 20
) AS t79
) AS tt
GROUP BY tag
ORDER BY max(popularity) DESC
) AS ttt
) AS tttt
ORDER BY ARRAY_upper(ARRAY(SELECT UNNEST(arr) EXCEPT SELECT UNNEST(la)), 1) DESC;
Создание веб-приложения
Поскольку я не веб-разработчик и, конечно, не эксперт по пользовательским интерфейсам, я решил создать простое одностраничное приложение, основанное на Bootstrap.
Я создал опцию «Поиск по тегу» и извлёк самые популярные теги, чтобы сделать результаты поиска кликабельными.
Для визуализации результатов поиска я использовал столбчатую диаграмму. Сперва я попробовал Hightcharts и D3, но у них были проблемы с отзывчивостью и настройкой, поэтому я создал свою отзывчивую диаграмму на основе SVG:
На данный момент этот блок не поддерживается, но мы не забыли о нём!Наша команда уже занята его разработкой, он будет доступен в ближайшее время.
Заключение
Этот сервис весьма полезен для людей, у которых нет времени изучать огромные списки книг о программировании, особенно учитывая их разнонаправленность, а поиск по тегам делает работу с проектом очень быстрой и удобной. Автор обещает опубликовать полный отчет в конце марта.
Обновление 25.03: Автор опубликовал полный отчёт из Google Analytics и Amazon вместе со своей историей. Слово Владу.
На данный момент этот блок не поддерживается, но мы не забыли о нём!Наша команда уже занята его разработкой, он будет доступен в ближайшее время.
Как сайт приносит прибыль
Для этого проекта я выбрал партнерскую программу Amazon, потому что Amazon — это самый большой известный мне книжный магазин. Регистрация учетной записи была довольно простой, поэтому я получил свой тег в партнерской программе для использования в ссылках на моем веб-сайте менее чем за час.
Начальный запуск
После запуска я разместил ссылку на dev-books.com на сайтах Hacker News и Reddit, пытаясь привлечь внимание некоторых разработчиков к моему проекту.
К сожалению, это не привлекло широкой аудитории, на которую я надеялся. Но я начал получать отзывы, исправил некоторые ошибки и получил предложения о том, как продвинуть мой проект.
В конце дня я получил сообщение от преподавателя freeCodeCampКуинси Ларсона. Он предложил мне написать рассказ о моем проекте и о его создании, чтобы сделать его более доступным для людей.
Прорыв
Я прочитал статью Куинси Ларсона и нашел очень полезный инструмент для создания заголовков, Headline Analyzer. Я проверил заголовок, который использовал для своих постов, и получил довольно низкую оценку. Я улучшил его для повторной отправки на мой сайт.
Довольно скоро я набрал 65 очков и опубликовал свежий заголовок для Hacker News и Reddit.
На этот раз, по данным Google Analytics, было зафиксировано более 750 одновременных посетителей в течение нескольких часов подряд, а мой пост в Hacker News был в топе.
На следующий день я узнал, что у dev-books.com более 5000 репостов на Facebook.
Геолокация
На следующий день я создал отчет по геолокации в Google Analytics и выяснил, что довольно много трафика идет из Азии и Европы, в особенности из России.
Мои азиатские и европейские посетители были вынуждены ожидать заказы из США. Чтобы избежать этого ожидания, я создал партнерские аккаунты на amazon.co.uk, amazon.de и amazon.fr. Из-за этой проблемы я потерял несколько заказов.
В ту же ночь мои посетители начали получать ссылки на ближайший магазин.
Самая интересная часть
На приведенных ниже рисунках вы можете увидеть статистику Amazon.com за февраль 2017 года. Из всего европейского кластера Amazon я получил чуть больше 250 долларов. Возможно, было бы больше, если бы я сделал ссылки на основе геолокации с самого начала.
Мой заработок за февраль: $2534,40.
Доволен ли я результатом? Абсолютно.
Dev-books.com по-прежнему работает и продолжает получать заказы. За полтора месяца с момента запуска сервис принес мне более 3000 долларов.