Данная статья берет начало из моего комментария к ранее опубликованной на этом блоге статье «Группировка данных в Qlik: основы» http://blog.atkcg.ru/gruppirovka-dannyx-v-qlik-osnovy/. Все-таки накопленный опыт по этому вопросу вполне заслуживает полноценной статьи, к чтению которой вы и приступаете.
Самое важное, с чего нужно начинать разговор: механизм агрегации данных Group By на уровне выполнения скрипта при создании модели данных разительно отличается от механизма агрегации в вычислениях Expressions в чартах на уровне GUI на уже «собранной» ассоциативной модели. Первый (в скрипте) — намного менее производительный и более ресурсоемкий.
Так, если поставить эксперимент и запустить кейс, подобный описанному в ранее указанной статье, на существенном объеме данных (от десятка миллионов строк), можно с помощью Диспетчера Задач Windows понаблюдать, что загрузка CPU будет лишь на одно ядро, потребление RAM подскочит, и процесс займет несколько минут или более. Если же загрузить в ассоциативную модель данные без Group By, и в GUI сделать чарт с аналогичными измерениями и выражениями Sum, то загрузка CPU будет полная и результат будет рассчитан в секунды!
Озвученное утверждение приводит к следующему выводу: использовать Group By на больших объемах данных нужно только в том случае, если вы четко уверены, что положительный эффект (сокращение объема данных в ассоциативной модели, потеря аналитической детализации) перевесит отрицательные эффекты (значительное увеличение времени выполнения скрипта, значительный рост ресурсоемкости на этапе перезагрузки данных).
Qlik выгодно выделяется именно тем, что позволяет загружать в модель все данные на исходном уровне детализации (#seethewholestory). Его укрупнение без необходимости — противоречит как Qlik best practice, так и общепринятой концепции Kimball’s dimensional modelling.
Так что перед тем, как начинать оптимизацию моделей методом Group By, попробуйте проанализировать ваши QVW Document Analyzer’ом — возможно, удаление лишних полей и уменьшение кардинальности и оптимизация ключей принесет бОльший эффект меньшими затратами.
Советы по оптимальному применению Group By
Если Group By большой таблицы все же необходим (всевозможные построчные расчеты, ABC-классификации в скрипте и т.п.), то примените следующие лучшие практики:
1. Контринтуитивный, но проверяемый факт: Group By на резидентной таблице работает со всеми ее полями, а не только с перечисленными! Поэтому, перед тем как делать Group By на широкой таблице, создайте сначала временную резидентную, куда загрузите те и только те поля, которые будете использовать в LOAD … Group By. Ресурсоемкость при таком подходе значительно снизится.
Реализация в коде:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
/* исходная широкая таблица фактов */ [Fact table]: LOAD /* ключи измерений */ [Dim Key 1], [Dim Key 2], [Dim Key 3], [Dim Key 4], /* поля мер */ [Measure 1], [Measure 2], [Measure 3] From [...] ; /* временная таблица с полями для группировки */ [tmp.table]: NoConcatenate LOAD [Dim Key 1], [Measure 1] Resident [Fact table] ; /* выполнение группировки в отдельной таблице */ [Groupped Table]: LOAD [Dim Key 1], Sum( [Measure 1] ) Resident [tmp.table] ; DROP Table [tmp.table] ; |
2. Во многих случаях Group By с партиционированием/ секционированием будет быстрее, чем без него: цикл с 12 итерациями Group By каждого месяца и конкатенация результатов будет быстрее и менее ресурсоемок, чем Group By целого года. На qRUG Day 2015 рассказывалось об этом, вот ссылка на пример.
Некоторые WorkAround’ы
В некоторых условиях вместо Group By можно найти другой, более оптимальный путь решения задачи предварительной агрегации данных.
Если Group By вам был нужен лишь для того, чтобы подготовить агрегированные данные для сохранения в файл и передачи далее по цепочке ETL — попробуйте использовать для этой задачи NPrinting! Вот в этой статье блога я рассказал о таком подходе, 3-й раздел: http://blog.atkcg.ru/qlikview-kak-istochnik-dannyx-dlya-biznes-prilozhenij-i-it-sistem/
Также можно посмотреть в сторону специализированных ETL-инструментов в интеграции с Qlik’ом, начиная от практически бесплатной и простой утилиты EasyMorph и до enterprise-софта Alteryx.
Другие интересные моменты
Экспериментально проверено, что предварительный Order By никак не влияет на производительность Group By, так что не стоит тратить ресурсы.
Хенрик Кронстром (HIC), отвечая на задаваемые ему из зала вопросы на qRUG Camp 2016, пояснил, что:
- Group By действительно не является оптимальным способом агрегации данных, и дословно, «старайтесь не использовать его»;
- каких-либо изменений в этот механизм Qlik пока вносить не планирует;
- однако в связи с тем, что новый движок QIX Engine, доступный начиная с QlikView 12, теперь основан на Columnar технологии в отличие от Row Based QlikView 11, производительность Group By в QlikView 12 должна возрасти по сравнению с QlikView 11. Подробнее см. презентацию Хенрика «The QIX Engine — under the hood».
Напоследок, еще раз: следите за ресурсоемкостью! Непродуманный Group By может забрать всю память и повесить систему не хуже, чем непродуманный Join!
Свежие комментарии