Работа с базами данных

Работа с базами данных осуществляется с помощью блоков:

• SQL-запрос
• Выбрать строки через SQL-запрос
• Добавить строки
• Обновить строки
• Получить схему таблицы
• Создать представление
• Создать таблицу
• Удалить таблицу
• Переименовать таблицу
• Очистить таблицу

SQL-запрос

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

У SQL-запроса обязательными параметрами являются запрос и подключение (при обращении к БД).

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

1. Возьмем таблицу «phases».
2. В скрипт добавим блок SQL-запрос и создадим запрос в соответствующем поле.
3. Протестируем и вернемся в таблицу, чтобы убедиться, что данные удалены.

Выбрать строки через SQL-запрос

Блок позволяет создать произвольный SQL-запрос в определенный пакет.

Параметры блока:

• SQL-запрос — инструкции выбора SELECT
• Limit — количество строк
• Offset — количество пропускаемых строк
• Order by — порядок строк/сортировать по

Пример: выгрузка данных пользовательской активности

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

1. Добавим блок Ручной запуск и протестируем его.
2. Добавим блок Выбрать строки через SQL-запрос и выберем таблицу main.monitoring_activity. Протестируем блок.
3. Добавим блок Добавить строки и в его параметрах укажем, что необходимо создать новую таблицу на основе выходных данных блока Выбрать строки через SQL-запрос. Протестируем блок.
4. Протестируем скрипт полностью и опубликуем его.
5. Перейдем в модель данных и проверим, что таблица main.monitoring_activity добавлена.
6. При необходимости можем выгрузить полученные данные в удобном формате: xlsx, xls, .csv.

Добавить строки

Блок используется для заполнения и создания таблиц с использованием выходных данных предыдущих блоков.

Обязательные параметры блока:

• Подключение (при обращении к БД)
• Таблица — можно выбрать из списка или задать вручную
• Колонки (название, значение)

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

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

Через редактор таблиц на левой панели вы можете изменять, добавлять и удалять колонки. Изменения влияют на таблицы в пространстве.

Чтобы создать таблицу:

1. Кликните по полю Таблица и выберите + Добавить таблицу.
2. Выберите Создать вручную и заполните поля:
   • Название таблицы
   • Название колонки
   • Тип данных
   Чтобы добавить новую колонку, кликните + Добавить колонку. При нажатии на Выбрать из выходных полей открывается окно с выходными данными. С их помощью можно добавлять колонки из данных предыдущих блоков.
   • Создать из выходных данных. Появляется список колонок из предыдущих блоков. Вы можете выбрать колонки по одной или отметить блок и автоматически выбрать все его колонки
   
3. Для сохранения нажмите Добавить, чтобы отменить действие нажмите Отмена.

В этом блоке можно использовать визуальный маппинг.

Обновить строки

Блок обновляет информацию в таблицах с помощью маппинга.

Обязательные параметры блока:

• Подключение (при обращении к БД)
• Таблица
• Колонка-ключ — указывается колонка, по которой будут сравниваться значения
• Значение ключа — указывается значение колонки-ключа
• Новые значения колонок

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

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

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

Получить схему таблицы

Блок позволяет получить имя и схему конкретной таблицы базы данных.

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

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

Создать представление

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

Используйте блок, чтобы:

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

С помощью блока можно создать два типа представлений:

• Обычное представление (View) — это виртуальная таблица. Она не хранит данные, а каждый раз при обращении выполняет заданный запрос и показывает его результат. Это помогает упростить работу с данными и ограничить доступ к ним
• Материализованное представление (Materialized view) — в отличие от обычного, сохраняет результат запроса в отдельной таблице. Такое представление позволяет быстро получать данные без повторного выполнения запроса и упрощает работу со сложными или часто используемыми вычислениями

Параметры блока:

• Тип представления:
  • View
  • Materialized view
• Тип действия:
  • Создать новое представление — create
  • Создать, если не существует — create if not exists
  • Заменить, если существует (только для View) — create or replace
• Название представления
• Select-запрос — для описания структуры колонок представления
• Целевая таблица — таблица, куда будут сохраняться данные (только для Materialized view)
• Комментарий

Создание представления

Настроим скрипт, который формирует отчет по продажам за текущий месяц.

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

1. Добавим в скрипт блок Создать представление.
2. В параметрах блока выберем тип представления — View.
3. Выберем тип запроса — Создать или заменить.
4. Зададим название представления — current_month_sales.
5. В поле Select-запрос добавим запрос:

   SELECT *
   FROM sales
   WHERE toStartOfMonth(sale_date) = toStartOfMonth(today())
   

   
6. Протестируем и опубликуем скрипт.

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

1. Добавим в скрипт блок Выбрать строки через SQL-запрос.
2. Укажем запрос, который будет получать данные за текущий месяц:

   SELECT * FROM current_month_sales
   

   
3. Добавим в скрипт два блока:
   • Сформировать CSV — для преобразования данных в отчет
   • Отправить письмо — для отправки готового файла заинтересованным сотрудникам
   
4. Протестируем и опубликуем скрипт. Теперь при выполнении запроса будут возвращаться только данные за текущий месяц.

Создать таблицу

Блок Создать таблицу позволяет создавать таблицы в базе данных пространства.

Блок работает и в обычном, и в кластерном режиме ClickHouse.

Параметры блока:

• Тип действия — выберите запрос из списка:
  • Создать
  • Создать, если не существует
  • Создать или заменить
• Название таблицы — укажите вручную или с помощью маппинга
• Колонки — создайте список колонок для таблицы, для каждой из которых задайте:
  • Название колонки
  • Тип данных — выберите из списка или укажите произвольный
• Order by — задайте порядок сортировки данных в таблице
• Partition by — настройте партиции таблицы

Блок не возвращает данные.

Продвинутый режим

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

• Получить структуру из select-запроса — активируйте переключатель, чтобы автоматически получать структуру таблицы из запроса
• Структура таблицы — укажите колонки и индексы в формате SQL
• Select-запрос для заполнения — введите запрос для заполнения таблицы или создания ее структуры (если переключатель Указать структуру таблицы не активирован)
• Движок таблицы — выберите движок, на котором будет работать таблица. При работе в кластерном режиме автоматически используется соответствующая Replicated-версия

Движок таблицы

Поддерживаемые типы движков и их дополнительные параметры указаны в таблице ниже.

Поддерживаемые типы движков

Для всех движков MergeTree дополнительно доступны следующие параметры:

• Order by
• Partition by
• Primary key
• Sample by
• TTL
• Settings — задаются через список ключей и значений

Settings

Чтобы задать дополнительные параметры таблицы в разделе Settings, нажмите Добавить и укажите ключ и его значение.

Поддерживаются следующие параметры:

• allow_nullable_key — позволяет использовать Nullable-значения в первичных ключах. Доступные значения:
  • 0 (по умолчанию) — запрещает использование Nullable-типов в первичных ключах
  • 1 — разрешает Nullable-типы, что позволяет использовать NULL в первичных ключах
• allow_experimental_json_type:
  • 0 — отключено
  • 1 — включает поддержку типа JSON
• log_queries:
  • 0 — отключено
  • 1 — включает логирование запросов, выполняемых в таблице

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

1. Выберем форму запроса Создать, если не существует.
2. Подставим название таблицы в соответствующее поле с помощью маппинга.
3. Добавим нужные колонки и зададим им названия и типы данных.
4. В поле Order by укажем колонку, по которой таблица будет сортироваться.
5. Протестируем блок.
6. Блок выполнен успешно. Во вкладке Тест отображается соответствующее сообщение.

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

1. Выберем форму запроса Создать.
2. Зададим название таблицы users.
3. В поле Структура таблицы укажем структуру таблицы:

   user_id Nullable(UInt32),
   username String,
   registration_date Date
   

4. Поле Select-запрос для заполнения оставим пустым.
5. Выберем движок таблицы — MergeTree.
6. В поле Order by укажем user_id.
7. Перейдем к разделу Settings. Нажмем Добавить и зададим параметр:
   • Ключ — allow_nullable_key
   • Значение — 1
   Это позволит использовать колонку user_id с типом Nullable в качестве ключа сортировки.
8. Протестируем блок.
9. Во вкладке Тест отображается информация об успешном выполнении блока.

• Теперь колонка user_id может содержать NULL, даже если она является частью первичного ключа
• ClickHouse будет корректно индексировать и сортировать данные с учетом NULL-значений
• Можно выполнять запросы с NULL-значениями в условиях фильтрации по первичному ключу

Удалить таблицу

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

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

Блок не возвращает данные.

Переименовать таблицу

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

Параметры:

• Таблица (подставьте с помощью маппинга)
• Новое имя

Блок не возвращает данные.

Очистить таблицу

Используйте блок, чтобы очищать данные выбранных таблиц в базе данных пространства при работе в кластерном режиме.

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

Блок не возвращает данные.

Использование сессий

Для всех блоков пакетов Пространство и ClickHouse есть возможность активировать режим сессий, который позволяет отправлять запросы в ClickHouse в рамках сессии и предоставляет возможность работать с временными таблицами и параметрами.

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

Время сессий у блоков пакета ClickHouse настраивается при создании подключения в пространстве.

Блоки, для которых возможно использовать режим сессий:

• SQL-запрос
• Добавить строки
• Выбрать строки через SQL-запрос
• Обновить строки
• Получить схему таблицы