Главная » Microsoft SQL Server, Базы данных » Выполнение массовых операций

0

В работе часто требуется быстро выполнить копирование больших объемов информации. Это могут быть данные, наработанные за ночь, или “плоские” файлы с разделителями, которые еще нужно обработать. Если несколько сотен мегабайт информации должны переместиться в базы данных SQL Server за ограниченный промежуток времени, то на помощь придут массовые операции.

Популярность формата XML растет с каждым днем, однако еще быстрее увеличиваются размеры файлов этого формата. Теги XML добавляют в файл данных существенный объем, иногда увеличивая его размер вчетверо. Для очень больших файлов чаще используют текстовый формат с разделителями. Для вставки данных из этого наследия прошлого лучше всего использовать массовые операции.

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

?               Простая модель восстановления. В журнал заносятся только текущие транзакции.

?               Модель восстановления с протоколированием массовых операций. Отдельные транзакции массовых операций идут в обход журнала, однако впоследствии в него заносятся данные операции в целом.

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

Дополнительная Более детально о моделях восстановления и их настройке вы узнаете в главе 36. информация Особенности ведения журнала транзакций будут рассмотрены в главе 51.

С технической точки зрения синтаксис инструкции SELECT INTO можно также назвать массовой операцией, поскольку она также обходит стороной журнал транзакций. Она создает новую таблицу из результирующего набора данных инструкции SELECT (см. главу 16).

Операции массовой вставки являются одним этапом процесса ETL (эта аббревиатура буквально переводится как “извлечь, преобразовать, загрузить”). Несмотря на то что программирование этих процессов на языке Т-SQL допустимо, серьезную конкуренцию ему составила служба интеграции SQL Server (Integration Service). Подробно разработка решений службы интеграции описана в главе 42.

Массовая вставка имеет исключительно высокое быстродействие, и я успешно использую ее в производственных процессах. Однако следует особо отметить, что в данном случае исходные данные должны быть предварительно проверены. Вариации типов данных, а также отсутствующие и нерегулярные столбцы могут вызвать серьезные проблемы.

Массовые операции можно выполнить с помощью утилиты командной строки ВСР, инструкции bulk insert языка T-SQL, а также службы интеграции.

Команда bulk insert

Команда bulk insert может использоваться в сценариях Т-SQL и хранимых процедурах для импорта данных в SQL Server. В параметрах этой команды указываются таблица, получающая данные, путь к исходному файлу и параметры.

Если вы хотите протестировать эту команду, воспользуйтесь файлом Address.csv, из которого выполняется загрузка данных в учебную базу данных Ad venture works. Этот файл уже может находиться на вашем жестком диске; также его можно загрузить с сайта MDSN. Его объем — 4 Мбайт, и в нем содержится 19614 строк адресных данных (честно говоря, этого слишком мало для операций ETL).

Следующий пакет поможет выполнить массовую вставку из файла Address в таблицу AWAddress базы данных Adventure works:

Use Tempdb;

CREATE TABLE AWAddressStaging (

ID INT,

Address VARCHAR(500),

City VARCHAR(500),

Region VARCHAR(500),

PostalCode VARCHAR(500),

GUID VARCHAR(500),

Updated DATETIME ) ;

BULK INSERT AWAddressStaging FROM ‘C:\Program Files\Microsoft SQL Server\90\Tools\Samples\ AdventureWorks OLTP\Address.csv1 WITH (FIRSTROW = 1,ROWTERMINATOR =’\n’);

На моем ноутбуке Dell XPS эта операция массовой вставки заняла меньше половины секунды.

Первое, что следует узнать об операции bulk insert, — это то, что каждый столбец источника данных вставляется непосредственно в таблицу назначения, используя отображение “один к одному”. Первый столбец источника вставляется в первый столбец таблицы приемника, второй — во второй и т.д. Если в таблице приемника слишком много столбцов, то лишние игнорируются. Если же в ней столбцов меньше, чем нужно, то из лишних данных источника получится настоящее месиво.

Так как операция bulk insert зависима от расположения столбцов таблиц источника и приемника, то лучше использовать между этими таблицами еще один уровень абстракции в виде представления. Если структуры таблиц источника и приемника не совпадают, скорректируйте представление, и вам не придется вносить глобальные правки в схему базы данных.

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

Дополнительная Команда bulk insert не поддерживает конкатенацию строк и переменных в па- информация раметре from. По этой причине, если вам нужно присоединить путь к имени фай- ‘ ла, сгенерируйте динамическую инструкцию SQL для выполнения массовой вставки. О создании и выполнении динамических инструкций SQL см. в главе 18.

Параметры команды bulk insert

В практических задачах мне постоянно приходилось использовать некоторые параметры команды bulk insert.

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

?               RowTerminator. В этом параметре определяется символ, завершающий строку данных. По умолчанию используется 1 \п1 — стандартный символ перевода строки. Однако файлы на мэйнфреймах и в других системах часто используют другие признаки конца строки. В этом случае откройте шестнадцатеричный редактор и посмотрите, какими кодами завершаются строки в файле, а затем определите конец строки в шестнадцатеричных кодах. К примеру, шестнадцатеричное значение 1 О А’ программируется следующим образом:

ROWTERMINATOR = ‘ОхОА’

?               FirstRow. Этот параметр вам пригодится, если исходный файл содержит заголовки столбцов. В этом случае задайте номер той строки, в которой начинаются реальные данные, заносимые в таблицу.

?               TabLock. Этот параметр позволяет заблокировать всю таблицу для монопольного доступа, что защитит SQL Server от проблем блокирования страниц таблицы, используемых только для вставки. Использование этого параметра может значительно повысить производительность операции, но в то же время не даст возможности в процессе вставки обращаться к таблице другим пользователям. Если массовая вставка является частью процесса ETL, связанного с обособленной таблицей, то это не вызовет проблем. Но если массовая вставка выполняется в рабочую таблицу, из которой пользователи извлекают данные, то использование этого параметра не рекомендуется.

?               Rows per Batch. Этот параметр указывает серверу выполнять в одном пакете вставку только заданного количества строк, а не всего файла. Настройка размера пакета может положительно сказаться на производительности. Я рекомендовал бы начать со 100 строк, а затем поэкспериментировать, чтобы найти приемлемое значение.

?               MaxErrors. Этот параметр определяет, вставка скольких строк должна пройти неудачно, прежде чем будет отменена вся операция. В зависимости от конкретных производственных требований вам может потребоваться установить значение этого параметра в нуль.

Параметр Errorf ile указывает на файл, в котором будут накапливаться стро- Новинка   ки’ отвергнутые операцией bulk insert. В производственных условиях лучше

2005      не пренебрегать этим действием.

Остальные параметры мне ни разу не пригодились на практике. Среди них Check_ Constraints, CodePage, DataFileType, Fire_Triggers, Keepldentity, Keep- Null s, Kilobytes_per_batch и Order. Если данные вставляются изначально в обособленную таблицу и только потом объединяются с основной информацией после выполнения соответствующих преобразований, то использование этих параметров вряд ли уместно.

Операция массовой вставки обрабатывает столбцы в том порядке, в котором они находятся в файле с разделителями. Этот порядок должен в точности совпадать с таблицей-приемником. Массовая вставка в представление добавляет дополнительный уровень абстракции, поэтому в данном случае можно будет внести изменения в порядок столбцов позже.

При создании инструкций массовой вставки часто имеет смысл открывать исходный файл с помощью Excel и проверять данные. Сортировка данных в столбцах поможет выявить существующие аномалии форматирования.

Утилита вср

Утилита ВСР (эта аббревиатура расшифровывается как “программа массового копирования”) — вариация командной строки операции массового копирования. Она отличается от операции массовой вставки тем, что может как импортировать, так и экспортировать данные. Эта утилита использует многие из параметров операции bulk insert. Ее базовый синтаксис следующий:

ВСР таблица_назначения путь_к_файлу_данных параметры

Для таблицы назначения используйте полное четырехкомпонентное имя (сервер. база_ данных, схема, объект). Если хотите ознакомиться с полным синтаксисом этой утилиты, наберите в командной строке ВСР.

Так как эта программа является внешней, она требует авторизации для подключения к серверу. У вас есть два варианта действий: указать в командной строке параметр -Р и запрограммировать пароль в пакетном файле или не указывать параметр – Р, и тогда система сама запросит у вас ввод пароля. Лично мне не нравится ни один из этих вариантов.

Для простых операций ETL я предпочитаю использовать операцию T-SQL bulk insert. В сложных операциях, содержащих преобразования данных, выигрывает использование службы интеграции. Если говорить честно, то я крайне редко прибегаю к услугам утилиты командной строки вср.

Резюме

Эту главу можно считать дополнением к предыдущей — в ней рассматривалась специфическая команда Т-SQL. Массовые операции являются той рабочей лошадкой, которая позволяет импортировать громадные массивы данных, игнорируя журнал транзакций и направляя данные непосредственно в таблицы. Единственным их недостатком является то, что они усложняют план восстановления данных. Лучше всего выполнять операции массовой вставки с помощью команды bulk insert языка T-SQL или проектов службы интеграции.

В следующей главе мы рассмотрим еще одну специфическую функцию Т-SQL, и это одна из моих любимых тем. Присоединяйтесь ко мне и откажитесь от использования курсоров!

Курсор

Язык SQL отлично справляется с обработкой наборов строк. Однако SQL вырос из коротких штанишек старых структур файлов ISAM, и построчная обработка данных осталась только в форме бесконечно медленного курсора.

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

Курсоры SQL создают обманчивое впечатление легко настраиваемых. Когда программисты видят длинный список их параметров, они думают, что с их помощью можно добиться высокой производительности курсоров. Да и типы курсоров имеют такие привлекательные названия, как курсор прямого доступа, динамический и ключевой. Приведу цитату из MSDN: “СУБД Microsoft SQL Server 2000 реализовала курсор с оптимизированной производительностью, назвав его курсором прямого доступа”. Вопрос 70-229 экзамена по проектированию баз данных SQL Server 2000 даже содержал вопрос, какой из курсоров можно образно назвать “пожарным”. Не верьте всему этому. Курсоры не могут быть быстрыми в силу своей природы — они за один раз подбирают одну “крошку” данных.

Второй уровень теории оптимизации (см. главу 6) рассматривает вопросы создания высокопроизводительных систем. Он предполагает создание пакетных, а не итеративных решений. Несмотря на то что в этой главе мы рассмотрим именно итеративный проход по данным с помощью курсора, вы должны из нее вынести стратегический вывод, что нужно максимально ограничить использование курсоров, заменяя их пакетами запросов.

Дополнительная Курсоры SQL Server являются серверными, что ^информаций отличает их от клиентских курсоров ADO. Эти

            w курсоры размещаются в сервере перед тем, как

какие-либо данные отправляются клиенту. Клиентские курсоры часто используются для прокрутки строк в наборах данных ADO при заполнении пользовательских форм.

О          курсорах ADO речь пойдет в главе 30.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии