Розробка · 15 вересня 2022, 15:00 771

Maksym Belko, Senior Data Engineer в ThredUP

Як ми мігрували моделі з експлорами в Looker

Мене звати Максим Белько, я експерт з даних з досвідом понад 10 років. Брав участь в телекомпроєктах з Oracle, та ERP систем на базі MS SQL, та аналітичних систем з Redshift.

Останні 3 роки я працюю в ролі Data Engineer та допомагаю командам отримувати необхідні дані у зручному форматі, а також адмініструю Looker. Після проведеної успішної міграції експлорів в компанії ThredUp було вирішено, що було б чудово поділитися практичним досвідом з командами, які зараз використовують Looker, як інструмент для звітів та аналітики.

В цій статті будуть розібрані приклади міграції моделей та збережених звітів в Looker. Вона може бути цікавою наступним спеціалістам: Data Engineers, Data Science Engineers, Business Intelligent Engineers, а також всім, кого цікавить Looker, та хто розглядає його застосування в своєму проєкті.

Загальні дані по лукеру (Looker)

Модель — файл моделі вказує підключення до бази даних і набір експлорів (Explore), які використовують це підключення. Файл моделі також визначає самі експлори та їхні зв’язки з іншими представленнями (Views).
Файл view зазвичай визначає окреме view у Looker. View відповідає або одній таблиці у вашій базі даних, або одній derived tables (далі DT). У view файлі вказується таблиця для запиту та поля (dimensions та measures), які потрібно включити з цієї таблиці.

Підготовка до міграції

Під час міграції LookML коду у Data Engineering (далі DE) команди були наступні челенджи:

No code freeze, бізнес повинен працювати і користувачі повинні мати можливість змінювати код та будувати звіти, незалежно від активностей DE команди.
Мігрувати звіти (Looks та Dashboards). Вони повинні працювати за старими URL і старим ID
Мігрувати всі заплановані (scheduled) звіти та алерти.

Після активного обговорення в команді DE та зацікавлених користувачів ми збудували план міграції

Загальний опис BI архітектури до міграції:

За допомогою Fivetran ми синхронізуємо дані з транзакційних баз даних (RDS в нашому випадку) в Redshift.
ETL — кастомні таблиці на основі даних з Redshift та інших джерел (sftp, spreadsheets, Amazon s3 та інші). Результат ETL зберігаємо в необхідному місці та виді. Розглянемо випадок, що аутпут таблиці ми зберігаємо в Redshift.
В Looker створюємо новий конектор, який має привілеї читати дані з Redshift та всі необхідні моделі з експлорами, а також на створення derived tables.

Загальний опис BI архітектури під час міграції:

За допомогою Fivetran ми синхронізували дані з RDS в Redshift та Data Lakehouse (далі Lakehouse).
ETL почали працювати в обох системах: Redshift та Lakehouse.
В Looker створюється новий проєкт зі своїм приватним репозиторієм github та новий конектор, який має привілеї читати дані з Lakehouse та всі необхідні моделі з експлорами. При цьому Redshift конектор продовжує працювати.

Загальний опису BI архітектури після міграції:

За допомогою Fivetran ми синхронізували дані з RDS в Lakehouse і виключаємо всі конектори, що пишуть в Redshift.
ETL — кастомні таблиці на основі даних з Lakehouse та інших джерел (sftp, spreadsheets, s3...). Результат ETL зберігаємо в необхідному місці та виді. Розглянемо випадок, що аутпут таблиці ми зберігаємо в Lakehouse, всі пайплайни, що писали в Redshift, виключаються.
В Looker залишається конектор, який має привілеї читати дані з Lakehouse та всі необхідні моделі з експлорами, а конектор для Redshift видаляється з усіма моделями, де він раніше використовувався. Проєкт, який використовував Redshift видаляється з Looker, а також архівується відповідний репо в гіті.

Почнемо з No code freeze

Оскільки бізнес — це живий організм, що росте та змінюється, DE командою було прийнято рішення, що мігрувати треба «на ходу» без фрізу. І тут одразу на думку приходить варіант розбити скоуп на якнайменші таски.

Перший варіант, за яким ми почали рухатися, було обрано мігрувати по моделях. Але оскільки не всі дата сети (таблиці) були доступні в новому сторі, ми були заблоковані і чекали поки будуть мігровані всі. Тому цей варіант також був відкинутий і ми зупинилися, що найменшою сутність має і повинна бути експлор.

Які бенефіти ми отримали? Стало можливим планувати та мігрувати експлори і попадати в дедлайн.

Тепер — про технічні челенджи та лайфхаки

Впродовж аналізу команда зрозуміла, що є таблиці, які вже не використовуються, і вони можуть бути видалені зі старого проєкту в Looker.

Та ж сама історія з кастомними таблицями, частина пайплайнів може бути видалена з конфігурації пайпланів, в нашому випадку — Apache Airflow.

І як результат, деякі джойни в експлорах можуть бути видаленими, ба більше, за допомогою внутрішнього інструменту моніторингу, ми з’ясували, як використовуються експлори в Looker, і ми могли видаляти експлори з моделі цілком (софт деліт, коментували код в моделі).

Отже, виявилось, що під час міграції DE команда може «почистити код» і ми почали це робити.

Були створені наступні списки:

ETL з технологією та аутпут таблицею.
Експлори погруповані в рамках моделі.
Неймінг конвенція для таблиць.
Маппінг імен таблиць RS <> LH.

Вважаю за доцільне зупинитися на п.3 та.4

Згрупували імена схем на рівні доменів: маркетинг, фінанси, дата сайнс, оперейшн ...

І, як результат, в нас вийшов маппінг таблиць.

Приклад: mysql_prod.users (Redshift) -> ods_shop.users (Lakehouse)

І для цього була створена таблиця в Lakehouse з наступними колонками: Redshift схема, Redshift таблиця, Lakehouse схема, Lakehouse таблиця, тип таблиці (синхронізуються з проду чи кастомний ETL), а також прапорець нотифікації, чи готова таблиця для використання в Lakehouse. Ця таблиця використовується для декількох задач: отримати інформацію, чи було мігровано таблицю в Lakehouse, а також ми використовували її при конвертації sql коду, оскільки таблиці можуть мати нові схеми чи імена.

Як ви, мабуть, вже здогадалися, був написаний код, який міг конвертувати sql синтаксис з Redshift в spark sql (у вашому випадку може бути MySQL > Oracle, MSSQL > Redshift, тобто на практиці вбудований набір функцій SQL в різних базах може і буде відрізнятися), який використовується для читання даних з Lakehouse (в нашому випадку «під капотом» таблиці у delta форматі з даними на s3).

Крім проблем в різниці sql синтаксису були і проблеми з LookML (мова для кодування моделей, експлорах та views).

І тому в коді тули для конвертування були маппінги вбудованих функцій, які різні в діалектах SQL, ось деякі приклади:

"CONVERT_TIMEZONE($1, $2, $3)": "from_utc_timestamp(to_utc_timestamp($3, $1), $2)",
"TO_CHAR($1, 'YYYY-MM')": "DATE_FORMAT($1, 'yyyy-MM')",
"TO_CHAR($1, 'YYYY-MM-DD')": "DATE_FORMAT($1, 'yyyy-MM-dd')",
"TO_CHAR($1, 'MM-DD')": "DATE_FORMAT($1, 'MM-dd')",

"GETDATE()": "current_timestamp()",

"BTRIM($1)": "trim($1)",
"LEN($1)": "length($1)",

# nvl
"NVL($1)": "COALESCE($1)",
"NVL($1, $2)": "COALESCE($1, $2)",
"NVL($1, $2, $3)": "COALESCE($1, $2, $3)",
"NVL($1, $2, $3, $4)": "COALESCE($1, $2, $3, $4)",
"NVL($1, $2, $3, $4, $5)": "COALESCE($1, $2, $3, $4, $5)",

# json
"json_extract_path_text($1, $2)": "get_json_object($1, '$.' || $2)",
"json_extract_path_text($1, $2, $3)": "get_json_object($1, '$.' || $2)",
"split_part($1, $2, $3)": "split($1, $2)[$3-1]",

# Date Diffs
"DATEDIFF(secs, $2, $3)": "(UNIX_TIMESTAMP($3) - UNIX_TIMESTAMP($2))", # it's important brackets !!!
"DATEDIFF('secs', $2, $3)": "(UNIX_TIMESTAMP($3) - UNIX_TIMESTAMP($2))",
"DATEDIFF('sec', $2, $3)": "(UNIX_TIMESTAMP($3) - UNIX_TIMESTAMP($2))",
"DATEDIFF(sec, $2, $3)": "(UNIX_TIMESTAMP($3) - UNIX_TIMESTAMP($2))",
"DATEDIFF(second, $2, $3)": "(UNIX_TIMESTAMP($3) - UNIX_TIMESTAMP($2))",
"DATEDIFF('second', $2, $3)": "(UNIX_TIMESTAMP($3) - UNIX_TIMESTAMP($2))",
"DATEDIFF(seconds, $2, $3)": "(UNIX_TIMESTAMP($3) - UNIX_TIMESTAMP($2))",
"DATEDIFF('seconds', $2, $3)": "(UNIX_TIMESTAMP($3) - UNIX_TIMESTAMP($2))",

# Date Adds
"DATEADD('hour', $2, $3)": "($3 + interval '$2 hour')",
"DATEADD(hour, $2, $3)": "($3 + interval '$2 hour')",
"DATEADD(hours, $2, $3)": "($3 + interval '$2 hour')",
"DATEADD(h, $2, $3)": "($3 + interval '$2 hour')",
"DATEADD('day', $2, $3)": "($3 + interval '$2 day')",
"DATEADD(day, $2, $3)": "($3 + interval '$2 day')",
"DATE_ADD(day, $2, $3)": "($3 + interval '$2 day')",
"DATE_ADD('day', $2, $3)": "($3 + interval '$2 day')",

"REGEXP_COUNT($1, $2)": "(size(split($1, $2)) - 1)",

"STRTOL($1,$2)": "CONV($1, $2, 10)",

Отже, в нас на разі є все для міграції

Була обрана модель та набір експлорів, які ми могли змігрувати. Хочу детальніше описати цей процес:

Локально пулиться мастер гілка старого проєкту.
І дев гілка нового.
Проганяється парсинг по старому проєкту згідно конфігурації (які експлори ми хочемо мігрувати).
Потім запускаємо процес конвертування, в результаті отримаємо в діфі всі ці зміни, обов’язково перевіряємо і робимо коміт в дев гілці в Lakehouse проєкті.
Далі — валідація та деплой в прод.

Як саме ми мігрували Looks & Dashboards

Маючи досвід Looker, та після вивчення форумів та офіційної документації, була помічена цікава закономірність: якщо є 2 однойменні експлори в різних моделях з одним і тим же набором джойнів views, то в URL достатньо змінити ім’я моделі, і після оновлення сторінки всі dimensions, measures, фільтри та стилістика візуалізації (чарти чи таблиці...) будуть збережені.

Отже, якщо у користувача є набір збережених закладок з ad-hoc Looker пулами, йому буде легко почати використовувати нову модель. Для цього була додатково створена документація для користувачів і поширена через доменні канали зв’язку, а також надана підтримка командою DE з вирішенням цих питань в тих же каналах зв’язку.

Проаналізувавши API Looker, ми розробили код для оновлення Looks. В нашому випадку — це Python ноутбук в Databricks. В блоці були описи в форматі: ім’я старої моделі, ім’я експлору, що конвертуються в нову модель. Сценарій приблизно такий: ми знаємо список експлорів, які потрібно мігрувати, в циклі перебираємо список id луків та зберігаємо їх, потім для цих луків також в циклі переключаємо їх на нові моделі.

З дашбордами трохи складніше, тому що там зазвичай не одна плитка, яка в свою чергу може бути або збереженим раніше луком, або просто звичайним Looker пулом. Рахуємо так, як із луками, але є певна вкладеність. В результаті, є дашборди, в яких будуть переключені плитки на нові моделі, саме ті, які зазначені в конфігурації, тобто якщо в дашборді є 10 плиток і тільки 5 стосується конфігурації, то лише 5 будуть переключені.

В результаті ми отримали луки та дашборди, які використовують вже нові моделі.

Пропоную зупинитися детальніше на нашому процесі міграції на одному прикладі.

Припустимо, що ми мігруємо експлор А1 з моделі А в нову модель АА.

Для початку треба проаналізувати і впевнитися, що всі таблиці доступні в новому сорсі, і що ми видалили все зайве в старому. В дев бранчі конвертуємо наш експлор в нову модель, потім валідуємо LookML код і робимо коміт, для зручності на цьому етапі в лейбел експлора в кінці додаємо — LH (WIP). Приклад нижче:

{
   'model': 'Main model A',
   'rewrites': {'content': {'connection': 'lh', 'label': 'LH AA'},
                'file_name': 'lh_aa.model.lkml'},
   'explores': [
       {'explore': 'A1', 'rewrite': {'label': 'A1 - LH (WIP)'}},
       {'explore': 'A2',
        'rewrite': {'label': 'A2 - LH (WIP)'}},
   ]
},

Потім проганяємо і дістаємо список луків і дашбордів через тулу для міграції, але не мігруємо їх, тобто нам важливо отримати старі та нові URL-и, і ці дані списками імпортуємо в spreadsheets для аналізу і валідації, трекаємо по принципу: дані 100% ок, є незначні неточності, або помилка, наприклад, ми все таки упустили якусь таблицю під час аналізу міграції, або якась функція некоректно змігрувалася.

В залежності від результату, ми повертаємося і фіксимо всі неточності та помилки. Вносимо зміну в тулу для конвертації LookML коду за необхідності, або навіть змінюємо код в старій моделі, переважно це доводилось робити при роботі з DT.

Повторюємо попередні кроки за необхідності, і валідуємо згідно збереженого списку. Коли ми пересвідчилися, що все ок, деплоїмо новий експлор в прод, потім запускаємо блок в ноутбуці, що відповідає переключенню моделей для луків та дашбордів. І, що важливо, коментуємо код експлору в старій моделі.

За результатами, інформуємо в каналах зв’язку, що експлор А1 переїхав з моделі А в АА, з проханням звернутися до команди DE за допомогою, якщо щось перестало працювати або дані підлягають уточненню.

Оскільки ми мігрували Looks та Dashboards, зберігаючи їх оригінальні ID, то всі scheduled об’єкти стали доступними зі старими конфігураціями і не потребували додаткових змін. З винятків: ручного аналізу та перебудови потребували алерти з Dashboards, які були налаштовані на плитках на базі експлорів, а не збережених Looks, які були об’єктами Dashboard. В адмінпанелі Looker є вкладка з алертми, і ці проблемні алерти будуть не валідні, їх було перебудовано вручну стекхолдерами, або DE командою.

З важливих моментів: в нашому проєкті є спільні views, які використовуються в різних екплорах чи моделях. Оскільки ми рухалися без код фрізу то за умови, що одна view була змігрована в рамках міграції експлора, наприклад 2 місяці тому, то існує велика ймовірність, що ця view могла бути змінена одночасно і в старому, і в новому проєкті, часто було так, що ми мали нові dimensions чи measures в обох проєктах. В цей момент треба бути максимально уважним і перенести все зі старого в новий проєкт в ручному режимі, під час аналізу відмінностей в гіті.

Для зручності була створена документація, яка доповнювалась по готовності нових експлорів в новому проєкті. Вона має вигляд таблиці з наступними колонками: дата міграції, стара модель, старий експлор, а також нова модель, новий експлор з гіперлінками на файли моделей та експлори.

Додатково був доданий прапорець статусу експлору в старому проєкті, якій змінювався від Legacy, у випадку, якщо була необхідність тримати 2 однакові експлори за запитом власників експлорів для додаткової валідації, до Commented, коли експлор був видалений (закоментований) зі старої моделі.

В результаті міграції ми отримали

Абсолютно новий проєкт в Looker з точки зору джерела даних, а також LookML коду, проте з тими ж Looks & Dashbords та перенесеними експлорами, які дійсно використовуються в компанії.

Змогли відмовитися від Redshift на користь Data Lakehouse та закрили Tech debt зі старими пайплайними, які використовували легасі технології. Наразі команди можуть використовувати ноутбуки в Databricks або DBT для побудови нових пайплайнів.