Аналитика в Yandex DataLens за пределами CSV: начало работы с PostgreSQL

У новых пользователей DataLens разные потребности: от использования дашбордов вместо презентаций в PowerPoint до полноценного мониторинга и бизнес-аналитики.

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

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

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

Вы также можете воспользоваться сервисами Yandex Cloud, где уже есть DataLens и настроенный платёжный аккаунт. Если аккаунта ещё нет, его можно завести, перейдя по ссылке.

Для развёртывания систем управления базами данных в Yandex Cloud существуют managed сервисы. Одна из самых распространённых систем — PostgreSQL. В ней проще найти специалистов и примеры различных решений. При этом, если объёмы данных не достигают миллионов записей, можно обойтись достаточно скромной конфигурацией Managed Service for PostgreSQL.

Выполним 8 шагов по развёртыванию системы и демонстрации простого примера.

1. Развёртывание базы данных1. Развёртывание базы данных

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

Большинство настроек можно оставить как есть, но обязательно включить публичный доступ и доступ из DataLens.

Если вы не хотите писать команды в командной строке, изучать язык запросов и не питаете симпатий к универсальным утилитам, после установки базы данных можно скачать и установить pgAdmin. В его дружелюбном интерфейсе вы сможете создавать таблицы и загружать данные из CSV.

2. Настройка подключения к базе данных2. Настройка подключения к базе данных

Шаг второй. Для начала надо настроить подключение к созданной базе данных, используя данные из меню «Подключиться» созданного кластера.

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

mkdir $HOME\AppData\Roaming\postgresql; `
curl.exe -o $HOME\AppData\Roaming\postgresql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem

Пример подключения подойдёт любой, из него берутся необходимые параметры.

В pgAdmin, соответственно, создаём подключение:

Указываем параметры из окна сервиса и путь к созданному сертификату.

3. Создание таблиц3. Создание таблиц

Шаг третий. Теперь всё готово для создания таблицы в базе и загрузки в неё данных. Если знаете их структуру, раскройте дерево объектов и создайте таблицу под них. Например, под такие данные:

Создаём таблицу:

Для большинства данных понадобятся следующие типы: даты — date, целые числа — bigint или integer, строки — character varying или varchar, дробные числа — double precision. Те колонки, которые не могут быть пустыми, отмечаются селектором Not NULL. Во избежание трудностей при загрузке данных из CSV даты нужно представлять в формате YYYY-MM-DD, а в качестве десятичного знака использовать точку.

4. Импорт данных4. Импорт данных

Шаг четвёртый. После создания таблицы можно приступать к загрузке в неё данных из CSV:

В настройках импорта, возможно, потребуется указать кодировку (WIN1251 для Windows), наличие в файле строки с заголовками полей и символ — разделитель полей (по умолчанию обычно tab). Также можно выбрать конкретные поля для загрузки.

В результате данные оказываются в базе. Новые данные можно добавлять таким же образом по мере поступления. Обратите внимание, что рассматривается простой путь, без вопросов уникальности и целостности данных.

5. Создание таблицы с дополнительными данными5. Создание таблицы с дополнительными данными

Пятый шаг — совмещение данных для последующей визуализации. Это могут быть как значения плана для сравнения с фактическими значениями, так и справочные данные, упрощающие трактовку. В примере, который мы рассматриваем, не помешают данные с расшифровкой позиций. Их можно загрузить так же, как и данные о продажах, или ввести вручную (для чего создать ещё одну таблицу).

Для вставки строк нужно выбрать соответствующий скрипт, заполнить в нём значения на месте знаков вопроса и выполнить. Редактировать вставленные значения можно будет прямо в табличном виде, который до этого мы использовали для просмотра результатов загрузки.

6. Создание представления в базе данных6. Создание представления в базе данных

Шаг шестой. Можно переходить к созданию подключения в DataLens. Но в ряде случаев лучше изменить представление данных в самой базе и для построения диаграмм использовать уже подготовленные данные.

Бывает целесообразно выполнить часть агрегаций или вычислений, а также объединение данных из нескольких таблиц. Отдельные операции над данными удобнее выполнять в базе данных — у некоторых из них может не быть аналогов в DataLens.

Для объединения данных о продажах со справочником товаров свяжем таблицы в представление. Тут придётся написать простой скрипт, но основу для него можно получить, если выбрать SELECT Script в меню таблицы.

Для создания представления нужно перейти к соответствующим объектам в дереве, задать имя, а на вкладке кода ввести следующий скрипт:

SELECT s.dt, s.source, s.item, s.price, s.volume, i.name
  FROM sales s,items i
WHERE s.item = i.id

7. Подключение DataLens к базе данных7. Подключение DataLens к базе данных

Шаг седьмой. Можно возвращаться в DataLens и создавать подключение:

При использовании Managed Service for PostgreSQL в том же каталоге, что и DataLens, большинство параметров просто выбирается из списков. Надо только ввести пароль и выбрать уровень доступа SQL.

8. Создание дата-сета и чарта8. Создание дата-сета и чарта

Шаг восьмой, заключительный. Нам остаётся создать дата-сет на основании таблиц или представления. Для создания подключения можно воспользоваться кнопкой в правом верхнем углу окна настроек созданного подключения или меню слева.

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

Затем добавить связь, выбрав соответствующие поля. Это значительно проще, чем создавать представление в базе данных.

Во вкладке «Поля» можно переименовать поля, отредактировать их тип, задать вид агрегации и добавить новые, вычисляемые.

И начать строить красивые визуализации.

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

psql --command " "\\copy public.sales (dt, source, item, price, volume) FROM 'sales.csv' DELIMITER '|' CSV HEADER ENCODING 'WIN1251' QUOTE '\"' ESCAPE '''';""

Получить актуальный скрипт для данных можно после выполнения импорта в pgAdmin, нажав «More details…».

ВыводыВыводы

Таким образом, за несколько шагов мы создаем визуализацию данных с возможностью их обновления по мере необходимости, а также использования инструментов базы данных, чтобы управлять дополнительными данными и выполнять предварительную обработку.

Подготовили для вас скрипты и команды, которые можно использовать для реализации структур и загрузки данных этого примера в psql или любом другом клиенте, разделе SQL, а также для управления кластером Managed Service for PostgreSQL:

CREATE TABLE IF NOT EXISTS public.sales
(
  dt date NOT NULL,
  source character varying COLLATE pg_catalog."default" NOT NULL,
  item bigint,
  price double precision,
  volume numeric
):
CREATE TABLE IF NOT EXISTS public.items
(
  id bigint NOT NULL,
  name character varying COLLATE pg_catalog."default"
);
CREATE OR REPLACE VIEW public.itemsales
 AS
SELECT s.dt, s.source, s.item, s.price, s.volume, i.name
  FROM sales s
  JOIN items i ON s.item = i.id;
  
\copy public.sales (dt, source, item, price, volume) FROM 'sales.csv' DELIMITER '|' CSV HEADER ENCODING 'WIN1251' QUOTE '\"' ESCAPE ''''