Управление пакетами для массового импорта данных

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

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

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

Примечание

Если задается размер пакета или размер файла данных, точность не особо важна.

В следующей таблице дается сводка квалификаторов, поддерживающих эти решения.

Команда	Размер пакета	Количество строк в пакете	Количество килобайт в пакете
bcp	-b batch_size	-h "ROWS_PER_BATCH = bb"	-h "KILOBYTES_PER_BATCH = cc"
BULK INSERT	BATCHSIZE = batch_size	ROWS_PER_BATCH = rows_per_batch	KILOBYTES_PER_BATCH = kilobytes_per_batch Примечание При использовании этого параметра вместе с BATCHSIZE формируется ошибка.
INSERT... SELECT * FROM OPENROWSET (BULK...)	—	ROWS_PER_BATCH = rows_per_batch	—

1. В команде bcp не следует использовать одновременно аргумент -b batch_size и подсказку ROWS_PER_BATCH или KILOBYTES_PER_BATCH. При их совместном использовании формируется ошибка.

2. В команде BULK INSERT, если аргумент BATCHSIZE используется вместе с ROWS_PER_BATCH или KILOBYTES_PER_BATCH, у BATCHSIZE преимущество.

3. У функции OPENROWSET нет ни аргумента BATCHSIZE, ни KILOBYTES_PER_BATCH.

Задание приблизительного размера пакета

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

Чтобы импортировать файл данных в виде последовательности пакетов приблизительно заданного размера, используйте следующий квалификатор.

· Для команды bcp: -b

· Для инструкции BULK INSERT: BATCHSIZE

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

Примечание

Размеры пакета не применяются при выполнении экспорта данных из экземпляра SQL Server в файл данных.

Задание приблизительного размера файла данных

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

· Оценка количества строк в пакете

Чтобы оценить количество строк, используйте подсказку или параметр ROWS_PER_BATCH. При задании значения > 0 обработчик запросов использует значение ROWS_PER_BATCH в качестве подсказки для назначения ресурсов в плане запроса. Значение должно быть того же порядка, что и реальное количество строк.

Хотя все строки из файла данных копируются в экземпляр SQL Server одним пакетом, программа bcp через каждые 1000 строк отображает сообщение: «SQL Server передано 1 000 строк». Это сообщение служит только в информационных целях и возникает независимо от размера пакета.

· Оценка количества килобайт в пакете

Чтобы оценить объем файла данных в килобайтах, используйте подсказку или параметр KILOBYTES_PER_BATCH. SQL Server оптимизирует операцию массового импорта согласно заданному значению.

Примечание

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

 

CHECK_CONSTRAINTS

Указывает, что при выполнении операции массового импорта будет выполняться проверка всех ограничений целевой таблицы или представления. Без параметра CHECK_CONSTRAINTS все ограничения CHECK и FOREIGN KEY пропускаются, и после завершения операции ограничение таблицы помечается как ненадежное.

Примечание

Ограничения UNIQUE, PRIMARY KEY и NOT NULL проверяются в любом случае.

Рано или поздно необходимо проверять всю таблицу на соответствие ограничениям. Если таблица перед началом операции массового импорта была не пуста, затраты на повторную проверку ограничений могут превысить затраты на применение ограничений CHECK к добавочным данным.

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

CODEPAGE = { ' ACP ' | ' OEM ' | ' RAW ' | ' code_page ' }

Указывает кодовую страницу данных в файле данных. Аргумент CODEPAGE имеет смысл только в том случае, если данные содержат столбцы типа char, varchar или text с символами, коды которых больше 127 или меньше 32.

Значение аргумента CODEPAGE	Описание
ACP	Столбцы типа char, varchar или text преобразуются из кодовой страницы ANSI/Microsoft Windows (ISO 1252) в кодовую страницу SQL Server.
OEM (по умолчанию)	Столбцы типов данных char, varchar и text преобразуются из системной кодовой страницы OEM в кодовую страницу SQL Server.
RAW	Преобразование кодовой страницы не производится. Это самый быстрый режим.
code_page	Номер кодовой страницы, например 850. Важно! SQL Server не поддерживает кодовую страницу 65001 (кодировка UTF-8).

 

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }

Указывает, что инструкция BULK INSERT выполняет импорт из файла определенного типа.

Значение DATAFILETYPE	Представление данных
char (по умолчанию)	В символьном формате. Дополнительные сведения см. в разделе Применение символьного формата при импорте и экспорте данных.
native	В собственных типах базы данных. Создайте файл данных собственных типов путем массового импорта данных из SQL Server с помощью программы bcp. Значение собственного типа обеспечивает более высокую производительность по сравнению со значением типа char. Дополнительные сведения см. в разделе Использование собственного формата для импорта и экспорта данных.
widechar	В Юникоде. Дополнительные сведения см. в разделе Использование символьного формата Юникода для импорта и экспорта данных.
widenative	В собственных типах базы данных, за исключением столбцов типа char, varchar и text, в которых данные хранятся в Юникоде. Файл данных типа widenative создан путем массового импорта данных из SQL Server с помощью программы bcp. Тип widenative обеспечивает более высокую производительность по сравнению с widechar. Если файл данных содержит символы национального алфавита ANSI, укажите значение widenative. Дополнительные сведения см. в разделе Использование собственного формата Юникода для импорта или экспорта данных.

 

FIELDTERMINATOR =' field_terminator '

Указывает признак конца поля, используемый для файлов данных типа char и widechar. По умолчанию, признаком конца поля является символ табуляции (\t). Дополнительные сведения см. в разделе Определение признаков конца поля и строки.

FIRSTROW = first_row

Указывает номер первой строки для загрузки. Значение по умолчанию — первая строка указанного файла данных. Значения аргумента FIRSTROW начинаются с 1.

Примечание

Атрибут FIRSTROW не предназначен для пропуска заголовков столбцов. Пропуск заголовков не поддерживается инструкцией BULK INSERT. При пропуске строк компонент SQL Server Database Engine выполняет поиск только в признаках конца поля и не проверяет данные в полях пропущенных строк.

FIRE_TRIGGERS

Указывает, что при массовом импорте будут выполняться триггеры типа INSERT, определенные для целевой таблицы. Если для операции INSERT определены триггеры в целевой таблице, они будут срабатывать для каждого загруженного пакета.

Если параметр FIRE_TRIGGERS не указан, триггеры Insert не выполняются.

Дополнительные сведения см. в разделе Управление выполнением триггеров при массовом импорте данных.

FORMATFILE =' format_file_path '

Указывает полный путь к файлу форматирования. Файл форматирования содержит описание файла данных — сведения, полученные с помощью программы bcp на такой же таблице или представлении. И предназначен для случаев, когда:

· файл данных содержит больше или меньше столбцов, чем таблица или представление;

· столбцы расположены в другом порядке;

· отличаются разделители столбцов;

· имеются какие-либо другие изменения в формате данных. Файлы форматирования обычно создаются с помощью программы bcp и затем при необходимости изменяются в текстовом редакторе. Дополнительные сведения см. в разделе Программа bcp.

KEEPIDENTITY

Указывает, что значение или значения идентификаторов в файле импортированных данных будут использоваться для столбца идентификаторов. Если параметр KEEPIDENTITY не указан, значения идентификаторов для этого столбца проверяются, но не импортируются, а SQL Server автоматически назначает уникальные значения на основе начального значения и приращения, указанных при создании таблицы. Если файл данных не содержит значений для столбца идентификаторов, укажите в файле форматирования, что столбец идентификаторов в таблице или представлении при импорте данных следует пропустить. В этом случае SQL Server автоматически назначит уникальные значения для этого столбца. Дополнительные сведения см. в разделе DBCC CHECKIDENT (Transact-SQL).

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

KEEPNULLS

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

KILOBYTES_PER_BATCH = kilobytes_per_batch

Определяет приблизительное число килобайт данных в пакете как kilobytes_per_batch. По умолчанию, значение KILOBYTES_PER_BATCH неизвестно. Дополнительные сведения о вопросах производительности см. в подразделе «Примечания» далее в этом разделе.

Дополнительные сведения см. в разделе Управление пакетами для массового импорта данных.

LASTROW = last_row

Указывает номер последней строки для загрузки. Значение по умолчанию 0, что обозначает последнюю строку в указанном файле данных.

MAXERRORS = max_errors

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

Примечание

Параметр MAX_ERRORS не применяет проверки ограничения или преобразование типов данных money и bigint.

ORDER ({ column [ ASC | DESC ] } [,... n ])

Указывает, каким образом отсортированы данные в файле. Производительность массового импорта увеличивается, если импортируемые данные упорядочены согласно кластеризованному индексу таблицы (при наличии). Если файл данных упорядочен в другом порядке, то есть в порядке отличном от порядка ключа кластеризованного индекса или если в таблице отсутствует кластеризованный индекс, то предложение ORDER не обрабатывается. В целевой таблице должны быть указаны имена столбцов. По умолчанию, операция массовой вставки считает, что файл данных не отсортирован. Для оптимизированного массового импорта SQL Server также проверяет сортировку импортированных данных.

Дополнительные сведения см. в разделе Управление порядком сортировки во время массового импорта данных.

n

Местозаполнитель, означающий, что может быть указано несколько столбцов.

ROWS_PER_BATCH = rows_per_batch

Указывает приблизительное число строк в файле данных.

По умолчанию все данные в файле отправляются на сервер за одну транзакцию, а число строк в пакете оптимизатору запросов неизвестно. Если указать аргумент ROWS_PER_BATCH (со значением > 0), сервер будет использовать это значение для оптимизации операции массового импорта. Значение, указанное в ROWS_PER_BATCH, должно приблизительно совпадать с фактическим числом строк. Дополнительные сведения о вопросах производительности см. в подразделе «Примечания» далее в этом разделе.

Дополнительные сведения см. в разделе Управление пакетами для массового импорта данных.

ROWTERMINATOR =' row_terminator '

Указывает признак конца строки, используемый для файлов данных типа char и widechar. По умолчанию признаком конца строки является символ \r\n (символ переноса строки). Дополнительные сведения см. в разделе Определение признаков конца поля и строки.

TABLOCK

Указывает необходимость запроса блокировки уровня таблицы на время выполнения массового импорта. Если таблица не имеет индексов и указано ключевое слово TABLOCK, загрузка в таблицу может производиться параллельно несколькими клиентами. По умолчанию работа блокировки определяется параметром таблицы table lock on bulk load. Блокировка на время выполнения массового импорта значительно повышает производительность, позволяя снизить конфликты блокировок таблицы. Дополнительные сведения о вопросах производительности см. в подразделе «Примечания» далее в этом разделе.

Дополнительные сведения см. в разделе Управление операциями блокировки при массовом импорте.

ERRORFILE =' file_name '

Указывает файл, используемый для сбора строк, которые имеют ошибки форматирования и не могут быть преобразованы в набор строк OLE DB. Эти строки без изменений копируются из файла данных в файл ошибок.

Файл ошибок создается на стадии выполнения команды. Если он уже существует, возникает ошибка. Дополнительно создается управляющий файл с расширением ERROR.txt. в котором содержатся ссылки на каждую из строк в файле ошибок и диагностические сведения. После исправления ошибок эти данные могут быть повторно загружены.

Замечания

Инструкция BULK INSERT может быть выполнена в пользовательской транзакции. Откат пользовательской транзакции, содержащей инструкцию BULK INSERT с предложением BATCHSIZE, производящим импорт данных в таблицу или представление несколькими пакетами, вызывает откат всех пакетов, отправленных на SQL Server.

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

В SQL Server 2005 и более поздних версиях в инструкцию BULK INSERT включена новая, более строгая проверка загружаемых из файла данных, что может вызвать прекращение работы существующих сценариев, которые ранее работали с неправильными данными. В частности, теперь BULK INSERT проверяет, что:

· собственные представления типов данных float или real являются допустимыми;

· данные в Юникоде имеют четную длину.

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

Ограничения

При использовании файла форматирования с инструкции BULK INSERT можно указать не более 1024 поля. Это значение совпадает с максимальным числом столбцов в таблице. При использовании инструкции BULK INSERT с файлом данных, который содержит больше 1024 полей, формируется ошибка 4822. Программа bcp не имеет этого ограничения, поэтому для файлов данных, которые содержат больше 1024 поля, используйте команды bcp.

Вопросы производительности

Если число страниц, которые должны быть записаны на диск в едином пакете, превышает внутренний порог, может быть произведен полный просмотр буферного пула для определения страниц, подлежащих записи на диск при фиксации пакета. Такой полный просмотр может повредить производительности массового импорта. Превышение внутреннего порога может возникнуть, если большой буферный пул работает с медленной подсистемой ввода-вывода. Избежать переполнения буфера на больших компьютерах можно либо отказавшись от использования подсказки TABLOCK (что удалит оптимизацию массовых операций), либо задав меньший размер пакета (что сохранит оптимизацию массовых операций).

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