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

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

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

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

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

Схема Stack Overflow

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

Подготовленные данные​

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

Следующие временные показатели относятся к облачному кластеру ClickHouse с 24 vCPU и размером 96 GiB, расположенному в 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/votes/2020.parquet

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

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

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

Награды​

История постов​

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

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

Скачивание​

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

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

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

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

Установите 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.