Обзор возможностей и ограничений ETL в QlikView Script

Мысль о текущей статье зародилась в ходе эксперимента по переносу ETL процесса с PL/SQL (Oracle Database) на инструментарий QlikView. До этого я слепо верил, что с помощью скрипта в QlikView можно с легкостью реализовать полноценный ETL-процесс.
С одной стороны это правда (когда Вам нужен простой ETL в QlikView). С другой стороны, когда у Вас огромная база данных промышленной ERP-системы, то сложность разработки ETL-процесса на QlikView возрастает во много раз, но при этом появляются значительные преимущества в скорости. Не будем спешить и рассмотрим все по порядку. Но предупрежу сразу – данная статья не дает однозначной оценки ETL-инструментарию QlikView.

Предпосылки

Прежде чем я перейду к основным положения статьи, хочу дать общие характеристики, которыми должны обладать ETL-инструменты (мое личное субъективное мнение). 🙂

Итак, ETL инструменты должны:

  1. Иметь в своем составе все стандартные операторы или аналоги операторов, которые есть в SQL (например, where, group by, having, join, update, delete, drop).
  2. Иметь функционал написания пользовательских функций, макросов и т.п., которые можно неоднократно использовать при обработке данных.
  3. Иметь свой репозиторий. Это могут быть свой собственный формат данных, либо база данных.
  4. Графический интерфейс в принципе не обязателен для небольших проектов (хотя и сложный проект можно реализовать без графического интерфейса при наличии опытных разработчиков).
  5. Наличие различных функций для работы со строками, для форматирования, работы с датами, расчет агрегатов и так далее.
  6. Возможность подключаться к различным источникам данных.

Общая схема ETL-процесса в QlikView Script

etl_qlikview_schema

Проблема №1 «SELECT-FROM-WHERE для нескольких таблиц»

Первым «сюрпризом» стало, что в QlikView нет функционала для написания запросов из нескольких внутренних таблиц QlikView и использовать при этом условия Where. Например, вот так нельзя написать:

Такой простенький вариант в SQL преобразуется в довольно нетривиальный вид:

Но данный подход не решает возникшей проблемы. Это подтверждает следующий пример:

А что если нужно использовать 3 или больше таблиц для условий where? При этом, если одно поле используется в нескольких таблицах, то функцию Exists() применить не получится. Все это вносит на первый взгляд не существенные неудобства, но у бизнес-пользователей порой возникают сложные алгоритмы в голове, которые и требуют как раз вот таких вот запросов. В данном случае придется делать длинную цепочку преобразований с применением JOIN, что приводит к логическим ошибкам в преобразованиях данных.

Схема построения процесса ETL с множеством JOIN’ов в QlikView Script:
ETL с множеством JOIN'ов в QlikView Script

Проблема 2 «Отсутствует DELETE-FROM-WHERE»

Вот захотели Вы удалить часть строк по условию Where из внутренней таблицы QlikView, написали DELETE команду и поняли – ее в скрипте QlikView нет. Приходится перезаписывать данные из этой таблицы в другую таблицу с условием, удалять таблицу и переименовывать новую таблицу. Небольшое, но неудобство. К тому же – что если таблица огромная, а удалить строки нужно в цикле?
Рабочий пример правильного удаления строк в QlikView:

Проблема №3 «Отсутствует команда UPDATE»

В QlikView нельзя обновлять записи во внутренних таблицах. Но, если порыться, то можно найти библиотеку QlikView компонентов, в которой существует функция Qvc.UpdateQvd, позволяющая обновлять QVD таблицей с новыми данными.

Проблема №4 «Сложные запросы в QlikView трудно реализуемы»

Данная проблема похожа на проблему №1. Запросы, которые с легкостью можно реализовать на PL/SQL или T-SQL, требуют трудоемкой работы в QlikView.
Например, структуру нижеприведенного запроса реализовать на QlikView крайне сложно:

Возможно, скорость обработки данных в QlikView будет намного эффективней выполняться по сравнению с запросами в Oracle DB или MS SQL. Но трудоемкость реализации цепочки преобразования данных в QlikView значительно выше. На каждом отдельном проекте необходимо выбирать свои инструменты для реализации процесса ETL.
Особенно сложно реализуются SQL запросы к базе данных при использовании данных из существующих QVD файлов. Т.е. когда уже часть данных находится в QVD файлах или во внутренних таблицах QlikView и их нужно использовать в качестве условий для выгрузки новых данных из базы данных.
Можно конечно записать данные в переменную и подставить в SQL-запрос, который направляется в базу данных (но это не везде применимо). Пример такого запроса:

Проблема №5 «Нет аналогов ROW_NUMBER() OVER(), RANK() OVER() и DENSE_RANK() OVER()»

В скрипте QlikView отсутствуют аналоги операций ROW_NUMBER() OVER(PARTITION BY … ORDER BY …), RANK() OVER(PARTITION BY … ORDER BY …) и DENSE_RANK() OVER(PARTITION BY … ORDER BY …).
Опять же, необходимо отсылать запросы в базу данных и оттуда получать обработанные данные. Но если у Вас часть данных находится в QVD или во внутренних таблицах QlikView, то ничего не получится сделать. Придется переписывать весь ETL или менять логику.
Как удалось обойти данную задачу средствами QlikView (опять же нестандартный подход).
Вместо строки (которая была в исходном ETL на PL/SQL):

пришлось выдумать вот такую конструкцию (в QlikView):

QlikView не позволил использовать поле в процессе своего формирования, т.к. до загрузки первой строки отсутствует это поле и QlikView выдает ошибку о несуществующем поле. Для этого в таблице источнике было создано тоже самое поле R с постоянным числом 1 и для первой строки использовалось поле из таблицы источника, а для других строк используется формируемая таблица

Проблема N6 «Нет регулярных выражений в QlikView Script»

Стандартный функционал скрипта QlikView не позволяет применять регулярные выражения для обработки данных (Regular expressions in the load script). В QlikView регулярные выражения можно применить через VBScript, но он работает гораздо медленнее, чем скрипт QlikView. Но в любом случае – если Вы начали строить ETL-процесс на клике, то лучше продолжить разработку через VBScript.
Подробнее в статье “Regular expressions in the load script”.

Достоинство №1 «Наличие возможности написания пользовательских функций в QlikView»

Проблема №3 закрывается функцией из общедоступной библиотеки функций Qvc.UpdateQvd. Итак, вот как ее можно использовать:

Таким образом, в пользовательских функциях (или подпрограммах) QlikView скрыт тот спасательный круг для разработчиков, с помощью которого можно продолжить разработку ETL процесса (если Вы ее уже начали) или же начать строить ETL в QlikView.
Уверен, что проблема с отсутствием команды DELETE может с легкостью быть решена за счет использования пользовательской подпрограммы.
Запись переменных можно осуществлять следующим образом через подпрограмму:

Пример использования функции:

Достоинство №2 «Скорость загрузки QVD файла в приложение QlikView»

Загрузка из QVD в QlikView и из QlikView в QVD проходит довольно шустро.
Провел ряд экспериментов и получил следующие результаты:

  • загрузка из QVD в клик -> 106 млн строк за 23 секунды;
  • сохранение таблицы (106 млн строк) в QVD за 18 секунд.

Для сравнения – из csv в QlikView 9,5 млн строк грузится за 23 секунды. Т.е. получается, что QlikView со своим форматом данных работает быстрее в 11 раз, чем с csv файлами.
В общем, отлично!

Достоинство № 3 «Внутренние таблицы»

QlikView имеет очень удобный инструмент быстрого создания внутренних таблиц. Можно создавать временные таблицы, добавлять поля (при помощи JOIN), удалять ненужные поля. После использования таблицу можно удалить из приложения QlikView. Данный прием позволяет снизить нагрузку на базу данных, например, при запросах в циклах. Из базы данных загружаются все данные во внутреннюю таблицу, которая хранится в оперативной памяти. А затем в цикле производятся операции сравнения. Скорость работы алгоритма в цикле возрастает в десятки/сотни раз (в зависимости от скрипта).

Замечание №1 про QVD

В QlikView нельзя несколько внутренних таблиц записать в один QVD. Также нельзя аддитивно накапливать данные в один файл (т.е. каждый раз STORE table INTO table.qvd перезаписывает файл table.qvd). Но при высокой скорости данное замечание можно считать нейтральным.

Замечание №2 про SELECT-CASE/Switch-Case

Вместо SELECT-CASE/Switch-Case или DECODE (из PL/SQL) в QlikView нужно использовать совокупность функций Pick & Match. Пример преобразования вида DECODE в стандартный код клика.

DECODE:

Pick & Match:

Если необходимо значение по умолчанию, то конструируется следующая схема:

Резюме

  1. Для источников, у которых есть сложные взаимосвязи между данными (реляционные базы данных): То, что делается в sql в одну строку, требует значительных извращений с внутренними функциями QlikView, что в итоге выливается в увеличение количества строк кода. Плюс сложность кода возрастает.
  2. Написанные сценарии загрузки крайне сложно поддерживать из-за тех выкрутасов, которых требует Qlik в рамках трансформации данных.
  3. Есть возможность писать внутренние функции (подпрограммы), что значительно увеличивает гибкость работы с данными в рамках QlikView Script.
  4. QlikView превосходно работает с подготовленными данными, можно очень быстро рассчитать дополнительные показатели, которые требуются бизнесу.
  5. Часть ETL-процесса можно распараллелить (правда тут все не тривиально, но об этом в другой раз).
  6. Манипуляция с данными в QlikView отчасти напоминает взаимодействие с различными встроенными функциями в Excel. Зная различные внутренние функции QlikView можно быстро разрабатывать сложные трансформации данных. Без знания всех внутренних функций QlikView невозможно качественно писать ETL-процесс. Будет много сложно читаемого кода.
  7. С помощью QlikView можно подключаться к многочисленным источникам данных. Сложные преобразования данных можно осуществлять на стороне сервера базы данных. QlikView позволяет отправлять на сервер сложные SQL запросы, которые возвращают готовые данные для загрузки.

Вывод: несмотря на некоторые шероховатости, QlikView имеет отличную функциональность, которая позволяет разрабатывать полноценные ETL-процессы (простые преобразования данных). Но в случае, если у Вас очень много таблиц источников, то следует подумать о дополнительном ETL-инструменте («Подумать» не равно «Необходимо использовать сторонний ETL»).

А пока, у меня вот такая сложилась ассоциация с разработкой ETL на QlikView:
ETL_in_qlikview

“Рекомендательная” схема организации ETL в QlikView

Рекомендательная схема ETL-процесса в QlikView

Подборка видео


Комментарии:

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *