Перенос данных из базы данных SQL Server в Базу данных SQL с помощью Фабрики данных Azure

Статья
04/03/2022
Чтение занимает 7 мин

В этой статье показано, как переместить данные из базы данных SQL Server в Базу данных SQL Azure через Хранилище BLOB-объектов Azure с помощью Фабрики данных Azure (ADF). Этот метод поддерживается как устаревший вариант, преимуществом которого является репликация промежуточной копии. Мы рекомендуем изучить более новые варианты на странице, посвященной миграции данных.

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

Введение. Что такое ADF и когда ее использовать при переносе данных?

Фабрика данных Azure представляет собой полностью управляемую облачную службу интеграции информации, которая организует и автоматизирует перемещение и преобразование данных. Ключевым принципом модели ADF является конвейер. Конвейер — это логическая группа действий, каждое из которых определяет операции, выполняемые с данными, содержащимися в наборах данных. Информация, необходимая фабрике данных для подключения к внешним ресурсам, определяется связанными службами.

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

ADF стоит использовать в следующих случаях:

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

ADF позволяет выполнять планирование и отслеживание заданий с помощью простых сценариев JSON, управляющих перемещением данных на периодической основе. ADF также обладает другими возможностями, такими как поддержка сложных операций. Дополнительные сведения об ADF см. в документации по фабрике данных Azure.

Сценарий

Мы настраиваем конвейер ADF, который объединяет два действия переноса данных. Их сочетание позволяет ежедневно переносить данные из Базы данных SQL Server в Базу данных SQL и обратно. Эти два действия таковы:

Копирование из базы данных SQL Server в учетную запись Хранилища BLOB-объектов Azure.
Копирование данных из учетной записи Хранилища BLOB-объектов Azure в Базу данных SQL Azure.

Примечание

Описанные здесь действия — адаптированный вариант более подробного руководства, подготовленного разработчиками ADF: Копирование из базы данных SQL Server в хранилище BLOB-объектов Azure. Во всех случаях, когда это уместно, предоставляются ссылки на разделы этого руководства.

Предварительные требования

Для выполнения действий, описанных в этом учебнике, вам необходимо следующее.

Подписка Azure. Если у вас нет подписки, вы можете зарегистрироваться для получения бесплатной пробной версии.
Azure storage account. Учетная запись хранения Azure используется в этом учебнике для хранения данных. Если у вас ее нет, см. раздел Создание учетной записи хранения. После создания учетной записи хранения необходимо получить ключ, используемый для доступа к хранилищу. См. статью Управление ключами доступа к учетной записи хранения.
Доступ к базе данных SQL Azure. Если вам нужно настроить Базу данных SQL Azure, см. статью Начало работы с Базой данных SQL Azure, которая содержит сведения о подготовке нового экземпляра Базы данных SQL Azure.
Установленная и настроенная локальная среда Azure PowerShell. Инструкции см. в статье Приступая к работе с командлетами Azure PowerShell.

Примечание

В этой процедуре используется портал Azure.

Отправка данных в экземпляр SQL Server

Для демонстрации процесса переноса данных мы используем набор данных о такси Нью-Йорка . Набор данных о такси Нью-Йорка доступен, как отмечено в этой статье, в хранилище BLOB-объектов Azure здесь. Данные содержатся в двух файлах: trip_data.csv, содержащем сведения о поездках, и trip_far.csv, содержащем сведения о тарифах для каждой поездки. Пример и описание этих файлов приведены в описании набора данных «Поездки такси Нью-Йорка».

Вы можете либо адаптировать описанные здесь процедуры к собственному набору данных, либо выполнить описанные действия с набором данных о такси Нью-Йорка. Для загрузки набора данных о такси Нью-Йорка в базу данных SQL Server выполните процедуру, описанную в разделе массовый импорт данных в базу данных SQL Server.

Создание фабрики данных Azure

Инструкции по созданию фабрики данных Azure и группы ресурсов на портале Azure представлены здесь. Задайте имя adfdsp для нового экземпляра ADF и имя adfdsprg для созданной группы ресурсов.

Установка и настройка фабрики данных Azure Integration Runtime

Integration Runtime — это управляемая клиентом инфраструктура среды выполнения интеграции, которую Фабрика данных Azure использует для обеспечения интеграции данных между разными сетевыми средами. Эта среда выполнения раньше называлась "Шлюз управления данными".

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

Создание связанных служб для подключения к ресурсам данных

Информация, необходимая фабрике данных для подключения к внешним ресурсам, определяется связанными службами. В этом сценарии есть три ресурса, для которых требуются связанные службы.

Локальный сервер SQL Server
хранилище BLOB-объектов Azure
База данных SQL Azure

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

Определение и создание таблиц для определения доступа к наборам данных

Для создания таблиц, которые определяют структуру, расположение и доступность наборов данных, используются следующие процедуры на основе сценариев. Для определения таблиц используются файлы JSON. Дополнительные сведения о структуре этих файлов см. в статье Наборы данных в фабрике данных Azure.

Примечание

Перед выполнением командлета New-AzureDataFactoryTable необходимо проверить, правильно ли выбрана подписка для его выполнения, выполнив командлет Add-AzureAccount. Документацию по этим командлетам см. в статье Add-AzureAccount.

В определениях на основе JSON в таблицах используются следующие имена:

имя таблицы в локальном экземпляре SQL Server имеет значение nyctaxi_data;
the имя контейнера в учетной записи хранилища больших двоичных объектов Azure — containername

Для этого конвейера ADF необходимо определить три таблицы:

Локальная таблица SQL.
Таблица BLOB-объектов.
таблица SQL Azure.

Примечание

В этих процедурах для определения и создания действий ADF используется Azure PowerShell. Однако эти задачи также можно выполнить на портале Azure. Дополнительные сведения см. в разделе Создание наборов данных.

Локальная таблица SQL.

Определение таблицы для SQL Server указывается в следующем файле JSON:

{
    "name": "OnPremSQLTable",
    "properties":
    {
        "location":
        {
            "type": "OnPremisesSqlServerTableLocation",
            "tableName": "nyctaxi_data",
            "linkedServiceName": "adfonpremsql"
        },
        "availability":
        {
            "frequency": "Day",
            "interval": 1,
            "waitOnExternal":
            {
                "retryInterval": "00:01:00",
                "retryTimeout": "00:10:00",
                "maximumRetry": 3
            }
        }
    }
}

Сюда не были включены имена столбцов. Для выбора только некоторых столбцов включите сюда их список (дополнительные сведения приведены в статье Документация по ADF).

Скопируйте определение таблицы JSON в файл с именем onpremtabledef.json и сохраните его в известном месте (например, C:\temp\onpremtabledef.json). Создайте таблицу в ADF с помощью следующего командлета Azure PowerShell:

New-AzureDataFactoryTable -ResourceGroupName ADFdsprg -DataFactoryName ADFdsp –File C:\temp\onpremtabledef.json

таблица больших двоичных объектов;

Определение таблицы выходных больших двоичных объектов задается следующим образом (здесь данные, полученные из локальных расположений, сопоставляются с большим двоичным объектом Azure):

{
    "name": "OutputBlobTable",
    "properties":
    {
        "location":
        {
            "type": "AzureBlobLocation",
            "folderPath": "containername",
            "format":
            {
                "type": "TextFormat",
                "columnDelimiter": "\t"
            },
            "linkedServiceName": "adfds"
        },
        "availability":
        {
            "frequency": "Day",
            "interval": 1
        }
    }
}

Скопируйте определение таблицы JSON в файл с именем bloboutputtabledef.json и сохраните его в известном месте (например, C:\temp\bloboutputtabledef.json). Создайте таблицу в ADF с помощью следующего командлета Azure PowerShell:

New-AzureDataFactoryTable -ResourceGroupName adfdsprg -DataFactoryName adfdsp -File C:\temp\bloboutputtabledef.json

таблица SQL Azure.

Определение таблицы для выходных данных SQL Azure задается следующим образом (эта схема сопоставляет данные, полученные из большого двоичного объекта).

{
    "name": "OutputSQLAzureTable",
    "properties":
    {
        "structure":
        [
            { "name": "column1", "type": "String"},
            { "name": "column2", "type": "String"}
        ],
        "location":
        {
            "type": "AzureSqlTableLocation",
            "tableName": "your_db_name",
            "linkedServiceName": "adfdssqlazure_linked_servicename"
        },
        "availability":
        {
            "frequency": "Day",
            "interval": 1
        }
    }
}

Скопируйте определение таблицы JSON в файл с именем AzureSqlTable.json и сохраните его в известном месте (например, C:\temp\AzureSqlTable.json). Создайте таблицу в ADF с помощью следующего командлета Azure PowerShell:

New-AzureDataFactoryTable -ResourceGroupName adfdsprg -DataFactoryName adfdsp -File C:\temp\AzureSqlTable.json

Определение и создание конвейера

Укажите действия, которые принадлежат конвейеру, и создайте конвейер с помощью следующих процедур на основе сценариев. Для определения свойств конвейера используется файл JSON.

Сценарий предполагает, что имя конвейера — AMLDSProcessPipeline.
Также обратите внимание, что мы задаем периодичность выполнения конвейера ежедневно и используем время выполнения задания по умолчанию (00:00 по Гринвичу).

Примечание

В следующих процедурах для определения и создания конвейера ADF используется Azure PowerShell. Однако эту задачу также можно выполнить на портале Azure. Дополнительные сведения см. в разделе Создание конвейера.

Используя приведенные выше определения таблиц, определение конвейера ADF указывается следующим образом:

{
    "name": "AMLDSProcessPipeline",
    "properties":
    {
        "description" : "This pipeline has two activities: the first one copies data from SQL Server to Azure Blob, and the second one copies from Azure Blob to Azure Database Table",
        "activities":
        [
            {
                "name": "CopyFromSQLtoBlob",
                "description": "Copy data from SQL Server to blob",
                "type": "CopyActivity",
                "inputs": [ {"name": "OnPremSQLTable"} ],
                "outputs": [ {"name": "OutputBlobTable"} ],
                "transformation":
                {
                    "source":
                    {
                        "type": "SqlSource",
                        "sqlReaderQuery": "select * from nyctaxi_data"
                    },
                    "sink":
                    {
                        "type": "BlobSink"
                    }
                },
                "Policy":
                {
                    "concurrency": 3,
                    "executionPriorityOrder": "NewestFirst",
                    "style": "StartOfInterval",
                    "retry": 0,
                    "timeout": "01:00:00"
                }
            },
            {
                "name": "CopyFromBlobtoSQLAzure",
                "description": "Push data to Sql Azure",
                "type": "CopyActivity",
                "inputs": [ {"name": "OutputBlobTable"} ],
                "outputs": [ {"name": "OutputSQLAzureTable"} ],
                "transformation":
                {
                    "source":
                    {
                        "type": "BlobSource"
                    },
                    "sink":
                    {
                        "type": "SqlSink",
                        "WriteBatchTimeout": "00:5:00",
                    }
                },
                "Policy":
                {
                    "concurrency": 3,
                    "executionPriorityOrder": "NewestFirst",
                    "style": "StartOfInterval",
                    "retry": 2,
                    "timeout": "02:00:00"
                }
            }
        ]
    }
}

Скопируйте определение JSON конвейера в файл с именем pipelinedef.json и сохраните его в известном месте (например, C:\temp\pipelinedef.json). Создайте конвейер в ADF с помощью следующего командлета Azure PowerShell:

New-AzureDataFactoryPipeline  -ResourceGroupName adfdsprg -DataFactoryName adfdsp -File C:\temp\pipelinedef.json

Запуск конвейера

Конвейер можно запустить с помощью следующей команды:

Set-AzureDataFactoryPipelineActivePeriod -ResourceGroupName ADFdsprg -DataFactoryName ADFdsp -StartDateTime startdateZ –EndDateTime enddateZ –Name AMLDSProcessPipeline

Значения параметров startdate и enddate необходимо заменить фактическими датами, определяющими временной интервал запуска конвейера.

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

Мы не используем возможность поэтапного поступления данных в конвейер, предоставляемую ADF. Дополнительные сведения об этой и других возможностях ADF см. в документации по ADF.