Блог

Главная - Вопросы по программированию - Как быстро вставить ~ 300 ГБ / миллиард записей с привязкой в базу данных PostgreSQL?

Как быстро вставить ~ 300 ГБ / миллиард записей с привязкой в базу данных PostgreSQL?

#postgresql #bigdata #bulkinsert

#postgresql #bigdata #bulkinsert

Вопрос:

Я работал над этим месяцами, но все еще не нашел решения, я надеюсь, что смогу получить от вас помощь…

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

Данные в реальном времени предоставляются в виде файлов. Каждый файл содержит до 200 000 записей json, одна запись в одну строку. Каждый день несколько / десятки файлов предоставляются онлайн, начиная с 2013 года.

Я подсчитал все хранилище файлов и получил общий размер около 300 ГБ. Я оценил все количество записей (я могу получить размеры файлов через rest api, но не номера строк каждого файла), это должно быть около миллиарда записей или чуть больше.

Прежде чем я смогу импортировать / вставить одну запись в нашу базу данных, мне нужно выяснить два параметра (station, parameter) из записи и установить связь.

Итак, рабочий процесс выглядит примерно так:

  1. Найдите параметр в нашей базе данных, если параметр существует в нашей базе данных, просто верните parameter.id ; в противном случае параметр должен быть вставлен в таблицу параметров как новая запись и новый parameter.id будет создано и возвращено;
  2. Найдите станцию в нашей базе данных, аналогично, если эта станция уже существует, просто введите ее идентификатор, иначе будет создана новая станция и новая станция.идентификатор будет создан;
  3. Затем я могу вставить эту запись json в нашу основную таблицу данных с ее идентифицированным parameter.id и station.id чтобы установить связь.

По сути, это простая структура базы данных с тремя основными таблицами (данные, параметр, станция). Они связаны parameter.id и станция.идентификатор с привязкой первичный ключ / внешний ключ.

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

Я выполнил две попытки:

  1. Просто используйте обычные SQL-запросы с массовой вставкой. Рабочий процесс описан выше. Для каждой записи a) получаем parameter.id б) получить station.id c) вставить эту запись Даже при массовой вставке я мог вставить только миллион записей за неделю. Записи не такие уж короткие и содержат около 20 полей.

  2. После этого я попробовал следующее: я не проверяю параметр и станцию заранее, а просто использую команду COPY для копирования записей в промежуточную таблицу без связи. Для этого я подсчитал, что все миллиард записей могут быть импортированы примерно за десять дней. Но после КОПИРОВАНИЯ мне нужно вручную найти все диктуемые станции (есть только несколько параметров, поэтому я могу игнорировать часть параметров) с помощью SQL-запроса select distinct или group by и создать эти станции в таблице станций, а затем UPDATE все записи с их соответствующими station.id , но эта UPDATE операция занимает очень-очень много времени.

Вот пример: я потратил полтора дня, чтобы импортировать 33 000 000 записей в промежуточную таблицу. Я запросил с помощью select longitude, latitude from records group by longitude, latitude и получил 4500 станций, я вставил эти 4500 станций в таблицу станций И для каждой станции я делаю

 update record set stationid = station.id where longitude=station.longitude and latitude=station.latitude

The job is still running but I estimate it will take two days
And this is only for 30,000,000 records, I have one billion.

So my question is, how can I insert this amount of data into the database quickly?

Thank you very much in advance!

2020-08-20 10:01
Thank you all very much for the comments!

@Brits:
Yes and no, all the «COPY»s took over 24 hours. One «COPY» command for one json file. For each file I need doing following:

  1. Download the file
  2. Flat the Json file (no relation check of station, but with parameter check, it is very easy and quick, parameter is like constant in the project) to a CSV like text file for «COPY» command
  3. Выполнение команды «КОПИРОВАТЬ» через Python 1, 2 и 3 все вместе займет, если я нахожусь в корпоративной сети, около одной минуты для файла Json, содержащего ~ 200 000 записей, и около 20 минут, если я работаю из дома. Поэтому просто игнорируйте утверждение «полтора дня». По моим оценкам, если я буду работать в сети компании, я смогу импортировать все 300 ГБ / миллиард записей в свою промежуточную таблицу без проверки связи через десять дней.

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

  1. выберите долготу, широту, количество (*) из группы записей по долготе, широте
  2. вставьте эту комбинацию долготы и широты в таблицу станций
  3. для каждой записи в таблице станций обновляйте записи, устанавливающие stationid = station.идентификатор, где долгота = станция.долгота и широта = станция.широта

Это 3. вышеописанное занимает очень много времени (также 1. занимает несколько минут только для 34 миллионов записей, я понятия не имею, сколько времени 1. потребуется для одного миллиарда записей)

@Mike Organek @Laurenz Albe большое спасибо. Даже ваши комментарии сейчас для меня сложны для понимания. Я изучу их и дам отзыв.

Общее количество файлов составляет 100 000 , я подумываю о том, чтобы проанализировать все файлы и сначала получить отдельные станции, а затем с помощью station.идентификатор и parameter.id заранее сделайте «КОПИИ». Я дам отзыв.

2020-09-04 09:14

Я наконец получил то, что хотел, даже я не уверен, правильно ли это.

Что я сделал с момента моего вопроса:

  1. Проанализируйте все файлы json и извлеките уникальные станции по координатам и сохраните их в таблице станций
  2. Снова проанализируйте все файлы json и сохраните все поля в таблице записей с идентификатором параметра из constants и идентификатором станции из 1) с помощью команды COPY

Я выполнил 1) и 2) в конвейере, они выполнялись параллельно. И 1) занимало больше времени, чем 2), поэтому мне всегда нужно было дать 2) подождать.

И через 10 дней у меня есть мои данные в postgres, с примерно 15000 станциями и 0,65 миллиардами записей в общей сложности, каждая запись имеет соответствующий идентификатор станции.

@steven-matison Большое вам спасибо, и не могли бы вы, пожалуйста, объяснить немного больше?

Комментарии:

1. Я помещаю это в комментарий, потому что это не решение. Будут ли ваши данные хорошо разбиты на временные метки? Если да, рассматривали ли вы возможность использования расширения TimescaleDB? Пробовали ли вы гибридный подход с использованием copy для ввода отдельного файла объемом до 200 000 записей, а затем использования функций json с запросами для выполнения обслуживания таблиц station и parameter ? Если вы можете разбивать и обрабатывать отдельные файлы, то вы могли бы распараллелить загрузку через годы, если у вас есть процессоры и операции ввода-вывода, необходимые для обработки одновременных загрузок.

2. Когда вы говорите «Я потратил полтора дня на импорт 33 000 000 записей», вы имеете в виду, что COPY это заняло более 24 часов? (Я ожидал бы, что это будет намного быстрее, но это зависит от того, во что вы импортируете). Возможно, вы могли бы опубликовать инструкции SQL, которые вы запускали (из CREATE TABLE record ), и указать, сколько времени занимала каждая из них (чтобы мы могли точно видеть, где проблемы, подтвердить COPY vs COPY , посмотреть, как вы извлекаете долготу, широта из JSON, оценить влияние индексов / ограничений и т.д.).

3. Ваш второй подход многообещающий. При надлежащем оборудовании и настройке COPY это может быть намного быстрее. Затем запустите одиночные DELETE инструкции, которые соединяют загруженную таблицу с существующими и удаляют строки по мере необходимости. Если удалить большинство строк, будет быстрее сгенерировать новую таблицу и вставить туда оставшиеся строки. Настройте эти инструкции. Это может спасти положение, если разделить миллиард строк на части и обрабатывать каждую часть одновременно.

4. Можете ли вы подробнее рассказать о «создании отношений»? Когда вы выходите за пределы определенной точки, выполнение соединений в базе данных становится дорогостоящим. Итак, вы можете отменить нормализацию и сделать таблицу самодостаточной, т.Е. нет зависимости от других таблиц. Это может быть процесс поиска идентификатора станции, вставить, если не найден, который требует времени, потому что это делается для каждой строки. Это можно сделать в три этапа: импортировать, выбрать те, у которых есть известное название станции, идентификатор обновления, выбрать с неизвестным и вставить обновить.

5. Семь страниц текста и ни одного упоминания спецификации для Postgres и / или системы сбора данных… Это простой вариант использования для распределенной обработки, кластеризации и ОТКАЗА от использования традиционного «Postgres» для запроса параметров или обработки и хранения входящих и исходящих данных. -1