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

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

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

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

Хотя выделенный пул SQL поддерживает множество методов загрузки, включая популярные варианты SQL Server, такие как bcp и API SqlBulkCopy, загрузка с помощью внешних таблиц PolyBase и инструкции COPY — это самый быстрый и масштабируемый способ загрузки данных.

С помощью PolyBase и инструкции COPY можно обращаться к внешним хранимым данным в хранилище BLOB-объектов Azure или Azure Data Lake Storage, используя язык T-SQL. Для наибольшей гибкости при загрузке рекомендуем использовать инструкцию COPY.

Что такое ELT?

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

Основные шаги по реализации ELT:

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

Учебник по загрузке см. в статье Загрузка данных из хранилища BLOB-объектов Azure.

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

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

Поддерживаемые типы файлов

С помощью PolyBase и инструкции COPY можно загружать данные из CSV-файлов или текстовых файлов с разделителями в кодировке UTF-8 и UTF-16. Кроме CSV-файлов или текстовых файлов с разделителями, данные также загружаются из форматов файлов Hadoop, таких как ORC и Parquet. PolyBase и инструкция COPY также могут загрузить данные из сжатых файлов Gzip и Snappy.

Не поддерживаются расширенная кодировка ASCII, форматы с фиксированной шириной и вложенные форматы, такие как WinZip или XML. Если вы выполняете экспорт из SQL Server, можно воспользоваться программой командной строки bcp для экспорта данных в текстовые файлы с разделителями.

2. Разместите данные в Azure Blob Storage или Azure Data Lake Store

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

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

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

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

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

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

Сначала определите таблицы, которые вы загружаете в выделенный пул SQL при использовании инструкции COPY.

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

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

При загрузке файлов Parquet используйте следующее сопоставление типов данных SQL:

Тип Parquet Логический тип Parquet (аннотация) Тип данных SQL
BOOLEAN bit
ДВОИЧНЫЕ / BYTE_ARRAY varbinary
DOUBLE float
FLOAT реальный
INT32 int
INT64 bigint
INT96 datetime2
FIXED_LEN_BYTE_ARRAY binary
BINARY UTF8 nvarchar
BINARY STRING nvarchar
BINARY ENUM nvarchar
BINARY UUID uniqueidentifier
BINARY DECIMAL десятичное
BINARY JSON nvarchar(MAX)
BINARY BSON varbinary(MAX)
FIXED_LEN_BYTE_ARRAY DECIMAL десятичное
BYTE_ARRAY INTERVAL varchar(MAX)
INT32 INT(8, true) smallint
INT32 INT(16, true) smallint
INT32 INT(32, true) int
INT32 INT(8, false) tinyint
INT32 INT(16, false) int
INT32 INT(32, false) bigint
INT32 DATE дата
INT32 DECIMAL десятичное
INT32 TIME (MILLIS) time
INT64 INT(64, true) bigint
INT64 INT(64, false ) decimal(20,0)
INT64 DECIMAL десятичное
INT64 TIME (MILLIS) time
INT64 TIMESTAMP (MILLIS) datetime2
Сложный тип LIST varchar(max)
Сложный тип MAP varchar(max)

Внимание

  • В настоящее время выделенные пулы SQL не поддерживают типы данных Parquet с точностью MICROS и NANOS.
  • При несоответствии типов Parquet и SQL или неподдерживаемых типов данных Parquet может возникнуть следующая ошибка: HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:...
  • Загрузка значения вне диапазона от 0 до 127 в столбец типа tinyint для форматов файлов Parquet и ORC не поддерживается.

Пример создания внешних объектов см. в статье Создание внешних таблиц.

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

При использовании PolyBase необходимо, чтобы внешние объекты были согласованы со строками текстовых файлов, внешней таблицей и определением формата файла. Данные в каждой строке текстового файла должны совпадать с определением таблицы.

Для форматирования текстовых файлов сделайте следующее:

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

4. Загрузка данных с помощью PolyBase или инструкции COPY

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

Варианты загрузки

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

  • Инструкция COPY — рекомендуемой вариант загрузки, т. к. позволяет легко и гибко загружать данные. Заявление имеет множество дополнительных возможностей загрузки, которые не предоставляет PolyBase. См. учебник по нью-йоркскому такси для COPY, чтобы ознакомиться с пробным примером.
  • Для использования Polybase с поддержкой T-SQL необходимо определить внешние объекты данных.
  • PolyBase и инструкция COPY с Фабрикой данных Azure (ADF) представляют собой другое средство оркестрации. Оно определяет конвейер и планирует расписания заданий.
  • PolyBase с поддержкой SQL Server Integration Services подходит для ситуаций, когда исходные данные находятся в SQL Server. Службы SSIS определяют сопоставления исходной и целевой таблиц, а также управляют загрузкой. При наличии пакетов служб SSIS можно изменить пакеты для работы с новым назначением хранилища данных.
  • PolyBase с Azure Databricks передает данные из таблицы в кадр данных Databricks и (или) записывает данные из кадра данных Databricks в таблицу с помощью PolyBase.

Ознакомьтесь с доступными руководствами.

Другие варианты загрузки

Помимо PolyBase и инструкции COPY можно использовать программу bcp или API SqlBulkCopy. Утилита bcp загружает информацию непосредственно в базу данных, минуя хранилище BLOB-объектов Azure, и предназначена только для небольших объемов данных.

Примечание.

Производительность загрузки этих опций ниже, чем у PolyBase и инструкции COPY.

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

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

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

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

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

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

Многие из наших партнеров предлагают решения для загрузки. Чтобы узнать больше, ознакомьтесь с перечнем наших партнеров по решениям.

Следующий шаг

Рекомендации по загрузке данных

  • Last updated on