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

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

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

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

bcp {data table} {in | out | queryout | format} {data file} {options}

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

ключевое слово 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 будет выглядеть следующим образом:

bcp [TB44_SANDBOX].[mis].[test_table_bcp] in "C:\Users\UserName\Documents\datafile.csv" -T -S hyperion.ca.sbrf.ru\hyperion -F2 -c -t"~" -C ACP –k

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

CREATE TABLE [TB44_SANDBOX].[mis].[test_table_bcp] ( [client_id] [nvarchar](500) NULL, [npackid] [nvarchar](500) NULL, [application_num] [nvarchar](500) NULL, [application_date] [nvarchar](500) NULL, [check_date] [nvarchar](500) NULL, [application_end_date] [nvarchar](500) NULL, [npackid_prev] [nvarchar](500) NULL, [c_num_decl] [nvarchar](500) NULL, [c_date_close] [nvarchar](500) NULL, [create_doc_prev] [nvarchar](500) NULL, [contract_num_prev] [nvarchar](500) NULL, [cred_sum_prev] [nvarchar](500) NULL, [annuity_date] [nvarchar](500) NULL, [annuity_summ] [nvarchar](500) NULL, [cred_percent_prev] [nvarchar](500) NULL, [dosr] [nvarchar](500) NULL, [next_annuity_summ] [nvarchar](500) NULL, [client_birth] [nvarchar](500) NULL, [sozaim] [nvarchar](500) NULL, [month_payment_ods] [nvarchar](500) NULL, [cred_percent_prev_ods] [nvarchar](500) NULL, [last_upd_date_ods] [nvarchar](500) NULL, [liab_appnum_ods] [nvarchar](500) NULL, [residual_debt_ods] [nvarchar](500) NULL, [liab_credit_type_ods] [nvarchar](500) NULL, [ref14] [nvarchar](500) NULL, [begin_date] [nvarchar](500) NULL, [end_date] [nvarchar](500) NULL, [loan_term] [nvarchar](500) NULL, [annuit_payment_calculated] [nvarchar](500) NULL, [predpens] [nvarchar](500) NULL, [btw] [nvarchar](500) NULL, [new_cash] [nvarchar](500) NULL, [annuity_count] [nvarchar](500) NULL, [payment_diff] [nvarchar](500) NULL, [payment_diff1] [nvarchar](500) NULL ) ON [PRIMARY] WITH(DATA_COMPRESSION=PAGE)

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

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

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

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

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

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

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

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

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