Direct Query: как забирать данные в QlikView и Qlik Sense напрямую из Exasol

На qRUG Camp 2017 был доклад о примерах использования Direct Discovery. Там мы напрямую обращались к внешней базе данных через запрос Direct Query и получали детализацию по агрегированным данным, а также выполняли сложные аналитические запросы во внешней базе данных для реализации Market Basket Analysis. Все эти эксперименты мы проделывали с Exasol, самой быстрой аналитической базой данных в мире, по данным теста TPC-H. Сегодня расскажу, как использовать Direct Query, чтобы QlikView и Qlik Sense работали с базой Exasol напрямую, не загружая данные в свою оперативную память.

Direct Discovery: матчасть

Основная функция Direct Discovery – возможность выполнения из QlikView или Qlik Sense запроса к внешней базе данных.

Что дает Direct Discovery:

• объем данных, используемых в запросе Direct Discovery, не ограничен размером используемой QlikView/Qlik Sense оперативной памяти
• cохраняется возможность ассоциативного анализа
• можно использовать в диаграммах измерения и меры, данные которых не загружены в модель данных QlikView/Qlik Sense

Когда Direct Discovery может пригодиться:

• Организация системы on-line мониторинга с постоянным обновлением информации по заданным ключевым показателям в QlikView/Qlik Sense
• Наличие большого объема данных для анализа и отсутствие технической возможности или архитектурного желания загрузить его в модель данных QlikView/Qlik Sense
• Ограниченный размер оперативной памяти для Qlik
• Реализация drill-down – когда нужна справочная или более детальная информация, которая не очень нужна в основных аналитических данных
• Реализация продвинутых аналитических функций, которые сложно или трудоемко реализовать в QlikView/Qlik Sense. Например, MBA (Market Basket Analysis) – анализ сочетаемости групп продуктов.

QlikView Help о Direct Discovery: https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/DirectDiscovery/direct-discovery-introduction.htm

Exasol: матчасть

На сегодняшний день, Exasol – самая быстрая в мире аналитическая база данных, по данным независимого исследования производительности СУБД TPC-H:

• Максимальное кол-во запросов в час при объеме данных 1 Тб: 5’246’338
• В 7 раз больше, чем у ближайшего конкурента (по данным 2017 года)

Основные преимущества Exasol:

• высокая скорость выполнения запросов,
• использование поколоночного хранения данных (columnar DB),
• высокая маштабируемость базы данных Exasol благодаря добавлению в кластер новых узлов,
• автоматическое распределение данных таблиц между узлами кластера,
• использование технологий MPP (Massively parallel processing) –многопоточной параллельной обработки данных,
• минимальная трудоемкость настроек для обеспечения высокой производительности за счет автоматической оптимизации выполняемых запросов и автоматического формирования индексов на основе статистики выполнения запросов,
• наличие бесплатной версии Exasol, позволяющей выполнять аналитические запросы на загруженных в оперативную память данных объемом до 200 ГБ, что эквивалентно 2 Терабайтам данных на жестком диске.

Из действительно впечатляющего: тестировали Exasol на «живых» данных крупной розницы. Вот один из примеров:

Объем данных: 6 млрд строк

Запрос:

• Выделить Топ-10 лучших магазинов,
• В них – Топ-100 самых продаваемых товаров
• Показать все магазины, где эти товары не продаются

Время выполнения запроса: 01 мин 04 сек

Это реально крутое время для такого многослойного запроса на 6 млрд. строк.

Бесплатная версия Exasol – Exasol Community Edition (1 Тб сырых данных, 1 узел, полная функциональность, поддержка в сообществе пользователей): https://www.exasol.com/en/download/

QlikView + Exasol: схема интеграции через Direct Query по шагам

Собственно, теперь рассмотрим схему реализации прямого обращения к внешней базе данных Exasol через запрос Direct Query по шагам.

Предварительные требования:

• Установлена версия QlikView: 12.10 SR7
• Установлена исходная внешняя база данных Exasol: бесплатная версия БД Exasol (6.0.0), работающая на 1-м узле.

В моем случае Exasol установлен на 1-м узле под управлением ОС CentOS,в виртуальной машине на Hyper-v.

• Установлен 64-битный Exasol ODBC-драйвер
• DNS конфигурируется при помощи инструмента Windows Data Source Administrator

Процесс по шагам:

1. Добавляем источник данных

Открываем QlikView -> «File» -> «Edit script». Оказываемся в редакторе загрузки и добавляем новый источник данных ODBC:

1. Пишем скрипт загрузки:

Используем Direct Query, чтобы QlikView обращался к данным в Exasol, а не загружал их к себе в оперативную память.

Вот такой скрипт у меня получился для примера:

Подробности по написанию скрипта при помощи QlikView Direct Discovery даны, как обычно в Help-е: https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/DirectDiscovery/direct-discovery-introduction.htm

1. Загружаем скрипт

Кликаем по «Reload». На этом шаге предрасчитываются отдельные значения для полей измерений и никаких фактических данных не загружается в QlikView

1. Начинаем строить визуализации QlikView на основе больших объемов данных, доступных в Exasol в реальном времени:

Qlik Sense + Exasol: схема интеграции через Direct Query по шагам

Теперь то же самое проделаем с Qlik Sense – прямое обращение к внешней базе данных Exasol через запрос Direct Query по шагам.

Предварительные требования:

• Установлена актуальная версия Qlik Sense: November 2017
• Установлена исходная внешняя база данных Exasol: бесплатная версия БД Exasol (6.0.0), работающая на 1-м узле.

В моем случае Exasol установлен на 1-м узле под управлением ОС CentOS,в виртуальной машине на Hyper-v.

• Установлен 64-битный Exasol ODBC-драйвер
• DNS конфигурируется при помощи инструмента Windows Data Source Administrator

Процесс по шагам:

1. Добавляем источник данных

Открываем Qlik Sense -> «Data Load Editor». Оказываемся в редакторе загрузки и добавляем новый источник данных ODBC:

1. Пишем скрипт загрузки:

Используем Direct Query, чтобы Qlik Sense обращался к данным в Exasol, а не загружал их к себе в оперативную память.

Вот такой скрипт у меня получился для примера:

Подробности по написанию скрипта при помощи Qlik Sense Direct Discovery даны, как обычно в Help-е: http://help.qlik.com/en-US/sense/November2017/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/direct-query.htm

1. Загружаем скрипт

Нажимаем на «Load Data». На этом шаге предрасчитываются отдельные значения для полей измерений и никаких фактических данных не загружается в Qlik Sense:

1. Начинаем строить визуализации Qlik Sense на основе больших объемов данных, доступных в Exasol в реальном времени:

Всем большой аналитики с QlikView, Qlik Sense и Exasol!

P.s. Коллеги, нужна ли отдельная статья по тому, как работает Qlik Direct Discovery или все уже в курсе?