Стратегии загрузки данных для выделенного пула 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. Поместите данные в хранилище BLOB-объектов Azure или 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. Помещение данных в хранилище BLOB-объектов Azure или Azure Data Lake Storage

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

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

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

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

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

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

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

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

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

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

Тип Parquet Логический тип Parquet (заметка) Тип данных SQL
BOOLEAN bit
BINARY / BYTE_ARRAY varbinary
DOUBLE FLOAT
FLOAT real
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 Decimal
BINARY JSON nvarchar(MAX)
BINARY BSON varbinary(max)
FIXED_LEN_BYTE_ARRAY DECIMAL 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 Decimal
INT32 TIME (MILLIS) time
INT64 INT(64, true) BIGINT
INT64 INT(64, false) decimal(20,0)
INT64 DECIMAL Decimal
INT64 TIME (MILLIS) time
INT64 TIMESTAMP (MILLIS) datetime2
Сложный тип Список 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 для преобразования данных перед их вставкой в рабочие таблицы.

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

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