Добрый день! Qlik Sense – это BI-платформа с, казалось бы, очевидной областью применения - разработка аналитических приложений и отчётов на разнообразных данных. Но что, если я скажу вам, что я на нем сделал...конструктор pyspark-запросов?
Заинтересовало? Тогда готовьте чай/кофе и плюшки - и погнали, сейчас все расскажу.
1. Постановка задачи
Для начала обозначу историю вопроса и текущие вводные для лучшего понимания контекста. В нашем подразделении не так давно прошла IT-трансформация, в рамках которой сетевая инфраструктура, инструментарий и процессы работы с ними претерпели значительные изменения. С одной стороны, они стали более прозрачными и систематизированными, а с другой – окно IT-возможностей в моменте сузилось в силу того, что на замену старому привычному инструментарию (SQL + веб-приложения на выделенном сервере) для работы с данными пришёл новый (Hadoop, BI), и мы до сих пор учимся эффективно с ним работать.
На мой взгляд, главным нововведением, затронувшим абсолютное большинство сотрудников, стал отказ от SQL в качестве основного средства работы с данными и переход к Hadoop (Hive, Spark). В связи с гибкостью настройки сессий и большей производительностью запроса приоритет отдавался Spark.
С отказом от использования выделенных серверов для приложений нам тоже пришлось мириться – разработка приложений и фич в классическом сетевом стеке в новых условиях проходила бы слишком долго. Для того, чтобы сохранить скорость разработки было решено попробовать перенести существующие веб-приложения на Qlik Sensе. Тем более, большая часть их функционала покрывалась BI-платформой…но не вся.
Когда веб-приложение предназначено для анализа данных – возможности BI оказывались очень кстати, и позволяли из коробки быстро делать аналитические визуализации. Ситуация меняется, если приложению не нужны возможности BI, и в то же время ему требуется развитая внутренняя логика. Написание развитой логики на BI имеет свои ограничения – отсутствие возможности писать собственные функции, специфическая работа с переменными, фокус на запросах к данным в силу того, что BI заточен именно под анализ данных.
Когда-то мы собрали свою базу знаний на Confluence, ключевым элементом которой были описания БД и витрин данных автоматизированных систем. После перехода на новый целевой стек нашим аналитикам стали доступны реплики этих БД на Hadoop – фактически, они стали работать с данными наравне с дата-инженерами. Здесь и пришло решение облегчить практическое использование накопленных данных, создав конструктор запросов PySpark.
Рассмотрим концепцию построения pyspark-запросов на небольшом примере:
Приведенный выше пример запроса является общим, и не отражает весь функционал spark. Однако, его достаточно для того, чтобы рассмотреть синтаксис запросов и принцип их организации, что позволит лучше понять нашу задачу.
2. Подготовка данных и их загрузка в QS
Исходные данные по описаниям БД, собранные на Confluence, имели следующую структуру:
Для того, чтобы Qlik Sense правильно работал с этими данными и агрегировал их, разобьем эту таблицу на 3 отдельные таблицы – для систем и схем, для таблиц и для столбцов:
Структуру для хранения соединений таблиц можно представить в виде пар идентификаторов столбцов-ключей соединений, каждый из которых можно соединить с таблицей столбцов:
После того, как мы подготовили данные, загрузим их в QS.
Для соединения таблиц я использовал оператор Keep – Qlik Sense связывает таблицы по ключам так же, как и join, но не «склеивает» их в одну. Структура данных остается такой, какой разработчик определяет её в скрипте загрузки. После загрузки таблиц systems, tables и columns я подгружаю таблицу joins_columns – она играет ключевую роль для реализации механики соединения таблиц в запросе. Также для того, чтобы эта механика работала, необходимо комбинацию таблиц columns-tables-systems связать с другой стороны joins_columns – для этого я повторно использую уже загруженные таблицы с помощью оператора Resident (<загруженная ранее таблица>). Особенностью созданных таким образом таблиц является то, что они полностью независимы от таблицы-первоисточника, что не будет искажать выставленные нами фильтры на таблицах.
ВАЖНО: если вы пересоздаете таблицы с помощью Resident – модифицируйте имя таблицы и названия столбцов с помощью, например, суффикса или префикса, как это сделал я – в противном случае велик риск путаницы с ними при настройке визуализации.
Итоговая структура данных в приложении приведена ниже:
Далее приступим к построению отчёта.
3. Реализация функционала инструмента на QS
После того, как я продумал пользовательские сценарии использования конструктора, я вывел следующую структуру построения приложения:
На первом листе, который я назвал «Выбор основной таблицы» пользователь выбирает любую нужную ему систему и основную таблицу, вокруг которой будут строиться доступные джойны. На этом шаге можно выбрать только одну таблицу, т.к в одном запросе все таблицы должны быть связаны между собой. На втором листе «Выбор дополнительных таблиц для соединения» пользователь может обогатить выбранную таблицу дополнительными с помощью списка доступных соединений. На третьем листе «Итоговый запрос» основа запроса уже готова, и пользователь может уточнить атрибутный состав и скопировать готовый запрос к себе.
Теперь давайте реализуем эту концепцию на практике и начнем с наполнения первого листа.
А. Шаг 1: Лист «Выбор основной таблицы».
Выбор систем и таблиц можно легко реализовать с помощью комбинации двух фильтров.
Для выбора таблиц пользователю необходимо выбрать нужную ему систему – в элементах UI Vozlib можно реализовать это правило с помощью условия вычисления:
Где GetSelectedCount – функция, возвращающая количество выбранных значений в столбце, название которого передано как параметр. С помощью значений 1 и 0, которые переданы в функцию IF, показ данных в фильтре либо производится, если в столбце [system] что-то выбрано, либо нет. Отмечу, что в других элементах пользовательского интерфейса, где реализуются подобные ограничения, я использую аналогичный подход.
Для того, чтобы пользователь после выбора основной таблицы мог переходить к следующим шагам, реализуем кнопку «Следующий шаг», в которой пропишем такие действия:
· Преобразование выбранной таблицы в часть запроса (определение таблицы) и запись результата в переменную vTable. Определением таблицы является строка следующего вида:
Определением таблицы является строка следующего вида:
Пока преобразование данных в указанную строку затрагивает единственную выбранную таблицу, его легко осуществить с помощью следующего выражения:
С помощью символа & я соединяю различные символы и строки между собой. Функция Chr() дает возможность подставить символ по его числовому коду, например, Chr(39) – это одинарная кавычка, а Chr(10) – это перенос строки, что позволяет форматировать запрос и сделать его более читаемым.
· Переход на следующий лист
Настройка кнопки на переход к листу предусмотрена базовым функционалом QS, поэтому я не буду останавливаться на ней (описано в официальной документации).
Разработанный лист имеет следующий вид (для повышения дружелюбия присутствуют дополнительные элементы UI вроде предпросмотра столбцов таблицы или кнопки очистки всех фильтров):
Б. Шаг 2: Лист «Выбор дополнительных таблиц для соединения».
Теперь перейдем к реализации второго листа «Выбор дополнительных таблиц для соединения». Суть его функционала заключается в том, что пользователь на листе может выбрать дополнительные таблицы, которые хотел бы соединить с основной. При разработке конструктора я тестировал различные варианты UI для выбора соединений, но остановился на таблице, значения которой кликабельны и доступны для выбора. Визуально это выглядит так:
Логика работы с данным интерфейсом проста: если пользователь видит в этом списке дополнительные таблицы, которые хочет присоединить к ранее выбранной, он должен кликнуть на их названия в последнем столбце, который я для привлечения внимания покрасил в контрастный цвет.
Для того, чтобы из выбранных пользователем таблиц сформировать следующие части запроса, продублируем кнопку «К следующему шагу» по аналогии с первым листом, в котором пропишем следующий функционал:
· Формирование из выбранных таблиц их определений в pyspark и запись в переменную vExtTables.
Формирование определений доп. таблиц происходит аналогично первому листу, за исключением одного фактора – на этом шаге пользователь по своему желанию может выбрать несколько таблиц. Чтобы выражение отрабатывало правильно, дополним его функциями concat() и distinct():
· Формирование итогового запроса (result) из основной и присоединенной таблицы c соединениями между ними и его запись в переменную vResult.
Чтобы из всех выбранных таблиц (основной + дополнительных) сформировать правильный результирующий запрос, необходимо сформировать строку следующего вида:
Исходя из этого, я написал следующее выражение:
Результирующий запрос для неподготовленного пользователя выглядит сложновато 😊
· Перенос выбранных значений из столбца [ext_tables] в [tables].
Важно перенести выбранные значения из столбца [ext_tables] в [tables], поскольку это упростит реализацию уточнения атрибутного состава на следующем листе. Это можно сделать, задав значения столбца [tables] следующим выражением:
='(' & GetFieldSelections(table_name) & if(GetSelectedCount(ext_table_name)>0, '|' & GetFieldSelections(ext_table_name,'|')) & ')'
· Переход на следующий лист.
В итоге у нас получается следующий лист:
В. Шаг 3: Лист «Итоговый запрос».
Перейдем к третьему и последнему листу приложения «Итоговый запрос» - в нем пользователь может при желании детализировать атрибутный состав запроса и скопировать его к себе. Для формирования итогового запроса на листе достаточно создать текстовое поле, в котором мы просто соединяем собранные переменные между собой:
Возвращаемый пользователю результат – готовый запрос, который выглядит так:
Единственное, что остаётся реализовать – уточнение атрибутного состава запроса, которое несложно реализовать:
1. Столбцы всех выбранных пользователем таблиц отображаем в табличном представлении.
2. Добавляем в выражение выше следующее дополнение, которое приписывает выбранные столбцы в конец запроса:
Итоговое выражение выглядит следующим образом:
А весь лист выглядит так:
Заключение
В итоге у нас получился инструмент, который генерирует запросы как из одной, так и нескольких таблиц, даёт пользователю возможность уточнить атрибутный состав запроса, и всё это на Qlik Sense. А какие необычные приложения на Qlik Sense доводилось делать Вам?