Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
За несколько лет Whoosh в несколько раз вырос по числу самокатов, пользователей и локаций, а данных по ним накопилось на 30 терабайт. Прежней архитектуры уже не хватало для работы. К тому же платить за I/O (input/output)-операции на Aurora (PostgreSQL) выходило дорого (тогда еще не было I/O‑optimized версии, однако с ее появлением, актуальность не исчезла). Другое дело — Redshift: расходы постоянны (n$/час), а работает он быстрее, благодаря колоночному формату хранения данных. В этом году мы переехали с одного хранилища на базе PostgreSQL — того, где вся отчётность для бизнеса и модели dbt — на рельсы Data Lake в AWS.
Меня зовут Никита Зеленский, я главный по данным в Whoosh. Эту статью я написал вместе с другими участниками переезда — Пашей Сивохиным, ГИС-аналитиком, и Костей Малыхиным, руководителем группы анализа данных. Надеюсь, наш опыт будет полезен всем, кому предстоит миграция данных, особенно если вы работаете с геоаналитикой.
WHOOSH
Архитектура до переезда
До переезда наша архитектура состояла из двух основных частей — Aurora Prod и Aurora DWH, обе на базе PostgreSQL. Все данные с наших самокатов и приложения поступали сначала в Aurora Prod — там у нас протекают все процессы, связанные с клиентами и устройствами. Затем оттуда по логической репликации они переносились в Aurora DWH, которая была основной базой для разработчиков, дата-инженеров, аналитиков, и здесь же находился динамический тариф и BI-инструменты — Grafana и геосервис Martin (о том, как мы работаем со всем этим — чуть дальше).
Это хорошая архитектура для начала бизнеса. Но когда данных становится очень много и Aurora раздувается, то вылезают следующие проблемы.
Первое. BI-отчёты начинают деградировать по скорости. На то, чтобы обновить карту города или страны и сделать обыкновенный отчёт, требовалось 7 минут ожидания на рельсах PostgreSQL (сейчас — секунд 10 на рельсах Редшифта).
Второе. Логическая репликация — жёсткая сцепка между двумя базами данных. Что это означает? Если какой-то процесс начинает лочить какую-нибудь таблицу в Aurora DWH, то логическая репликация этой таблицы приостанавливается, а на Aurora Prod начинают копиться транзакционые логи и dead rows (которые Автовакуум не будет вычищать, пока не закончится транзакция). Не самая удачная идея, когда у нас на Aurora Prod завязан весь бизнес, ведь процессору придется сканировать все больше строк, которые уже не нужны.
Третье. И самое главное. Амазон биллит за количество I/O-операций. Если наш продуктовый аналитик напишет неоптимизированный запрос, который приведёт к большому числу I/O-операций и full-scan исходных таблиц, то компания за эту аналитику спасибо не скажет — запрос получится дорогим.
Наша цель состояла в том, чтобы отказаться от Aurora DWH. В этой точке начинается приключение на 5,5 месяцев.
Как мы представляли наш переезд
Всю работу мы ведем в dbt — это фреймворк с открытым исходным кодом для выполнения, тестирования и документирования SQL-запросов. В нём много приятных вещей: от автоматизации sql-моделей до настройки автоматического накопления историчности таблиц по SCD-2 и не только. Переезд означал, что мы должны были разнести все модели dbt на две составные части:
Облако S3 (Simple Storage System) — фактически Data Lake
Redshift — колоночная база данных облака AWS
Предполагалось, что мы сделаем "водораздел" между дата-инженерами, которые будут работать с источниками данных в S3 с помощью Athena и Glue-каталогов, и аналитиками, — они получат готовые агрегаты в Редшифте. Также было желание, чтобы Grafana и Martin работали на Редшифте.
Для миграции данных в режиме реального времени из Aurora Prod в Redshift мы выбрали амазоновскую DMS (Database Migration Service), про неё мы обстоятельно расскажем дальше.
И у нас было дополнительное условие. Мы хотели переехать, не прибегая к помощи команды разработки. У ребят хватает своих задач, к тому же нам было интересно прокачать компетенции работы с AWS и показать, что мы можем справиться сами.
Казалось бы, что по этому плану могло пойти не так?
Особенности гео-аналитических расчетов
Редшифт отлично подходит для DWH-хранилища и работы с длинными аналитическими запросами, которые зачастую требуют множественной агрегации, соединения таблиц. К тому же он быстрее и отлично масштабируется. Но есть одно но.
Мы в Whoosh сильно завязаны на геоданных, поэтому нам важно иметь инструмент, способный грамотно работать с ними. Спойлер: мы так и не перенесли часть процессов по геоаналитике в Редшифт, сейчас поддерживаем их также и в PostgreSQL.
Дело в том, что на PostgreSQL завязаны два важных продукта:
динамический тариф — он напрямую влияет на ценообразование для конечного пользователя.
Martin — тайловая система с открытым исходным кодом, которую мы переделали под себя для визуализации пространственных данных.
Перевезти Martin и динамический тариф трудно или невозможно, особенно, не затрагивая ресурс команды разработки.
Во-первых, для расчёта динамического тарифа нужен набор инструментов для работы с пространственными данными, которых нет в Редшифте.
Во-вторых, если даже с инструментами можно что-то придумать, то никуда не денется бутылочное горлышко Редшифта — работа с короткими запросами. А командам внутри Whoosh нужно иметь доступ к данным в реальном времени и для этого — формировать много коротких запросов в базу. Сотни запросов в секунду будут губительны для Редшифта. PostgreSQL гораздо больше подходит под такие задачи.
Что касается инструментов для работы с пространственными данными. Если вы не сталкивались с ними, то стоит уточнить, что на первый взгляд — это обычные данные, которые можно также просматривать в табличном виде, с набором полей и характеристик. Но с дополнительным критерием, который и делает их пространственными, — наличие геопривязки. Например, это точка, которая может быть отображена как парковка, самокаты, велосипеды. Линия — треки поездок. Полигоны — медленные зоны, где самокат снижает свою скорость, или зоны запрета для поездок.
Расчёты по геоданным мы делаем с помощью наших ETL-процессов на Python и функций в PostGIS, а визуализируем всё в Мартине. Martin— опенсорс-инструмент для создания векторных тайлов с помощью PostGIS (это расширение для PostgreSQL). Первый нюанс — PostGIS работает только с PostgreSQL.
Стоит отметить, что PostGIS — лидер по числу пространственных функций. Позволяет работать с пространственными данными под любым углом. В Редшифте в 3,5 раза меньше функций для работы с этим типом данных. А некоторых важных инструментов в нём совсем нет, например, для починки проблемной топологии — когда нужно преобразовать невалидную геометрию в валидную.
Или другой пример — построение регулярной или нерегулярной сетки. PostGIS позволяет создавать наборы регулярной сетки, например, гексагоны. Redshift — нет. А это отличный инструмент для аналитики. На картинке ниже вы видите поездки в Санкт-Петербурге. С помощью таких данных можно определять хот-споты в городе, понимать где больше всего ездит людей, чтобы подстраивать инфраструктуру компании под потоки пользователей: устанавливать парковки, наращивать флот в определённых локациях и давать советы операционной команде.
Попытки решить эти проблемы
Если вы следите за выступлениями и новостями разработчиков AWS, то слышали про обходной способ — использование Athena и UDF вместе с AWS Lambda, благодаря которым можно стучаться в uber H3. О пространственном индексе H3 можно почитать здесь. H3 — это и есть гексагоны, просто они по-другому сконфигурированы в пространстве, что позволяет заполнить всю поверхность земли.
Мы решили что сможет решить проблему этим способом. Но на простых юзкейсах столкнулись с массой проблем.
Aurora PostgreSQL позволяет объединить геометрию в поле. Это здорово помогает, когда делаешь аналитику, связанную с доступностью. Например, мы хотим посмотреть, насколько наши парковки охватывают точки притяжения — ТРЦ. Мы строим изохроны доступности от каждой парковки и объединяем их на уровне всего города. На выходе получается один большой полигон доступности — тут мы смотрим как много ТРЦ попадает в него.
В Redshift нельзя объединять геометрию, находящуюся в одной колонке. Надо либо расширять таблицу (записывать геометрию в каждой новой колонке и уже так объединять её), либо использовать похожие функции. Например, функция st_collect запихивает всю геометрию в одну строку — так происходит наложение данных и, соответственно, задваивание чисел.
Тут - про возможные форматы гео-данных. Будем ссылаться потом на них далее по тексту
Геометрию можно представлять в нескольких форматах. Самый распространенный — Geometry/Geography. Первый — растягивание нашего земного шара на 2D-пространство, второй — проекция земного шара на эту сферу.
В этом случае мы используем самую простую систему координат — ESPG:4326 или WGS 84 с количеством знаков после запятой в долготе или широте, равной шести, чтобы получить точность на уровне одного человека. Так геометрия записывается, как некий мультиполигон.
Есть текстовый формат WKT (Well-Known Text), который по стилю записи похож на Geometry/Geography, но в формате varchar. Мы получаем запись о том, что есть мультиполигон и в нём такой-то набор координат. Тоже понятный способ, но стоит помнить, что это просто буквы в ячейке.
Следующий формат — Well-KnownBinary или EWKB (Extended Well-Known Binary). Здесь можно уместить больше геометрии, занимая меньшее количество байтов. EWKB — формат, где хранится система координат, тип геометрии, долгота и широта объекта.
Это классный формат, но в него довольно сложно переводить данные из текста. Для таких задач есть Hex-encoded EWKB, который использует чуть больше символов, но даже вместительнее, потому что напоминает геохеш. Тут мы записываем не определённые вершины геометрии, а гексагончики, где лежат наши данные.
У Redshift в работе с гео есть несколько особенностей. Например, в ячейку влезают данные размером около миллиона байтов, если это геометрия, и всего 65 тысяч байтов (2^16), если это текст. И многие инструменты для переноса данных, в том числе пространственных, например, логическая репликация и DMS, не могут напрямую переносить геометрию — они сначала переводят её в текст.
Здесь мы поняли, что 10% сырых полигональных данных, которые мы получаем с прода от разработки, не поместятся в одну ячейку, Redshift будет её обрезать. Аналитику на таком уровне делать нельзя, особенно геоаналитику — те же зоны доступности (зоны, где самокату разрешено ездить, а где должно быть ограничение) просто не влезали бы.
Какие были варианты? Мы могли попробовать переносить геометрию без трансформации в текст. Или попробовать различные циклы, которые упрощали бы нашу геометрию на основе её веса, чтобы она могла влезть в хранилище. Ни в первом, ни во втором варианте мы бы не справились без разработки.
И мы нашли третий вариант. Из Aurora Prod мы льём данные в S3 в формате Hex-encoded EWKB. Стоит заметить, что Аврора, и PostgreSQL, и S3 с Athena, и Redshift немного по-разному записывают Hex-encoded EWKB, и там неоднородные форматы. Поэтому мы написали dbt-макрос, который каждый раз немножко изменял эту запись и удалял ненужные части, портящие всю картину потом.
Далее из S3 мы забираем данные в Redshift, переводя их в WKB, и уже дальше храним все данные в этом формате. А для аналитиков переводим геометрию в Geometry/Geography через простую функцию ST_GeomFromEWKB.
Да, способ достаточно нетривиальный и задействует много технологий, но сработал как надо — и мы сейчас активно его используем.
Что ещё нам дают несколько вариантов работы с данными. На картинках вы видите, что с 2020-го по 2021 год у нас значительно выросли поездки в Санкт-Петербурге.
Проблема была в том, что с ростом количества поездок (и, соответственно, данных) и несмотря на оптимизацию (пространственные индексы), подсчёт в Авроре занимал несколько часов. Из-за этого мы тратили много денег и иногда затрагивали успешное выполнение других процессов. Здесь нам на помощь пришли две вещи:
— партицирование — иерархическая организация на основе метаданных. По сути это сохранение данных в отдельных файликах. Мы нарезаем все наши поездки по дням — это отдельные файлы, а потом на их основе строим аналитику.
— dbt utils = python + скорость Athena. Очень удобно, что у нас именно dbt и язык Jinja, на котором к нему можно обращаться. Даже базовые dbt-утилиты позволяют использовать питоновский код. Так мы можем делать простые циклы, где используем айдишник города и строим все поездки по нему отдельно, не перегружая при этом Athena и не вылезая за её ограничения.
Почему дата-инженеру важно помнить школьную математику
Мы часто отвечаем бизнесу на вопрос, к какой парковке относится самокат. Представим, что у нас несколько парковок рядом. Одна находится через дорогу. Нам нужно точно понимать, где самокат. Эта привязка влияет не только на опыт пользователя, но и на работу сервисной команды, которая производит ребаланс, заряжает, забирает самокаты на ремонт. Представим, что сотрудник сервиса едет на машине, ему важно точно понимать, где устройство, чтобы не попасть в ситуацию, когда между ним и парковкой — дорога с двойной сплошной и надо делать крюк.
В Aurora мы бы использовали функцию сross join lateral. С помощью cross join мы получаем это произведение, перемножение строк, а функция lateral позволяет использовать результаты подзапроса в дальнейших вычислениях. Что это означает? Если мы нашли парковку для какого-то самоката, то мы не будем перебирать их дальше, а остановимся на этом результате. Соответственно, мы оптимизируем работу сross join-а. В Redshift такой функции нет.
Что же делать? Мы подумали: широта и долгота — это же ведь десятичные числа. А что, если мы домножим до тысячи, округлим до целого и просуммируем полученные значения по широте и долготе? Таким образом, мы получим некий псевдоайдишник самоката и псевдоайдишник парковки. Дальше нам нужен inner join.
Здесь надо понимать, что возникает ошибка возможного округления (как в большую, так и в меньшую сторону). Нужно сделать пять таких джойнов, а потом соединить результаты через Union All. Да, на первый взгляд, выглядит, как костыль. Но, сравнив эти подходы между собой (cross join lateral и наш «pseudo-id»), мы увидели, что скорость выполнения запросов выросла на порядок. А когда сравнили с питоновской библиотекой — эталоном в нашем понимании, — то выяснилось, что у неё под капотом ровно такой же подход. Мы дошли до него самостоятельно, нам показалось любопытным то, что школьной математикой и базовым пониманием того, как работают джойны, можно решить такую задачку для бизнеса.
Вернёмся к примеру. Мы видим, что псевдо-айдишник нашего самоката — 84082, парковки №1 — 84080, для парковки №2 — 84082. Из-за корректировки ошибки округления парковка №1 тоже попадёт в конечную выборку списка парковок-самокатов (хотя фактическое расстояние между двумя точками больше километра). А дальше по каждой такой паре будет рассчитано расстояние стандартными способами. Нам остается для каждой такой пары самокат-парковка найти такую парковку, где расстояние минимально. Так мы скажем, что самокат находится на парковке №2.
Как устроена обработка запросов в Redshift
Ещё немного расскажу про особенности работы с Редшифтом. Напомню, что у него есть проблема с короткими запросами. Разберёмся, как в нём происходит обработка запроса.
Первый шаг — парсинг. Запрос распарсивается на то, чтобы создать логическое дерево запроса. Второй шаг — логическая структура запроса для машины поступает в оптимизатор. Оптимизатор состоит из двух частей — это третьи и четвёртые шаги. Логическая трансформация, которая разбивает один запрос на несколько маленьких. И физическое планирование. Здесь определяется, какие ресурсы нужны, сколько памяти задействовать, где находятся нужные таблицы. Когда мы пишем EXPLAIN, то получаем на выходе план запроса после оптимизатора.
Пятый шаг: план запроса отправляется на бэкенд и там происходит компиляция кода. Получаем инструкцию, как выполнить запрос на наших узлах кластера, которая включает три части: шаг, сегменты, стрим. Шаг — это атомарная единица операции для создания запроса. Из шагов формируются сегменты. Из сегментов состоит стрим. Шестой шаг: код исполняется на узлах кластера. И результат возвращается на мастер-ноду, где происходит финальная агрегация результата, сортировка, если необходимо.
Обработка запроса с первого по пятый шаг затратна по времени. В сумме — больше 5 секунд. Поэтому Redshift старается лишний раз не проделывать работу до пятого шага. Если вы дадите ему одинаковый запрос дважды, то во второй раз он сразу отправит его на шестой пункт. А если у вас ещё и исходные данные не поменялись, то он и шестой шаг делать не станет, а возьмёт их из горячего кэша.
В чем тут проблема? Если идут одинаковые запросы, но с разной переменной, например: «Сделай селект чего-либо по городу Х», а потом город меняется, то с точки зрения Редшифта — это уже другой запрос, поэтому он проведёт его с первого по пятый шаг. У нас 52 локации, и если все операционные управляющие захотят посмотреть информацию по городам, Редшифт им спасибо не скажет. В моменте это не сильно влияет на производительность, но мы понимаем, что может возникнуть очередь из запросов.
Если вы захотите сделать BI или Grafana на базе Редшифта, то лучше не пускать в него абы какого пользователя, который будет обращаться к нему через прямые запросы. Можно через оркестратор сделать обновление таблицы и выдачу результата, который уже не будет меняться, а BI или Grafana будут гораздо быстрее переходить к конечному результату.
DMS для миграции данных. Ожидание...
DMS, по сути, виртуальная машина, администрируемая Амазоном, которая заточена под одну задачу: перенести данные из одного источника в другой. Вообще-то классный инструмент, преимуществ у неё больше, чем недостатков, поэтому мы до сих пор ей пользуемся. Но, наверное, в будущем поменяем.
Что понравилось:
сильно облегчила нам жизнь по переносу таблиц в режиме «онлайн + CDC».
Не затрагивает мощности исходной базы (за исключением времени, когда требуется Full Load при включенном CDC)
Простой пользовательский путь - настраивается все за пару кликов мышкой
Как это работает? Представим, что у нас есть временной ряд наполнения wal-логов (wal-логи и транзакционные логи — одно и то же) на исходной базе данных. DMS говорит: «Ага, ты хочешь таблицу из этой базы данных. Давай-ка я сделаю чекпоинт, а потом зафиксируюсь, чтобы выбирать информацию о том, что изменилось с этой таблицей». Мы, грубо говоря, фиксируемся, откуда потом будем менять информацию для CDC. Если CDC не требуется, чекпоинты тоже не нужны. А под капотом DMS вычитывает и конвертирует бинарные логи в обычные SQL-команды, которые затем применяет в целевой базе данных.
Пока у вас перекачивается вся таблица, а сначала нужно скачать всё, а потом делать с ней CDC, растёт число транзакционных логов на исходной базе данных. Этого, к сожалению, не избежать (нужно будет по ним пробежаться, найти то, что соответствует реплицируемой таблице и перенести в целевую базу данных). Да, это сначала аффектит исходную базу данных, но когда включается фаза CDC, wal-логи достаточно оперативно сходят на нет, и она почти не замечает присутствие DMS.
Казалось бы, инструмент надёжный, как часы.
.. и реальность
DMS врёт про типы данных. Если она не видит подходящего типа данных в целевом хранилище для типа данных, полученном с источника, то просто конвертирует их в текст. Вспоминаем, что в ячейку Redshift не влезает значений больше 2^16 символов текста.
Наша исходная база данных — OLTP, если внутри запускается процедура begin end, а внутри begin end находился какой-нибудь alter table, то DMS такую операцию проигнорирует и засунет в отдельную таблицу исключений. С ней потом нужно будет повзаимодействовать для того, чтобы результат перенести в хранилище.
И больше всего нас расстроило то, что с последним обновлением сломался параллелизм при заполнении данных в Редшифте. В июле DMS обновилась на версию 3.5.1, а до этого была 3.4.7. Прежняя версия умела параллельно заливать данные в Redshift, а новая — почему-то не любит этого делать. Теперь мы должны все наши данные заполнять последовательно.
Самое главное — это ограниченные функции по отладке ошибок. Если CDC встанет, то начнут расти транзакционные логи на исходной базе данных. Единственное, что можно сделать — перезапустить переливку данных, потому что у PostgreSQL нет временного wal-лога, а только численно-буквенный чекпоинт, который ещё надо сопоставить с версией таблицы (и непонятно, как она соотнесётся с вашей). Не страшно, если таблица скромных размеров. Но если у нас, допустим, таблица весит 154 гб, а репликация сломалась, то это беда. Запуск DMS с нуля означает, что запущена транзакция, что в свою очередь означает, что начинают копиться dead rows — в конечном счете, если за этим не следить, то мы положим базу, так как её процессор перестанет вывозить сканирование такого количества dead rows в поисках нужной строки.
Справедливости ради, это не проблема конкретно DMS. Скорее, это вопросы к Aurora-PostgreSQL, так как сама DMS умеет вставать на временной чекпоинт таблицы (который есть, например, у Оракла), и откопировать только те изменения, которые прошли с того момента времени. Не будет необходимости перекачивать таблицу заново.
Что мы получили в итоге?
Может показаться, что мы 5,5 месяца изобретали велосипед самокат и можно было столько граблей не собирать. Но на самом деле, все это меркнет и бледнеет по сравнению с тем фактом, что мы получили на выходе.
Мы выстроили процессы дата-пайплайна. Теперь любой дата-инженер, который приходит к нам в команду, понимает, как выстроен процесс от источника до отчёта и как с этим работать (отчасти спасибо dbt за его функциональность и в особенности за dbt-docs).
Мы сильно ускорили работу всей отчётности и доставке инсайтов бизнесу. И оптимизировали затраты на хранилище.
Моя команда выросла в компетенциях работы c AWS, ведь до этого у нас был классический стек инхаус-разработки.
Что больше всего радует — мы получили гибкое и масштабируемое архитектурное решение. Недавно Whoosh вышел на рынок одежды и аксессуаров, однако на процессы с данными это никак не повлияло. И аренда самокатов, и продажа одежды с точки зрения потока данных выстроена точно также.
Смекаете?
Бесконечность - не предел
Куда пойдем дальше?
Хотим попробовать сделать DataQuality с помощью машинного обучения;
Возможно, заменить PostGIS на Python Lambda;
Перейти на Geoparquet — интересная, интригующая идея, но под вопросом;
Есть желание всё-таки попробовать универсальное решение под одной крышей — LakeHouse;
Продолжим растить компетенции команды в направлении аналитик-инженеров.
Закончить нашу статью хочу философией, которую мы преследовали, продумывая переезд:
Лучше один раз сделать, чем много раз спорить
С ветерком, Whoosh!
Напишите, пожалуйста, в комментариях, о чем было бы интересно прочитать в будущих статьях. Насколько интересна тема гео-аналитики? Стоит ли рассказать про наш опыт с LakeHouse? Возможно, стоит больше рассказать, как мы выстраиваем процессы?
