Сейчас ты у меня всё напишешь

Добрый день! Qlik Sense – это BI-платформа с, казалось бы, очевидной областью применения - разработка аналитических приложений и отчётов на разнообразных данных. Но что, если я скажу вам, что я на нем сделал...конструктор pyspark-запросов?

Заинтересовало? Тогда готовьте чай/кофе и плюшки - и погнали, сейчас все расскажу.

Для начала обозначу историю вопроса и текущие вводные для лучшего понимания контекста. В нашем подразделении не так давно прошла IT-трансформация, в рамках которой сетевая инфраструктура, инструментарий и процессы работы с ними претерпели значительные изменения. С одной стороны, они стали более прозрачными и систематизированными, а с другой – окно IT-возможностей в моменте сузилось в силу того, что на замену старому привычному инструментарию (SQL + веб-приложения на выделенном сервере) для работы с данными пришёл новый (Hadoop, BI), и мы до сих пор учимся эффективно с ним работать.

На мой взгляд, главным нововведением, затронувшим абсолютное большинство сотрудников, стал отказ от SQL в качестве основного средства работы с данными и переход к Hadoop (Hive, Spark). В связи с гибкостью настройки сессий и большей производительностью запроса приоритет отдавался Spark.

С отказом от использования выделенных серверов для приложений нам тоже пришлось мириться – разработка приложений и фич в классическом сетевом стеке в новых условиях проходила бы слишком долго. Для того, чтобы сохранить скорость разработки было решено попробовать перенести существующие веб-приложения на Qlik Sensе. Тем более, большая часть их функционала покрывалась BI-платформой…но не вся.

Когда веб-приложение предназначено для анализа данных – возможности BI оказывались очень кстати, и позволяли из коробки быстро делать аналитические визуализации. Ситуация меняется, если приложению не нужны возможности BI, и в то же время ему требуется развитая внутренняя логика. Написание развитой логики на BI имеет свои ограничения – отсутствие возможности писать собственные функции, специфическая работа с переменными, фокус на запросах к данным в силу того, что BI заточен именно под анализ данных.

Когда-то мы собрали свою базу знаний на Confluence, ключевым элементом которой были описания БД и витрин данных автоматизированных систем. После перехода на новый целевой стек нашим аналитикам стали доступны реплики этих БД на Hadoop – фактически, они стали работать с данными наравне с дата-инженерами. Здесь и пришло решение облегчить практическое использование накопленных данных, создав конструктор запросов PySpark.

Рассмотрим концепцию построения pyspark-запросов на небольшом примере:

Приведенный выше пример запроса является общим, и не отражает весь функционал spark. Однако, его достаточно для того, чтобы рассмотреть синтаксис запросов и принцип их организации, что позволит лучше понять нашу задачу.

Исходные данные по описаниям БД, собранные на Confluence, имели следующую структуру:

Для того, чтобы Qlik Sense правильно работал с этими данными и агрегировал их, разобьем эту таблицу на 3 отдельные таблицы – для систем и схем, для таблиц и для столбцов:

Структуру для хранения соединений таблиц можно представить в виде пар идентификаторов столбцов-ключей соединений, каждый из которых можно соединить с таблицей столбцов:

После того, как мы подготовили данные, загрузим их в QS.

Для соединения таблиц я использовал оператор Keep – Qlik Sense связывает таблицы по ключам так же, как и join, но не «склеивает» их в одну. Структура данных остается такой, какой разработчик определяет её в скрипте загрузки. После загрузки таблиц systems, tables и columns я подгружаю таблицу joins_columns – она играет ключевую роль для реализации механики соединения таблиц в запросе. Также для того, чтобы эта механика работала, необходимо комбинацию таблиц columns-tables-systems связать с другой стороны joins_columns – для этого я повторно использую уже загруженные таблицы с помощью оператора Resident (<загруженная ранее таблица>). Особенностью созданных таким образом таблиц является то, что они полностью независимы от таблицы-первоисточника, что не будет искажать выставленные нами фильтры на таблицах.

ВАЖНО: если вы пересоздаете таблицы с помощью Resident – модифицируйте имя таблицы и названия столбцов с помощью, например, суффикса или префикса, как это сделал я – в противном случае велик риск путаницы с ними при настройке визуализации.

Итоговая структура данных в приложении приведена ниже:

Далее приступим к построению отчёта.

После того, как я продумал пользовательские сценарии использования конструктора, я вывел следующую структуру построения приложения:

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

Теперь давайте реализуем эту концепцию на практике и начнем с наполнения первого листа.

Выбор систем и таблиц можно легко реализовать с помощью комбинации двух фильтров.

Для выбора таблиц пользователю необходимо выбрать нужную ему систему – в элементах UI Vozlib можно реализовать это правило с помощью условия вычисления:

Где GetSelectedCount – функция, возвращающая количество выбранных значений в столбце, название которого передано как параметр. С помощью значений 1 и 0, которые переданы в функцию IF, показ данных в фильтре либо производится, если в столбце [system] что-то выбрано, либо нет. Отмечу, что в других элементах пользовательского интерфейса, где реализуются подобные ограничения, я использую аналогичный подход.

Для того, чтобы пользователь после выбора основной таблицы мог переходить к следующим шагам, реализуем кнопку «Следующий шаг», в которой пропишем такие действия:

· Преобразование выбранной таблицы в часть запроса (определение таблицы) и запись результата в переменную vTable. Определением таблицы является строка следующего вида:

Определением таблицы является строка следующего вида:

Пока преобразование данных в указанную строку затрагивает единственную выбранную таблицу, его легко осуществить с помощью следующего выражения:

С помощью символа & я соединяю различные символы и строки между собой. Функция Chr() дает возможность подставить символ по его числовому коду, например, Chr(39) – это одинарная кавычка, а Chr(10) – это перенос строки, что позволяет форматировать запрос и сделать его более читаемым.

· Переход на следующий лист

Настройка кнопки на переход к листу предусмотрена базовым функционалом QS, поэтому я не буду останавливаться на ней (описано в официальной документации).

Разработанный лист имеет следующий вид (для повышения дружелюбия присутствуют дополнительные элементы UI вроде предпросмотра столбцов таблицы или кнопки очистки всех фильтров):

Теперь перейдем к реализации второго листа «Выбор дополнительных таблиц для соединения». Суть его функционала заключается в том, что пользователь на листе может выбрать дополнительные таблицы, которые хотел бы соединить с основной. При разработке конструктора я тестировал различные варианты 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,'|')) & ')'

· Переход на следующий лист.

В итоге у нас получается следующий лист:

Перейдем к третьему и последнему листу приложения «Итоговый запрос» - в нем пользователь может при желании детализировать атрибутный состав запроса и скопировать его к себе. Для формирования итогового запроса на листе достаточно создать текстовое поле, в котором мы просто соединяем собранные переменные между собой:

Возвращаемый пользователю результат – готовый запрос, который выглядит так:

Единственное, что остаётся реализовать – уточнение атрибутного состава запроса, которое несложно реализовать:

1. Столбцы всех выбранных пользователем таблиц отображаем в табличном представлении.

2. Добавляем в выражение выше следующее дополнение, которое приписывает выбранные столбцы в конец запроса:

Итоговое выражение выглядит следующим образом:

А весь лист выглядит так:

В итоге у нас получился инструмент, который генерирует запросы как из одной, так и нескольких таблиц, даёт пользователю возможность уточнить атрибутный состав запроса, и всё это на Qlik Sense. А какие необычные приложения на Qlik Sense доводилось делать Вам?