Параметризация источников в DataLens

В этом руководстве вы создадите подключение к источнику данных и узнаете, как использовать параметризацию источников датасета в DataLens.

Параметризация позволит:

• Подменять таблицу в запросах.
• Передавать в SQL, определяющий датасет, параметр как часть запроса.

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

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

1. Создайте воркбук.
2. Создайте подключение.
3. Создайте датасет с параметром для замены таблиц.
4. Создайте чарт с выбором таблицы в параметре.
5. Создайте датасет с параметром для подзапроса.
6. Создайте чарт с выбором условия для подзапроса в параметре.

Перед началом работыПеред началом работы

Чтобы начать работать с DataLens:

Если у вас возник технический вопрос по работе сервиса, обратитесь в службу поддержки Yandex Cloud. Чтобы спросить совета, обсудить решение вашей задачи или лучшие практики работы сервиса, напишите в чат DataLens в Telegram.

Создайте воркбукСоздайте воркбук

1. Перейдите на главную страницу DataLens.
2. На панели слева выберите Коллекции и воркбуки.
3. В правом верхнем углу нажмите Создать → Создать воркбук.
4. Введите название воркбука — Dataset parametrisation.
5. Нажмите кнопку Создать.

Создайте подключениеСоздайте подключение

Для доступа к базе данных будет создано подключение Sample ClickHouse.

1. В правом верхнем углу воркбука нажмите Создать → Подключение.

2. В разделе Базы данных выберите подключение ClickHouse®.

   1. В открывшемся окне выберите тип подключения Указать вручную и укажите параметры подключения:

      • Имя хоста — rc1a-ckg8nrosr2lim5iz.mdb.yandexcloud.kz.
      • Порт HTTP-интерфейса — 8443 (по умолчанию).
      • Имя пользователя — samples_ro.
      • Пароль — MsgfcjEhJk.

   2. Включите опцию Уровень доступа SQL запросов и выберите Разрешить подзапросы в датасетах и параметризация источников.

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

   4. Нажмите кнопку Создать подключение.

   5. Введите название подключения — Sample ClickHouse.

   6. Нажмите кнопку Создать.

      

Дождитесь сохранения подключения.

Создайте датасет с параметром для замены таблицСоздайте датасет с параметром для замены таблиц

Создайте датасет на базе подключения Sample ClickHouse:

1. На странице подключения в правом верхнем углу нажмите кнопку Создать датасет.

2. Перенесите на рабочую область таблицу samples.MS_SalesFacts.

3. Включите параметризацию в настройках датасета. Для этого вверху нажмите → Включить параметризацию.

   

4. Сохраните датасет:

   1. В правом верхнем углу нажмите кнопку Сохранить.
   2. Введите название датасета — Dataset with parametrisation и нажмите кнопку Создать.

5. Добавьте параметр с именем таблицы:

   1. Перейдите на вкладку Параметры.

   2. Нажмите кнопку Добавить и укажите:

      • Название — table_name;
      • Тип — Строка;
      • Значение по умолчанию — MS_SalesFacts;
      • Включите опцию Разрешить использовать в настройке источника и оставьте валидацию по умолчанию.

      

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

      

6. Измените валидацию датасета так, чтобы она проходила успешно для значения MS_SalesFacts в параметре. Для этого:

   1. Нажмите кнопку Добавить и укажите Название, Тип и Значение по умолчанию как на предыдущем шаге.

   2. Включите опцию Разрешить использовать в настройке источника и для валидации значений выберите Регулярное выражение.

   3. В поле введите регулярное выражение Python, позволяющее использовать заглавные и строчные символы, цифры и символ нижнего подчеркивания _:

      ^[a-zA-Z0-9_]+$
      

   

   Название таблицы MS_SalesFacts удовлетворяет такому регулярному выражению. При нажатии на кнопку Добавить валидация датасета пройдет успешно.

7. Сохраните датасет.

8. Измените определение имени таблицы по параметру:

   1. Перейдите на вкладку Источники.

   2. В списке таблиц напротив MS_SalesFacts нажмите → Изменить настройки.

   3. Cправа от поля Имя таблицы со значением MS_SalesFacts нажмите и выберите параметр table_name. Теперь имя таблицы будет определяться указанным параметром.

      

   4. Нажмите кнопку Применить и сохраните датасет.

Создайте чарт с выбором таблицы в параметреСоздайте чарт с выбором таблицы в параметре

1. Вверху датасета нажмите кнопку Создать чарт.

2. Выберите тип визуализации Таблица.

3. Создайте вычисляемое поле:

   1. В левой части экрана над списком полей датасета нажмите → Поле.
   2. Введите название: Year.
   3. Введите формулу: YEAR([OrderDatetime]).
   4. Нажмите кнопку Создать.

4. Добавьте в чарт новое поле. Для этого из раздела Измерения перетащите поле Year в секцию Столбцы.

5. Добавьте в чарт сумму продаж. Для этого из раздела Измерения перетащите поле Sales в секцию Столбцы и измените агрегацию для него:

   1. В секции Столбцы нажмите значок # у поля Sales.
   2. В поле Агрегация выберите Сумма, затем нажмите Применить.

   

6. Откройте инспектор и убедитесь, что данные выбираются из таблицы MS_SalesFacts. Для этого в правом верхнем углу чарта нажмите → Инспектор.

   

7. Сохраните чарт:

   1. В правом верхнем углу нажмите кнопку Сохранить.
   2. В открывшемся окне введите название чарта Table with parametrisation и нажмите кнопку Сохранить.

   

8. Измените имя таблицы в параметре чарта:

   1. В разделе Параметры нажмите значок у параметра table_name.

   2. Значение по умолчанию укажите MS_SalesFacts_up.

      

   3. Нажмите кнопку Сохранить. Теперь данные для чарта выбираются из таблицы MS_SalesFacts_up.

      

Создайте датасет с параметром для подзапросаСоздайте датасет с параметром для подзапроса

Создайте еще один датасет на базе подключения Sample ClickHouse:

1. Перейдите к подключению Sample ClickHouse и в правом верхнем углу нажмите кнопку Создать датасет.

2. Добавьте SQL-запрос к источнику:

   1. Внизу слева под списком таблиц нажмите → Добавить.

   2. Перейдите на вкладку SQL и в поле Подзапрос введите текст запроса:

      SELECT * FROM samples.MS_SalesFacts
      

   3. Нажмите кнопку Применить.

3. Включите параметризацию в настройках датасета. Для этого вверху нажмите → Включить параметризацию.

4. Сохраните датасет:

   1. В правом верхнем углу нажмите кнопку Сохранить.
   2. Введите название датасета — Dataset with parametrisation for sql и нажмите кнопку Создать.

5. Добавьте параметр с условием для подзапроса:

   1. Перейдите на вкладку Параметры.

   2. Нажмите кнопку Добавить и укажите:

      • Название — custom_where;

      • Тип — Строка;

      • Значение по умолчанию — 1=1. Такое условие всегда будет возвращать значение True;

      • Включите опцию Разрешить использовать в настройке источника и для валидации значений выберите Регулярное выражение. В поле введите регулярное выражение Python, позволяющее использовать символы, которые могут понадобиться при составлении SQL-запросов:

        ^[a-zA-Z0-9а-яАА-ЯёЁ_\s\(\)\.\'\=\-\+\*/\,\<\>!]+$
        

      При нажатии на кнопку Добавить валидация датасета пройдет успешно.

6. Сохраните датасет.

7. Добавьте условие в текст SQL-запроса, используя параметр:

   1. Перейдите на вкладку Источники.

   2. В списке таблиц напротив SQL нажмите → Изменить настройки.

   3. В поле Подзапрос к тексту запроса добавьте строку WHERE:

      SELECT * FROM samples.MS_SalesFacts
      WHERE
      

   4. Под полем ввода запроса нажмите Вставить параметр и выберите параметр custom_where. Теперь условие в SQL-запросе будет определяться указанным параметром:

      SELECT * FROM samples.MS_SalesFacts
      WHERE {{custom_where}}
      

      

8. Сохраните датасет.

Создайте чарт с выбором условия для подзапроса в параметреСоздайте чарт с выбором условия для подзапроса в параметре

1. Вверху датасета нажмите кнопку Создать чарт.

2. Выберите тип визуализации Индикатор.

3. Из раздела Измерения перетащите поле Sales в секцию Показатель. К полю автоматически применится агрегация, оно станет показателем.

   

4. Откройте инспектор и убедитесь, что в тексте SQL-запроса используется значение параметра из датасета 1=1.

   

5. Измените условие SQL-запроса в параметре:

   1. В разделе Параметры нажмите значок у параметра table_name.

   2. Значение по умолчанию укажите: ProductID IN (SELECT ProductID FROM samples.MS_Products WHERE ProductCategory='Техника для дома').

   3. Нажмите кнопку Сохранить. Данные в чарте отфильтрованы по условию: только продукты категории Техника для дома.

      

   4. Откройте инспектор и убедитесь, что в условии SQL-запроса теперь используется новое значение параметра.

      

6. Сохраните чарт:

   1. В правом верхнем углу нажмите кнопку Сохранить.
   2. В открывшемся окне введите название чарта Chart with parametrisation sql и нажмите кнопку Сохранить.

ClickHouse® является зарегистрированным товарным знаком ClickHouse, Inc.