Стратегии загрузки данных для выделенного пула 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:
- Извлеките исходные данные в текстовые файлы.
- Поместите данные в хранилище BLOB-объектов Azure или Azure Data Lake Store.
- Подготовьте данные для загрузки.
- Загрузите данные в промежуточные таблицы с помощью PolyBase или команды COPY.
- Преобразуйте данные.
- Вставьте данные в рабочие таблицы.
Учебник по загрузке см. в статье Загрузка данных из хранилища 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 для преобразования данных перед их вставкой в рабочие таблицы.
Варианты загрузки
Для загрузки данных можно использовать любые из приведенных ниже вариантов загрузки:
- Инструкция 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, а затем загрузить в промежуточную таблицу.
Партнерские решения для загрузки
Многие из наших партнеров предлагают решения для загрузки. Дополнительные сведения см. в статье Партнеры по бизнес-аналитике хранилища данных SQL.
Дальнейшие действия
Рекомендации по загрузке данных см. здесь.