Азбука BCP, MS SQL Server, Бази даних, статті

За матеріалами статті 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.

[Зміст]

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*