Файлы формата

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

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

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

Некоторые столбцы в целевой таблице должны быть пропущены.

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

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

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

Возможность создания применяемого по умолчанию файла формата (который, допустим, должен быть задан как отличный от XML в целях обеспечения обратной совместимости), предназначенного для использования в качестве исходного файла, предоставляется при эксплуатации утилиты ВСР в интерактивном режиме. После ввода ответов на приглашения, требующие предоставления информации о столбцах, пользователю дается возможность сохранить файл формата. По умолчанию файл формата имеет имя ВСР. f mt, но ему может быть присвоено любое допустимое имя файла.

Чтобы создать с учетом указанных условий применяемый по умолчанию файл формата для таблицы HumanResources. Department базы данных AdventureWorks, можно вызвать на выполнение следующую команду:

ВСР AdventureWorks.HumanResources.Department out с:\department.txt -Т

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

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

формата; в рассматриваемом случае решено сохранить этот файл под именем Department . f mt. В дальнейшем этот файл формата можно отредактировать в соответствии с конкретными требованиями с помош;ью любого текстового редактора, такого как Notepad Windows.

Рассмотрим только что подготовленный файл формата:

9.0 4

1 SQLSMALLINT О 2 , 1 DepartmentID

2 SQLNCHAR 2 100 2 Name SQL Latinl General CPl CI AS

3 SQLNCHAR 2 100 3 GroupName SQL Latinl General CPl CI AS

4 SQLDATETIME 0 8 , 4 ModifiedDate

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

Первый столбец в этих строках указывает номер поля исходного файла. Нумерация начинается с 1 и охватывает все поля. За этим номером следует обозначение типа данных исходного файла. В файле, рассматриваемом в качестве примера, используются данные нескольким разных типов. Весь текст представлен в формате Unicode, поэтому типом данных всех полей с символьными данными является SQLNCHAR (Unicode), причем, если в данных не содержатся специальные символы, то можно с таким же успехом предусмотреть использование формата SQLCHAR (ASCII).

Следующие два поля описывают префикс и длину данных для полей данных. Префикс указывает, сколько байтов используется для представления информации о длине поля с данными. Благодаря использованию префикса появляется возможность указать реалыгую длину данных в файле ВСР и сократить объем, применяемый для размещения данных в файле. Поле, содержащее сведения о длине данных, указывает максимальную длину данных, хранимых в текущем поле. В следующем столбце представлен разделитель полей (разграничитель). В данном случае в качестве разделителя полей используется запятая, а разделителем строк служит символ конца строки. Следующие два столбца описывают столбцы целевой таблицы; в них указаны номер столбца целевой таблицы и имя столбца этой таблицы. В данном примере имеется непосредственное соответствие между столбцами целевой таблицы и полями строк исходного файла, поэтому номера столбцов и полей совпадают, но в файле формата могут быть заданы другие варианты согласования, а не только непосредственное согласование. В последнюю очередь указана схема упорядочения для каждого столбца (напомним, что в SQL Server 2000 и более новых версиях допускается использование разных схем упорядочения в различных столбцах одной и той же таблицы).

А теперь перейдем к рассмотрению версии файла формата на языке XML. Для создания этой версии вызывается на выполнение почти такая же команда, но с добавлением параметра -х:

ВСР AdventureWorks.HumanResources.Department out c:\department.txt -Т -x

В результате формируется файл формата, который выглядит совершенно иначе по сравнению с файлом формата, отличным от XML: <?xml version= l.0 ?>

<BCPFORMAT xmlns= http: schemas.microsoft.com/sqlserver/2004/bulkload/format xmlns:xsi= http: www.w3.org/2001/XMLSchema-instance >

<RECORD>

<FIELD ID= 1 xsi:type= NativeFixed LENGTH= 2 />

<FIELD ID= 2 xsi:type= NCharPrefix PREFIX LENGTH= 2 MAX LENGTH= 100 COLLATION= SQL Latinl General CPl CI AS />

<FIELD ID= 3 xsi:type= NCharPrefix PREFIX LENGTH= 2 MAX LENGTH= 100 COLLATION= SQL Latinl General CPl CI AS />

<FIELD ID= 4 xsi:type= NativeFixed LENGTH= 8 /> </RECORD> <ROW>

<COLUMN S0URCE= 1 NAME= DepartmentID xsi:type= SQLSMALLINT /> <COLUMN S0URCE= 2 NAME= Name xsi:type= SQLNVARCHAR /> <COLUMN S0URCE= 3 NAME= GroupName xsi:type= SQLNVARCHAR /> <COLUMN S0URCE= 4 NAME= ModifiedDate xsi:type= SQLDATETIME /> </ROW> </BCPFORMAT>

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

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

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

Подготовка файлов формата в условиях отсутствия согласования между столбцами

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

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

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