qlikview загрузка данных в скрипте
QlikView: Экспресс загрузка данных
В этой статье я расскажу о не стандартном, по крайней мере, для нашей компании методе обновления данных.
У нас в компании экспресс-загрузка оказалась полезной в таких случаях:
Для описанных выше случаев мы используем одинаковую схему, которую мы назвали «Экспресс загрузка».
Экспресс-загрузка данных: Схема метода
Шаг 0.
Шаг 1.
Шаг 2.
Шаг 3.
Теперь распишу подробнее эти пункты.
Шаг 1. Предоставление данных пользователем и их обработка
Чтобы любой пользователь передавал нам параметры, по которым ему нужно представить данные, нужен удобный интерфейс. Мы делаем этот интерфейс либо на web, либо используя уже имеющиеся программные продукты в компании (так пользователям проще).
Как результат для QlikView готовятся данные в формате txt или csv. Также можно сделать таблицу в какой-либо базе данных, которую будет периодически считывать Qlik.
НА ЗАМЕТКУ! В основном мы используем текстовые файлы с разделителями. Сам файл называется логином того пользователя, который сделал запрос, содержимое файла содержит нужные нам колонки.
Шаг 2. Запуск QlikView с переданными параметрами
Для того чтобы оперативно отследить запрос пользователя мы используем PowerShell.
Если файлы есть, то средствами того же PowerShell мы запускаем нужное приложение или цепочку приложений. Если же файлов нет, т.е. нет запросов от пользователей, то на этом этапе работа скрипта завершается.
Создаём список файлов. Обязательно указать ‘*’ в конце пути.
Копируем все файлы на сервер QlikView
В приложении обязательно включаем лог файл, если во время перезапуска будет ошибка, то соответствующий отчёт придёт сотруднику QlikView. Если же ошибок не будет, то запустится следующее по порядку приложение.
Если для алгоритма есть полная ежедневная перезагрузка, то функция работает следующим образом: делается копия перезапускаемого приложения (к имени добавляется постфикс _express), и именно эта копия перезапускается, соответственно, QlikView генерит отдельный лог файл для этого процесса, который при необходимости можно будет анализировать и лог от ночного обновления не будет перезаписан.
Далее копии экстракторов удаляются, а копия основного приложения остаётся. И пользователь в итоге имеет два приложения: ежедневное со всеми данными и приложение только с запрашиваемыми данными (с постфиксом _express).
Чтобы добавить такой код, используем функцию DocumentName()
Какой код нужно добавить?
a. В первом (возможно единственном) приложении цепочки нужны выгрузки, необходимые для загрузки данные.
Например, так выгружается список товаров, по которым будет запускаться приложение.
b. Далее нужно выгрузить список пользователей, которым по завершении работы алгоритма будет осуществляться рассылка:
Имея логины, при необходимости, можно добавить и другие персональные данные. Поле group генерится, если рассылку делать с помощью NPrinting.
c. Чтобы алгоритм отрабатывал только по нужным товарам, в необходимых местах добавляем условие:
Так как переменная vExpress равна 1 только, когда имя приложения имеет постфикс _express, никаких сбоев при стандартной ночной или утренней перезагрузке уже не будет.
d. Если какая-то часть кода выполняется слишком долго, то при возможности лучше воспользоваться данными ночной выгрузкой, особенно, если эти данные в течение дня не меняются.
e. Все qvd-файлы, полученные при работе экстрактора с постфиксом _express, сохраняются в отдельную папку при добавлении в путь выгрузки переменной.
Переменная vAddPath принимает значение ‘\_Express’ также, когда приложение с постфиксом, соответственно минимизируется возможность некорректного сохранения файла во время ежедневного обновления данных.
При необходимости (в зависимости от алгоритма) можно инкрементально добавить данные из прошлых экспресс-загрузок, чтобы приложение аккумулировало информацию по всем запросам за день. В начале следующего дня данные можно обнулять.
f. Переменные, в примерах выше (vExpress и vAddPath) необходимо обнулить или присвоить им пустое значение в начале скрипта.
g. Для удобства сделать лог, чтобы понимать, кто с какими запросами обычно обращается к этому приложению и обращается ли вообще.
Я постарался описать основные изменения. Ситуация упрощается, если весь алгоритм работает только по запросу, и у нас есть только одно приложение.
Шаг 3. Рассылка об успешной/ не очень успешной загрузке
Если во время работы алгоритма ошибок не было, то пользователю нужно сообщить об успешном обновлении данных. Если же произошла какая-либо ошибка, об этом должен узнать и сотрудник QlikView. Работа скрипта, конечно же, останавливается.
Рассылку можно осуществить двумя способами:
После отправки писем тестовые файлы нужно удалить.
Сам скрипт установить на запуск в планировщике Windows каждые 5 или 10 минут, во время выполнения другая копия запускаться не будет. По умолчанию заданию устанавливается настройка:
Вместо заключения
Метод «Экспресс загрузки» напрашивался давно. Для нас это оказалось очень удобным. Соответственно стандартного механизма нет. Чтобы добавить возможность экспресс загрузки, для каждого алгоритма, нужен индивидуальный подход. Я же постарался описать идею в целом. На данный момент по подобной схеме в компании работает три алгоритма. Для примера: чтобы большей частью адаптировать к этой схеме цепочку из 5 экстракторов и итогового приложения, потребовалось около 3-х дней. В итоге, алгоритм, который при полной перезагрузке (по всем SKU) отрабатывает дольше, чем за 5 часов, в экспресс режиме отрабатывает за 5-10 минут для нескольких товаров.
Надеюсь, информация будет полезной и предложенная идея «Экспресс загрузки» будет использована в вашей профессиональной деятельности.
Выбор и загрузка данных
Загрузка данных из файлов, таких как Microsoft Excel или любых других поддерживаемых форматов файлов, легко выполняется с помощью диалогового окна выбора данных в редакторе загрузки данных.
Выполните следующие действия.
Использование нескольких разделов облегчает сохранять организацию скрипта. Этот раздел скрипта будет выполняться в последовательном порядке выполнения, когда осуществляется загрузка данных.
Скрипт должен выглядеть следующим образом:
Скрипт должен выглядеть следующим образом:
Данные загрузятся в приложение. Откроется окно хода выполнения скрипта. По завершении вы увидите сводку возможных ошибок и синтетических ключей (даже если этого нет).
Верхняя строка скрипта теперь должна выглядеть так:
// Загрузка данных из файла Dates.xlsx
Скрипт должен выглядеть следующим образом:
Измените поле Месяц следующим образом:
Скрипт должен выглядеть следующим образом:
Скрипт для загрузки выбранных данных из файла Dates.xlsx создан. Пора загрузить данные в приложение.
После выбора Загрузить данные данные будут загружены в приложение и скрипт будет сохранен.
Теперь вы можете увидеть, что оба поля Date в двух таблицах связаны.
Соответствующие данные теперь доступны в визуализациях в приложении. Как это делается, мы покажем вам далее в этом учебном пособии.
Qlik Sense ETL & ELT: Основы загрузки данных из систем учета, баз данных, файлов
Опубликовано qliksense-expert в 09.08.2019 09.08.2019
Содержание урока по Qlik Sense
Что такое ETL & ELT? В чем отличие? Как та или иная технология может быть применима в Qlik Sense
Извлечение, загрузка и преобразование (ELT) — это процесс, с помощью которого данные извлекаются из исходной системы, загружаются в хранилище данных, а затем преобразовываются.
E, T, L:
Извлечение, загрузка и преобразование (ELT) — это процесс, с помощью которого данные извлекаются из исходной системы, загружаются в хранилище данных, а затем преобразовываются.
QVD файлы
Qlik Sense имеет собственный формат хранения данных. Данные из разных систем выгружаются и сохраняются в QVD-формате. Этот формат файлов очень быстро загружается в Qlik Sense для дальнейшей обработки. Это как dump базы данных. Выгрузка и загрузка миллионов строк (без доп.условий) занимает секунды.
Иногда можно построить серьезные коммерческие проекты только на QVD файлах.
Эти файлы можно использовать в разных приложениях, за них могут отвечать разные сотрудники и сервисы. Скорость загрузки из таких файлов в десять раз быстрее чем из обычных источников данных. Это позволяет экономить на базе данных и обмениваться информацией между различными приложениями Qlik.
Пример организации ETL-процесса на примере QlikView (в Qlik Sense аналогичные процессы):
Организация ETL на базе Qlik Sense – Вариант 1
Краткая классическая схема ETL-процесса (процесса выгрузки, обработки данных)
Общая схема ETL-процесса в Qlik Sense
На завершающем этапе ETL-процесса данные загружаются в приложение (создается модель данных). Есть различные схемы моделей данных, целевая схема – звезда:
Пример модели данных:
TSEEQ: The Structured ETL Engine for Qlik Sense
TSEEQ – это структурированный движок ETL для Qlik, может быть полезен для вашего проекта. TSEEQ – это механизм ETL, а не процесс; TSEEQ можно использовать для оптимизации любого ETL-процесса, который вы используете в своей реализации Qlik Sense.
Основы проектирования хранилища данных для Qlik Sense
Хранилище данных (в формате QVD-файлов) и Bus Matrix
Хранилище данных Qlik Sense предпочтительно должно соответствовать корпоративной архитектуре данных со стандартными фактами и измерениями, которые могут совместно использоваться в разных моделях. Несмотря на то, что qvd-файлы используются в Qlik Sense, необходимо соблюдать существующие определения данных и управление ими. Любые новые факты, измерения и меры, разработанные с помощью Qlik Sense, должны дополнять архитектуру компании.
1. Создание Bus Matrix хранилища данных:
2. Создание Bus Matrix:
Extract Level: Описание основных элементов скрипта загрузки данных в Qlik Sense
Создание подключения в скрипте Qlik Sense
Для загрузки данных из разных источников совершенно необязательно знать синтаксис и команды Qlik Sense. На первоначальном этапе работы достаточно просто пользоваться мастером генерации скрипта.
Для этого в приложении заходим в редактор скрипта:
Справа нажимаем “Создать новое подключение”:
У Qlik Sense есть различные коннекторы к различным источникам данных. В рамках этого раздела мы создадим только коннектор к директории на диске (где лежат QVD-файлы):
Указываем директорию и название соединения:
Далее в соединении нажимаем на кнопку “Выбрать данные”:
Qlik Sense предложит список файлов для выбора из директории (можно провалиться в поддиректорию/подпапку):
В окне предпросмотра выбираете колонки, которые необходимо загрузить из файла (либо генерируете скрипт и в скрипте удаляете ненужные колонки):
Мы получили сгенерированный скрипт загрузки данных из QVD-файла. Такая цепочка действий выполняется практически для всех источников данных (включая базы данных). Единственное исключение, наверное, это получение данных из API. Но это уже продвинутая разработка, на первом этапе это не потребуется.
Операторы загрузки данных LOAD QVD & SQL SELECT
Для загрузки данных из QVD файлов используется структура:
Для выгрузки данных из базы данных необходимо создать подключение к БД и сформировать скрипт загрузки. Скрипт загрузки может быть составлен несколькими вариантами. Пока что рассмотрим три варианта без подробного разбора.
Вариант 1:
Вариант 2:
Вариант 3:
Загрузка данных из Excel – LOAD FROM XLSX
Пример загрузки данных из CSV – LOAD FROM CSV
Создание таблицы в скрипте – хранимая таблица в скрипте – LOAD * Inline []
Иногда в ходе загрузки данных требуется создать таблицу со значениями в скрипте. Например, для фильтров. Не всегда хочется генерить дополнительный файл XLSX, проще и быстрее сделать все в скрипте. Для этого есть конструкция LOAD * Inline:
Сохранение таблицы в QVD-файл
Transform Level: Описание функций, приемов эффективного преобразования данных
В этом разделе я опишу основные приемы обработки данных в скрипте Qlik Sense. Конечно же в одной статье нереально вместить и описать все случаи и все подходы по обработке данных. Основная цель этого раздела – заложить базис синтаксиса, основных конструкций по обработки данных в скрипте Qlik Sense.
Rename Table / Rename Field / Drop Table(s) / Drop Field(s)
todo
Load * Resident
todo
Загрузка данных по маске From ‘FileName_*.qvd’ (qvd)
todo
Concatenate & NoConcatenate
todo
AutoGenerate
todo
Exists()
todo
SubField()
todo
Match() & WildMatch()
todo
Mapping / ApplyMap
todo
Left Join & Inner Join
todo
For i To NoOfRows / NoOfFields. Peek() & FieldName()
todo
Load While IterNo()
todo
if(). Комбинация Pick() & Match() в скрипте
todo
Агрегация данных Group By. Предрасчет показателей в скрипте
todo
Load Level: Загружаем данные в модель. Основные концепции и подходы по созданию моделей
Краткий инструктаж по написанию скриптов загрузки
В этом разделе будут описаны некие принципы для организации 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 не ограничивается (используйте в меру разумного).
7. Нумерация строк в большой таблице фактов. Если нужно пронумеровать таблицу фактов, которая грузится из нескольких источников, то для начала загружаем все факты в 1 таблицу Qlik Sense, затем перекладываем таблицу саму себя (Load * Resident) с функцией RecNo().
RowNo() для больших таблиц не нужно использовать, т.к. она очень медленно работает.
8. Не создавайте меппинги напрямую из больших QVD файлов. Сначала грузим QVD в таблицу, затем перекладываем с помощью Resident данные в Mapping таблицу. Загрузка данных в Mapping таблицу отключает оптимизированную загрузку данных.
9. Избегайте сложных динамически генерируемых полей, таблиц, кусков кода. Любая динамика усложняет поддержку решения. Особенно это критично, когда один разработчик уходит из команды, код приходится изучать с нуля. Если другого выхода нет, кроме как использование динамики, то делайте генерацию кода, полей и т.п. Если есть простой способ написания кода – используйте его. Унификация и простота – залог облегчения поддержки инфраструктуры, etl-процесса. Не гонитесь за интересными решениями, прокачкой ИТ-навыков – это вредит ИТ-решениям компании (но разработчикам конечно это на пользу). Плюс повышаются косвенные затраты компании, в какой-то момент потребуется вместо 1 разработчика на поддержку использовать 2х разработчиков.
Написание скриптов в редакторе загрузки данных
НА ЭТОЙ СТРАНИЦЕ
Написание скриптов в редакторе загрузки данных
Также данные можно загрузить при помощи диспетчера данных, однако для создания, изменения и запуска скрипта загрузки данных следует использовать редактор загрузки данных.
В скрипте задаются поля и таблицы для загрузки. Скрипты часто используются для определения данных, которые необходимо загружать из источников данных. Можно также манипулировать структурой данных с использованием операторов скрипта.
Во время загрузки данных программа Qlik Sense определяет общие поля из различных таблиц (ключевые поля), чтобы связать данные. Получившуюся структуру данных в приложении можно отслеживать в просмотре модели данных. Структуру данных можно изменить путем переименования полей, чтобы получить другие связи между таблицами.
После загрузки данных в программу Qlik Sense они хранятся в приложении.
Редактор загрузки данных
Редактор скриптов
В редакторе имеется большое количество функций, облегчающих создание скрипта загрузки.
Доступ к справке по синтаксису для команд и функций
Доступ к порталу справки
Подробную справку можно получить на портале Qlik Sense двумя способами.
Поместите курсор внутрь или в конце ключевого слова и нажмите клавиши Ctrl+H.
Использование функции автоматического заполнения
Также для отображения списка ключевых слов можно использовать сочетание клавиш Ctrl+Space, а для отображения всплывающей подсказки — сочетание клавиш Ctrl+Shift+Space.
Комментирование в скрипте
С помощью отметок для комментариев можно вставлять комментарии в код скрипта или деактивировать части кода скрипта. Весь текст в строке справа от знака // (две косые черты) будет считаться комментарием и не будет выполняться во время работы скрипта.