Лекции по АБД. Лекция 9. Импорт и экспорт данных
 Скачать 267.25 Kb.
|
|
Импорт и экспорт данных Большое количество данных, находящихся в системе Microsoft SQL Server вводится непосредственно пользователями в приложениях, однако часто возни- кает необходимость импорта/экспорта данных. SQL Server предоставляет для этого набор инструментов: оператор BULK INSERT и функции OPENROWSET реализуются в ядре базы данных, утилиты BCP и служба SSIS являются внеш- ними по отношению к ядру БД. При выполнении массовых операций нередко возникает ряд проблем. Например, когда большие объемы данных должны быть вставлены в таблицы SQL Server, необходимо обеспечить наилучшую производительность. Для этого необходимо уметь правильно настраивать параметры для ограничений, тригге- ров и индексов. Не все данные могут быть введены пользователями базы данных по- строчно. Часто данные должны быть импортированы из внешних источников данных, таких как файлы или другие серверы баз данных. Кроме того, пользова- тели часто просят, чтобы данные из таблиц БД экспортировались в текстовые файлы. Может вызвать проблемы неправильно настроенные параметры сорти- ровки. Корректировка параметров сортировки базы данных часто требует экс- порта и повторного импорта данных из базы данных. Обзор вопросов передачи данных Передача данных. Хотя не все требования передачи данных идентичны, существует стандартный алгоритм, которого придерживается большинство за- дач передачи данных: - извлечение данных из источника данных; - преобразование данных в формат целевой системы; - загрузка данных в целевую систему. Вместе эти три шага обычно называют процессом ETL (Extract, Transform, Load), который может быть реализован с использованием ETL-инструментов. В некоторых ситуациях более подходящим может быть процесс ELT (Extract, Load, Transform) - выполнять преобразования данных уже после их за- грузки в базу данных. Извлечение данных. Извлечение данных, как правило, включает в себя вы- полнение запросов на источнике, или открытие и чтение исходных файлов, хотя есть и другие варианты. В процессе извлечения данных преследуют две следующие общие цели. - Избежать чрезмерного воздействия на систему-источник. Например, не читают целые таблицы данных, когда нужно прочитать только выбранные строки или столбцы. Кроме того, не перечитывают одни и те же данные и в лю- бом случае избегают выполнения операторов, которые блокируют пользователей системы-источника. - Обеспечить согласованность извлечения данных. Например, не вклю- чают в выходные данные одну строку из системы-источника больше, чем один раз. Преобразование данных. Этап преобразования процесса ELT обычно вклю- чает в себя несколько следующих шагов. - Данные должны быть очищены. Например, может потребоваться удалить ошибочные данные или предоставить значения по умолчанию для пропущенных (отсутствующих) столбцов. - Возможно, придется выполнять поиск. Например, входные данные могут включать имя клиента, но базе данных в таком случае может понадобиться иден- тификатор клиента. - Данные должны быть агрегированы. Например, входные данные могут включать в себя все транзакции, которые произошли за день, но базе данных мо- гут понадобиться только ежедневные итоговые значения. - Данные, возможно, потребуется разгруппировать. Это часто называют распределение данных. Например, входные данные могут включать в себя квар- тальные бюджеты, но база данных может потребовать ежедневный бюджет. Помимо этих общих операций, данные, возможно, должны быть в некото- ром роде реорганизованы, например, сведение данных таким образом, чтобы столбцы становились строками, объединяющими нескольких столбцов источ- ника в одну колонку, или разбиение одного исходного столбца на несколько столбцов. Загрузка данных. После того, как данные преобразованы в соответствую- щий формат, их можно загрузить в целевую систему. Вместо выполнения опера- ции вставки данных построчно, можно использовать специальные опции для массовой загрузки данных. Кроме того, можно изменить временную конфигура- цию для повышения производительности операции загрузки. Средства для массового импорта и экспорта данных SQL Server поддерживает массовый экспорт данных из таблиц SQL Server и массовый импорт данных в таблицы или несекционированные представления. Для выполнения этих задач SQL Server предоставляет набор инструментов. Важно понять, какой метод лучше всего использовать и для каких типов сцена- рия. Обзор доступных средств приведен в табл. 1. Таблица 1 Основные средства для выполнения операций импорта/экспорта данных Метод Описание Импорт данных Экспорт данных программа bcp Программа командной строки (bcp.exe), массово экспортирующая и импортирующая данные и со- здающая файлы форматирования. Да Да Инструкция BULK INSERT Инструкция T-SQL, импортирующая данные непосредственно из файла данных в таблицу базы данных или несекционированное представ- ление. Да Нет Инструкция IN- SERT … SELECT * FROM OPEN- ROWSET(BULK…) Инструкция T-SQL INSERT, использующая по- ставщик больших наборов строк OPENROWSET для массового импорта данных в таблицу. OPENROWSET – это табличная функция, для подключения и извлечения данных из источни- ков OLE DB. Полная информация о том, как под- ключиться к источнику данных, указывается в параметрах функции. SQL Server имеет OLE DB, именуемый BULK, который можно использовать Да Нет с помощью функции OPENROWSET. Можно ис- пользовать OPENROWSET для подключения к другим СУБД. мастер импорта и экспорта Создает простые пакеты, которые импортируют и экспортируют данные в многочисленных рас- пространенных форматах, включая базы данных, электронные таблицы и текстовые файлы. Да Да Подробное описание использования этих средств и методов можно найти в электронной документации Microsoft. Повышение производительности передачи данных При выполнении операций массового экспорта/импорта для того, чтобы обеспечить наилучшую производительность, рекомендуется: - минимизировать блокировки; - минимизировать ведение журнала транзакций; - отключить ограничения, индексы и триггеры. Для чего надо минимизировать блокировки. Блокировка используется в компоненте SQL Server Database Engine для синхронизации одновременного до- ступа нескольких пользователей к одному и тому же фрагменту данных. При из- менении фрагмента данных транзакция удерживает блокировку, защищая изме- нения до конца транзакции. По умолчанию SQL Server управляет гранулярно- стью блокировок, начиная с уровня блокировки строк и пытаясь укрупнить бло- кировку, когда значительное число отдельных строк заблокированы в таблице. Управление большим количеством блокировок занимает ресурсы, которые могли бы использоваться для минимизации времени выполнения запросов. При выполнении операций массового импорта, как правило, нецелесообразно ставить блокировки на уровне строк, а заблокировать всю таблицу сразу. Программа bcp и инструкция BULK INSERT и INSERT ... SELECT * FROM OPENROWSET(BULK...) позволяют указать, что на время выполнения операции массового импорта таблица будет блокирована (целиком). Если указана блоки- ровка таблицы для операции массового импорта, то к таблице применяется бло- кировка массового обновления (BU) на время выполнения операции массового импорта. Блокировка BU позволяет одновременно выполнять массовый импорт данных в одну и ту же таблицу нескольким потокам и вместе с тем предотвра- щает доступ к таблице других процессов, не осуществляющих массовый импорт данных. Блокировка таблицы может повысить производительность массового импорта, снизив конкуренцию за эту таблицу. В табл. 2 перечислены квалификаторы, определяющие блокировку таблиц в командах массовой загрузки. Таблица 2 Квалификаторы, задающие блокировку таблиц Команда Квалификатор Тип квалификатора программа bcp -h " TABLOCK " Подсказка Инструкция BULK INSERT TABLOCK Аргумент Инструкция INSERT … SELECT * FROM OPENROWSET(BULK…) WITH(TABLOCK) Табличная под- сказка Минимизация ведение журнала транзакций В простой модели восстановления массовые операции производятся с ми- нимальным протоколированием. В БД, использующей модель полного восста- новления, все операции вставки строк полностью записываются в журнал тран- закций. Во время импорта большого количества данных это может привести к быстрому заполнению журнала транзакций. Чтобы выполнять операции массо- вого импорта с минимальным протоколированием в базе данных, которая обычно использует модель полного восстановления, ее рекомендуется сначала переключить на модель восстановления с неполным протоколированием, а после выполнения массового импорта данных снова переключить на модель полного восстановления. Не все команды могут использовать минимальное протоколирование. Спо- собствовать минимизации ведения журнала помогут следующие рекомендации. - Таблица не реплицируется. - Указана блокировка таблицы (с помощью TABLOCK). - Если у таблицы нет кластеризованного индекса, но имеется один или не- сколько некластеризованных индексов, страницы данных всегда протоколиру- ются минимально. Однако, как идет запись страниц индекса в журнал, зависит от того, пуста ли таблица. - Если таблица пуста, страницы индекса протоколируются минимально. - Если таблица не пуста, страницы индекса протоколируются полностью. - Если таблица имеет кластеризованный индекс и пуста, ведется минималь- ная запись страниц данных и индекса в журнал. - Если таблица имеет кластеризованный индекс и не пуста, страницы дан- ных и индекса протоколируются полностью, независимо от модели восстановле- ния. Это не исчерпывающий перечень ограничений, которые должны быть вы- полнены для того, чтобы минимизировать запись в журнал транзакций. Отключение и перестроение индексов Чтобы в процессе импорта или обновления не проверять каждое значение каждого индекса для каждой строки, можно повысить общую производитель- ность путем отключения процесса обслуживания индексов до тех пор, пока все данные не будут загружены. Для этого можно индекс не удалять из базы данных, а только отключить его, т.е. метаданные об индексе остаются, просто останавли- вается его обновление. Запросы не будут использовать отключённые индексы. Отключение индекса обеспечивает: - предотвращает доступ пользователей к индексу; - предотвращает доступ к данным, если индекс кластеризованный; - сохраняет определение индекса в метаданных; - ускоряет импорт данных в таблицах. Индекс можно отключить с помощью графического интерфейса в SSMS или с помощью инструкции ALTER INDEX. В следующем примере кода отклю- чается индекс с именем idx_emailaddress в таблице БД dbo.Customer. ALTER INDEX idx_emailaddress ON dbo.Customer DISABLE; Можно отключить все индексы сразу в таблице БД dbo.Customer, как по- казано в следующем примере. ALTER INDEX ALL ON dbo.Customer DISABLE; Кластеризованный индекс определяет структуру таблицы. Если отключен кластеризованный индекс, таблица становится недоступной до тех пор, пока ин- декс не будет перестроен. После завершения импорта данных можно перестроить (фактически со- здать заново) индексы для таблицы с помощью графических средств в среде SSMS, с помощью инструкции ALTER INDEX или команды DBCC DBREINDEX. В следующем примере кода показано, как перестроить индекс с именем idx_emailaddress в таблице БД dbo.Customer. ALTER INDEX idx_emailaddress ON dbo.Customer REBUILD; Вы также можете использовать ключевое слово ALL с инструкции ALTER INDEX для перестроения всех индексов в указанной таблице (аналогично при- меру отключения индекса). Если загружен большой объем данных более эффективным может быть удаление существующих индексов и повторное создание индексов. Для повтор- ного создания индекса, который заменит существующий, можно использовать инструкцию CREATE INDEX с параметром DROP_EXISTING, как показано в сле- дующем примере. CREATE INDEX idx_emailaddress ON dbo.Customer(EmailAddress) WITH (DROP_EXISTING = ON); Отключение и включение ограничений Ограничения используются, чтобы обеспечить соблюдение правил целост- ности данных. Ограничения PRIMARY KEY и UNIQUE. SQL Server создает индексы для обеспечения этих ограничений. Чтобы отключить первичный ключ или ограни- чение уникальности, необходимо отключить индекс, связанный с ограничением. Это, как правило, используется только для некластеризованного первичного ключа. При повторном включении ограничения, соответствующие индексы ав- томатически восстанавливаются. Если во время перестройки индекса будут найдены повторяющиеся значения, повторное включение ограничения завер- шится ошибкой. По этой причине, если вы отключаете эти ограничения при им- порте данных, то должны быть уверены, что импортируемые данные не будут нарушать соблюдение этих ограничений. Ограничения FOREIGN KEY и CHECK. Ограничения внешнего ключа ис- пользуются, чтобы убедиться, что сущности в одной таблице, на которые ссыла- ются сущности из другой, на самом деле существуют. Например, поставщик дол- жен существовать до того, как может быть введен заказ на поставку. Ограниче- ния внешнего ключа при проверке ссылок используют первичный ключ или ограничения уникальности. Поэтому, если отключить первичный ключ или огра- ничение уникальности, на которое оно указывает, то ограничение внешнего ключа автоматически отключается. Тем не менее, при повторном включении первичного ключа или ограничения уникальности, ограничения внешнего ключа, которые на них ссылаются, не будут включены автоматически. Ограничения check можно использовать для ограничения значений, кото- рые могут содержаться в столбце или взаимосвязи между значениями в несколь- ких столбцах таблицы. Можно отключить и включить ограничения FOREIGN KEY и CHECK с помощью параметры CHECK и NOCHECK. Пример кода для отключения и включения ограничения с именем SalaryCap: ALTER TABLE Person.Salary NOCHECK CONSTRAINT SalaryCap; ALTER TABLE Person.Salary СНЕСК SalaryCap; Также можно отключить или включить все ограничения, заменив в ин- струкции ALTER TABLE имя ограничения на ключевое слово ALL. Копирование и перемещение баз данных В некоторых случаях необходимо скопировать или переместить всю базу данных с одного экземпляра SQL Server на другой. Это можно выполнить одним из следующих способов: - с помощью мастера копирования баз данных (Copy Database Wizard); - при помощи отсоединения (detach) и присоединения (attach); - путем создания (backup) и восстановления (restore) резервных копий; - приложений уровня данных (Data-tier applications). С помощью мастера копирования баз данных можно легко перемещать или копировать базы данных и их объекты с одного сервера на другой, без пере- рывов в работе сервера. Можно также обновить базы данных с прошлой версии SQL Server до версии SQL Server 2017. С помощью этого мастера можно сделать следующее. - Выбрать исходный и целевой серверы. - Выбрать базы данных для перемещения, копирования или обновления. - Указать расположение файлов для баз данных. - Создать имена входа для целевого сервера. - Копировать дополнительные вспомогательные объекты, задания, пользо- вательские хранимые процедуры и сообщения об ошибках. - Задать расписание перемещения или копирования баз данных. Мастер предоставляет два метода копирования или перемещения базы дан- ных. Он может быть настроен на использование метода отсоединения и присо- единения: это самый быстрый вариант, но имеет недостаток, так как исходная база данных должна находиться в автономном режиме. Второй – метод SMO, использует библиотеки объектов управления SQL Server (Server Management Objects) для создания объектов и передачи данных. Этот метод выполняет чтение определения каждого объекта базы данных-источ- ника и создает каждый из этих объектов в целевой базе данных. После этого про- исходит перенос данных из исходных таблиц в целевые таблицы с воссозданием индексов и метаданных. Этот вариант медленнее, но исходная база данных во время копирования может находиться в режиме online. Если в мастере выбран параметр «Переместить», то после перемещения базы данных мастер автоматически удаляет базу данных-источник. Если выбран параметр «Копировать», исходная база данных остается без изменений. Существует ряд ограничений: - мастер копирования баз данных недоступен в выпуске Express; - мастер копирования базы данных нельзя использовать для перемещения или копирования системных баз данных; - после обновления БД возврат к предыдущей версии невозможен; - на сервере назначения должен быть запущен агент SQL Server. Если исходная база данных используется, когда мастер пытается переме- стить или скопировать базу данных, операция не выполняется. Запуск мастера копирования баз данных требует привилегий системного администратора (sysadmin) на обоих экземплярах (как на исходном, так и на це- левом сервере) и наличия сетевого подключения. Использование операций Detach и Attach. Отсоединение базы данных в SQL Server можно выполнить с помощью среды SSMS или T-SQL. Отсоединенные файлы останутся на диске и могут быть повторно присоединены с помощью среды SSMS или с помощью инструкции CREATE DATABASE с параметрами FOR ATTACH или FOR ATTACH_REBUILD_LOG. Файлы можно также переместить на другой сервер и подсоединить там. Не рекомендуется подключать или восстанавливать базы данных, получен- ные из неизвестных или ненадежных источников. В этих базах данных может содержаться вредоносный код, вызывающий выполнение непредусмотренных инструкций T-SQL или появление ошибок из-за изменения схемы или физиче- ской структуры базы данных. Перед тем как использовать базу данных, получен- ную из неизвестного или ненадежного источника, выполните на тестовом сер- вере инструкцию DBCC CHECKDB для этой базы данных, а также изучите ис- ходный код в базе данных, например, хранимые процедуры и другой пользова- тельский код. Для отсоединения БД используется системная хранимая процедура sp_detach_db, которой надо передать как минимум один обязательный параметр – имя отсоединяемой базы данных. Следующий код выполняет отсоединение БД Works20. EXEC sp_detach_db 'Works20', 'true'; В этом примере указан второй – необязательный – параметр skipchecks в значении TRUE, чтобы не выполнять инструкцию UPDATE STATISTICS. Чтобы явно запустить инструкцию UPDATE STATISTICS, надо указать значение FALSE. При выполнении операции отсоединения, необходимо учитывать следую- щие обстоятельства. - Невозможно отсоединить базу данных, если она в настоящий момент ис- пользуется. Для отсоединения базы данных требуется монопольный доступ, для чего надо переключить ее в режим SINGLE_USER. Например, следующая инструкция ALTER DATABASE получает монополь- ный доступ к базе данных Works20 после отключения от этой базы данных всех текущих пользователей: USE master; ALTER DATABASE Works20 SET SINGLE_USER; GO - База данных помечена как подозрительная. Подозрительную базу данных перед ее отсоединением необходимо перевести в аварийный режим. - База данных является системной базой данных. - Перед отсоединением базы данных необходимо удалить все моменталь- ные снимки, если таковые имеются. - Для выполнения операций требуется членство в предопределенной роли сервера sysadmin. - При отсоединении базы данных все метаданные удаляются. Если эта база данных была базой данных по умолчанию для учетной записи входа, базой дан- ных по умолчанию становится master. Копирования базы данных с помощью Backup и Restore. В SQL Server можно создать новую базу данных, восстановив резервную копию пользователь- ской базы данных, созданной в SQL Server 2005 или более поздней версии. Од- нако резервные копии баз данных master, model и msdb, созданных в более ран- ней версии SQL Server, восстановить на SQL Server 2017 невозможно. Кроме того, резервные копии, созданные в SQL Server 2017, невозможно восстановить в более ранних версиях SQL Server. При использовании резервного копирования и восстановления для копи- рования базы данных на другой экземпляр SQL Server компьютер-источник и це- левой компьютер могут быть любой платформой, на которой запускается SQL Server. Основные этапы данной технологии следующие. 1. Создайте резервную копию базы данных источника, которая может находиться на экземпляре SQL Server 2005 или более поздней версии. Компью- тер, на котором запущен этот экземпляр SQL Server, называется компьютером- источником. 2. На компьютере, куда нужно скопировать базу данных (целевой компь- ютер), подключите экземпляр SQL Server, на котором будет восстановлена база данных. При необходимости создайте те же устройства резервного копирования на целевом экземпляре сервера, что использовались для резервного копирования баз данных-источников. 3. Восстановите резервную копию базы данных источника на целевом ком- пьютере. При восстановлении базы данных автоматически создаются все ее файлы. Существуют ряд других вопросов, влияющих на процесс копирования БД. При восстановлении базы данных необходимые файлы базы данных созда- ются автоматически. По умолчанию у файлов, созданных SQL Server в процессе восстановления, те же имена и пути, что и у файлов резервной копии исходной базы данных на компьютере-источнике. В некоторых ситуациях при восстанов- лении базы данных необходимо указать сопоставление дисков, имена файлов или путь для восстановления. Например, на целевом диске может быть недоста- точно свободного места, или используется имя базы данных, которое уже суще- ствует на целевом сервере восстановления, а имя каждого из ее файлов совпадает с именем файла базы данных в резервном наборе данных. Во избежание ошибок и непредвиденных последствий перед операцией восстановления можно использовать таблицы журнала backupfile, чтобы найти в резервной копии файлы базы данных и журнала, которые планируется восстано- вить. При восстановлении базы данных на другом компьютере имя входа поль- зователя SQL Server или Microsoft Windows, начавшего процесс восстановления, автоматически становится именем владельца базы данных. При восстановлении базы данных системный администратор или владелец новой базы данных могут сменить ее владельца. Для предотвращения несанкционированного восстановле- ния базы данных устанавливайте пароли на носители или сами резервные копии. Чтобы обеспечить целостность работы пользователей и приложений при восстановлении базы данных на другой экземпляр сервера, на новом экземпляре необходимо повторно создать некоторые или все метаданные, например имена входа и задания. Создание новой учетной записи может быть произведено с помощью си- стемной хранимой процедуры sp_addlogin. sp_addlogin [@login=] 'учетная_запись' [, [@password=] 'пароль'] [, [@defdb=] 'база_данных_по_умолчанию'] После завершения аутентификации и получения идентификатора учетной записи (login ID) пользователь считается зарегистрированным, и ему предостав- ляется доступ к серверу. Для каждой базы данных, к объектам которой он наме- рен получить доступ, учетная запись пользователя(login) ассоциируется с поль- зователем (user) конкретной базы данных, что осуществляется посредством про- цедуры sp_adduse. sp_adduser [@loginame=] 'учетная_запись' [, [@name_in_db=] 'имя_пользователя'] [, [@grpname=] 'имя_роли'] Data Export for SQL Server – это мощный инструмент, предназначенный для быстрого экспорта данных из баз данных SQL Server в любой из 20 доступ- ных форматов, включая MS Access, MS Excel, MS Word (RTF), HTML, XML, PDF, TXT, CSV, DBF, ODF, ACCDB и другие. Data Export for SQL Server располагает удобным мастером настройки для визуальной установки параметров экспорта для каждой таблицы (конечные имена файлов, экспортируемые поля, форматы данных и многое другое) и консольной утилитой для быстрого экспорта данных из таблиц и запросов (см. файл в папке КП ТЕМА 19 --- Data Export for SQL Server). |