Перейти к основному содержимому
Перейти к основному содержимому

Анализ данных Stack Overflow с помощью ClickHouse

Этот набор данных содержит все Posts, Users, Votes, Comments, Badges, PostHistory и PostLinks, которые когда-либо произошли на Stack Overflow.

Пользователи могут либо скачать заранее подготовленные версии данных в формате Parquet, содержащие каждый пост до апреля 2024 года, либо скачать новые данные в формате XML и загрузить их. Stack Overflow периодически предоставляет обновления этих данных — исторически каждые 3 месяца.

Следующая диаграмма показывает схему доступных таблиц с учетом формата Parquet.

Описание схемы этих данных можно найти здесь.

Заранее подготовленные данные​

Мы предоставляем копию этих данных в формате Parquet, актуальную на апрель 2024 года. Хотя для ClickHouse это небольшие данные по количеству строк (60 миллионов постов), этот набор данных содержит значительные объемы текста и большие строки.

Следующие временные показатели относятся к кластеру ClickHouse Cloud с 96 GiБ и 24 vCPU, расположенному в eu-west-2. Набор данных находится в eu-west-3.

Посты​

Посты также доступны по годам, например: https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2020.parquet

Голоса​

Голоса также доступны по годам, например: https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2020.parquet

Комментарии​

Комментарии также доступны по годам, например: https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2020.parquet

Пользователи​

Награды​

PostHistory​

Оригинальный набор данных​

Оригинальный набор данных доступен в сжатом (7zip) формате XML по адресу https://archive.org/download/stackexchange — файлы с префиксом stackoverflow.com*.

Скачивание​

Эти файлы занимают до 35 ГБ и могут занять около 30 минут для загрузки в зависимости от интернет-соединения — сервер загрузки ограничивает скорость до примерно 20 МБ/с.

Конвертация в JSON​

На момент написания ClickHouse не поддерживает XML в качестве формата ввода. Чтобы загрузить данные в ClickHouse, мы сначала конвертируем их в NDJSON.

Чтобы конвертировать XML в JSON, мы рекомендуем инструмент для Linux xq, простой обертки для jq для XML-документов.

Установите xq и jq:

Следующие шаги применимы ко всем указанным выше файлам. Мы используем файл stackoverflow.com-Posts.7z в качестве примера. Измените при необходимости.

Извлеките файл с помощью p7zip. Это создаст один XML-файл - в данном случае Posts.xml.

Файлы сжимаются примерно в 4.5 раз. При 22 ГБ в сжатом виде, файл постов требует около 97 ГБ в разжатом состоянии.

Следующее делит XML-файл на файлы, каждый из которых содержит 10000 строк.

После выполнения вышеприведенного пользователи получат набор файлов, каждый с 10000 строками. Это обеспечивает низкие накладные расходы по памяти для следующей команды (конверсия XML в JSON выполняется в памяти).

Вышеуказанная команда создаст один файл posts.json.

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

Примеры запросов​

Несколько простых вопросов для начала.

Пользователь с наибольшим количеством ответов (активные аккаунты)​

Учетная запись требует UserId.

Самые спорные посты​

Упоминания​

Мы благодарим Stack Overflow за предоставление этих данных по лицензии cc-by-sa 4.0, подтверждая их усилия и оригинальный источник данных на https://archive.org/details/stackexchange.