Поделиться через

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

  • Статья

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

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

Хотя пул SQL поддерживает множество методов загрузки, включая варианты, отличные от Polybase, такие как программа массового копирования (bcp) и API Массового копирования SQL, самый быстрый и наиболее масштабируемый способ загрузки данных осуществляется через PolyBase. PolyBase — это технология, которая обращается к внешним данным, хранящимся в хранилище BLOB-объектов Azure или Azure Data Lake Storage с помощью языка Transact-SQL (T-SQL).

Реализация Polybase ELT

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

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

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

Руководство по загрузке см. в разделе "Загрузка набора данных New York Taxicab".

Дополнительные сведения см. в разделе "Загрузка шаблонов и стратегий".

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

Получение данных за пределами исходной системы зависит от расположения хранилища. Целью является перемещение данных в текстовые файлы с разделителями 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
boolean bit
двойной точности с плавающей запятой
с плавающей запятой real
двойной точности money
двойной точности smallmoney
строка nchar
строка nvarchar
строка char
строка varchar
binary binary
binary varbinary
TIMESTAMP Дата
TIMESTAMP smalldatetime
TIMESTAMP datetime2
TIMESTAMP datetime
TIMESTAMP Время
Дата Дата
десятичное десятичное

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

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

Для перемещения данных в служба хранилища Azure можно использовать следующие средства и службы:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Связанный контент