Время прочтения: 5 мин.

Часто приходится иметь дело с данными, представляющими из себя таблицы в миллионы строк. Более того, в эти таблицы могут добавляться тысячи или миллионы строк ежедневно или ежемесячно.   При этом, для анализа выявления тенденций и аномалий бывает необходимым использовать и исторические данные.  Для тех, кто использует для анализа и визуализации 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 млн строк, не ожидая загрузки данных часами и не рискуя при этом создать огромную нагрузку на сервер.