Самые Популярные Книги По Версии Stack Overflow — Разработчик Рассказал О Создании Удобного...

[Sascha]

Пользователь ресурса

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

Влад Ветцель

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

, как найти «свою» книгу по программированию.

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

К счастью, Stack Exchange (родительская компания Stack Overflow) опубликовала дамп своей базы данных, которым и воспользовался Ветцель. Он запустил

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

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

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

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

Рассказывает

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

​

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


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

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

.

С самого начала было ясно, что нельзя выложить XML-файл размером 48 ГБ в новую базу данных (PostgreSQL), используя популярные методы, такие как myxml := pg_read_file(‘path/to/my_file.xml’), потому что на моем сервере не было 48 ГБ ОЗУ. Поэтому я решил использовать парсер

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

.

Все значения хранились в тегах <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%'";

Все номера

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

я извлёк при помощи регулярного выражения. Я поместил теги 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;
if(maxPop < Number(obj.pop)) {
maxPop = Number(obj.pop);
}
}

for(var i =0; i < max; i ++){
if(i > books.length - 1){
break;
}
obj = books;
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>');

Заключение


Этот сервис весьма полезен для людей, у которых нет времени изучать огромные списки книг о программировании, особенно учитывая их разнонаправленность, а поиск по тегам делает работу с проектом очень быстрой и удобной. Автор обещает опубликовать полный отчет в конце марта.

—

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

.