Инкрементальная загрузка в Qlik Sense

Часто приходится иметь дело с данными, представляющими из себя таблицы в миллионы строк. Более того, в эти таблицы могут добавляться тысячи или миллионы строк ежедневно или ежемесячно. При этом, для анализа выявления тенденций и аномалий бывает необходимым использовать и исторические данные. Для тех, кто использует для анализа и визуализации Qlik Sense, загрузка больших объемов занимает много времени.

Такой была задача построить на Qlik Sense аналитический отчет по продажам сотрудников, позволяющий ежемесячно отслеживать изменения клиентских потребностей. В месяц в мотивацию сотрудников входит порядка 15 млн операций. За 10 минут в Qlik Sense загружается около 8 млн строк.

Учитывая, что необходимо сравнивать данные помесячно, загрузка данных даже за 6 месяцев может длиться часами.

Спасла инкрементальная загрузка. Из источников загружались только новые или измененные записи, а не все имеющиеся.

Инкрементальная загрузка - это механизм в Qlik Sense, с помощью которого из внешнего источника загружаются только обновленные данные, а основная масса неизменившихся данных загружается из внутреннего хранилища. Реализован механизм посредством файлов QVD.

QVD (Qlik View Data) – это формат файла, в котором содержится таблица данных, экспортируемая из программ Qlik View или Qlik Sense, и который может быть прочитан только этими программами. Этот формат оптимизирован для скорости при чтении данных из скрипта Qlik Sense, при этом не занимает много места и чтение данных из него в 10-100 раз быстрее, чем из других источников данных.

Инкрементальная загрузка имеет несколько вариантов:

· только добавление;

· добавление и обновление;

· добавление, обновление и удаление.

Рассмотрим самый простой вариант- добавление данных.

Наша задача - загрузить в Qlik Sense для анализа таблицу из ~80 млн строк и в дальнейшем ежемесячно пополнять её на ~15 млн строк.

Прежде чем начинать использовать метод инкрементальной загрузки, нужно в первый раз загрузить все данные из исходной таблицы. Загруженные данные необходимо сохранить в QVD.

Первичная загрузка данных в QVD

1. Запустите Qlik Sense и создайте новое приложение.

2. Откройте редактор скрипта загрузки данных.

3. Создайте раздел Загрузка таблицы первоначальная.

4. Столкнулась с тем, что при загрузке первоначальных данных с удаленного ресурса (в моем случае - из таблицы в БД MS SQL), возникает ошибка. Поэтому рекомендую выгрузить в таблицу в excel TOP 1000 строк из загружаемой таблицы и воспользоваться ею для создания первоначального QVD файла.

5. Загрузите все данные из файла Operation.xlsx

6. Добавьте строку сохранения данных в QVD файл. Cкрипт для загрузки на листе Загрузка таблицы первоначальная будет выглядеть так:

Operation: LOAD DATE_REPORT, DATE_REPORT_SRC, DATE_MONTH, client_tid, EMPLOYEE_LOGIN, EMPLOYEE_FIO, EMPLOYEE_ID, EMPLOYEE_POSITION, EMPLOYEE_POSITION_GR, - - - - - - - - - - - - - - - - - - - - DATE_UPDATE, PAYMENT_STATUS, in_sup, epk_id, PROD_KPI, IS_SBOLPRO FROM [lib://AttachedFiles/Operation.xlsx] (ooxml, embedded labels, table is Operation); STORE Operation INTO [lib://AttachedFiles/operation.qvd];

7. Загрузите данные.

8. Закомментируйте скрипт раздела Загрузка таблицы первоначальная.

9. Создайте раздел Проверка QVD

10. Создайте скрипт загрузки данных из QVD

LOAD DATE_REPORT, DATE_REPORT_SRC, DATE_MONTH, client_tid, EMPLOYEE_LOGIN, EMPLOYEE_FIO, EMPLOYEE_ID, EMPLOYEE_POSITION, EMPLOYEE_POSITION_GR, - - - - - - - - - - - - - - - - - - - - - - - - - - - - DATE_UPDATE, PAYMENT_STATUS, in_sup, epk_id, PROD_KPI, IS_SBOLPRO FROM [lib://AttachedFiles/operation.qvd] (qvd);

11. Запустите скрипт. Убедитесь, что данные из QVD загружаются в оптимизированном режиме. Просмотрите модель данных, проверьте, что данные загружены.

12. Создайте новый раздел - Добавление данных в таблицу.

13. Для объединения новых загруженных данных с содержимым QVD-файла соединяем их со старыми данными, а затем снова сохраняем их в QVD. Новые данные можем брать уже из необходимого нам источника.

LIB CONNECT TO 'SERVER'; Operation: LOAD DATE_REPORT, DATE_REPORT_SRC, DATE_MONTH, client_tid, EMPLOYEE_LOGIN, EMPLOYEE_FIO, EMPLOYEE_ID, EMPLOYEE_POSITION, EMPLOYEE_POSITION_GR, - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - DATE_UPDATE, PAYMENT_STATUS, in_sup, epk_id, PROD_KPI, IS_SBOLPRO; [Operation]: SELECT "DATE_REPORT", "DATE_REPORT_SRC", "DATE_MONTH", "client_tid", "EMPLOYEE_LOGIN", "EMPLOYEE_FIO", "EMPLOYEE_ID", "EMPLOYEE_POSITION", "EMPLOYEE_POSITION_GR", - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "DATE_UPDATE", "PAYMENT_STATUS", "in_sup", "epk_id", "PROD_KPI", "IS_SBOLPRO" FROM "BD_DM".shema1."table_motivation" where date_month='2021.07.31'; Concatenate LOAD DATE_REPORT, DATE_REPORT_SRC, DATE_MONTH, client_tid, EMPLOYEE_LOGIN, EMPLOYEE_FIO, EMPLOYEE_ID, EMPLOYEE_POSITION, EMPLOYEE_POSITION_GR, - - - - - - - - - - - - - - - - - - - - - - - - - - PAYMENT_STATUS, in_sup, epk_id, PROD_KPI, IS_SBOLPRO FROM [lib://AttachedFiles/operation.qvd](qvd); STORE Operation INTO [lib://AttachedFiles/operation.qvd];

14. Сохраните изменения и запустите перезагрузку скрипта.

15. Новые данные загружаются в обычном режиме, а старые данные из QVD загружаются в оптимизированном режиме.

16. Просмотрите модель данных. Убедитесь, что данные в таблицу добавлены.

Таким образом, я по частям загрузила 6 месяцев данных об операциях. На сегодня в таблицу загружено более 81 млн строк.

Планирую в дальнейшем ежемесячно пополнять данные в приложении Qlik Sense на 13-15 млн строк, не ожидая загрузки данных часами и не рискуя при этом создать огромную нагрузку на сервер.