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

Добрый день! 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 – это переменная, хранящая открытую spark-сессию //ниже даем определения таблиц (spark dataframe), которые будем обрабатывать df_tbl1 = spark.table(‘schemaN.tableX’) df_tbl2 = spark.table(‘schemaM.tableY’) //result – это результат соединения df_tbl1 и df_tbl2 через функцию .join result = df_tbl1.alias(‘t1’)\ .join(df_tbl2.alias(‘t2’), col(‘t1.keyX’) == col(‘t2.keyY’)) //далее идут действия над сформированными данными, например, collect, saveAsTable, count, и т. д. resultdata = result.collect()

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

2. Подготовка данных и их загрузка в QS

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

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

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

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

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

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

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

[systems]: select //столбцы таблицы systems system_id, "system", "schema" from app_db_schema.schemas; keep ([systems]) LOAD //синтетический id таблицы, который нужен при формировании определений таблиц AutoNumber([system_id]&[table_id]) as [tbl_synth_id], system_id, table_id, table_name, table_desc ; [tables]: select system_id, table_id, table_name, table_desc from app_db_schema.tables; keep ([tables]) [columns]: select table_id, column_id, column_name, column_desc, column_type from app_db_schema.columns; keep([columns]) [joins_columns]: select column_id, ext_column_id from app_db_schema.joins_ids; keep(joins_columns) [ext_columns]: LOAD column_id as ext_column_id, column_name as ext_column_name, table_id as ext_table_id Resident columns; keep(ext_columns) [ext_tables]: LOAD table_id as ext_table_id, table_name as ext_table_name, table_desc as ext_table_desc, system_id as ext_system_id, tbl_synth_id as ext_tbl_synth_id Resident tables; keep(ext_tables) [ext_systems]: LOAD system_id as ext_system_id, system as ext_system, schema as ext_schema Resident systems;

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

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

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

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

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

3. Реализация функционала инструмента на QS

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

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

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

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

А. Шаг 1: Лист «Выбор основной таблицы».

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

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

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

If(GetSelectedCount([system])>0,1,0)

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

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

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

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

# Описание таблицы df = spark.table(‘название схемы.название таблицы’)

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

vTable =if( GetSelectedCount([table_name])>0, '#' & [table_desc] & chr(10) &'df' &[tbl_synth_id] &' = spark.table(' &Chr(39) &[schema] &'.' &[table_name] &Chr(39) &')' &Chr(10) &Chr(10) ) )

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

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

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

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

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

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

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

Б. Шаг 2: Лист «Выбор дополнительных таблиц для соединения».

Теперь перейдем к реализации второго листа «Выбор дополнительных таблиц для соединения». Суть его функционала заключается в том, что пользователь на листе может выбрать дополнительные таблицы, которые хотел бы соединить с основной. При разработке конструктора я тестировал различные варианты UI для выбора соединений, но остановился на таблице, значения которой кликабельны и доступны для выбора. Визуально это выглядит так:

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

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

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

· Формирование из выбранных таблиц их определений в pyspark и запись в переменную vExtTables.

Формирование определений доп. таблиц происходит аналогично первому листу, за исключением одного фактора – на этом шаге пользователь по своему желанию может выбрать несколько таблиц. Чтобы выражение отрабатывало правильно, дополним его функциями concat() и distinct():

vExtTables = if( GetSelectedCount([ext_table_name])>0, concat(distinct( '#' & [ext_table_desc] & chr(10) &'df' &[ext_tbl_synth_id]) &' = spark.table(' &Chr(39) &[ext_schema] &'.' &[ext_table_name] &Chr(39) &')' &Chr(10) &Chr(10) ), Null() )
Сейчас ты у меня всё напишешь

· Формирование итогового запроса (result) из основной и присоединенной таблицы c соединениями между ними и его запись в переменную vResult.

Чтобы из всех выбранных таблиц (основной + дополнительных) сформировать правильный результирующий запрос, необходимо сформировать строку следующего вида:

result = df_tbl1.alias(‘t1’)\ # основная таблица .join(df_tbl2.alias(‘t2’), col(‘t1.keyX’) == col(‘t2.keyY’))\ # присоединенная таблица .join(df_tbl3.alias(‘t3’), col(‘t1.keyX’) == col(‘t2.keyY’))\ # присоединенная таблица # и так далее.

Исходя из этого, я написал следующее выражение:

vResult = 'result = ' & 'df' & [tbl_synth_id] & '.alias(' & Chr(39) & lower(table_name) & Chr(39) &')' & if(GetSelectedCount(ext_table_name) > 0, '\' &Chr(10) & concat(distinct( '.join(df' & [ext_tbl_synth_id] & '.alias(' & Chr(39) & lower(ext_table_name)& Chr(39) &')' & ', ' & aggr(concat(distinct('col('& Chr(39) & lower(table_name) & '.' & [column_name]& Chr(39) & ') == col(' & Chr(39)& lower(ext_table_name) & '.' & [ext_column_name] & Chr(39) & ')'), CHR(10) &' && '), [ext_table_name]) & ')') , '\'&chr(10)) , Null() )
Сейчас ты у меня всё напишешь

Результирующий запрос для неподготовленного пользователя выглядит сложновато 😊

· Перенос выбранных значений из столбца [ext_tables] в [tables].

Важно перенести выбранные значения из столбца [ext_tables] в [tables], поскольку это упростит реализацию уточнения атрибутного состава на следующем листе. Это можно сделать, задав значения столбца [tables] следующим выражением:

='(' & GetFieldSelections(table_name) & if(GetSelectedCount(ext_table_name)>0, '|' & GetFieldSelections(ext_table_name,'|')) & ')'

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

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

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

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

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

В. Шаг 3: Лист «Итоговый запрос».

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

= vTable & vExtTables & vResult

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

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

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

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

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

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

& if(GetSelectedCount(column_name)>0,'\' & Chr(10) &'.select(' & Chr(10) & concat('col(' & Chr(39) &lower(table_name) & '.' & lower(column_name) & Chr(39) &')'& if(vEnableAliases=1,'.alias('& Chr(39) & [column_desc] & Chr(39) &')'),','&Chr(10)) & Chr(10) & ')' )0
Сейчас ты у меня всё напишешь

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

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

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

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

Заключение

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

1 комментарий

здравствуйте, как можно с вами связаться?

Ответить