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

Стратегии загрузки данных для выделенного пула 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 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. Подробнее при использование фабрики данных Azure с выделенными пулами 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 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 десятичное
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 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 в крошечный столбец для формата Parquet и ORC-файла не поддерживается.

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

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

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

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

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

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

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

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

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

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

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

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

Примечание.

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

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

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

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

ВСТАВКА В ... Оператор SELECT перемещает данные из промежуточной таблицы в постоянную таблицу.

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

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

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

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

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