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

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

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

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

SQL-запрос

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Получить системную таблицу

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

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

• Таблица — выбрать из списка
• Колонки — можно выбрать любое количество колонок. Если не выбрано ни одной колонки, то автоматически считается, что выбраны все

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

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

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

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

• Название таблицы — указывается вручную или с помощью маппинга
• Форма запроса — выбирается из списка
• Заполнить данными из SELECT — включает возможность заполнения таблицы данными из запроса
• Select-запрос — отображается, если включена опция Заполнить данными из SELECT. Введите часть запроса, начинающуюся c SELECT
• Колонки — отображается, если опция Заполнить данными из SELECT выключена. Необходимо указать название, тип данных и возможность использования Null в колонках
• Партиционирование — настройка партиций таблицы
• Сортировка — определение порядка сортировки данных в таблице
• Настройки — используются для настройки таблиц семейства MergeTree через секцию SETTINGS SQL-запроса

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

Параметр allow_nullable_key позволяет использовать Nullable-значения в первичных ключах:

• 0 (по умолчанию) — запрещает использование Nullable-типов в первичных ключах
• 1 — разрешает Nullable-типы, что позволяет использовать NULL в первичных ключах

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

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

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

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

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

1. Зададим название таблицы users.
2. Выберем форму запроса Создать таблицу
3. Добавим колонки таблицы:
   • user_id с типом Nullable(UInt32)
   • username с типом String
   • registration_date с типом Date
4. Укажем поле для сортировки user_id.
5. Добавим параметр в разделе Настройки:
   • Ключ — allow_nullable_key
   • Значение — 1
   
6. Протестируем блок.
7. Во вкладке Тест отображается информация об успешном выполнении блока:
   • Теперь колонка user_id может содержать NULL, даже если она является частью первичного ключа
   • ClickHouse будет корректно индексировать и сортировать данные с учетом NULL-значений
   • Можно выполнять запросы с NULL-значениями в условиях фильтрации по первичному ключу
   

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

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

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

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

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

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

Параметры:

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

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

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

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

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

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

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

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

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

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

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

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

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