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

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

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

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

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

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

Предварительно подготовленные данные​

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

Следующие временные показатели относятся к кластеру 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 мы рекомендуем использовать инструмент xq для linux, простой оберткой 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.