вторник, 21 января 2014 г.

Распространённые технологии импорта данных

Под распространёнными технологиями подразумеваются те, которые часто используются для импорта данных. Разумеется, для некоторых задач чаще могут использоваться технологии импорта, которые не вошли в список ниже.

1. BULK INSERT/bcp

bcp - это утилита командной строки для импорта/экспорта данных, входящая в состав Microsoft SQL Server 2012 tools (для более ранних версий она тоже присутствует). BULK INSERT - команда T-SQL, позволяющая импортировать данные тем же способом что и bcp, но без использования командной строки.

Основное назначение bcp - быстрое копирование данных между аналогичными по структуре таблицами. В принципе, с помощью файла формата (указанного в качестве опции) можно копировать данные между таблицами с разной структурой. Нередко используется копирование с помощью bcp в промежуточные таблицы, из которых средствами T-SQL данные преобразуются в итоговые таблицы.

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

Таким образом, bcp - идеальный выбор для переноса данных из одной таблицы в некоторой БД MSSQL, в другую таблицу в другой БД MSSQL если для данных не предполагаются преобразования. В более сложных случаях bcp имеет смысл рассматривать, когда объём данных велик и важна скорость (не зря в тестах TPC для MSSQL обычно используется bcp).

Исходя из своих возможностей, bcp обычно используется для первоначального импорта данных, хотя никто не запрещает использовать её для других задач.

2. Прямые запросы

Под прямыми запросами для импорта данных подразумеваются, как правило, запросы на T-SQL вида "INSERT ... SELECT ... FROM ..." или "SELECT ... INTO ... FROM ...". Подробнее о синтаксисе можно прочитать в документации (по SELECT и INSERT). Сами эти запросы могут быть в некоторой хранимой процедуре или где-то ещё.

Прямые запросы для импорта можно использовать, когда таблица (или таблицы) с исходными данными доступны в текущей БД. Самый простой вариант, когда все таблицы находятся в одной БД, однако это, согласитесь, странный вариант для импорта.

В случае, когда все таблицы находятся на одном MSSQL-сервере и соответствующим образом настроены права, можно использовать полное имя таблицы во FROM (database.schema.table_name). Если одна из таблиц находится на другом сервере или вообще не в MSSQL, её часто тоже можно использовать во FROM с помощью linked servers (о них пойдёт речь в следующем разделе, здесь подразумеваем что таблица на том же сервере).

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

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

3. Linked servers

Linked servers - это подключенные к текущему экземпляру MSSQL внешние источники данных (подключение обычно происходит через OLEDB). Причём источники могут быть самыми разнообразными, от Oracle и MySQL до файлов Excel. Разумеется, можно подключиться и к другому MSSQL-серверу.

Часто когда говорят про эту технологию, подразумевают заодно и OPENROWSET. В принципе, можно рассматривать OPENROWSET как обращение к linked server, при котором мы каждый раз указываем параметры соединения, а с помощью sp_addlinkedserver мы можем сохранить эти параметры в linked server.

Linked servers дают нам возможность использовать внешние источники данных во FROM с помощью такого именования: linked_server.catalog.schema.object. При этом для некоторых источников данных каталог (БД) и схема могут не использоваться, в этом случае применяется подобное именование: linked_server...object.

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

С другой стороны, при использовании linked servers вы можете столкнуться с проблемами. Обычно проблемы возникают с точки зрения безопасности (особенно если внешний источник доступен только через Internet) и настройки доступа. Также важно понимать, что обращения к linked server по определению более медленные, чем к таблицам в той же БД (хотя в некоторых случаях MSSQL может даже использовать статистику другого сервера для более эффективного соединения таблиц).

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

4. Программный код (DataSet, SqlBulkCopy, OPENXML)

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

Один из самых простых, но и самых медленных и ресурсоёмких способов импорта на .NET можно считать импорт с помощью DataSet. В этом случае вы получаете DataSet, например, с помощью DataAdapter.Fill, обращаясь к источнику данных. Затем, если требуется, производите необходимые преобразования и записываете DataSet в приёмник с помощью DataAdapter.Update.

Простота достигается за счёт того, что DataAdapter во многих случаях поможет избежать написания запросов. А проблемы, особенно на больших данных, могут возникнуть из-за того, что DataSet - это дополнительная структура в оперативной памяти, а DataAdapter будет вставлять записи по одной.

SqlBulkCopy позволяет избежать большинства проблем предыдущего подхода, потому что использует тот же низкоуровневый протокол что и bcp. За счёт этого скорость вставки данных значительно выше. Избежать проблем с доступным объёмом ОЗУ можно при использовании варианта метода SqlBulkCopy.WriteToServer, принимающего на вход IDataReader.

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

5. Integration Services

Все перечисленные выше технологии предполагают написание программного кода или скриптов для импорта. SQL Server Integration Services (SSIS), конечно, не является той заветной технологией, позволяющей осуществлять весь импорт с помощью инструмента "волшебная палочка". Однако она позволяет сделать процесс более наглядным за счёт визуализации.

SSIS содержит набор компонентов для импорта, которого обычно хватает для большинства задач. Для остальных можно использовать скрипт на C# или VB.NET. В отличие от прямых запросов к БД и linked servers SSIS предлагает доступ к источникам данных, к которым затруднительно обращаться из T-SQL (например, MSMQ и Analysis Services).

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

Есть довольно много дополнительных компонентов, одни из которых относятся непосредственно к импорту (например, "Data Mining Query Task"), другие выполняют стандартные действия, относящиеся к инфраструктуре (например, "Shrink Database Task"). Далее будут рассмотрены только несколько компонентов из базового набора.

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

Разумеется, в SSIS есть поддержка BULK INSERT, так что с точки зрения быстрого импорта большого объёма данных всё в порядке. Можно использовать скрипты на .NET, поэтому варианты, реализуемые программным кодом, реализуемы и здесь.

С моей точки зрения у SSIS есть несколько недостатков, которые и стоит учитывать при выборе технологии импорта (в остальном эта технология вполне хороша):

  • Иногда необъяснимо медленное сохранение проекта (даже на SSD), но с этим, в принципе, можно жить.
  • Странная обработка ошибок. Это сложно описать, с этим просто нужно несколько раз столкнуться.
  • Необъяснимо корявый интерфейс для конфигурирования. Особенно на контрасте с неплохим интерфейсом для диаграмм и отслеживания выполнения.
  • Кривая обучения местами весьма кривая, видимо потому что пытались сделать инструмент для аналитиков, а не разработчиков и некоторые вещи запрятали подальше, посчитав слишком продвинутыми. Постараюсь далее немного исправить этот недостаток.

Если у вас есть замечания, пожелания или новые темы – пишите в комментариях, твиттер или на olegaxenow.reformal.ru. Постараюсь учесть.

Комментариев нет:

Отправить комментарий