Написать пост

Самые популярные книги по версии Stack Overflow — разработчик рассказал о создании удобного сервиса и поделился месячным отчётом

Аватар Юлия Черкашина
Юлия Черкашина
Отредактировано

7К открытий7К показов
Самые популярные книги по версии Stack Overflow — разработчик рассказал о создании удобного сервиса и поделился месячным отчётом

Пользователь ресурса freeCodeCamp Влад Ветцель решил выяснить, как найти «свою» книгу по программированию.

Свободное время разработчика ограничено, а для чтения его нужно много. Поэтому очень важно выбрать хорошую книгу, после прочтения которой не возникнет ощущения потерянного времени.

К счастью, Stack Exchange (родительская компания Stack Overflow) опубликовала дамп своей базы данных, которым и воспользовался Ветцель. Он запустил сервис dev-books.com, который позволяет изучить все собранные и отсортированные им данные о книгах, когда-либо упомянутых на Stack Overflow. Сайт уже посетило более 100 000 человек.

Самые популярные книги по версии Stack Overflow — разработчик рассказал о создании удобного сервиса и поделился месячным отчётом 1

Кроме того, Влад поделился историей создания этого сервиса. Передаём ему слово.

Рассказывает Влад Ветцель

Получение и импорт данных

Я взял данные Stack Exchange из archive.org.

С самого начала было ясно, что нельзя выложить 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

Я перезагрузил конфиг и запустил PostgreSQL:

			systemctl daemon-reload
postgresql systemctl start postgresql

На этот раз для импорта потребовалась пара часов, но я использовал 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:

			var w = $('#plot').width();
var bars = "";var imgs = "";
var texts = "";
var rx = 10;
var tx = 25;
var max = Math.floor(w / 60);
var maxPop = 0;
for(var i =0; i < max; i ++){
  if(i > books.length - 1 ){
    break;
  }
  obj = books[i];
  if(maxPop < Number(obj.pop)) {
    maxPop = Number(obj.pop);
  }
}

for(var i =0; i < max; i ++){
  if(i > books.length - 1){
    break;
   }
   obj = books[i];
   h = Math.floor((180 / maxPop ) * obj.pop);
   dt = 0;
   
   if(('' + obj.pop + '').length == 1){
    dt = 5;
   }
   
   if(('' + obj.pop + '').length == 3){
    dt = -3;
   }
   
   var scrollTo = 'onclick="scrollTo(\''+ obj.id +'\'); return false;" "';
   bars += '<rect  x="'+ rx +'" y="' + (180 - h + 30) + '" width="50" height="' + h + '" ' + scrollTo + '>';
    
   bars += '<title>' + obj.name+ '</title>';
   bars += '</rect>';
   
   imgs += '<image height="70" x="'+ rx +'" y="220" href="img/ol/jpeg/' + obj.id + '.jpeg" onmouseout="unhoverbar('+ obj.id +');" onmouseover="hoverbar('+ obj.id +');" width="50" ' + scrollTo + '>';
   imgs += '<title>' + obj.name+ '</title>';
   imgs += '</image>';
   
   texts += '<text x="'+ (tx + dt) +'" y="'+ (180 - h + 20) +'"   style="font-size: 16px;" ' + scrollTo + '>' + obj.pop + '</text>';
   rx += 60;
   tx += 60;
}

$('#plot').html(
    ' <svg width="100%" height="300" aria-labelledby="title desc" role="img">'
  + '  <defs> '
  + '    <style type="text/css"><![CDATA['
  + '      .cla {'
  + '        fill: #337ab7;'
  + '      }'
  + '      .cla:hover {'
  + '        fill: #5bc0de;'
  + '      }'
  + '      ]]></style>'
  + '  </defs>'
  + '  <g>'
  + bars
  + '  </g>'
  + '  <g>'
  + imgs
  + '  </g>'
  + '  <g>'
  + texts
  + '  </g>'
  + '</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.

Самые популярные книги по версии Stack Overflow — разработчик рассказал о создании удобного сервиса и поделился месячным отчётом 2

Геолокация

На следующий день я создал отчет по геолокации в Google Analytics и выяснил, что довольно много трафика идет из Азии и Европы, в особенности из России.

Самые популярные книги по версии Stack Overflow — разработчик рассказал о создании удобного сервиса и поделился месячным отчётом 3

Мои азиатские и европейские посетители были вынуждены ожидать заказы из США. Чтобы избежать этого ожидания, я создал партнерские аккаунты на amazon.co.uk, amazon.de и amazon.fr. Из-за этой проблемы я потерял несколько заказов.

В ту же ночь мои посетители начали получать ссылки на ближайший магазин.

Самая интересная часть

На приведенных ниже рисунках вы можете увидеть статистику Amazon.com за февраль 2017 года. Из всего европейского кластера Amazon я получил чуть больше 250 долларов. Возможно, было бы больше, если бы я сделал ссылки на основе геолокации с самого начала.

Мой заработок за февраль: $2534,40.

Самые популярные книги по версии Stack Overflow — разработчик рассказал о создании удобного сервиса и поделился месячным отчётом 4

Доволен ли я результатом? Абсолютно.

Dev-books.com по-прежнему работает и продолжает получать заказы. За полтора месяца с момента запуска сервис принес мне более 3000 долларов.

Следите за новыми постами
Следите за новыми постами по любимым темам
7К открытий7К показов
Также рекомендуем
Поиск данных в таблице. SQL - это просто
Поиск данных в таблице. SQL - это просто
Выучим слово WHERE в SQL, позволяющее извлекать из таблицы не все данные подряд, а только те которые нам нужны.
Редкие языки программирования: зачем они нужны и что на них пишут
Редкие языки программирования: зачем они нужны и что на них пишут
Современные разработчики все чаще обращаются к редким языкам программирования, чтобы решить специфические задачи проекта. Разбираемся, как и когда их нужно использовать.
70-летняя бабуля изучила программирование и написала игру
70-летняя бабуля изучила программирование и написала игру
70-летняя бабушка изучила программирование и создала свою игру Grandma's Word, о чем сообщил её внук на Reddit. Игра представляет собой головоломку на JavaScript, где нужно угадать слово.
Почему именно SQL и Python — мастхев для аналитика?
Почему именно SQL и Python — мастхев для аналитика?
Канал Noukash выпустил ролик об основных инструментах аналитика: Python и SQL. Автор объяснил, почему важно владеть обоими языками, а мы от себя добавили ссылки на полезные материалы.