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

Анализ данных 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 с 96 GiB и 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

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

Значки​

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

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

Оригинальный набор данных доступен в сжатом (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.