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

Azure Data Factory, Azure Machine Learning, SSIS в виртуальных машинах Azure и сторонние инструменты ETL из Azure Marketplace Gallery — все эти средства располагают широким набором функций для переноса операций ETL из локальной платформы в «облако» с помощью Azure. Разработчикам хранилищ данных и бизнес-аналитикам, ответственным за процесс ETL, было бы полезно познакомиться с текущим состоянием ETL в «облаке».

Начну с того, что я довольно давно не писал статей. В последние пару лет я окунулся в мир открытого программного обеспечения и Больших Данных, но затем вернулся к продуктам Microsoft, и теперь буду чаще писать о текущей ситуации и тенденциях рынка.

Однако, если вы хотите познакомиться с историей «облачной» бизнес-аналитики в целом и развитием Microsoft Azure, загляните в мой блог от 2012 года (http://sqlmag.com/blog/microsoft-cloud-bi-update-2012).

С тех пор многое изменилось. В результате внедрения «облака» и крупных инвестиций Microsoft в Azure и «облачные» технологии ситуация очень быстро и заметно улучшалась. В данной статье описаны три эффективных подхода к ETL в «облаке». В настоящее время я являюсь архитектором решений по работе с данными в компании Microsoft и работаю с клиентами над миграцией локальных платформ и построением новых решений в Azure.

Ниже перечислены три подхода, которые я помогаю освоить пользователям.

1. Начнем с традиционной технологии ETL. Точнее, с платформ ETL, располагающих готовой к использованию в крупных компаниях функциональностью для конвейеров сложных данных, требующих интенсивного планирования, аудита, сред разработки, управления изменениями и т. д. Конечно, превосходный вариант для этой цели — службы SSIS. Вы можете очень быстро получить образ SQL Server в Azure из галереи (https://azure.microsoft.com/en-us/marketplace/partners/microsoft/sqlserver2016 rtmdeveloperwindowsserver2012 r2/). Это бесплатный выпуск для разработчиков. Выберите редакцию SQL, подходящую для производственной службы SSIS в зависимости от требований, предъявляемых вашей компанией к ETL. Таким образом вы получите все возможности, необходимые в производственной среде.

Конечно, это не настоящая платформа как услуга (PaaS), это так называемая инфраструктура как услуга (IaaS). Вы вручную организуете виртуальную машину и по-прежнему нуждаетесь в доступе к среде Visual Studio для проектирования и управления процессами ETL из локального «толстого» клиента, по RDP или Citrix в среде Visual Studio на этой виртуальной машине или другой виртуальной машине как средстве разработки.

Двигаясь в том же русле, Microsoft предоставляет встроенные шаблоны решения от партнеров, которые представляют собой виртуальные машины, готовые для развертывания из Azure ISV Marketplace Gallery: https://azure.microsoft.com/en-us/marketplace/. Здесь можно вспомнить, например, такие решения, как Informatica и Attunity.

2. Переходим от гибридного подхода к чистому инструментарию PaaS, такому как Azure Data Factory (https://azure.microsoft.com/en-us/services/data-factory/). ADF позиционируется как служба обработки данных; она находится в «облаке» Azure и полностью управляется компанией Microsoft. Нужно лишь построить конвейеры данных, предназначенные в основном для перемещения данных, но располагающие еще рядом функций для преобразования данных. Однако, чтобы перейти к сложным преобразованиям, необходимо применять внешние механизмы исполнения через «действия» (activities) ADF, которые могут обращаться к Hadoop, Machine Learning и SQL Server. Кроме того, ADF располагает шлюзом управления данных (https://docs.microsoft.com/en-us/azure/data-factory/data-factory-move-data-between-onprem-and-cloud), который позволяет подключаться к локальным источникам данных. Таким образом, ваш выбор не ограничивается лишь «облачными» источниками данных.

Рассмотрим очень простой пример использования мастера копирования в ADF.

Начнем с организации новой инфраструктуры данных из портала Azure (см. экран 1).

 

Создание новой инфраструктуры данных
Экран 1. Создание новой инфраструктуры данных

В новой ADF мастер копирования данных будет применяться для быстрого и простого построения конвейера данных, который будет брать пользовательский запрос из источника данных Azure SQL DB, изменит поле флага и обновит другую Azure SQL DB в качестве места назначения. Таким образом имитируется извлечение, преобразование и загрузка данных (ETL) в традиционном сценарии хранилища DW в ADF. Выберите Copy Data (Preview) («Копирование данных», «Предварительный просмотр»), как показано на экране 2.

 

Предварительный просмотр операции копирования
Экран 2. Предварительный просмотр операции копирования

Вы просто следуете указаниям мастера, выбрав Azure SQL DB в качестве источника. Для копирования данных на месте вы можете просто выбрать таблицу и поля в мастере, которые хотите переместить. Чтобы увидеть дополнительные сведения о настройке ETL, перейдите на вкладку Use Query («Использование запроса»).

В данном случае я присваиваю флагу Is Old значение 0, указывая, что это новые записи из источника. Поэтому я подготовил очень простую пользовательскую инструкцию SELECT, задав для параметра «возраст» значение 0:

$$Text.Format ('select Average_Rating,
   Num_of_Ratings, price,
   Customer_Average_Age,
   0 as age from stagingtable')

Возвращаясь к построенному конвейеру, можно проверить это в определении JSON вашего конвейера под sqlReaderQuery K/V.

Когда вы дойдете до шага Destination в мастере копирования, выберите Azure SQL DB в качестве приемника. На данном этапе мы будем использовать очень простой сценарий для обновления существующих строк в целевой таблице перед записью новых. В разделе Repeatability («Повторяемость») выберите custom script («Настраиваемый сценарий»). Здесь я назначаю всем строкам значение old. Это выполняется в первую очередь:

$$Text.Format (‘update stagingtable
   set isold=1’)

Чтобы установить данное значение вручную в формате JSON для конвейера, просто поищите ключ sqlWriterCleanupScript.

Собственно, в ADF можно использовать такого рода команды SQL при сборе данных и перед записью данных для выполнения ETL с использованием графического мастера копирования данных. Можно пойти гораздо дальше, если использовать Hadoop с запросами Hive или хранимые процедуры SQL Server в более сложных конвейерах. Ниже приведены две ссылки с дополнительными сведениями о конвейерных действиях ADF. Эти действия открывают богатые возможности для сложных ELT, где вы передаете данные для промежуточного хранения и преобразования в соответствующем исполнительном механизме:

  • https://docs.microsoft.com/en-us/azure/data-factory/data-factory-hive-activity;
  • https://docs.microsoft.com/en-us/azure/data-factory/data-factory-stored-proc-activity.

А вот ссылка для преобразования данных, в том числе Pig и MapReduce — https://docs.microsoft.com/en-us/azure/data-factory/data-factory-data-transformation-activities.

3. Наконец, вы можете выполнить более сложные преобразования данных, агрегирование, интеллектуальный анализ, машинное обучение и т. д. Для этого можно задействовать другую полностью управляемую службу PaaS с именем Azure Machine Learning или AML. С помощью AML можно построить интеллектуальные компоненты ETL в очень удобном пользовательском интерфейсе на основе браузера и выполнить оркестровку этих процессов ETL через запланированные ADF-конвейеры. Пример:

1) В среде Azure ML Studio (https://studio.azureml.net) воспользуемся примером из раздела Experiments, Samples («Эксперименты», «Приме­ры») с именем Sample 8: Apply SQL transforma­tion («Образец 8: применить трансформацию SQL»). Это хорошая отправная точка для использования AML для трансформации SQL в рабочем процессе ETL (см. экран 3). Здесь я также попытался показать, что вы составляете код SQLlite для SQL Transform и существует несколько отличных готовых преобразований данных для инженеров и разработчиков в AML. Кроме того, обратите внимание, что вы получаете очень удобную веб-среду разработки для экспериментов, похожую на SSIS в Visual Studio.

2) Если вам требуется сделать что-то более сложное, например обучить машинный алгоритм и использовать обученную модель в ETL для оценки данных в целях, скажем, обнаружения мошенничества, то вы строите отдельные эксперименты в той же среде AML, а также вызываете их из ADF. Пример, в котором я просто оцениваю стоимость продукта на основе данных с этикетки с применением готового алгоритма линейной регрессии в AML, приведен на экране 4. Я дилетант в области данных, так что, как видите, работать с AML действительно легко.

3) Я взял приведенный выше пример SQL Transformation и добавил шаг вывода, чтобы загрузить преобразованные данные в Azure SQL DB. Таким образом, все, что нужно сделать, — вызвать это преобразование из ADF, и данные будут загружены без необходимости ввода или вывода в моем конвейере ADF. Когда я провел этот эксперимент в среде разработки, я мог перемещать и преобразовывать данные с использованием шагов Input и Output, точно так же как в среде Visual Studio с пакетом SSIS.

4) Как было показано выше, существует среда разработки в Azure ML Studio, в которой вы проводите эксперименты. Чтобы применить этот подход к конвейеру ETL, вызываемому ADF, необходимо превратить его в веб-службу. Нажмите кнопку Set up Web Service («Настроить веб-службу») в нижней части Azure ML Studio. В результате будет сформирован программный код, который представляет ваши преобразования как веб-службу. В случае преобразований SQL для целей ETL вам не нужны шаги Input и Output веб-службы, поэтому просто удалите их. Мы будем собирать данные в службе AML и загружать все данные в одну службу.

 

Использование примера
Экран 3. Использование примера

 

Применение готового алгоритма линейной регрессии
Экран 4. Применение готового алгоритма линейной регрессии

По следующей ссылке можно найти более подробный учебник по преобразованию экспериментов в готовые к производственному применению службы: https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-walkthrough-5-publish-web-service. Из него же вы можете больше узнать о том, где находятся веб-службы и как управлять ими после развертывания.

5) В ADF постройте конвейер из инфраструктуры данных и выберите Author and deploy («Создать и развернуть»). Необходимо построить связанную службу с пакетной службой AML для AML SQL Transform. Мой пример приведен в листинге 1. Обратите внимание, что на этом шаге в создании конвейеров ADF мы работаем в формате JSON в портале Azure.

Чтобы получить ключ API и конечную точку для ML Service, перейдите к Azure ML Studio и щелкните значок «глобус» для Web Services на панели слева. Найдите службу, которую вы создали, и вы увидите ключ API и URL-адрес для пакетной конечной точки (см. экран 5).

 

Получение ключа API и конечной точки для ML Service
Экран 5. Получение ключа API и конечной точки для ML Service

6) Следуйте рекомендациям по построению остальной части конвейера ML в ADF по адресу: https://docs.microsoft.com/en-us/azure/data-factory/data-factory-azure-ml-batch-execution-activity. В листинге 2 приводится код JSON для вызова действия ETL, направленного к AML в конвейере ADF. Обратите внимание, что, для того чтобы достичь конечной точки ETL в ML, вызывается AzureMLLinkedService.

Листинг 1. Пример построения конвейера
{
    "name": "AzureMLLinkedService",
    "properties": {
        "description": "",
        "hubName": "kromer_hub",
        "type": "AzureML",
        "typeProperties": {
            "mlEndpoint": "https://ussouthcentral.services.azureml.net/workspaces/abcdefg/services/123455678/jobs",
            "apiKey": "**********"
        }
    }
}
Листинг 2. Код JSON для вызова действия ETL
        "activities": [
            {
                "type": "AzureMLBatchScoring",
                "typeProperties": {},
                "inputs": [
                    {
                        "name": "AzureSqlReportTable"
                    }
                ],
                "outputs": [
                    {
                        "name": "AzureSqlEventsTable"
                    }
                ],
                "policy": {
                    "timeout": "01:00:00",
                    "concurrency": 1,
                    "executionPriorityOrder": "NewestFirst",
                    "style": "StartOfInterval",
                    "retry": 3
                },
                "scheduler": {
                    "frequency": "Day",
                    "interval": 1
                },
                "name": "AzureMLScoringActivityTemplate",
                "description": "My demo ETL Pipeline",
                "linkedServiceName": "AzureMLLinkedService"
            }
        ],