Разработка стратегии загрузки данных PolyBase для выделенного пула SQL в Azure Synapse Analytics

  • Статья

В традиционных хранилищах данных SMP для загрузки данных используется процесс ETL (извлечение, преобразование и загрузка). Пул SQL Azure предоставляет архитектуру массовой параллельной обработки (MPP), которая имеет отличные характеристики масштабируемости и гибкости ресурсов вычислений и хранения. Процесс ELT (извлечение, загрузка и преобразование) позволяет использовать возможности обработки распределенных запросов и не тратить ресурсы на преобразование данных перед загрузкой.

Пул SQL поддерживает множество методов загрузки, в том числе без использования PolyBase, как, например, BCP и API SQL BulkCopy. Загрузка через PolyBase сейчас является самым быстрым и масштабируемым способом загрузки данных. PolyBase — это технология, которая обращается к внешним данным, хранящимся в хранилище BLOB-объектов Azure или Azure Data Lake Storage, с помощью языка T-SQL.

Извлечение, загрузка и преобразование (ELT)

Извлечение, загрузка и преобразование (ELT) — это процесс, в котором данные извлекаются из исходной системы, загружаются в хранилище данных, а затем преобразовываются.

Для реализации процесса PolyBase ELT для выделенного пула SQL нужно сделать следующее:

  1. Извлеките исходные данные в текстовые файлы.
  2. Поместите данные в хранилище BLOB-объектов Azure или Azure Data Lake Store.
  3. Подготовьте данные для загрузки.
  4. Загрузите данные в промежуточные таблицы выделенного пула SQL с помощью PolyBase.
  5. Преобразуйте данные.
  6. Вставьте данные в рабочие таблицы.

Инструкции по загрузке см. в статье Загрузка данных из Хранилища BLOB-объектов Azure в Azure Synapse Analytics с помощью PolyBase.

Дополнительные сведения см. в записи блога о стратегиях и шаблонах загрузки в хранилище данных SQL Azure.

1. Извлечение исходных данных в текстовые файлы

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

Форматы внешних файлов PolyBase

PolyBase загружает данные из текстовых файлов с разделителями в кодировке UTF-8 и UTF-16. PolyBase загружает данные из таких форматов файлов Hadoop, как RC File, ORC и Parquet. PolyBase также может загрузить данные из сжатых файлов Gzip и Snappy. PolyBase в настоящее время не поддерживает расширенную кодировку ASCII, форматы с фиксированной шириной и вложенные форматы, такие как WinZip, JSON и XML.

Если вы выполняете экспорт из SQL Server, то для экспорта данных в текстовые файлы с разделителями можно использовать программу командной строки bcp. Ниже описано сопоставление типов данных между Parquet и Azure Synapse Analytics.

Тип данных Parquet Тип данных SQL
tinyint tinyint
smallint smallint
INT INT
BIGINT BIGINT
Логическое bit
double FLOAT
FLOAT real
double money
double smallmoney
строка nchar
строка nvarchar
строка char
строка varchar
binary binary
binary varbinary
TIMESTAMP Дата
TIMESTAMP smalldatetime
TIMESTAMP datetime2
TIMESTAMP DATETIME
TIMESTAMP time
Дата Дата
Decimal Decimal

2. Помещение данных в хранилище BLOB-объектов Azure или Azure Data Lake Storage

Чтобы поместить данные в службу хранилища Azure, их можно переместить в хранилище BLOB-объектов Azure или Azure Data Lake Store. В любом расположении данные должны храниться в текстовых файлах. PolyBase может загрузить их из любого расположения.

Ниже приведены средства и службы, которые можно использовать для перемещения данных в службу хранилища Azure.

  • Служба Azure ExpressRoute повышает пропускную способность сети, производительность, а также предсказуемое поведение. ExpressRoute — это служба, которая направляет данные с помощью выделенного частного подключения в Azure. Подключения ExpressRoute не направляют данные через общедоступный Интернет. Они отличаются повышенной надежностью, более высокой скоростью, меньшей задержкой и дополнительной безопасностью по сравнению с обычными подключениями через общедоступный Интернет.
  • Служебная программа AZCopy перемещает данные в службу хранилища Azure через общедоступный Интернет. Этот способ оптимален, если размер данных не превышает 10 ТБ. Для выполнения загрузок на регулярной основе с помощью AzCopy проверьте скорость сети, чтобы просмотреть, подходит ли она.
  • Фабрика данных Azure (ADF) включает шлюз, который можно установить на локальном сервере. Затем можно создать конвейер для перемещения данных из локального сервера в службу хранилища Azure. Сведения об использовании Фабрики данных с выделенным пулом SQL см. в статье Загрузка данных в выделенный пул SQL.

3. Подготовка данных для загрузки

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

Определение внешних таблиц

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

Определение внешних таблиц включает указание источника данных, формата текстовых файлов и определений таблицы. Ниже приводится описание самых важных элементов синтаксиса T-SQL.

Форматирование текстовых файлов

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

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

4. Загрузка данных в промежуточные таблицы выделенного пула SQL с помощью PolyBase

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

Варианты загрузки данных с помощью PolyBase

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

  • PolyBase с использованием T-SQL хорошо работает, когда данные хранятся в хранилище BLOB-объектов Azure или Azure Data Lake Store. Этот вариант предоставляет наибольший контроль над процессом загрузки, но также требует определения объектов внешних данных. Другие методы определяют эти объекты в фоновом режиме, когда вы сопоставляете исходные таблицы с целевыми. Для оркестрации загрузок T-SQL можно использовать фабрику данных Azure, службы SSIS или функции Azure.
  • PolyBase с поддержкой SQL Server Integration Services хорошо подходит для ситуаций, когда исходные данные находятся в SQL Server. Службы SSIS определяют сопоставления исходной и целевой таблиц, а также управляют загрузкой. При наличии пакетов служб SSIS можно изменить пакеты для работы с новым назначением хранилища данных.
  • PolyBase с фабрикой данных Azure (ADF) представляет собой другое средство оркестрации. Оно определяет конвейер и планирует расписания заданий.
  • PolyBase с Azure Databricks передает данные из таблицы Azure Synapse Analytics в кадр данных Databricks и (или) записывает данные из кадра данных Databricks в таблицу Azure Synapse Analytics с помощью PolyBase.

Варианты загрузки, отличные от PolyBase

Если данные несовместимы с PolyBase, можно использовать программу bcp или API-интерфейс SQLBulkCopy. BCP загружает данные напрямую в выделенный пул SQL, минуя хранилище BLOB-объектов Azure, и подходит только для небольших загрузок. Обратите внимание, что скорость загрузки у этих вариантов ниже, чем у PolyBase.

5. Преобразование данных

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

6. Вставка данных в рабочие таблицы

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

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

Партнерские решения для загрузки

Многие из наших партнеров предлагают решения для загрузки. Дополнительные сведения см. в статье Партнеры по бизнес-аналитике хранилища данных SQL.

Дальнейшие действия

Инструкции по загрузке см. здесь.