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

7419

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

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

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

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

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

На данный момент этот блок не поддерживается, но мы не забыли о нём!Наша команда уже занята его разработкой, он будет доступен в ближайшее время.

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

Я взял данные 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.

Геолокация

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

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

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

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

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

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

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

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

7419