В нашей компании мы полагаемся на Azure Data Factory в качестве оркестратора для наших конвейеров данных в Azure. Одной из наших проблем является загрузка плоских файлов из различных местоположений в Azure SQL Database. Схема этих плоских файлов может меняться в зависимости от типа файла, и даже разделитель может варьироваться. Ручное создание набора данных и конвейера в Azure Data Factory для каждого файла является утомительным и затратным по времени. Есть ли более эффективный способ справиться с этим?

В Azure Data Factory набор данных описывает схему и местоположение источника данных. Однако набор данных не должен быть настолько точным; он не должен описывать каждый столбец и его тип данных. Вместо этого мы можем использовать его в качестве заполнителя для общего типа файла. Например, если у нас есть два файла .csv, один с запятой в качестве разделителя, а другой с точкой с запятой, мы все равно можем использовать один и тот же набор данных. Нам просто нужно добавить параметр к определению разделителя.

Более того, используя действие For Each, мы можем выполнять цикл по метаданным и загружать несколько файлов параллельно. Когда мы объединяем оба функционала, мы можем создать метаданными-ориентированный конвейер, который динамически загружает несколько типов плоских файлов. В этой статье мы рассмотрим архитектуру такого решения.

Настройка теста

Давайте рассмотрим сценарий, в котором нам нужно загрузить три файла, хранящихся в Azure Blob Storage, в Azure SQL DB. Эти файлы содержат данные о 250 лучших фильмах. Первый файл содержит первые 100 фильмов с использованием точки с запятой в качестве разделителя. Второй файл содержит следующие 200 фильмов, также с точкой с запятой в качестве разделителя. Третий файл содержит последние 50 фильмов, но он был создан в другой системе, где в качестве разделителя использовалась запятая. Эти файлы хранятся внутри контейнера Azure Blob, причем первые два находятся в виртуальной папке с названием “semicolon”, а последний файл – в виртуальной папке с названием “comma”.

Решение

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

• Местоположение в контейнере (виртуальная папка)
• Используемый разделитель
• Имя таблицы назначения

Мы можем вставить эти метаданные в таблицу с помощью SQL-скриптов:

CREATE TABLE dbo.Metadata_ADF (
  ID INT IDENTITY (1, 1) NOT NULL,
  SourceType VARCHAR(50) NOT NULL,
  ObjectName VARCHAR(500) NOT NULL,
  ObjectValue VARCHAR(1000) NOT NULL
);

INSERT INTO dbo.Metadata_ADF (SourceType, ObjectName, ObjectValue)
VALUES ('BlobContainer', 'semicolondata', 'semicolon'),
       ('BlobContainer', 'commadata', 'comma');

INSERT INTO dbo.Metadata_ADF (SourceType, ObjectName, ObjectValue)
VALUES ('Delimiter', 'semicolondata', ';'),
       ('Delimiter', 'commadata', ',');
       
INSERT INTO dbo.Metadata_ADF (SourceType, ObjectName, ObjectValue)
VALUES ('SQLTable', 'semicolondata', 'topmovies_semicolon'),
       ('SQLTable', 'commadata', 'topmovies_comma');

Нам также нужно создать таблицы назначения с использованием следующего DDL:

CREATE TABLE [dbo].[topmovies_semicolon] (
  [Index] [BIGINT] NULL,
  [MovieTitle] varchar(500) NULL
);

CREATE TABLE [dbo].[topmovies_comma] (
  [Index] [BIGINT] NULL,
  [MovieTitle] varchar(500) NULL
);

Конвейер ADF

Первый шаг – добавить наборы данных в Azure Data Factory. Вместо создания четырех наборов данных (два для хранения блобов и два для таблиц SQL Server), мы создадим только два набора данных: один для хранения блобов и один для SQL Server. Для каждого набора данных соответствующие атрибуты будут обновляться динамически во время выполнения с помощью параметров.

Начнем с набора данных для блобов. В среде редактирования Azure Data Factory нажмите на многоточие рядом с заголовком наборов данных, чтобы добавить новый набор данных. Выберите Azure Blob Storage в качестве источника данных. На следующем экране выберите формат данных, который в нашем примере является DelimitedText. Укажите имя и наличие заголовков в первой строке. Для связанной службы вы можете выбрать существующую службу или создать новую. На следующем экране выберите контейнер. После создания набора данных перейдите на вкладку Schema и очистите все метаданные, чтобы обеспечить действительно динамический набор данных. Затем добавьте параметры для папки и разделителя. Во вкладке Connection присвойте параметры конкретным полям конфигурации.

Нам также нужен набор данных для нашего приемника, который является SQL Server. Добавьте новый набор данных и выберите Azure SQL Database в качестве источника данных. Укажите имя для набора данных, создайте связанную службу или выберите существующую, и не импортируйте схему. Для этого набора данных нам нужно создать параметр для имени таблицы. Во вкладке Connection укажите схему и добавьте параметр в качестве имени таблицы.

Вывод

В этой статье мы обсудили, как создать метаданными-ориентированный конвейер в Azure Data Factory. Используя метаданные, сохраненные в таблице, мы можем динамически загружать несколько типов плоских файлов в Azure SQL Database. Такой подход позволяет избежать ручного создания наборов данных