Verschieben von Daten aus einer SQL Server-Datenbank in eine SQL-Datenbank mit Azure Data Factory

  • Artikel
  • 8 Minuten Lesedauer

In diesem Artikel wird beschrieben, wie Sie Daten aus einer SQL Server-Datenbank mithilfe von Azure Data Factory (ADF) per Azure Blob Storage zu Azure SQL-Datenbank verschieben. Bei dieser Methode handelt es sich um einen unterstützten Legacyansatz, der die Vorteile einer replizierten Stagingkopie nutzt. Es wird jedoch empfohlen, sich auf unserer Datenmigrationsseite über die neuesten Optionen zu informieren.

Eine Tabelle, in der verschiedene Optionen für das Verschieben von Daten in eine Azure SQL-Datenbank zusammengefasst sind, finden Sie unter Verschieben von Daten in eine Azure SQL-Datenbank für Azure Machine Learning.

Einführung: Was ist ADF und wann sollte ADF für die Migration von Daten verwendet werden?

Azure Data Factory ist ein vollständig verwalteter, cloudbasierter Datenintegrationsdienst, der das Verschieben und Transformieren von Daten orchestriert und automatisiert. Das Hauptkonzept im ADF-Modell ist die Pipeline. Eine Pipeline ist eine logische Gruppierung von Aktivitäten, von denen jede die Aktionen definiert, die mit den in Datasets enthaltenen Daten auszuführen sind. Verknüpfte Dienste werden verwendet, um die Informationen zu definieren, die Data Factory benötigt, um eine Verbindung mit den Datenressourcen herzustellen.

Mit ADF können vorhandene Datenverarbeitungsdienste zu Datenpipelines zusammengestellt werden, die hoch verfügbar sind und in der Cloud verwaltet werden. Diese Datenpipelines können geplant werden, um Daten zu erfassen, vorzubereiten, zu transformieren, zu analysieren und zu veröffentlichen, und die komplexen Daten und Verarbeitungsabhängigkeiten werden von ADF verwaltet und orchestriert. Lösungen lassen sich schnell in der Cloud erstellen und bereitstellen, wodurch eine wachsende Anzahl von lokalen und Clouddatenquellen miteinander verbunden werden.

Ziehen Sie ADF in Betracht:

  • wenn Daten fortlaufend in ein Hybridszenario migriert werden müssen, das auf lokale und Cloudressourcen zugreift
  • wenn bei der Migration Transformationen der Daten erforderlich sind oder ihnen Geschäftslogik hinzugefügt werden muss

ADF ermöglicht die Planung und Überwachung von Aufträgen mithilfe einfacher JSON-Skripts, die das Verschieben von Daten in regelmäßigen Abständen verwalten. ADF verfügt außerdem über weitere Funktionen wie Unterstützung für komplexe Vorgänge. Weitere Informationen zu ADF finden Sie in der Dokumentation zu Azure Data Factory (ADF).

Das Szenario

Wir richten eine ADF-Pipeline ein, die zwei Aktivitäten für die Migration von Daten aufweist. Gemeinsam verschieben sie täglich Daten zwischen einer SQL Server-Datenbank und Azure SQL-Datenbank. Die zwei Aktivitäten sind:

  • Kopieren von Daten aus einer SQL Server-Datenbank in ein Azure Blob Storage-Konto
  • Kopieren von Daten aus dem Azure Blob Storage-Konto in Azure SQL-Datenbank

Hinweis

Die hier gezeigten Schritte wurden aus dem detaillierteren Tutorial übernommen, das vom ADF-Team bereitgestellt wird: Kopieren von Daten aus einer SQL Server-Datenbank in Azure Blob Storage. Verweise auf die relevanten Abschnitte dieses Themas werden gegebenenfalls angegeben.

Voraussetzungen

In diesem Tutorial wird Folgendes vorausgesetzt:

Hinweis

In diesem Verfahren wird das Azure-Portalverwendet.

Hochladen der Daten in die SQL Server-Instanz

Wir verwenden das NYC Taxi-Dataset , um den Migrationsprozess zu demonstrieren. Das NYC Taxi-Dataset ist, wie in diesem Beitrag erwähnt, in Azure Blob Storage unter NYC Taxi Dataverfügbar. Die Daten umfassen zwei Dateien, die Datei „trip_data.csv“, die Details zu den einzelnen Fahrten enthält, und die Datei „trip_far.csv“, die Details zu den pro Fahrt bezahlten Fahrpreisen enthält. Ein Beispiel und eine Beschreibung dieser Dateien finden Sie unter Beschreibung des NYC Taxi Trips-Datasets.

Sie können entweder das hier beschriebene Verfahren auf einen Satz Ihrer eigenen Daten anpassen oder die Schritte wie beschrieben unter Verwendung des NYC Taxi-Datasets durchführen. Um das NYC Taxi-Dataset in Ihre SQL Server-Datenbank hochzuladen, befolgen Sie das unter Massenimport von Daten in eine SQL Server-Datenbank beschriebene Verfahren.

Erstellen einer Azure Data Factory-Instanz

Die Schritte zum Erstellen einer neuen Azure Data Factory und einer Ressourcengruppe im Azure-Portal finden Sie unter Erstellen einer Data Factory. Nennen Sie die neue ADF-Instanz adfdsp und die erstellte Ressourcengruppe adfdsprg.

Installieren und Konfigurieren von Azure Data Factory Integration Runtime

Integration Runtime ist eine vom Kunden verwaltete Datenintegrationsinfrastruktur, die von Azure Data Factory genutzt wird, um Datenintegrationsfunktionen in verschiedenen Netzwerkumgebungen bereitzustellen. Diese Runtime wurde früher als „Datenverwaltungsgateway“ bezeichnet.

Zum Einrichten der Runtime befolgen Sie die Anweisungen zum Erstellen einer Pipeline.

Erstellen von verknüpften Diensten zum Herstellen einer Verbindung mit den Datenressourcen

Ein verknüpfter Dienst definiert die Informationen, die Azure Data Factory für das Herstellen einer Verbindung mit einer Datenquelle benötigt. Wir haben drei Ressourcen in diesem Szenario, für die verknüpfte Dienste erforderlich sind:

  1. Lokaler SQL Server
  2. Azure Blob Storage
  3. Azure SQL-Datenbank

Die schrittweise Anleitung zum Erstellen von verknüpften Diensten finden Sie unter Erstellen von verknüpften Diensten.

Definieren und Erstellen von Tabellen, um die Art des Zugriffs auf Datasets anzugeben

Erstellen Sie Tabellen, in denen die Struktur, der Speicherort und die Verfügbarkeit der Datasets angegeben werden, mit den folgenden skriptbasierten Verfahren. Zum Definieren der Tabellen werden JSON-Dateien verwendet. Weitere Informationen zur Struktur dieser Dateien finden Sie unter Datasets.

Hinweis

Sie müssen das Cmdlet Add-AzureAccount ausführen, bevor Sie das Cmdlet New-AzureDataFactoryTable ausführen, um sicherzustellen, dass das richtige Azure-Abonnement für die Ausführung der Befehle ausgewählt ist. Die Dokumentation zu diesem Cmdlet finden Sie unter Add-AzureAccount.

Die JSON-basierten Definitionen in den Tabellen verwenden die folgenden Namen:

  • Der Tabellenname in der SQL Server-Instanz lautet nyctaxi_data.
  • Der Containername im Azure Blob Storage-Konto lautet containername

Für diese ADF-Pipeline sind drei Tabellendefinitionen erforderlich:

  1. Lokale SQL-Tabelle
  2. Blob-Tabelle
  3. SQL Azure-Tabelle

Hinweis

In den folgenden Verfahren wird Azure PowerShell verwendet, um die ADF-Aktivitäten zu definieren und zu erstellen. Diese Aufgaben können Sie allerdings auch über das Azure-Portal ausführen. Weitere Informationen finden Sie unter Erstellen von Datasets.

Lokale SQL-Tabelle

Die Tabellendefinition für den SQL Server wird in der folgenden JSON-Datei angegeben:

{
    "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
            }
        }
    }
}

Die Spaltennamen sind hier nicht enthalten. Sie können eine Unterauswahl in den Spalten treffen, indem Sie sie hier aufnehmen (Details finden Sie in der ADF-Dokumentation).

Kopieren Sie die JSON-Definition der Tabelle in eine Datei namens onpremtabledef.json, und speichern Sie sie an einem bekannten Speicherort (hier wird C:\temp\onpremtabledef.json vorausgesetzt). Erstellen Sie die Tabelle in ADF mit dem folgenden Azure PowerShell-Cmdlet:

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

Blob-Tabelle

Die Tabellendefinition für den Blob-Speicherort der Ausgabe finden Sie im Folgenden (hierbei werden die erfassten lokalen Daten dem Azure-Blob zugeordnet):

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

Kopieren Sie die JSON-Definition der Tabelle in eine Datei namens bloboutputtabledef.json, und speichern Sie sie an einem bekannten Speicherort (hier wird C:\temp\bloboutputtabledef.json vorausgesetzt). Erstellen Sie die Tabelle in ADF mit dem folgenden Azure PowerShell-Cmdlet:

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

SQL Azure-Tabelle

Die Tabellendefinition für die SQL Azure-Ausgabe finden Sie im Folgenden (dieses Schema ordnet die vom Blob stammenden Daten zu):

{
    "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
        }
    }
}

Kopieren Sie die JSON-Definition der Tabelle in eine Datei namens AzureSqlTable.json, und speichern Sie sie an einem bekannten Speicherort (hier wird C:\temp\AzureSqlTable.json vorausgesetzt). Erstellen Sie die Tabelle in ADF mit dem folgenden Azure PowerShell-Cmdlet:

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

Definieren und Erstellen der Pipeline

Geben Sie die Aktivitäten an, die zu der Pipeline gehören, und erstellen Sie die Pipeline mit den folgenden skriptbasierten Verfahren. Zum Definieren der Pipeline-Eigenschaften wird eine JSON-Datei verwendet.

  • Das Skript setzt voraus, dass der PipelinenameAMLDSProcessPipelinelautet.
  • Beachten Sie außerdem, dass wir die Häufigkeit auf eine tägliche Ausführung festgelegt haben und die Standardausführungszeit des Auftrags verwenden (12 Uhr UTC).

Hinweis

Die folgenden Verfahren verwenden Azure PowerShell zum Definieren und Erstellen der ADF-Pipeline. Diese Aufgabe können Sie allerdings auch über das Azure-Portal ausführen. Weitere Informationen finden Sie unter Erstellen einer Pipeline.

Unter Verwendung der oben angegebenen Tabellendefinitionen wird die Pipelinedefinition für die ADF wie folgt angegeben:

{
    "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"
                }
            }
        ]
    }
}

Kopieren Sie diese JSON-Definition der Pipeline in eine Datei namens pipelinedef.json, und speichern Sie sie an einem bekannten Speicherort (hier wird C:\temp\pipelinedef.json vorausgesetzt). Erstellen Sie die Pipeline in ADF mit dem folgenden Azure PowerShell-Cmdlet:

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

Starten der Pipeline

Die Pipeline kann jetzt mithilfe der folgenden Befehle ausgeführt werden:

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

Die Parameterwerte startdate und enddate müssen durch die tatsächlichen Daten ersetzt werden, zwischen denen die Pipeline ausgeführt werden soll.

Sobald die Pipeline ausgeführt wird, sollten Sie die Daten sehen können, die im für den Blob ausgewählten Container angezeigt werden – immer eine Datei pro Tag.

Wir haben nicht die Funktion von ADF zum inkrementellen Übertragen von Daten per Pipe genutzt. Weitere Informationen zur Vorgehensweise sowie zu weiteren von ADF bereitgestellten Funktionen finden Sie in der ADF-Dokumentation.