Выстраивание коммуникаций между брендами и людьми — то, чем мы в Dentsu Aegis Network занимаемся каждый день, и неотъемлемой частью этой работы является анализ данных. В ряде случаев этот процесс не требует data science (хотя и он у нас есть), тогда мы используем BI платформу Tableau. Ее основная цель — дать нашим сотрудникам и клиентам удобный интерфейс для потребления данных без написания скриптов, SQL запросов и т.п.
В этой статье мы расскажем, как нам удалось решить проблему взаимодействия Tableau с ClickHouse.
Перед нами стояла классическая задача. У нас есть люди. Они любят фрукты. Какие-то люди любят один фрукт, какие-то любят все фрукты, а остальные могут любить любое сочетание фруктов.
Итак, необходимо дать возможность пользователю в дашборде, построенном в Tableau, произвольным образом выбирать несколько фруктов и смотреть, сколько людей любят хотя бы один фрукт из набора. У нас конечно были не фрукты, но люди были настоящие, просто на “фруктах” легче понять задачу.
Объем данных в нашем случае достаточно большой. Разных “фруктов” было 13 тысяч. У самого популярного “фрукта” было почти 34 миллиона поклонников. В среднем каждый “фрукт” любят 450 тысяч человек. Всего любителей фруктов — 282 миллиона.
Так получилось, что данные для этой задачи у нас лежали в PostgreSQL (PG) и в ClickHouse (CH). В PG была таблица-справочник по “фруктам”, в CH — большая таблица со структурой: идентификатор “фрукта” и идентификатор человека, который этот “фрукт” любит. Нативного коннектора для CH в Tableau нет, а перекладывать данные куда-то ещё не хотелось, потому что это потребовало бы серьезных переработок существующей системы.
Попробовали подключить Tableau к CH с помощью ODBC драйвера и посмотреть, что получится.
Дальше мы решили использовать sampling внутри запроса к базе CH, то есть делать нашу оценку количества любителей выбранного сочетания “фруктов” не на всех людях, а на пятипроцентной выборке, чтобы сделать экстракт меньше. Плюс, мы сразу сделали inner join выборки из CH со справочником “фруктов” из PG, чтобы получить имена “фруктов”. Это помогло — наш экстракт смог сгенерироваться за 5 часов.
Обновлять данные в дашборде нам нужно было раз в день, поэтому 5 часов обновления экстракта вроде бы ничего страшного — будем обновлять ночью. Но в будущем нам бы потребовались дополнительные мощности: “фруктов” должно было становиться больше, соответственно, количество и размер групп людей, пересечение которых нам нужно было вычислять, тоже должно было увеличиться. Поэтому долгое обновление экстракта — это совсем не наш вариант.
Кроме этого, из-за сэмплинга появилась и другая проблема. Получилось так, что в разных частях дашборда цифры, которые должны по идее совпадать, у нас отличались. Это происходило из-за того, что в одном месте мы считали количество любителей одного фрукта точно, а в части с сочетанием фруктов — неточно. Такой результат не понравился ни нам, ни нашим пользователям.
Тогда мы решили не создавать экстракт вообще. Чтобы избежать загрузки огромного объема данных, разделили датасеты и для CH использовали live connection. Между датасетами устанавливали связь посредством встроенного функционала Tableau Edit relationship. Датасорс PG делали основным и связывали его с CH, как со вторичным, с помощью идентификатора “фрукта”, который был в обеих таблицах.
Таким образом, мы получили возможность фильтровать вторичный датасорс с помощью основного (Data blending). Но нас ожидала неудача, так как после пробрасывания фильтра из одного датасорса в другой нам надо было применить функцию подсчета людей в получившемся подмножестве (COUNTD), а у Data blending есть ограничение, которое просто не дает это делать. Такая функция напрямую при таком соединении данных не работает в принципе.
Существует workaround, который помогает обходить это ограничение Tableau, но его можно использовать на сравнительно небольших массивах данных, что явно не относится к нашему случаю.
После этого мы попробовали еще один вариант. Датасеты все еще были разделены и для CH использовали live connection. Здесь фильтр из датасета с описанием “фруктов” в датасет с любителями “фруктов” прокидывали внутри Tableau уже с использованием set action. Но этот вариант в итоге не подошел из-за неудобного UI. Пользователю вместо привычного ему фильтра пришлось бы смотреть весь список и выбирать “фрукты” через cntrl + click, при этом отсутствовала функция apply, когда все выбранные значения применяются разом.
В итоге после всех наших попыток нам пришлось вернуться к варианту с экстрактом и сэмплированием, ужасно медленному и дающему только приближенный ответ.
Очевидно же, что не нужно нам тянуть в экстракт Tableau все данные. Пользователю неудобно видеть все данные сразу — количество людей, любящих все “фрукты”. Ему нужен набор в среднем из 10 “фруктов”. Жаль, что Tableau так делать не умеет.
В нашей команде есть ребята, пишущие на Python. Поэтому мы решили в наших поисках двигаться в этом направлении и нашли TabPy.
TabPy — это веб-сервис, который позволяет внутри калькуляции в Tableau получать результат выполнения Python скриптов.
Как это работает:
В Script Functions аргументы всегда передаются в виде массивов, результат тоже возвращается массивом.
Не все сразу заработало. Главное, что мы поняли: писать Python скрипт прямо в вычисляемом поле в Tableau — не слишком хорошая идея. По двум причинам:
Поэтому использовали еще одну штуку — TabPy Client.
TabPy Client — это библиотека, которая позволяет публиковать питоновские скрипты на сервере TabPy и дальше вызывать их внутри Tableau. При ее использовании вместо того, чтобы писать скрипт внутри Tableau, мы вызываем лежащий на TabPy сервере файл .py с помощью прописанных в нем самом параметров, передаем в него аргументы и выполняем.
Такой подход решил наши проблемы использования TabPy и Tableau. Скрипт пишется и тестируется в привычной среде разработки и хранится отдельно от книжки, которой теперь не требуется ручная поддержка.
Для решения нашей конкретной задачи пришлось сделать следующее.
Сначала мы пробовали решить ее без использования TabPy Client. В таком варианте внутри Tableаu создавался Calculation field следующего вида:
Работало, но были проблемы, которые описывались выше. Когда мы разобрались с TabPy Client, то поняли, что разделив Calculation field и сам скрипт, получается более удобная и правильная система. Так выглядели Calculation field и файл .py со скриптом:
Здесь видно, что 'people_count_test' — это идентификатор для TabPy Client, благодаря которому понятно какой скрипт выполнять в этом Calculation field.
И в итоге именно такой подход нас полностью устроил.
Пользователи довольны, потому что могут произвольно выбирать сочетание “фруктов” и быстро получать количество любителей хотя бы одного из них, а цифры в разных частях дашборда совпадают.
BI разработчики довольны, что можно работать с ClickHouse из Tableau, при этом не подключаясь к нему напрямую.
Наш Tableau Server доволен, что не нужно по ночам делать огромный экстракт.
В целом, TabPy даёт BI разработчикам больше свободы для работы с данными, когда из коробки Tableau не имеет подходящего решения. Например, чтобы встраивать data science модели прямо в Tableau, но это уже совсем другая история…
Статья написана совместно с моими коллегами Димитрием Щербенко (dima_vs) и Суховеевым Иваном (suho_v) R&D Dentsu Aegis Network Russia.
В этой статье мы расскажем, как нам удалось решить проблему взаимодействия Tableau с ClickHouse.
Общая формулировка задачи
Перед нами стояла классическая задача. У нас есть люди. Они любят фрукты. Какие-то люди любят один фрукт, какие-то любят все фрукты, а остальные могут любить любое сочетание фруктов.
Итак, необходимо дать возможность пользователю в дашборде, построенном в Tableau, произвольным образом выбирать несколько фруктов и смотреть, сколько людей любят хотя бы один фрукт из набора. У нас конечно были не фрукты, но люди были настоящие, просто на “фруктах” легче понять задачу.
Объем данных в нашем случае достаточно большой. Разных “фруктов” было 13 тысяч. У самого популярного “фрукта” было почти 34 миллиона поклонников. В среднем каждый “фрукт” любят 450 тысяч человек. Всего любителей фруктов — 282 миллиона.
Первое решение “в лоб”
Так получилось, что данные для этой задачи у нас лежали в PostgreSQL (PG) и в ClickHouse (CH). В PG была таблица-справочник по “фруктам”, в CH — большая таблица со структурой: идентификатор “фрукта” и идентификатор человека, который этот “фрукт” любит. Нативного коннектора для CH в Tableau нет, а перекладывать данные куда-то ещё не хотелось, потому что это потребовало бы серьезных переработок существующей системы.
Попробовали подключить Tableau к CH с помощью ODBC драйвера и посмотреть, что получится.
- Hе все ODBC драйверы одинаково полезны. Нужна определённая версия, в которой работает нужная часть функционала, но нет гарантии, что остальная часть будет работать, если вдруг она вам понадобится.
- Затянуть в экстракт Tableau все данные нам так и не удалось, потому что это 13 000 * 450 000 = 5 850 000 000 записей.
Дальше мы решили использовать sampling внутри запроса к базе CH, то есть делать нашу оценку количества любителей выбранного сочетания “фруктов” не на всех людях, а на пятипроцентной выборке, чтобы сделать экстракт меньше. Плюс, мы сразу сделали inner join выборки из CH со справочником “фруктов” из PG, чтобы получить имена “фруктов”. Это помогло — наш экстракт смог сгенерироваться за 5 часов.
Обновлять данные в дашборде нам нужно было раз в день, поэтому 5 часов обновления экстракта вроде бы ничего страшного — будем обновлять ночью. Но в будущем нам бы потребовались дополнительные мощности: “фруктов” должно было становиться больше, соответственно, количество и размер групп людей, пересечение которых нам нужно было вычислять, тоже должно было увеличиться. Поэтому долгое обновление экстракта — это совсем не наш вариант.
Кроме этого, из-за сэмплинга появилась и другая проблема. Получилось так, что в разных частях дашборда цифры, которые должны по идее совпадать, у нас отличались. Это происходило из-за того, что в одном месте мы считали количество любителей одного фрукта точно, а в части с сочетанием фруктов — неточно. Такой результат не понравился ни нам, ни нашим пользователям.
Тогда мы решили не создавать экстракт вообще. Чтобы избежать загрузки огромного объема данных, разделили датасеты и для CH использовали live connection. Между датасетами устанавливали связь посредством встроенного функционала Tableau Edit relationship. Датасорс PG делали основным и связывали его с CH, как со вторичным, с помощью идентификатора “фрукта”, который был в обеих таблицах.
Таким образом, мы получили возможность фильтровать вторичный датасорс с помощью основного (Data blending). Но нас ожидала неудача, так как после пробрасывания фильтра из одного датасорса в другой нам надо было применить функцию подсчета людей в получившемся подмножестве (COUNTD), а у Data blending есть ограничение, которое просто не дает это делать. Такая функция напрямую при таком соединении данных не работает в принципе.
Существует workaround, который помогает обходить это ограничение Tableau, но его можно использовать на сравнительно небольших массивах данных, что явно не относится к нашему случаю.
После этого мы попробовали еще один вариант. Датасеты все еще были разделены и для CH использовали live connection. Здесь фильтр из датасета с описанием “фруктов” в датасет с любителями “фруктов” прокидывали внутри Tableau уже с использованием set action. Но этот вариант в итоге не подошел из-за неудобного UI. Пользователю вместо привычного ему фильтра пришлось бы смотреть весь список и выбирать “фрукты” через cntrl + click, при этом отсутствовала функция apply, когда все выбранные значения применяются разом.
В итоге после всех наших попыток нам пришлось вернуться к варианту с экстрактом и сэмплированием, ужасно медленному и дающему только приближенный ответ.
Найденное решение
Очевидно же, что не нужно нам тянуть в экстракт Tableau все данные. Пользователю неудобно видеть все данные сразу — количество людей, любящих все “фрукты”. Ему нужен набор в среднем из 10 “фруктов”. Жаль, что Tableau так делать не умеет.
В нашей команде есть ребята, пишущие на Python. Поэтому мы решили в наших поисках двигаться в этом направлении и нашли TabPy.
TabPy — это веб-сервис, который позволяет внутри калькуляции в Tableau получать результат выполнения Python скриптов.
Как это работает:
- Tableau взаимодействует с TabPy и, в свою очередь, с Python при помощи так называемых Script Functions. Script Functions содержат в себе сам скрипт на языке Python, требуемый тип данных результата и аргументы, которые мы в эту функцию передаем. В нашем случае аргументами были идентификаторы “фруктов”, количество любителей которых мы хотели посчитать.
- TabPy преобразует полученный текст Script Functions в скрипт и отдаёт его интерпретатору. Подключение к базе CH прописывалось нами внутри скрипта.
- Далее TabPy возвращает обратно в Tableau результат выполненного скрипта.
В Script Functions аргументы всегда передаются в виде массивов, результат тоже возвращается массивом.
Не все сразу заработало. Главное, что мы поняли: писать Python скрипт прямо в вычисляемом поле в Tableau — не слишком хорошая идея. По двум причинам:
- Внутри Script Functions иногда сложно использовать привычный синтаксис Python. Например, не воспринимаются множественные кавычки.
- Подумав о будущей поддержке дашборда, мы поняли, что если нам потребуется как-то поменять скрипт, то каждый раз придется менять его в самой книжке Tableau. А это явно не оптимальный путь, поскольку мы всеми силами пытаемся избегать ручной поддержки дашбордов.
Поэтому использовали еще одну штуку — TabPy Client.
TabPy Client — это библиотека, которая позволяет публиковать питоновские скрипты на сервере TabPy и дальше вызывать их внутри Tableau. При ее использовании вместо того, чтобы писать скрипт внутри Tableau, мы вызываем лежащий на TabPy сервере файл .py с помощью прописанных в нем самом параметров, передаем в него аргументы и выполняем.
Такой подход решил наши проблемы использования TabPy и Tableau. Скрипт пишется и тестируется в привычной среде разработки и хранится отдельно от книжки, которой теперь не требуется ручная поддержка.
Для решения нашей конкретной задачи пришлось сделать следующее.
Сначала мы пробовали решить ее без использования TabPy Client. В таком варианте внутри Tableаu создавался Calculation field следующего вида:
| IF FIRST()==0 THEN SCRIPT_INT(" from clickhouse_driver import Client client = Client(host=host_name, database=database_name, user=user_name, password=password) -----script_text----- ", SUM([people]), ATTR([fruits_id])) END |
| Calculation field | SCRIPT_INT(" return tabpy.query('people_count_test',_arg1, _arg2)['response'] ", SUM([people]), ATTR([fruits_id])) |
| Файл .py | from clickhouse_driver import Client import tabpy_client connection = tabpy_client.Client('http://localhost:9004/') def unique_people_count(people, fruits_id): client = Client(host=host_name, database=database_name, user=user_name, password=password) -----script_text----- connection.deploy('people_count_test', unique_people_count, 'comment', override = True) |
И в итоге именно такой подход нас полностью устроил.
Итог
Пользователи довольны, потому что могут произвольно выбирать сочетание “фруктов” и быстро получать количество любителей хотя бы одного из них, а цифры в разных частях дашборда совпадают.
BI разработчики довольны, что можно работать с ClickHouse из Tableau, при этом не подключаясь к нему напрямую.
Наш Tableau Server доволен, что не нужно по ночам делать огромный экстракт.
В целом, TabPy даёт BI разработчикам больше свободы для работы с данными, когда из коробки Tableau не имеет подходящего решения. Например, чтобы встраивать data science модели прямо в Tableau, но это уже совсем другая история…
Статья написана совместно с моими коллегами Димитрием Щербенко (dima_vs) и Суховеевым Иваном (suho_v) R&D Dentsu Aegis Network Russia.