ClickHouse – визуально быстрый и наглядный анализ данных в Tabix. Игорь Стрыхарь06.03.2020 10:53

Предлагаю ознакомиться с расшифровкой доклада 2017 года Игорь Стрыхарь «ClickHouse — визуально быстрый и наглядный анализ данных в Tabix».

Веб-интерфейс для ClickHouse в проекте Tabix.
Основные возможности:

• Работает с ClickHouse напрямую из браузера, без необходимости установки дополнительного ПО;
• Редактор запросов с подсветкой синтаксиса;
• Автодополнение команд;
• Инструменты графического анализа выполнения запросов;
• Цветовые схемы на выбор.

Я — технический директор СМИ2. Мы — новостной обменный агрегатор новостей. Мы храним много данных, которые получаем от наших партнеров и регистрируем их в ClickHouse — порядка 30 000 запросов в секунду.

Это такие данные, как:

• Клики по новостям.
• Показы новостей в агрегаторе.
• Показы баннеров в нашей сети.
• И регистрируем события с нашего собственного счетчика, который наподобие Яндекс.Метрики. Это наша собственная микроаналитика.

У нас была очень бурная жизнь до ClickHouse. Мы очень мучились, пытаясь складировать эти данные куда-то и как-то их проанализировать.

Жизнь до ClickHouse — infiniDB

Первое, что у нас было, это infiniDB. Она у нас прожила 4 года. Мы ее запустили с трудом.

• Она не поддерживает кластеризацию или шардирование. Никакие такие умные вещи она из коробки по умолчанию не завелись.
• У нее сложности с загрузкой данных. Только специфичная консольная утилита, которая могла только грузить CSV-файлы и только как-то очень непонятно.
• База данных одна поточная. Можно было либо писать, либо читать. Но она позволяла обрабатывать большой объем данных.
• И еще у нее был интересный костыль. Каждую ночь нужно было ребутать сервер, иначе он не работал.

Проработала она у нас до конца 2016 года, когда мы полностью перешли на ClickHouse.

Жизнь до ClickHouse — Cassandra

Т. к. infiniDB была однопоточной, мы решили, что нам нужна какая-то многопоточная база, в которой мы можем писать очень много потоков одновременно.

Мы попробовали много интересных вещей. Потом решили попробовать Cassandra. С Cassandra у нас все было прекрасно. 10 000 запросов в секунду на ставку. 2 000 запросов где-то на чтение.

Но у нее тоже были свои интересности. Раз в месяц или раза в два месяца у нее случалась рассинхронизация базы данных. И приходилось просыпаться, и бежать чинить Cassandra. Рестартовали сервера по очереди. И все становилось гладко и красиво.

Жизнь до ClickHouse — Druid

Потом мы поняли, что нам нужно еще больше писать данных. В 2016 году мы начали смотреть Druid.

Druid — это открытая база, написанная на Java. Очень специфичная. И подходила под clickstream, когда нам нужно хранить какой-то поток событий и потом производить по ним агрегацию или делать аналитические отчеты.

Druid имел версию 0.9.X.

Сама по себе база данных разворачивается очень тяжело. Это сложность инфраструктуры. Чтобы ее развернуть нужно было поставить много-много железа. И каждое железо отвечало за свою отдельную роль.

Чтобы загрузить данные в нее, нужно было применять какое-то шаманство. Есть OpenSource проект — Tranquility, который в потоке терял у нас данные. Когда мы в него загружали данные, он их терял.

Но как-то мы его начали внедрять. Мы, как ежики, которые кололись, но продолжали есть кактус, начали его внедрять. Где-то месяц нам понадобился, чтобы подготовить всю инфраструктуру под него. Т. е. заказать сервера, настроить роли, произвести полную автоматизацию разворачивания. Т. е. в случае падения кластера, чтобы автоматически разворачивался второй кластер.

Но тут случилось чудо. Я был в отпуске, и мои коллеги мне присылают ссылку на habr, где говорится о том, что Яндекс решил открыть ClickHouse. Я говорю, что давайте попробуем.

И буквально за 2 дня развернули тестовый кластер ClickHouse. Начали загружать в него данные. По сравнению с infiniDB — это элементарно, по сравнению с Druid — это элементарно. По сравнению с Cassandra тоже элементарно. Потому что если из php грузить в Cassandra данные, то это не элементарно.

Что мы получили? Перфоманс в скорости. Перфоманс в хранении данных. Т. е. используется гораздо меньше дискового пространства. ClickHouse — быстрый, он очень быстрый по сравнению с другими продуктами.

На момент запуска, когда Яндекс опубликовал ClickHouse в OpenSource, был только консольный клиент. Мы в нашей компании СМИ2 решили попробовать сделать нативный клиент под web, чтобы можно было из браузера открыть страницу, написать запрос и получить результат, потому что мы много начали писать запросов. Писать в консоли тяжело. И мы сделали первую нашу версию.

И где-то ближе к зиме прошлого года начали появляться сторонние инструменты для работы с ClickHouse. Это такие инструменты, как:

Я рассмотрю некоторые из этих инструментов, т. е. те, с которыми я работал.

Хороший инструмент, но для Druid. Когда внедряли Druid, я щупал SuperSet. Мне он понравился. Для Druid он работает очень быстро.

Для ClickHouse он не подходит. Т. е. он подходит, он запускается, но готов обрабатывать только элементарные запросы типа: SELECT event, GROUP BY event. Он не поддерживает более сложный синтаксис ClickHouse.

Следующий инструмент — Apache Zeppelin. Это хорошая и интересная вещь. Работает. Он поддерживает блокноты, дашборды, поддерживает переменные. Я знаю, что кто-то из сообщества ClickHouse его использует.

Но нет поддержки синтаксиса ClickHouse, т. е. придется запросы писать либо в консоли, либо еще где-то. Далее проверять, что все это работает. Это просто неудобно. Но поддержка дашбординга у него хорошая.

Следующий инструмент — это Redash.IO. Redash хостится в интернете. Т. е. в отличии от предыдущих инструментов его не нужно устанавливать. И это такой дашбординг с возможностью консолидации данных из разных DataSource. Т. е. вы можете выгрузить из ClickHouse, из MySQL, из PostgreSQL и из прочих баз данных.

Буквально месяц назад (в марте 2017 года) появилась поддержка в Grafana. Когда вы строите отчеты в Grafana, допустим, по состоянию вашего железа или по каким-то метрикам, то теперь можно строить такой же график или какую-то панельку из данных из ClickHouse напрямую. Это очень удобно, и мы это используем у себя. Это позволяет найти аномалии. Т. е. если что-то происходит и какое-то железо падает или напрягается, то можно посмотреть причину, если эти данные успели попасть в ClickHouse.

Мне было очень неудобно писать в этих инструментах или в консоли. И я решил доработать наш первый интерфейс. И подсмотрел идею у EventSQL, SeperSet, Zeppelin.

Что хотелось? Хотелось получить графики, улучшенный редактор, реализовать поддержку словарей подсказок. Потому что у ClickHouse есть отличная функция — это словари. Но со словарями тяжело работать, потому что нужно помнить формат хранимых значений, т. е. это число или строка и т. д. А так как мы часто используем словари в их разных вариациях, то достаточно тяжело было писать запросы.

Прошло 3 месяца от релиза первой нашей версии. Я наделал порядка 330 коммитов в закрытую ветку и получился Tabix.

В отличии от предыдущей версии, которая называлась ClickHouse-Frontend, я решил переименовать его в простое название. И получился Tabix.

Что появилось?

Рисует графики. Поддерживает SQL синтаксис ClickHouse. Подсказывает по функциям и много чего интересного умеет.

Вот так выглядит общая схема Tabix. Слева представлено дерево. По центру — это редактор запроса. И снизу — это результат этого запроса.

Дальше покажу, как работает редактор запроса.

Тут автоматически автокомплит сработал на таблице и подсказывает, соответственно, автокомплит по полям. И подсказки по функциям. Если нажать ctrl ввод, то запрос выполнится или свалится с ошибкой. Самый простой запрос отправляется в Tabix и получается результат, т. е. быстро можно работать с ClickHouse.

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

Это, казалось бы, простая вещь, но в ClickHouse она очень интересно помогает. Из-за того, что ClickHouse поддерживает только вложенные join, запрос растет вниз и в ширину достаточно сильно. И когда скобочка открывается, и идет какое-то длинное выражение, то такая достаточно простая вещь, как сворачивание запроса, помогает легче работать с самим запросом. Потому что когда запрос в 200–300 строк и в ширину он очень огромный, то очень спасает сворачивание запроса и после найти какое-то место или как-то его локализовать.

Дерево объектов, мультизапросы и вкладки (Видео 13:46 https://youtu.be/w1-XsL3nbRg? t=826)

Следующими покажу про дерево, вкладки. Слева — это дерево, сверху можно создавать несколько вкладок. Вкладки — это как workspace. Вы можете создать несколько вкладок и каждую по свойму назвать. Это как минисистема для построения отчета.

Вкладки автоматически сохраняются. Если вы перезагрузите браузер или закроете, или откроете Tabix, то все это сохранится.

Hotkey — удобно (Видео 14:39 https://youtu.be/w1-XsL3nbRg? t=879)

Есть hotkey и их достаточно много. Я некоторые вывел из них здесь для примера. Это переключение вкладки, исполнить запрос или исполнить несколько запросов.

Покажу, как работать с результатом. Отправляем запрос. Здесь я рисую sin, cos и tg. Можно подсветить результат, т. е. типовую карту на колонку отрисовать. Можно подсветить положительные или отрицательные значения. Или просто раскрасить какой-то конкретный элемент таблицы. Это удобно, когда таблица огромная и нужно глазами найти какую-то аномалию. Когда я искал аномалии, я подсвечивал какие-то строчки, какие-то элементы зеленым или красным цветом.

Есть там много интересных вещей. Например, как скопировать в Redmine Markdown. Если нужно откопироать куда-то результат, то это очень удобно. Можно просто выделить область, сказать «Copy to Redmine» и он откопирует в Redmine Markdown или создаст запрос Where.

Дальше — это оптимизация запросов. Я один раз забыл указать поле «date». И у меня запрос в ClickHouse обрабатывался не очень-очень быстро, но быстро, т. е. менее секунды. Когда я увидел, сколько он строк пробегает, мне стало страшно. Мы за сутки столько строк не пишем в эту таблицу. Я начал анализировать запрос и увидел, что пропустил в одном месте дату. Т. е. забыл указать, что мне данные нужны не по всей таблице, а нужно за какой-то конкретный период.

В Tabix есть вкладка «Stats», в которой хранятся вся история отправленных запросов, т. е. там можно посмотреть, сколько строк было прочитано этим запросом и сколько времени он выполнялся. Это позволяет оптимизировать.

Над результатом запроса можно строить сводную таблицу. Вы отправили запрос в ClickHouse, получили какие-то данные. И дальше эти данные можно подергать мышкой и построить какую-то сводную таблицу.

Следующая интересная вещь — это построение графиков. Допустим, у нас вот такой запрос: на sin, cos от 0 до 299. И чтобы его отрисовать нужно выбрать вкладку «Draw» и вы получите график с вашими sin и cos.

Можно разбить это на разные оси, т. е. можно отрисовать сразу два графика рядом. Написать одну команду и вторую команду.

Можно отрисовать гистограммы.

Можно разбить это на матрицу графиков.

Можно построить тепловую карту.

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

Следующий — это Treemap.

Sankeys — интересный график. Он либо Streamgrahps, либо River. Но я его называю River. Он тоже позволяет искать какие-то аномалии. Он очень удобный. Рекомендую использовать его для поиска.

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

Но проблема с этой картой в том, что она рисует только карту мира, нет детализации.

В последствии я добавил Google map. Если вы храните широту, долготу, то можно отрисовать результат на Google map, но без поддержки самолетиков.

Основные функции работы с результатом, с запросом в Tabix мы обговорили.

Следующий — это анализ вашего сервера ClickHouse. Есть отдельная вкладка «Метрики», в которых можно посмотреть размер хранимых данных по каждой колонке. На скриншоте показано, что вот это поле «referrer» у нас занимает что-то порядка 730 Gb. Если мы откажемся от этого поля, то мы сэкономим три шарда по 700 GB, т. е. порядка 2 TB, которые нам не нужны.

Также у нас есть поле «request_id», которое мы храним в строке. Но если мы начнем его хранить в числовом виде, то сожмет это поле колоссально.

Также здесь показана конфигурация сервера и список нод вашего кластера.

Следующая вкладка — это метрики. Они забираются в realtime c ClickHouse и просто позволяют проанализировать состояние сервера и понять, что с ним происходит. Это не замена полноценной Grafana. Это нужно для того, чтобы быстро проанализировать.

Следующая вкладка — это процессы. По ним можно понять, что происходит на сервере. Понять, что там творится. У меня был запрос, который отжирал на чтение 200 GB каждый раз. Я это увидел благодаря этому интерфейсу. Отловил его и подправил. И получилось порядка 30 GB, т. е. перфоманс в разы.

Спасибо! И это в OpenSource

Я закончил. И, кстати, это в OpenSource, это бесплатно и даже не надо скачивать. Открывайте в браузере и все будет работать.

Вопросы

Игорь, что дальше? Куда ты будешь развивать этот инструмент?

Дальше появится дашбординг, т. е., возможно, появится дашбординг. Интеграция с другими базами данных. Это я сделал, но еще не опубликовал в OpenSource. Это MySQL и, возможно, PostgreSQL.Т. е. можно будет из Tabix отправлять запросы не только в ClickHouse, но и в другие инструменты.

Видно, что сделан огромный объем работы. Получилась достаточно полноценная идея. В браузере сделано, видимо, для того, чтобы исключить костыли на всяких осях и быстренько накидать все это дело. Я так слышал, что вы на php работаете, поэтому проще всего накидать в браузере и везде будет работать. К этому вопросов нет. Вопрос такой. Там сделано действительно очень многое. Сколько человек над этим работало? И сколько времени все это заняло? Потому что инструменты под себя обычно не обладают таким большим функционалом.

С лета по осень работал один человек из нашей команды. Это была первая версия. Потом 330 коммитов я один делал. То, что вы видите, мы с коллегой пополам это делали. За 3 месяца из первой самой версии до последней я один в большей части сделал. Но я очень плохо знаю Javascript. Это был мой единственный и, надеюсь, последний мой проект на Javascript, с которым я работал. Он мне достался, я посмотрел — о, ужас. Но мне очень хотелось доделать продукт и вот что получилось.

Спасибо большое за доклад! Это отличный инструмент. С Tableau сравнивали?

Спасибо. Поэтому Tabix и назвал, потому что первые буквы совпадают.

Потому что конкурируете?

Будет много инвестиций, будем конкурировать.

Как предложите продать внутренним аналитикам, что этот инструмент полностью заменит *Tableau*? Какие будут аргументы?

Нативно работает с ClickHouse. Я пробовал Tableau, но там нельзя писать поддержку словарей и тому подобнее. Я знаю, как работают с Tabix люди. Они пишут запрос, выгружают в CSV и загружают в BI. И уже там что-то делают. Но я себе с трудом представляю, как они это делают, потому что — это графический инструмент. Он может выгрузить 5 000 строк, максимум 6 000 строк, но не больше, иначе браузер не выдержит.

Т. е. есть какие-то серьезные ограничения по объему данных, да?

Да. Я не представлю себе, что вы захотите 10 000 строк себе в таблицу выгрузить на экран браузера. Зачем?

Подразумевается, что это интерфейс для быстренького просмотра данных? Покрутить немного, повертеть?

Да, быстро посмотреть, как оно работает и просто построить график сводный. А дальше отдавать куда-то. У нас это своя собственная репортинг-система, откуда я просто беру вот этот запрос. Рисую в Tabix и отправляю в репортинг наш.

И еще вопрос. Когортный анализ?

Если будут пожелания, добавим.

Когда только начинали пользоваться ClickHouse, сколько заняло по времени внедрение ClickHouse и доведение до production-состояния?

Как я и сказал, мы внедрили тестовый кластер за очень сжатые сроки. За два дня мы его развернули. И еще пару недель мы его тестировали. И до production мы дошли месяца за 3. Но у нас был свой собственный ETL, т. е. инструмент для записи данных. И он у нас писал во все, что можно. Он умеет писать в MongoDB, Cassandra, MySQL. Научить его писать в ClickHouse было просто. У нас была готовая инфраструктура для быстрого внедрения. Месяца за 3 мы первый компонент начали выкидывать. За 6 месяцев мы полностью отказались от всего остального. У нас остался один ClickHouse.

Игорь, спасибо большое за доклад. Мне очень понравился функционал построения путей по картам. Планируется ли интеграция с Яндекс.Картами и в частности с кастомными Яндекс.Картами?

Я попробовал интегрироваться вместо Google map, но я не нашел темную тему на Яндекс.Картах. Я не рассказал один кусочек. Отмотаю, чтобы дополнить.

Слайд — Google map. Вот там есть команда «DRAW_GMAPS», которая рисует карту. Есть команда «DRAW_YMAPS», т. е. может отрисовать Яндекс.Карту. А фактически под этой командой находится Javascript, т. е. данные, которые вы получается из ClickHouse можно передать в Javascript, который вы здесь напишите. И у вас на выходе есть область, куда она должна отрисоваться. Можно любой график отрисовать, т. е. любой график, карту, можете собственный свой компонент отрисовать. У меня до этого была другая библиотека для отрисовки самих графиков.

Т. е. там есть инструмент для кастомизации функционала отображения?

Любые. Можно взять и перекрасить эти точки, сделав их не красными, а синими, зелеными.

Спасибо за доклад! У вас был слайд, на котором были представлены альтернативные инструменты для запросов ClickHouse для построения дашбордов, аналитических отчетов. Я так понял, что на момент, когда вы начали работать с ClickHouse, для этих инструментов не были написаны адаптеры. И мне интересно, почему вы решили делать свой собственный инструмент, вместо того, чтобы написать адаптер для какого-то готового инструмента? Я думаю, что дотюнить тестовый редактор — это быстро. Почему вы решили делать столько работы?

Тут интересный такой момент — дело в том, что я технический директор, а не дата-сайентист. К тому моменту, как мы начали внедрять Druid, у меня в roadmap было где-то 50% задач — давайте посчитаем вот это или давайте посчитаем вот это, или вот это проанализируем. И получилось так, что мы внедрили ClickHouse. И начал все быстро строить, считать, закрыл быстро свой roadmap. И к тому моменту я понял, что мне не хватает знаний по Data Science, по визуализации данных. Tabix — это своего рода мое домашнее задание по изучению визуализации данных. Я смотрел, как дополнить Zeppelin. У меня есть легкая не любовь к его программированию. Redash я посмотрел, как дополнить, но мне хватало там нормального редактора. И SuperSet тоже написан на том языке, который я не очень люблю. И поэтому я решил велосипедить, и вот, что получилось.

Игорь, Pull request вы принимаете?

Да.

Спасибо большое за доклад! И два вопроса. Первый — вы не очень лестно отзываетесь о Javascript. Вы писали на голом Javascript или все-таки это какой-то фреймворк?*

Лучше на голом Javascript.

Так какой фреймворк?

Angular.

Понятно. И второй вопрос. Не рассматривали ли вы R и *Shiny**?*

Рассматривал. Игрался.

Можно же было тоже просто написать адаптер.

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

*Нет, по поводу визуализации, там же есть.

Ты говоришь, что есть такая штука и она отрисует тебе график. Я открыл книжку по визуализации данных. И подумал: «Давай-ка я попробую эти данные отвизуализировать. Сам напишу, чтобы он отстроил данные». И я начал лучше разбираться в технологии подачи данных. А если бы я взял готовый компонент, я лично хуже бы научился бы им пользоваться, т. е. именно визуализацией. Но так — да, R мне понравилась, но я еще не читал книжку «R для чайников».

Спасибо!

Простой вопрос. Какие-то есть возможности быстро выгрузить табличку, график?

Можно выгрузить в CSV, в Excel.

Не данные, а готовую табличку, готовый график? Например, чтобы показать начальству.

Есть кнопочка «Выгрузить» и есть кнопочка «Выгрузить график в png, в jpg».

Спасибо!

P.S. Мини-инструкция по установке tabix

• скачать последний релиз https://github.com/smi2/tabix.ui/releases
• распаковать, скопировать директорию build в nginx root_path
• Настройить nginx

© Habrahabr.ru