BCP или как быстро импортировать объемный CSV файл на SQL Server

Порой перед пользователем встает задача загрузки большого файла в таблицу на SQL Server, чтобы в дальнейшем с ней работать. В этой статье познакомимся и разберем одно из средств MS SQL Server, которое позволит решить нашу задачу довольно быстро.

Итак, bcp (bulk copy program) – встроенная консольная утилита, которая применяется для массового перемещения данных между сервером MS SQL и файлом пользователя в необходимом «направлении».

В общем виде синтаксис вызова bcp выглядит следующим образом:

Рассмотрим каждый из представленных выше блоков:

• ключевое слово bcp дает указание для запуска утилиты;

• в блоке {data table} указывается заранее созданная на сервере таблица (блок должен содержать полное название таблицы — [БД].[схема].[таблица], по структуре она должна соответствовать загружаемому файлу);

• в блоке {in | out | queryout | format} указывается то самое «направление» для перемещения данных (для загрузки файла будем использовать in);
• в блоке {data file} указывается полный путь к файлу;
  

• · в последнем блоке {options} перечисляются команды, которые определяют работу утилиты bcp.

  
  

Остановимся подробнее на блоке {options} и основных командах, которые мы будем использовать:

[-T] – указывает, что утилита bcp будет устанавливать доверительное (trusted) соединение с сервером, т.е. без необходимости указания логина/пароля пользователя;

[-S [server name[\instance name]] – после этой команды указывается сервер и, если это необходимо, конкретный инстанс для подключения;

[-F2] – задает номер строки в файле, с которой начнутся чтение и вставка данных (указываем число 2 для пропуска заголовков столбцов);

[-c] – указывает, что операция вставки будет выполняться с использованием символьного типа данных (char);

[-t «field term»] – задает разделитель столбцов;

[-C {ACP | OEM | RAW | codepage}] – задает кодировку для данных;

[-k] – определяет значения пустых столбцов — они должны остаться в значении NULL (указание этой команды игнорирует ограничения default, установленные для столбцов в таблице).

Полный перечень команд можно увидеть, если в командной строке набрать «bcp –h» (без кавычек), также он доступен из официальной документации Microsoft (https://docs.microsoft.com/ru-ru/sql/tools/bcp-utilit? view=sql-server-ver15#k).

В итоге наша строка для вызова bcp будет выглядеть следующим образом:

Самое время проверить все на практике. Для начала создадим таблицу, которая повторяет структуру файла:

В файле, который необходимо загрузить, содержится около 1 млн строк и 36 столбцов. В качестве разделителя столбцов используется символ «~».

Затем откроем командную строку и вставим наше выражение для утилиты bcp, которое мы сформировали ранее.

Нажимаем Enter и наблюдаем.

Как мы видим, загрузка такого объемного файла заняла всего 23 секунды, что достаточно быстро.

Наиболее очевидной альтернативой утилите bcp для пользователя является «Мастер импорта и экспорта SQL Server». Он позволяет осуществлять преобразования данных, которые в bcp недоступны, автоматически создавать таблицу в БД, если её ещё не существует на сервере, и всё это доступно пользователю через графический интерфейс. Но, к сожалению, все его преимущества сходят на нет, когда необходимо переместить большой объем данных.

Чтобы не быть голословными в своих утверждениях, проверим какой объем данных загрузит «Мастер импорта и экспорта SQL Server» за то же время. Для этого пройдем все необходимые шаги в «Мастере» и запустим сформированный пакет.

Как мы видим на двух скриншотах выше, за время работы bcp «Мастера импорта и экспорта SQL Server» смог загрузить всего 13 тыс. строк из общего количества 1 млн строк в файле.

Таким образом, потратив некоторое время для подготовки структуры таблицы на сервере и написание необходимой строки для запуска копирования через bcp, мы получим ощутимый выигрыш в скорости загрузки данных в БД.