SQL Базовый №4. Импорт и экспорт данных

Файлы к уроку:

Если ваши данные находятся в текстовых CSV-файлах, то их можно разом импортировать в базу данных. В PostgreSQL для этого есть команда COPY. Этой командой можно как импортировать данные, так и экспортировать.

3 шага для импорта данных из CSV:

Подготовить CSV файл
Создать таблицу в базе данных
Выполнить импорт данных из CSV файла в заготовленную таблицу с использованием команды COPY

Работа с CSV-файлами

Многие приложения хранят данные в своих собственных уникальных форматах. Такие форматы сложно прочитать и конвертировать в нужный вам формат. К счастью, большинство программных продуктов позволяют экспортировать данные в формат CSV.

Каждая строка CSV-файла — это строка таблицы. В каждой строке значения столбцов разделены каким-то символом. Это может быть любой символ. В России в роли разделителя чаще всего используется двоеточие. На западе чаще всего применяется запятая.

Обычная строка CSV-файла выглядит примерно так:

1,Assumption Cathedral,Central Administrative District,Tver district,Kremlin,Lenin's Library,Sokolnica line,(495) 695-37-76,assumption-cathedral.kreml.ru,"37,617071","55,751012"

Разделители отделяют данные разных столбцов друг от друга. Используется одна запятая без пробела после нее.

Кавычки

Значения разных столбцов разделены запятыми. А что делать, если само значение содержит запятые? Например, в таблице есть столбцы широты и долготы, в которых целые части от дробных отделены запятыми. Если столбец содержит разделитель, то все его значения должны начинаться и заканчиваться специальным символом text qualifier. Чаще всего это двойные кавычки.

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

Строка заголовка

В CSV-файле обычно присутствует заголовок. Это строка, в которой перечислены имена столбцов. Выглядит она примерно так:

ID,Name,AdmArea,District,Address,MetroStation,MetroLine,PublicPhone,WebSite,Longitude_WGS84,Latitude_WGS84

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

Импорт данных с помощью COPY

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

copy имя_таблицы_в_которую_импортируются_данные
from 'путь_к_файлу_из_которого_копируются'
with (format CSV, header);

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

Формат файла. Параметром format имя_формата указывается какой формат файла читается или пишется. Названия форматов: CSV, TXT, BINARY. Чаще всего применятся формат CSV. В файле TXT обычно в роли разделителя выступает табуляция.
Строка заголовка. Параметр header означает, что в файле в первом столбце находятся заголовки. Этот параметр говорит базе данных, что импортировать данные нужно со второй строки.
Разделитель. Параметр delimiter ‘символ_разделитель’ указывает какой символ в файле выступает разделителем. Разделителем может быть только 1 символ. Например, если в файле значения столбцов разделяются точкой с запятой, то параметр выглядит так: delimiter ‘;’.
Символ кавычек. Двойные кавычки говорят о том, что данные между ними нужно считать одним значением. Вместо кавычек в CSV-файле может использоваться другой символ. В таком случае нужно воспользоваться параметром quote ‘символ_quote_qualifier’

Создаем таблицу

Создадим таблицу, в которую загрузим данные из CSV-файла с перечнем всех православных храмов Москвы.

create table religion (
  id smallint,
  church_name varchar(300),
  adm_area varchar(50),
  district varchar(50),
  address varchar(100),
  metro_station varchar(40),
  metro_line varchar(40),
  phone varchar(100),
  site varchar(200),
  longitude numeric(8, 6),
  latitude numeric(8, 6)
)

copy religion
from 'c:\Users\user\Desktop\sql_training\churches.csv'
with (format csv,
	  header,
	  delimiter ';',
	  encoding 'WIN1251')

Импорт некоторых столбцов

Если в вашем CSV-файле есть данные только для некоторых столбцов вы все равно можете выполнить импорт. Нужно будет указать какие столбцы есть в данных.

Добавим в нашу таблицы данные по мечетям. В CSV-файле с данными о мечетях нет столбцов MetroStation, MetroLine, Longitude, Latitude. Если попытаться импортировать данные из этого файла в таблицу religion, то вернется ошибка SQL Error [22P04]: ОШИБКА: нет данных для столбца «site».

Названия столбцов в CSV-файле не совпадают с названиями столбцов в базе данных. В таком случае импорт делает в несколько шагов:

Создается временная таблица
Во временную таблицу импортируются данные из CSV-файла
Из временной таблицы в основную таблицу с помощью insert into копируются нужные столбцы
Временна таблица удаляется

-- Создание временной таблицы
create temporary table mosques (
  id smallint,
  object_name varchar(300),
  adm_area varchar(50),
  district varchar(50),
  address varchar(100),
  phone varchar(100),
  email varchar(40),
  site varchar(200)
)

-- Импортируем данные во временную таблицу
copy mosques
from 'c:\Users\user\Desktop\sql_training\mosques.csv'
with (format csv,
	  header,
	  delimiter ';',
	  encoding 'WIN1251')

-- Копирование нужных столбцов из временной таблицы  
insert into religion (id,
					  object_name,
					  adm_area,
					  district,
					  address,
					  phone,
					  site)
select
  id,
  object_name,
  adm_area,
  district,
  address,
  phone,
  site
from mosques;

-- Удаляем временную таблицу
drop table mosques;

Экспорт с помощью COPY

Командой COPY можно не только импортировать данные, но и экспортировать. Разница в том, что теперь вместо ключевого слова FROM используется TO.

Есть 3 варианта экспорта:

Таблица целиком
Экспорт отдельных столбцов
Экспорт результата запроса

-- Экспорт таблицы целиком
copy religion
to 'c:\Users\user\Desktop\sql_training\full_export.csv'
with (FORMAT csv,
	  header,
	  delimiter ';');

-- Экспорт выбранных столбцов
copy religion (object_name,
			   district,
			   address)
to 'c:\Users\user\Desktop\sql_training\certain_cols_export.csv'
with (FORMAT csv,
	  header,
	  delimiter ';');

-- Экспорт результата запроса
-- Выбираем столбцы
-- Оставляем только храмы из южного района
copy (select
	    object_name,
	    district,
	    address,
	    metro_station,
	    metro_line,
	    longitude,
	    latitude
	  from religion
	  where adm_area ilike '%southern%')
to 'c:\Users\user\Desktop\sql_training\query_export.csv'
with (FORMAT csv,
	  header,
	  delimiter ';');

Экспорт с помощью UI

Вся таблица целиком

Чтобы экспортировать всю таблицу целиком найдите ее в панели Базы данных — Правый клик — Экспорт данных.

Определенные строки и столбцы

Выполните запрос. Под превью нажмите на кнопку экспорта данных. Далее нужно выбрать удобный вам формат и указать количество строк для экспорта. Если вам нужно сохранить все вернувшиеся строки, то можете предварительно посчитать количество строк с помощью функции COUNT().

предыдущая запись

SQL Базовый №3. Типы данных

следующая запись