Азбука BCP

По материалам статьи Curt A. Gilman:
The ABCs of BCP


Пошаговое руководство для "чайников" в MS SQL Server BCP

Содержание

1.Введение
2.Основы BCP
3.Быстрое массовое копирование
4.Сравнение BCP и DTS Import/Export Wizard
5.Сравнение BCP и резервирования с восстановлением
6.Примечания автора

Введение

Bulk copy program (BCP), это специальная утилита командной строки для массового копирования, которая поставляется со всеми изданиями SQL Server. Она незаслуженно редко используется, но является необходимым компонентом инструментария DBA. Хотя эта утилита не имеет своего ярлыка в папке SQL Server, к которой можно перейти из меню Start, и только кратко упоминается в SQL Server Books Online (BOL), программа BCP пережила многочисленные версии SQL Server и даже претерпела незначительные улучшения в его последних реализациях.
Несмотря на не высокую популярность BCP, она остается одним из лучших инструментов для быстро перемещения большого объёма данных между базами SQL Server. Если Вы правильно используете BCP, с её помощью можно перемещать большие наборы данных быстрее, чем с помощью встроенных средств Enterprise Manager и Data Transformation Services (DTS), которые чаще всего применяются для этих целей.
Если Вы плохо знакомый с BCP, ниже будет представлен краткий обзор её основных возможностей. BCP перемещает данные из таблиц базы данных в файлы и наоборот. BCP работает в основном с плоскими файлами, данные в которых разделены табуляцией, или с текстовыми файлами с данными фиксированной длинны. BCP работает также и со специальными файлами, разработанного для SQL Server формата, которые позволяют передавать даже не символьные данные. BCP не умеет создавать объекты базы данных, так что таблицы должны существовать до того, как Вы будете использовать BCP, чтобы перенести в них данные.
Как правило, когда Вы вставляете записи в таблицу, сервер вначале регистрирует эти операции в журнале транзакций (transaction log), а затем уже непосредственно записывает данные в базу. Для больших наборов данных, эта операция требует существенного времени и дискового пространства, т.к. применяется двойная фиксация транзакций. Однако, BCP может импортировать данные в таблицу быстрее чем обычная регистрируемая вставка, потому что, при некоторых обстоятельствах, сервер не будет регистрировать операции с записями в transaction log. Вставка записей в таблицу без регистрации в transaction log называется быстрым массовым копированием (fast bulk copy), и автор более подробно описывает такие операции ниже в статье. Но перед этим предлагается более подробно рассмотреть способы запуска утилиты BCP из командной строкой.

[Содержание]

Основы BCP

Запуск утилит из командной строки Windows NT всё реже используется современными DBA. Для Windows 2000/NT ярлык командной строки "Command Prompt" доступен через меню Start. Если в командной строке набрать: "BCP -h" будет представлен листинг общего синтаксиса использования утилиты:

C:\>bcp -h
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]

C:\>

Листинг показывает пару дюжин параметров, из которых нужно знать только несколько, чтобы усвоить советы, которые представляет автор статьи. Имейте в виду, что параметры командной строки зависят от регистра, так что, например, -n не означает то же самое, что -N. Вообще, синтаксис командной строки определяет следующая строка:

BCP {dbtable} {in | out | queryout | format} datafile options

Команда начинается с BCP, затем указывается полностью квалифицированное имя таблицы, обозначенное как dbtable. Полностью квалифицированное имя таблицы содержит имя базы данных, владельца table-object и имя table-object. Например, Northwind.dbo.Employees - это полностью квалифицированное имя таблицы. Вы должны указать направление: in или out. Если Вы используете BCP, чтобы копировать данные из файла в таблицу базы данных, используйте опцию in. Если Вы используете BCP, чтобы копировать данные из таблицы базы данных в файл, используйте опцию out. Синтаксис команды продолжает имя файла - datafile. Имя файла может быть абсолютным, например: C:\Temp\Northwind.dat, или это может быть относительное от текущей папки имя, например: ..\Northwind.dat. (две лидирующие точки указывают, что файл Northwind.dat расположен на одну папку выше в иерархии папок). Если Вы используете BCP с опцией out, создаётся новый файл с указанным именем, который автоматически перезаписывает любого файла с тем же самым именем, если он уже существует. После этого Вы можете разместить любые другие, необходимые параметры утилиты BCP.
Рассмотрим теперь использование некоторых важных параметров BCP. Вы можете использовать опцию -S, чтобы указать имя сервера (не обязательно указывать имя сервера, если Вы запускаете BCP на локальном компьютере SQL Server). Если необходимый экземпляр SQL Server не доступен по заданному по умолчанию протоколу, Вы должны прописать имя сервера как псевдоним для компьютера - клиента в SQL Server Client Network Utility. Для подключения к с серверу через его собственную систему аутентификации, необходимо указать имя пользователя и пароль, которые задаются через параметры -U и -P соответственно. При использовании доверительного подключения, указание через параметры имени пользователя и его пароля не требуется, нужно только указать опцию -T.
Для указания типа файла данных, используйте опцию -n, если файл данных, который Вы хотите копировать, имеет собственный формат SQL Server (native-формат), или опцию -c, если файл должен быть текстовым, имеющим в качестве разделителя табуляцию. Для того, что бы закрепить порядок использования параметров BCP, рассмотрим пример, в котором используется следующая информация о параметрах BCP, таблице базы данных и транзитного файла:

· Исходная таблица - Employees, владелец которой dbo. · Таблица находится в базе данных Northwind. · Файл экспорта - текстовый файл Northwind.txt имеющий в качестве разделителей табуляцию. · Файл находится в текущей папке на сервере с именем HOMER, к которому Вы обращаетесь через учетную запись sa (которая без пароля).

Воспользовавшись представленными выше правилами, Вы получите следующий набор параметров для утилиты BCP:

BCP Northwind.dbo.Employees out Northwind.txt -c -S HOMER -U sa -P

Теперь, чтобы полностью изменить направление потока данных, давайте рассмотрим пример перемещения данных средствами BCP из файла в таблицу базы, и запишем команду, которая будет использовать следующую информацию:

· Исходный файл данных - файл типа native SQL Server, с именем Invoices.dat.
· Файл находится на сервере с именем BART, к которому Вы обращаетесь через доверительное подключение.
· Таблица назначения - Invoices, владелец которой Lisa.
· Таблица находится в базе данных Northwind.

В результате Вы должны получить следующую команду:


BCP Northwind.lisa.Invoices in Invoices.dat -n -S BART -T

Эти примеры демонстрируют наиболее типичное использование BCP. Вы можете перемещать большие таблицы или базы данных целиком используя представленные в примерах параметры. С помощью BCP можно выполнять и более сложные задачи, используя текстовые файлы фиксированной длинны или файлы с различными разделителями. Для получения дополнительной информации о параметрах BCP, см. BOL.

[Содержание]

Быстрое массовое копирование

Если база данных, в которую копируются данные, отвечает двум важным условиям, BCP может осуществлять наиболее быстрое и эффективное массовое копирование. Во-первых, для базы данных должна быть включена опция массового копирования. Для SQL Server 7.0 откройте Enterprise Manager, щёлкните правой кнопкой мыши по базе данных, в которую или из которой будет осуществляться копирование, и выберите Properties в выпадающем меню. После того, как появляется диалоговое окно Properties, откройте закладку Options, чтобы определить, активизирована ли опция Select into/bulk copy. Для SQL Server 2000 должна быть указана Simple или Bulk-Logged Recovery model. Во вторых, используемые для вставки данных из файла таблицы не должны иметь индексов. В этом также можно убедиться с помощью Enterprise Manager: перейдите к принимающей данные таблице, щёлкните по ней правой кнопкой мыши, и выберете из меню пункт All Tasks, а потом Manage indexes. Вы можете временно удалить имеющиеся индексы в окне Manage indexes. Если таблица новая, Вы можете не создавать индексы, пока не скопируете в неё с помощью BCP данные.
Когда Вы копируете данные в новую базу, эти два условия не сложно выполнить. Однако, для промышленных баз данных, которые интенсивно используют прикладные программы, Вы можете столкнуться с невозможностью использования такого подхода, поскольку BCP не регистрирует вставку строк в transaction log. Вы не сможете полностью откатить изменения в данные, если произойдёт сбой в работе BCP. Поэтому, Вы должны быть внимательным при использовании BCP, когда заполняете таблицы, к которым обращаются прикладные программы, и возможно Вам придется рассмотреть другие возможности для импорта данных. Также Вы должны тщательно обдумать изменение установок промышленной базы данных. Активизация опции базы данных Select into/bulk copy не позволит восстановить операции массового копирования из резервных копий transaction log, сделанных после последнего, полного резервного копирования базы данных. Хотя Вы можете использовать BCP, чтобы передать несколько таблиц в другую базу данных, если Вы хотите осуществить перенос таблиц с максимальной скоростью, таблицы для размещения данных должны удовлетворять этим двум условиям.
Поскольку BCP не создаёт объекты базы данных, чтобы создать таблицы для загрузки данных, Вы должны сгенерировать их SQL-скрипты на исходной базе данных. Вы можете легко сгенерировать эти SQL-скрипты в Enterprise Manager: перейдите в исходную базу данных, щёлкните по ней правой кнопкой мыши и выберите Generate SQL Scripts из меню All Tasks. После этого, выберите необходимые объекты на закладке General и проверьте на закладке Formatting, что для каждого из выбранных объектов в генерируемом скрипте будут только команды CREATE. Если в скрипт попадут команды DROP, это значит, что объекты вначале будут уничтожены, т.ч. лучше удаление объектов сделать вручную.
Кроме того, в закладке Options, нужно проверить, что параметры: Script indexes, Script triggers и Script PRIMARY Keys, FOREIGN Keys, Defaults и Check Constraints активизированы.
После всего этого можно сохранить получившийся SQL-скрипты в файл, щёлкнув OK. Если Вы хотите предварительно увидеть скрипт, щёлкните Preview на вкладке General. И, наконец, чтобы создать необходимые таблицы и другие объекты в базе данных, куда будут копироваться данные, Вы должны запустить там эти SQL-скрипты на исполнение. Т.е. Вы должны открыть Query Analyzer, а затем выполнить команды CREATE TABLE из SQL-скриптов. Лучше сгруппировать команды CREATE TABLE вверху скрипта и выполнить их отдельно, потому что, если выполнить ещё и создание индексов, невозможно будет добиться быстрого массового копирования в новые таблицы.
После того, как Вы создали таблицы в базе данных, Вы можете без опаски использовать BCP для поочерёдного массового копирования таблиц исходной базы данных в файлы. Если Вы копируете данные между SQL серверами, логично использовать родной для них native-формат файлов, который в этом случае будет наиболее эффективен. После этого, все файлы, которые Вы только что создавали, могут быть также поочерёдно загружены в базу данных с вновь созданными таблицами. После завершения копирования данных, Вы можете запустить на исполнение в импортирующей базе данных оставшиеся команды из SQL-скриптов, которые были сгенерированы ранее. Эти команды создадут все ограничения ключей, триггеры и индексы, которые были у исходных таблиц, но не были ещё созданы для новых.

[Содержание]

Сравнение BCP и DTS Import/Export Wizard

Использование BCP для передачи данных не выглядит простой задачей. Резонно задать вопрос: "Существует ли более простой метод?". В SQL Server 7.0 и 2000 альтернативным методом передачи данных является использование DTS. DTS имеет много удобных свойств, таких, как графический интерфейс. Ярлык DTS (Import and Export Data) также можно найти через меню Start, и он запускает Import/Export Wizard. Эта утилита имеет в своём арсенале несколько удобных визардов интегрированных с Enterprise Manager, и обладает более широкими возможностями, а также может осуществлять более сложные преобразования данных, которые не доступны с помощью BCP. Также Вы можете использовать DTS для передачи данных между гетерогенными СУБД. На первый взгляд может показаться не понятным почему, если DTS имеет такие большие возможности, автор статьи рекомендует использовать BCP для копирования данных? Главным преимуществом использования для этого BCP, а не DTS Import/Export Wizard, является скорость передачи данных. Import/Export Wizard больше подходит для перемещения не большого количества данных или для одновременного преобразования данных. Поскольку этот визард часто требует меньшего времени для выполнения предварительных настроек копирования, он более удобен для небольших наборов данных. Однако, этот выигрыш во времени перестаёт быть решающим, когда наборы данных станут большими, и BCP начнёт превосходить его по быстродействию. Также, визард не может копировать данные без регистрации в журнале транзакций, что сказывается на увеличении времени его работы. Однако, наиболее важной причиной того, что автор статьи старается не использовать DTS, это то, что он (с настройками по умолчанию) не всегда создает таблицы, которая точно дублирует исходные таблицы. Могут быть утеряны ограничения, индексы и identity. Эта проблема не является критичной, если Вы передаёте не много и маленькие таблицы. Однако, при перемещении или копировании базы данных, Вы должны гарантировать, что вся база данных будет передана так, как она была изначально определена. Используя BCP и SQL-скрипты, которые генерирует Enterprise Manager, Вы создаете новые таблицы точно такими же, как исходные таблицы, наряду с их ограничениями, индексами и identity. Поскольку данные, которые перемещаются, будут идентичны, то и каждые пары таблиц в старой и новой базах будут функционально эквивалентны.

[Содержание]

Сравнение BCP и резервирования с восстановлением

SQL Server имеет и другую, альтернативную BCP возможность перемещения данных путём её резервного копирования и последующего восстановления на другом сервере. Все необходимые операции можно легко сделать с помощью соответствующих визардов Enterprise Manager. В результате Вы можете очень быстро получить точную копию исходной базы данных. К сожалению, восстановление резервной копии базы данных лишает Вас возможности полностью управлять этим процессом. Поскольку новая база данных будет в точности такой же, как оригинальная, все ошибки, которые Вы сделали в исходной базе данных, перекочуют и в новую. Если у старой базы transaction log занимал сотни мегабайт, он станет такого же размера в восстанавливаемой базе данных. Все индексы, который Вы не переиндексировали, останутся в новой базе данных в таком же виде. Кроме того, таким методом Вы на сможете копировать только выборочные объекты. Восстанавливается или всё, или ничего. Использование BCP и SQL-скриптов, которые генерирует Enterprise Manager, позволяет существенно уменьшить занимаемое таблицами место за счёт отсутствия фрагментации, а создание индексов после импорта данных позволит сделать их использование наиболее оптимальным. Кроме того, применение BCP не приведёт к разрастанию журнала транзакций. Используя BCP, Вы можете передавать только необходимые объекты между базами данных.

[Содержание]

Примечания автора

1. При передаче всех таблиц базы данных вы можете использовать возможности системной хранимой процедуры: sp_MSforeachtable, например:


USE Northwind
GO
sp_MSforeachtable @Command1="master..xp_cmdshell 'BCP Northwind.dbo.? 
out D:\?.out -S ServerName -U sa -P  -n' "
GO

Можно конечно обойтись и без sp_MSforeachtable, тогда стоит использовать конструкцию на подобии представленной ниже, которая создаёт файлы в csv-формате:


DECLARE @tbl varchar( 40)
DECLARE TableCursor CURSOR FOR
select name from sysobjects where type = 'U' order by name
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @tbl
WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE @cmd varchar( 250)
    select @cmd = 'bcp Northwind.dbo.' + @tbl + ' out ' + 'c:\export\' + 
	   @tbl + '.csv -S ' + @@servername + ' -c -t, -U sa' 
    select @cmd = 'master..xp_cmdshell ''' + @cmd + ''''
    exec( @cmd) 
    select @cmd
    FETCH NEXT FROM TableCursor INTO @tbl
END
CLOSE TableCursor
DEALLOCATE TableCurso

2. При передаче больших по размеру таблиц, можно получить дополнительный выигрыш во времени за счёт использования параметра BCP: [-b batchsize]. Подробнее об этом параметре можно прочитать в SQL Server Books Online, поиск по ключевым словам: Batch Switches.

[Содержание]

MS SQL Server  |  01.09.2005  | 

MS SQL Server, Базы данных, похожие статьи

Проблема Я работаю в ведущей архитектурной компании в Омахе, Небраска: HDR, Inc. У компании есть офисы не только в Омахе; по большому счёту, HDR обладает более чем 100 офисами и стройплощадками...
MS SQL Server  |  05.10.2015
По материалам статьи Brian Knight "10 Steps to Securing your SQL Server" Перевод: Сергея Снисаренко
MS SQL Server  |  05.10.2015
В пятницу компания выложила вторую версию community technology preview СУБД SQL Server 2005. Выпуск третьей тестовой версии, которая будет доступна более широкому кругу заказчиков, запланирован на начало будущего года. Финальная версия СУБД должна появиться летом.
MS SQL Server  |  05.10.2015
Автор: mysorian (англ. оригинал). Перевод: lyubayev для Realcoding.NET Пошаговое руководство по созданию отчетов с нуля с использованием генераторов отчетов SQL Server 2000 ВСТУПЛЕНИЕ
MS SQL Server  |  05.10.2015




Яндекс цитирования