Мысль о текущей статье зародилась в ходе эксперимента по переносу ETL процесса с PL/SQL (Oracle Database) на инструментарий QlikView. До этого я слепо верил, что с помощью скрипта в QlikView можно с легкостью реализовать полноценный ETL-процесс.
С одной стороны это правда (когда Вам нужен простой ETL в QlikView). С другой стороны, когда у Вас огромная база данных промышленной ERP-системы, то сложность разработки ETL-процесса на QlikView возрастает во много раз, но при этом появляются значительные преимущества в скорости. Не будем спешить и рассмотрим все по порядку. Но предупрежу сразу — данная статья не дает однозначной оценки ETL-инструментарию QlikView.
Contents
- 0.1 Предпосылки
- 0.2 Общая схема ETL-процесса в QlikView Script
- 0.3 Проблема №1 «SELECT-FROM-WHERE для нескольких таблиц»
- 0.4 Проблема 2 «Отсутствует DELETE-FROM-WHERE»
- 0.5 Проблема №3 «Отсутствует команда UPDATE»
- 0.6 Проблема №4 «Сложные запросы в QlikView трудно реализуемы»
- 0.7 Проблема №5 «Нет аналогов ROW_NUMBER() OVER(), RANK() OVER() и DENSE_RANK() OVER()»
- 0.8 Проблема N6 «Нет регулярных выражений в QlikView Script»
- 0.9 Достоинство №1 «Наличие возможности написания пользовательских функций в QlikView»
- 0.10 Достоинство №2 «Скорость загрузки QVD файла в приложение QlikView»
- 0.11 Достоинство № 3 «Внутренние таблицы»
- 0.12 Замечание №1 про QVD
- 0.13 Замечание №2 про SELECT-CASE/Switch-Case
- 1 Резюме
- 2 Подборка видео
Предпосылки
Прежде чем я перейду к основным положения статьи, хочу дать общие характеристики, которыми должны обладать ETL-инструменты (мое личное субъективное мнение). 🙂
Итак, ETL инструменты должны:
- Иметь в своем составе все стандартные операторы или аналоги операторов, которые есть в SQL (например, where, group by, having, join, update, delete, drop).
- Иметь функционал написания пользовательских функций, макросов и т.п., которые можно неоднократно использовать при обработке данных.
- Иметь свой репозиторий. Это могут быть свой собственный формат данных, либо база данных.
- Графический интерфейс в принципе не обязателен для небольших проектов (хотя и сложный проект можно реализовать без графического интерфейса при наличии опытных разработчиков).
- Наличие различных функций для работы со строками, для форматирования, работы с датами, расчет агрегатов и так далее.
- Возможность подключаться к различным источникам данных.
Общая схема ETL-процесса в QlikView Script
Проблема №1 «SELECT-FROM-WHERE для нескольких таблиц»
Первым «сюрпризом» стало, что в QlikView нет функционала для написания запросов из нескольких внутренних таблиц QlikView и использовать при этом условия Where. Например, вот так нельзя написать:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Tbl1: LOAD * Inline [ field1, field2 Ivan, 29 Victor, 32 Marina, 23 Anna, 26 ]; Tbl2: LOAD * Inline [ field3, field4 Ivan, m Marina, w ]; NoConcatenate Tbl3: LOAD Tbl1.field1, Tbl1.field2, Tbl2.field4 Resident Tbl1, Tbl2 Where Tbl1.field1=Tbl2.field3 AND Tbl2.field4='m'; DROP tables Tbl1, Tbl2; |
Такой простенький вариант в SQL преобразуется в довольно нетривиальный вид:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Tbl1: LOAD * INLINE [ field1, field2 Ivan, 29 Victor, 32 Marina, 23 Anna, 26 ]; NoConcatenate Tbl2: LOAD * INLINE [ field3, field4 Ivan, m Marina, w ]; NoConcatenate Tbl3: LOAD field1, field2, lookup('field4','field3',field1,'Tbl2') as field4 //эквивалентно функции ВПР в Excel (берет первый найденный результат) Resident Tbl1 Where Exists(field3,field1) and lookup('field4','field3',field1,'Tbl2')='m' //эквивалентно Where Tbl1.field1 IN (SELECT Tbl2.field3 FROM Tbl2); |
Но данный подход не решает возникшей проблемы. Это подтверждает следующий пример:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Tbl1: LOAD * INLINE [ field1, field2 Ivan, 29 Victor, 32 Marina, 23 Anna, 26 ]; NoConcatenate Tbl2: LOAD * INLINE [ field3, field4 Ivan, m Marina, w Ivan, m2 Ivan, m3 Marina, w2 ]; NoConcatenate Tbl3: LOAD field1, field2, lookup('field4','field3',field1,'Tbl2') as field4 //эквивалентно функции ВПР в Excel (берет первый найденный результат) Resident Tbl1 Where Exists(field3,field1); //эквивалентно Where Tbl1.field1 IN (SELECT Tbl2.field3 FROM Tbl2); DROP tables Tbl1, Tbl2; |
А что если нужно использовать 3 или больше таблиц для условий where? При этом, если одно поле используется в нескольких таблицах, то функцию Exists() применить не получится. Все это вносит на первый взгляд не существенные неудобства, но у бизнес-пользователей порой возникают сложные алгоритмы в голове, которые и требуют как раз вот таких вот запросов. В данном случае придется делать длинную цепочку преобразований с применением JOIN, что приводит к логическим ошибкам в преобразованиях данных.
Схема построения процесса ETL с множеством JOIN’ов в QlikView Script:
Проблема 2 «Отсутствует DELETE-FROM-WHERE»
Вот захотели Вы удалить часть строк по условию Where из внутренней таблицы QlikView, написали DELETE команду и поняли – ее в скрипте QlikView нет. Приходится перезаписывать данные из этой таблицы в другую таблицу с условием, удалять таблицу и переименовывать новую таблицу. Небольшое, но неудобство. К тому же – что если таблица огромная, а удалить строки нужно в цикле?
Рабочий пример правильного удаления строк в QlikView:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Tbl1: LOAD * Inline [ field1, field2 Ivan, 29 Victor, 32 Marina, 23 Anna, 26 ]; NoConcatenate TMP_Tbl: LOAD * Resident Tbl1 Where field1 <> 'Marina'; DROP Table Tbl1; RENAME Table TMP_Tbl to Tbl1; |
Проблема №3 «Отсутствует команда UPDATE»
В QlikView нельзя обновлять записи во внутренних таблицах. Но, если порыться, то можно найти библиотеку QlikView компонентов, в которой существует функция Qvc.UpdateQvd, позволяющая обновлять QVD таблицей с новыми данными.
Проблема №4 «Сложные запросы в QlikView трудно реализуемы»
Данная проблема похожа на проблему №1. Запросы, которые с легкостью можно реализовать на PL/SQL или T-SQL, требуют трудоемкой работы в QlikView.
Например, структуру нижеприведенного запроса реализовать на QlikView крайне сложно:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
INSERT INTO TargetTable( Field1, ..., FieldN ) WITH A AS( --сложный запрос ), B AS( --сложный запрос ), C AS( --сложный запрос ), D as( --сложный запрос ), E AS( --сложный запрос ) SELECT FROM A, B, C, D, E WHERE A.FIELD(+)=D.FIELD AND B.FIELD=D.FIELD AND C.FIELD(+)=D.FIELD AND C.FIELD(+)=D.FIELD AND E.FIELD(+)=D.FIELD AND E.FIELD(+)=D.FIELD AND E.FIELD(+)=D.FIELD AND E.FIELD(+)=D.FIELD; |
Возможно, скорость обработки данных в QlikView будет намного эффективней выполняться по сравнению с запросами в Oracle DB или MS SQL. Но трудоемкость реализации цепочки преобразования данных в QlikView значительно выше. На каждом отдельном проекте необходимо выбирать свои инструменты для реализации процесса ETL.
Особенно сложно реализуются SQL запросы к базе данных при использовании данных из существующих QVD файлов. Т.е. когда уже часть данных находится в QVD файлах или во внутренних таблицах QlikView и их нужно использовать в качестве условий для выгрузки новых данных из базы данных.
Можно конечно записать данные в переменную и подставить в SQL-запрос, который направляется в базу данных (но это не везде применимо). Пример такого запроса:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
QVD_Table: LOAD concat(chr(39) & SHOP_CODE & chr(39), ',') as concat_SHOP_CODE FROM '$(PATH_QVD)QVD_Table.qvd' (qvd); LET storeList = Peek('concat_SHOP_CODE',0,'QVD_Table'); DROP Table QVD_Table; Target_Table: SQL SELECT --+MATERIALIZE TAPCFIN,TAPCCIN,TAPCINR,TAPSEQVL,TAPUAPP,TAPPBRUT,TAPSITE, CASE WHEN '$(date)' BETWEEN TAPDDEB AND TAPDFIN THEN 0 ELSE GREATEST(ABS(TO_DATE('$(date)')-TAPDFIN),ABS(TO_DATE('$(date)')-TAPDDEB)) END IS_VALID FROM TABLE_SOURCE WHERE ROWNUM>0 AND TAPSITE IN ($(storeList)); |
Проблема №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):
1 |
DENSE_RANK() OVER(PARTITION BY RESSITE,TAPCFIN,TAPCCIN,TAPCINR,TAPSEQVL ORDER BY IS_VALID ASC,TAPUAPP ASC,TAPPBRUT DESC) R |
пришлось выдумать вот такую конструкцию (в QlikView):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
if ( RESSITE = Previous(RESSITE) and TAPCFIN = Previous(TAPCFIN) and TAPCCIN = Previous(TAPCCIN) and TAPCINR = Previous(TAPCINR) and TAPSEQVL = Previous(TAPSEQVL), if ( IS_VALID = Previous(IS_VALID) and TAPUAPP=Previous(TAPUAPP) and TAPPBRUT=Previous(TAPPBRUT), Peek( R, -1, if(RowNo()>1 , 'RESEAU_TMP', 'RESEAU') ), Peek( R, -1, if(RowNo()>1 , 'RESEAU_TMP', 'RESEAU') ) + 1 ), 1 ) as R ... ORDER BY RESSITE, TAPCFIN, TAPCCIN, TAPCINR, TAPSEQVL, IS_VALID, TAPUAPP, TAPPBRUT DESC; |
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. Итак, вот как ее можно использовать:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Orders: LOAD * INLINE [ OrderId, Quantity 1, 100 2, 200 3, 300 ] ; STORE Orders INTO Orders.qvd (qvd); DROP TABLE Orders; Orders: LOAD * INLINE [ OrderId, Quantity 4, 400 5, 500 1, 999 ]; CALL Qvc.UpdateQvd ('Orders', 'Orders.qvd', 'OrderId'); DROP TABLE Orders; |
Таким образом, в пользовательских функциях (или подпрограммах) QlikView скрыт тот спасательный круг для разработчиков, с помощью которого можно продолжить разработку ETL процесса (если Вы ее уже начали) или же начать строить ETL в QlikView.
Уверен, что проблема с отсутствием команды DELETE может с легкостью быть решена за счет использования пользовательской подпрограммы.
Запись переменных можно осуществлять следующим образом через подпрограмму:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
//Установка значения переменной из SQL запроса - аналог SELECT INTO <переменная>; //CALL Set_Variable_From_SQL(VAR_NAME, SQL_QUERY, FIELD_NAME) SUB Set_Variable_From_SQL(VAR_NAME, SQL_QUERY, FIELD_NAME) Sub_tpm_tbl: SQL $(SQL_QUERY); LET FIELD_NAME = Upper('$(FIELD_NAME)'); LET $(VAR_NAME) = Peek('$(FIELD_NAME)',0,'Sub_tpm_tbl'); DROP table Sub_tpm_tbl; ENDSUB |
Пример использования функции:
1 2 |
SET k=0; //Если не создать k, то она будет носить локальный характер в функции CALL Set_Variable_From_SQL('k', 'SELECT COUNT(1) as CountRow FROM FCTSEM', 'COUNTROW'); |
Достоинство №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:
1 2 3 4 5 6 |
DECODE(A.TAPUAPP, 1,1, 21,B.SKU_IPCK, 41,B.SKU_PCK, 61,B.SKU_LAYER, 81,B.SKU_PALET) LU1_COEFF |
Pick & Match:
1 |
Pick( Match(TAPUAPP,1,21,41,61,81), 1,'SKU_IPCK','SKU_PCK','SKU_LAYER','SKU_PALET' ) as LU1_COEFF |
Если необходимо значение по умолчанию, то конструируется следующая схема:
1 |
pick(1+match(MyField,'Case 1','Case 2'),Default,Result1,Result2) |
Резюме
- Для источников, у которых есть сложные взаимосвязи между данными (реляционные базы данных): То, что делается в sql в одну строку, требует значительных извращений с внутренними функциями QlikView, что в итоге выливается в увеличение количества строк кода. Плюс сложность кода возрастает.
- Написанные сценарии загрузки крайне сложно поддерживать из-за тех выкрутасов, которых требует Qlik в рамках трансформации данных.
- Есть возможность писать внутренние функции (подпрограммы), что значительно увеличивает гибкость работы с данными в рамках QlikView Script.
- QlikView превосходно работает с подготовленными данными, можно очень быстро рассчитать дополнительные показатели, которые требуются бизнесу.
- Часть ETL-процесса можно распараллелить (правда тут все не тривиально, но об этом в другой раз).
- Манипуляция с данными в QlikView отчасти напоминает взаимодействие с различными встроенными функциями в Excel. Зная различные внутренние функции QlikView можно быстро разрабатывать сложные трансформации данных. Без знания всех внутренних функций QlikView невозможно качественно писать ETL-процесс. Будет много сложно читаемого кода.
- С помощью QlikView можно подключаться к многочисленным источникам данных. Сложные преобразования данных можно осуществлять на стороне сервера базы данных. QlikView позволяет отправлять на сервер сложные SQL запросы, которые возвращают готовые данные для загрузки.
Вывод: несмотря на некоторые шероховатости, QlikView имеет отличную функциональность, которая позволяет разрабатывать полноценные ETL-процессы (простые преобразования данных). Но в случае, если у Вас очень много таблиц источников, то следует подумать о дополнительном ETL-инструменте («Подумать» не равно «Необходимо использовать сторонний ETL»).
А пока, у меня вот такая сложилась ассоциация с разработкой ETL на QlikView:
Leave a Reply