Что такое ETL & ELT? В чем отличие? Как та или иная технология может быть применима в Qlik Sense

Извлечение, загрузка и преобразование (ELT) — это процесс, с помощью которого данные извлекаются из исходной системы, загружаются в хранилище данных, а затем преобразовываются.

E, T, L:

  • Extraction (Извлечение): извлечение необработанных данных из неструктурированного пула данных и их перенос во временное, промежуточное хранилище данных.
  • Преобразование (Transformation): структурирование, обогащение и преобразование необработанных данных в соответствии с целевым источником.
  • Загрузка (Loading): загрузка структурированных данных в хранилище данных для анализа и использования инструментами бизнес-аналитики (BI).

ETL требует управления необработанными данными, включая извлечение необходимой информации и проведение правильных преобразований, чтобы в конечном итоге удовлетворить потребности бизнеса. Каждый этап – извлечение, преобразование и загрузка – требует взаимодействия инженеров и разработчиков данных, а также работы с ограничениями емкости традиционных хранилищ данных. Используя ETL, аналитики и другие пользователи BI привыкли ждать , поскольку простой доступ к информации недоступен до тех пор, пока не будет завершен весь процесс ETL.

QVD файлы

Qlik Sense имеет собственный формат хранения данных. Данные из разных систем выгружаются и сохраняются в QVD-формате. Этот формат файлов очень быстро загружается в Qlik Sense для дальнейшей обработки. Это как dump базы данных. Выгрузка и загрузка миллионов строк (без доп.условий) занимает секунды.
Иногда можно построить серьезные коммерческие проекты только на QVD файлах.

  • Первый уровень данных — сырые данные (выгрузки из источников без каких либо изменений).
  • Второй уровень — обработанные файлы (объединение данных, дополнительные аналитики, флаги, специальные поля, рассчитанные меры и т.д.).
  • Третий уровень — агрегированные данные и т.д.

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

Пример организации ETL-процесса на примере QlikView (в Qlik Sense аналогичные процессы):

Краткий инструктаж по написанию скриптов загрузки

В этом разделе будут описаны некие принципы для организации ETL-процесса с помощью Qlik Sense (без использования других инструментов обработки данных).

1. В операциях Concatenate, Join не используйте Distinct. Если таблица формируется с помощью последовательных операций объединения данных в одну таблицу с помощью оператора принудительного объединения таблиц Concatenate (сходных по набору столбцов, но имеющих несколько разных колонок), операторов присоединения данных Join (Left, Right, Inner) – не используйте Distinct – иначе Вы потеряете одинаковые строки (это не дубликаты, просто в 1 документе могут быть указан один и тот же товар двумя строками, если в системе учета это не запрещено). Причем не важно, на каком шаге был применен Distinct (в самом начале обработки данных или в конце).

2. Используйте меппинги ApplyMap вместо Join. При большом объеме данных это ускоряет загрузку данных.

3. Одинаковые части формул рекомендуется выносить в переменные, чтобы упростить дальнейшую поддержку кода приложения. Переменные можно вести как в Excel, так и в Variable Manager. Также можно использовать различные расширения или extension (например, Qlik Sense Variable Editor Mashup).

4. Ключи оборачивать в TEXT(), даже если это hash-ключ из 1С Предприятие 8.3. Qlik имеет неприятную штуку с изменением ключей (встречается редко, но проскакивает). В обычных ключах очень часто клик может преобразовать запись “130E0” в “13E1”. Т.е. знак “E” он воспринимает как разряд.

5. Для генерации составных ключей вместо hash128() рекомендуется использовать autonumberhash128() – он быстрее. Генерация ключей с помощью функций Hash128 и Hash256 утяжеляет модель, что ведет к увеличению использования RAM.

6. Preceding Load: Для того, чтобы сократить объем кода и оптимизировать скорость загрузки данных рекомендую использовать Preceding Load (Предшествующий оператор LOAD). Preceding Load можно использовать и при загрузке из файлов, из баз данных, из уже загруженных таблиц с помощью оператора Resident. Количество “этажей” Preceding Load не ограничивается (используйте в меру разумного).

SimplePreceding:
LOAD
     *,
     [To Date] - [From Data] as Duration
;
LOAD
     Date(Date#(FromDate, 'YYYYMMDD'), 'DD MMM YYYY') as [From Date],
     Date(Date#(ToDate, 'YYYYMMDD'), 'DD MMM YYYY') as [To Date],
FROM [lib://SourceData/Durations.xlsx] (ooxml, embedded labels, table is Data);

7. Нумерация строк в большой таблице фактов. Если нужно пронумеровать таблицу фактов, которая грузится из нескольких источников, то для начала загружаем все факты в 1 таблицу Qlik Sense, затем перекладываем таблицу саму себя (Load * Resident) с функцией RecNo().

RowNo() для больших таблиц не нужно использовать, т.к. она очень медленно работает.

8. Не создавайте меппинги напрямую из больших QVD файлов. Сначала грузим QVD в таблицу, затем перекладываем с помощью Resident данные в Mapping таблицу. Загрузка данных в Mapping таблицу отключает оптимизированную загрузку данных.

9. Избегайте сложных динамически генерируемых полей, таблиц, кусков кода. Любая динамика усложняет поддержку решения. Особенно это критично, когда один разработчик уходит из команды, код приходится изучать с нуля. Если другого выхода нет, кроме как использование динамики, то делайте генерацию кода, полей и т.п. Если есть простой способ написания кода – используйте его. Унификация и простота – залог облегчения поддержки инфраструктуры, etl-процесса. Не гонитесь за интересными решениями, прокачкой ИТ-навыков – это вредит ИТ-решениям компании (но разработчикам конечно это на пользу). Плюс повышаются косвенные затраты компании, в какой-то момент потребуется вместо 1 разработчика на поддержку использовать 2х разработчиков.

Продолжение следует


Оставить комментарий

avatar
  Подписаться  
Уведомление о