В процессе обучения аналитике данных у человека неизбежно возникает вопрос о миграции данных из одной среды в другую. Поскольку одним из необходимых навыков для аналитика данных является знание SQL, а одной из наиболее популярных СУБД является PostgreSQL, предлагаю рассмотреть импорт и экспорт данных на примере этой СУБД.

В своё время, столкнувшись с импортом и экспортом данных, обнаружилось, что какой-то более-менее структурированной инфы мало: этот момент обходят на всяких там курсах по аналитике, подразумевая, что это очень простые моменты, которым не следует уделять внимание.

В данной статье приведены примеры импорта в PostgreSQL непосредственно самой базы данных в формате sql, а также импорта и экспорта данных в наиболее простом и распространенном формате .csv, в котором в настоящее время хранятся множество существующих датасетов. Формат .json хоть и является также очень распространенным, рассмотрен не будет, поскольку, по моему скромному мнению, с ним все-таки лучше работать на Python, чем в SQL.

1.    Импорт базы данных в формате в PostgreSQL

Скачиваем (получаем из внутреннего корпоративного источника) файл с базой данных в выбранную папку. В данном случае путь: 

C:\Users\User-N\Desktop\БД  

Имя файла: demo-big-20170815

Далее понадобиться командная строка windows или SQL shell (psql). Для примера воспользуемся cmd. Переходим в каталог, где находится скачанная БД, командой  cd C:\Users\User-N\Desktop\БД :

Далее выполняем команду для загрузки БД из sql-файла:

 "C:\Program Files\PostgreSQL\10\bin\psql" -U postgres -f demo-big-20170815.sql

Где сначала указывается путь, по которому установлен PostgreSQL на компьютере, -U – имя пользователя, -f  - название файла БД.

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

Заходим в pgAdmin и наблюдаем там импортированную БД:

С таблицами:

2. Импорт данных из csv-файла

Предполагается, что у вас уже есть необходимый .csv-файл, и первое, что нужно сделать, это перейти pgAdmin и создать там новую базу данных. Ну или воспользоваться уже существующей, в зависимости от текущих нужд. В данном случае была создана БД airtickets.

В выбранной БД создается таблица с полями, типы которых должны соответствовать «колонкам» в выбранном .csv-файле.

Далее воспользуемся SQL shell (psql) для подключения к нужной БД и для подачи команд на импорт данных. При открытии SQL shell (psql) она стандартно спросит про имя сервера, имя подключаемой БД, порт и пользователя. Ввести нужно только имя БД и пароль пользователя, всё остальное проходим нажатием ентра. Создается подключение к нужной БД – airtickets.

Ну и вводим команды на импорт данных из файла:

\COPY tickets FROM ‘C:\Users\User-N\Desktop\CSV\ticket_dataset_MOW.csv’ DELIMITER ‘,’ CSV HEADER;

Где tickets – название созданной в БД таблицы, из – путь, где хранится  .csv-файл, DELIMITER ‘,’ – разделитель, используемый в импортируемом .csv-файле, сам формат файла и HEADER, указывающий на заголовки «колонок».

Один интересный момент. Написание команды COPY строчными (маленькими) буквами привело к тому, что psql ругнулся, выдал ошибку и предложил написать команду прописными буквами.

Заходим в pgAdmin и удостоверяемся, что данные были загружены.

3. Экспорт данных в .csv-файл

Предположим, нам надо сохранить таблицу airports_data из уже упоминаемой выше БД demo.

Для этого подключимся к БД demo через SQL shell (psql) и наберем команду, указав уже знакомые параметры разделителя, типа файла и заголовка:

 \COPY airports_data TO ‘C:\Users\User-N\Desktop\CSV\airports.csv’ DELIMITER ‘,’ CSV HEADER;

Существует и другой способ экспорта через pgAdmin: правой кнопкой мыши по нужной таблице – экспорт – указание параметров экспорта в открывшемся окне.

4. Экспорт данных выборки в .csv-файл

Иногда возникает необходимость сохранить в .csv-файл не полностью всю таблицу, а лишь некоторые данные, соответствующие некоторому условию. Например, нам нужно из БД demo таблицы flights выбрать поля flight_id, flight_no, departure_airport, arrival_airport, где departure_airport = 'SVO'. Данный запрос можно вставить сразу в команду psql:

\COPY (SELECT flight_id, flight_no, departure_airport, arrival_airport FROM flights WHERE departure_airport = 'SVO') TO ‘C:\Users\User-N\Desktop\CSV\flights_SVO.csv' CSV HEADER DELIMITER ',';

Вот такой небольшой гайд получился.

Комментарии (7)


  1. phaggi
    30.03.2022 13:17

    Как я понял, плоскую таблицу загрузили в таблицу БД без связи с другими таблицами БД.

    А как загружать то же самое, если некоторые поля должны лечь в другие таблицы БД, а в данной таблице должны появиться соответствующие id из тех таблиц, связанных с этой?


    1. lizergil
      30.03.2022 17:39
      +1

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


      1. phaggi
        30.03.2022 20:08

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

        Где лучше посмотреть пример грамотного решения?


    1. KiraD Автор
      31.03.2022 03:24
      +1

      Вы про загрузку нескольких .csv-файлов в новую БД? А никак не лягут, если это потом не прописать кодом. Попробую следующей статьей показать на примере. Если Вы именно это имели ввиду.


      1. phaggi
        31.03.2022 07:50

        Нет. Я про загрузку одной main csv, в которой, к примеру, есть колонки, которые содержат часто повторяющиеся значения, и которые должны быть вынесены в отдельную таблицу со своими индексами и связаны с нашей main таблицей связью один ко многим. Например, название филиала.

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

        Или наоборот, грузить main как есть, а потом делать что-то вроде создания новой таблицы по select filials from main group by filials, создания там индекса и создания в main новой колонки filial_id со связью, а колонку main.filials удаляем. Я так делал, но правильно ли это?

        Или ещё как… Сделать функцию добавления новой строки, разбирающую строку исходной csv и раскладывающую нужные поля по уже созданным пустым таблицам и формирующую финальный insert для main уже с правильными значениями filial_id… медленно, но верно…тогда эта функция будет полезна в дальнейшем для пополнения или обновления.


    1. ArchDemon
      31.03.2022 09:08
      +1

      Можете попробовать через Pentaho Data Integration или Apache NiFi


    1. a14e
      01.04.2022 08:57

      Вероятно вам стоит сделать в другой бд буферную таблицу, потом на основе нее сделать что-то вроде

      insert .... from select ...

      Если это повторяющийся процесс, то посмотрите в сторону различных etl решений, например, nifi , apache airflow

      Если вы хотите выгрузить таблицу + связи, то вероятно вам потребуется выгрузка через pg_dump