Как вставить множество записей в базу данных за один раз: методы массовой вставки в PostgreSQL, Greenplum, Redis, Neo4j, MongoDB и Elasticsearch, а также их практическая значимость в дата-инженерии.
Массовая вставка записей в БД
Продолжая недавний разговор про транзакционные операции в базе данных, сегодня рассмотрим сценарий, который не часто случается в работе аналитика, да и разработчика тоже. А вот для дата-инженера массовая загрузка данных в хранилище – довольно частая операция, которую надо делать быстро и эффективно. Для этого в каждом хранилище данных есть соответствующие механизмы, которые позволяют «оптом» вставлять большое количество записей, представляя это единой транзакционной операцией, а не серией отдельных транзакций.
Например, недавно при развертывании стенда для аналитики данных с помощью BI-системы, я столкнулась с задачей наполнения PostgreSQL тестовыми данными. Когда нужно сделать несколько тысяч INSERT-запросов к таблице, это будет выполняться очень долго, если не завернуть их в единый транзакционный блок, поставив в начале этой серии вставок оператор BEGIN;, а в конце COMMIT;. Выглядеть это может так:
BEGIN; INSERT INTO shop.orders (created, finished, status, summa, customer) VALUES
('2024-05-15', '2024-05-23', 7, 567.0, 5409),
('2023-05-19', '2023-05-21', 8, 1127.9, 5878),
('2023-09-20', '2024-08-22', 1, 3454.3, 3992),
('2023-01-02', '2023-01-06', 3, 4765.5, 5815),
….
('2023-04-05', '2023-04-10', 6, 4360.0, 4266);
COMMIT; Такая массовая вставка в рамках одной транзакции выполняется довольно быстро. Например, вставка 10 000 записей в облачное развертывание PostgreSQL у меня заняла несколько секунд. Этот же самый объем данных при вставке отдельно взятыми транзакциями выполнялся бы около получаса.
Впрочем, есть еще один вариант быстрой вставки данных в PostgreSQL: использовать инструкцию COPY FROM, которая копирует данные из файла в таблицу. Исходный файл с данными должен быть в формате text, csv или binary. Аналогичные возможности есть и в Greenplum, которая основана на PostgreSQL и включает механизмы массово-параллельной обработки (MPP, Massive Parallel Processing). Поэтому для больших наборов данных Greenplum использует MPP-методы, которые позволяют загружать данные одновременно из нескольких файловых систем через несколько сетевых карт на разных хостах, достигая очень высоких скоростей передачи данных. При этом используется специальная утилита gpfdist и внешние таблицы, которые позволяют получать доступ к внешним файлам из базы данных.
В нереляционных хранилищах тоже есть подобные механизмы. Например, key-value СУБД Redis, о которой я писала здесь, тоже имеет соответствующую утилиту redis-cli, которая режим конвейера. Он был специально разработан для массовой загрузки данных в Redis и доступен с версии 2.6. При этом режим конвейера также выполняет копирование данных из текстового файла, в котором хранятся данные для массовой загрузки, соответствующие протоколу Redis, где командой SET задается ключ и его строковое значение.
Основы архитектуры и интеграции информационных систем
Код курса
OAIS
Ближайшая дата курса
5 ноября, 2024
Продолжительность
16 ак.часов
Стоимость обучения
36 000 руб.
В графовой базе данных Neo4j тоже используется прием массового импорта данных из CSV-файла, где описаны вершины графа и связи между ними.
А вот в документо-ориентированных MongoDB и Elasticsearch есть bulk-методы для массовых операций. Например, в MongoDB метод db.collection.bulkWrite() позволяет выполнять операции массовой вставки, обновления и удаления в рамках одной коллекции, т.е. группы документов. Еще в MongoDB есть метод db.collection.insertMany() для массовой вставки документов. В Elasticsearch с его REST API для массовой загрузки данных используются POST-запрос к ресурсу /_bulk, в теле которого передается JSON-документ с данными для вставки в индекс.
В колоночной БД Clickhouse, предназначенной для потоковой аналитики огромных объемов данных, для массовой вставки используется механизм буферных таблиц, которые буферизуют данные для записи в ОЗУ. Также можно использовать асинхронную вставку данных, которая применяет пакетный подход, сохраняя новые записи в сперва буфере памяти, а затем на диске.
Таким образом, каждая база данных имеет собственные варианты организации массовых вставок в рамках одной транзакции. Почему же транзакционная запись так важна? Разумеется, не только, чтобы сэкономить время на наполнение базы данных. Массовая вставка особенно важна в ETL/ELT-процессах при работе с хранилищами данных (DWH, Data WareHouse), которые агрегируют информацию из прикладных систем для получения согласованной отчетности. Слоистая структура DWH предполагает наличие так называемого stage-слоя (staging area), куда исходные данные сначала загружаются, а потом обрабатываются и копируются в последующие слои, т.е. другие целевые таблицы для последующего анализа с помощью BI-инструментов. Поэтому при реализации ETL/ELT-процессов дата-инженеру важно использовать именно методы массовой загрузки данных в хранилище одной транзакцией вместо поочередной вставки по одной записи за раз.
Подробнее об этом и других аспектах архитектуры данных я рассказываю на моем курсе в Школе прикладного бизнес-анализа на базе нашего лицензированного учебного центра обучения и повышения квалификации системных и бизнес-аналитиков в Москве:
