Spostare i dati da SQL Server locale a SQL Azure con Azure Data Factory

Questo argomento descrive come spostare i dati da un database di SQL Server locale a un database di SQL Azure tramite l'archiviazione BLOB di Azure usando Azure Data Factory (ADF).

Per un tabella che riepiloga le varie opzioni per lo spostamento dei dati in un database SQL Azure, vedere Spostare i dati a un database SQL Azure per Azure Machine Learning.

Introduzione: che cos'è l’ADF e quando deve essere utilizzato per la migrazione dei dati?

Il Data factory è un servizio di integrazione delle informazioni basato sul cloud che permette di automatizzare lo spostamento e la trasformazione dei dati. Il concetto chiave nel modello ADF è pipeline. Una pipeline è un raggruppamento logico di attività, ognuna delle quali definisce le azioni da eseguire sui dati contenuti nel set di dati. I servizi collegati vengono utilizzati per definire le informazioni necessarie affinché il servizio Data factory si connetta a risorse dati esterne.

Con ADF, i servizi di elaborazione dei dati esistenti possono essere composti in pipeline di dati, altamente disponibili e gestiti nel cloud. Queste pipeline di dati possono essere pianificate per inserire, preparare, trasformare, analizzare e pubblicare i dati e l’ADF gestisce e organizza i dati complessi e le dipendenze di elaborazione. Le soluzioni possono essere compilate e distribuite nel cloud, collegando un numero crescente di origini dati locali e cloud.

Considerare l'uso di ADF:

  • quando i dati sono soggetti a migrazione continua in uno scenario ibrido che accede alle risorse locali e cloud
  • quando i dati sono soggetti a transazioni, devono essere modificati o si vedono aggiungere una logica di business quando vengono migrati.

L’ADF consente la pianificazione e il monitoraggio dei processi utilizzando semplici script JSON che gestiscono lo spostamento dei dati su base periodica. ADF dispone anche di altre funzionalità quali il supporto di operazioni complesse. Per ulteriori informazioni sul file ADF, vedere la documentazione di Data factory di Azure (ADF).

Scenario

Si configura una pipeline ADF che compone due attività di migrazione dei dati. Insieme, queste attività spostano i dati giornalmente tra un database SQL locale e un database di SQL Azure nel cloud. Le due attività sono:

  • Copiare dati da un database di SQL Server locale in un account dell'archiviazione BLOB di Azure
  • Copiare i dati dall'account di archiviazione BLOB di Azure a un Database di SQL Azure.
Nota

I passaggi illustrati di seguito sono stati adattati dall'esercitazione più specifica fornita dal team ADF: Spostare dati tra origini locali e il cloud con gateway di gestione dati. I riferimenti alle sezioni pertinenti dell'argomento vengono resi disponibili laddove opportuno.

Prerequisiti

Il tutorial presuppone:

Nota

In questa procedura viene utilizzato il portale di Azure.

Caricare i dati in SQL Server locale

Utilizziamo il set di dati NYC Taxi per illustrare il processo di migrazione. Il set di dati NYC Taxi è disponibile, come indicato nel post, sull'archiviazione BLOB di Azure Dati NYC Taxi. I dati dispongono di due file, il file trip_data.csv che contiene i dettagli relativi alle corse e il file trip_far.csv che contiene i dettagli della tariffa pagata per ogni corsa. Un esempio e una descrizione di questi file sono inclusi in Descrizione del set di dati relativo alle corse dei taxi di NYC.

È possibile adattare le procedure riportate di seguito a un set di dati personalizzati o seguire i passaggi come descritto utilizzando il set di dati NYC Taxi. Per caricare il set di dati NYC Taxi nel database di SQL Server locale, seguire la procedura descritta in Importazione in blocco dei dati nel database SQL Server. Queste istruzioni sono per SQL Server in una macchina virtuale di Azure, ma la procedura per il caricamento in SQL Server locale è la stessa.

Creare un data factory di Azure

Le istruzioni per la creazione di una nuova data factory di Azure e un gruppo di risorse nel portale di Azure sono disponibili in Creazione di un'istanza di Data factory di Azure. Denominare la nuova istanza ADF adfdspe assegnare il nome adfdsprg al gruppo di risorse creato.

Installare e configurare i Gateway di gestione dati

Per abilitare le pipeline in Azure Data Factory per il funzionamento con SQL Server locale, è necessario aggiungerle come servizio collegato ad Azure Data Factory. Per creare un servizio collegato per SQL Server locale, è necessario:

  • scaricare e installare il gateway di gestione dati di Microsoft nel computer locale.
  • configurare il servizio collegato per l'origine dati locale per l'utilizzo del gateway.

Gateway di gestione dati serializza e deserializza i dati di origine e sink sul computer in cui è ospitato.

Per le istruzioni di configurazione e i dettagli sul Gateway di gestione dati, vedere Spostare dati tra origini locali e il cloud con Gateway di gestione dati

Creare servizi collegati per connettersi alle risorse di dati

I servizi collegati definiscono le informazioni necessarie affinché il servizio data factory si connetta a risorse dati. In Creazione di servizi collegativiene fornita la procedura dettagliata per la creazione di servizi collegati.

Sono disponibili tre risorse in questo scenario per il quale sono necessari servizi collegati.

  1. Servizio collegato per SQL Server locale
  2. Servizio collegato per archiviazione BLOB di Azure
  3. Servizio collegato per il database SQL Azure

Servizio collegato per il database SQL Server locale

Per creare un servizio collegato per SQL Server locale:

  • fare clic su Archivio dati nella pagina di destinazione di ADF nel portale di Azure classico
  • selezionare SQL e immettere il nome utente e la password per SQL Server locale. È necessario immettere il nome del server nella forma nome dell'istanza nomeserver completa barra rovesciata (nomeserver\nomeistanza). Nome servizio collegato adfonpremsql.

Servizi collegati per BLOB

Per creare un servizio collegato per l'account di archiviazione BLOB di Azure, è necessario:

  • fare clic su Archivio dati nella pagina di destinazione di ADF nel portale di Azure classico
  • selezionare Account di archiviazione di Azure
  • immettere il nome del contenitore e la chiave dell'account di archiviazione BLOB di Azure. Rinominare il servizio collegato adfds.

Servizio collegato per il database SQL Azure

Per creare un servizio collegato per il database SQL di Azure, è necessario:

  • fare clic su Archivio dati nella pagina di destinazione di ADF nel portale di Azure classico
  • selezionare SQL di Azure e immettere il nome utente e la password per il database SQL di Azure. Il nome utente deve essere specificato come user@servername.

Definire e creare tabelle per specificare la modalità di accesso al set di dati

Creare tabelle che specificano la struttura, la posizione e la disponibilità dei set di dati con le seguenti procedure basate su script. I file JSON vengono utilizzati per definire le tabelle. Per ulteriori informazioni sulla struttura di questi file, vedere Set di dati.

Nota

È necessario eseguire il cmdlet Add-AzureAccount prima di eseguire il cmdlet New-AzureDataFactoryTable per verificare che sia selezionata la sottoscrizione di Azure giusta per l'esecuzione del comando. Per la documentazione di questo cmdlet, vedere Add-AzureAccount.

Le definizioni basate su JSON nelle tabelle utilizzano i nomi seguenti:

  • il nome della tabella nel server SQL locale è nyctaxi_data
  • il nome del contenitore nell'account di archiviazione Blob di Azure è nomecontenitore

Per questa pipeline ADF sono necessarie tre definizioni di tabella:

  1. Tabella SQL locale
  2. Tabella BLOB
  3. Tabella SQL Azure
Nota

Queste procedure utilizzano Azure PowerShell per definire e creare le attività del file ADF. Tuttavia, queste attività possono inoltre essere eseguite tramite il portale di Azure. Per informazioni dettagliate, vedere Creare set di dati.

Tabella SQL locale

La definizione della tabella per SQL Server locale viene specificata nel file JSON seguente:

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

            }
        }
    }

Qui non sono inclusi i nomi di colonna. È possibile sottoselezionare i nomi delle colonne includendoli qui (per ulteriori informazioni consultare l'argomento Documentazione ADF ).

Copiare la definizione JSON della tabella in un file denominata onpremtabledef.json e salvarlo in una posizione nota (generalmente C:\temp\onpremtabledef.json). Creare la tabella nel file ADF con il seguente cmdlet Azure PowerShell:

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

Tabella BLOB

La definizione della tabella per il percorso del BLOB di output è la seguente. I dati inseriti vengono così mappati dal server locale al BLOB di Azure:

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

Copiare la definizione JSON della tabella in un file denominata bloboutputtabledef.json e salvarlo in una posizione nota (generalmente C:\temp\bloboutputtabledef.json). Creare la tabella nel file ADF con il seguente cmdlet Azure PowerShell:

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

Tabella SQL Azure

La definizione della tabella per l’output SQL Azure è la seguente (questo schema associa i dati provenienti dal blob):

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

Copiare la definizione JSON della tabella in un file denominata AzureSqlTable.json e salvarlo in una posizione nota (generalmente C:\temp\AzureSqlTable.json). Creare la tabella nel file ADF con il seguente cmdlet Azure PowerShell:

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

Definire e creare la pipeline

Specificare le attività che appartengono alla pipeline e creare la pipeline con le seguenti procedure basate su script. Un file JSON viene utilizzato per definire le proprietà della pipeline.

  • Lo script presuppone che il nome della pipeline sia AMLDSProcessPipeline.
  • Si noti inoltre che abbiamo impostato la periodicità della pipeline per l’esecuzione su base giornaliera e utilizza il tempo di esecuzione predefinito per il processo (12 am UTC).
Nota

Le procedure seguenti utilizzano Azure PowerShell per definire e creare la pipeline del file ADF. Tuttavia, questa attività può inoltre essere eseguita tramite il portale di Azure. Per informazioni dettagliate, vedere Creare una pipeline.

Se si utilizzano le definizioni di tabella fornite in precedenza, la definizione della pipeline per il file ADF viene specificata come segue:

    {
        "name": "AMLDSProcessPipeline",
        "properties":
        {
            "description" : "This pipeline has one Copy activity that copies data from an on-premises SQL to Azure blob",
             "activities":
            [
                {
                    "name": "CopyFromSQLtoBlob",
                    "description": "Copy data from on-premises 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"
                    }
                 }
            ]
        }
    }

Copiare la definizione JSON della pipeline in un file denominato pipelinedef.json e salvarlo in una posizione nota (generalmente C:\temp\pipelinedef.json). Creare la pipeline ADF con il seguente cmdlet Azure PowerShell:

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

Accertarsi che sia possibile visualizzare la pipeline nel file ADF sul portale di Azure classico come indicato di seguito (quando si fa clic sul diagramma)

Pipeline ADF

Avviare la pipeline

La pipeline è avviabile con il comando seguente:

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

I valori del parametro startdate ed enddate devono essere sostituiti con le date effettive tra le quali si desidera eseguire la pipeline.

Una volta eseguita la pipeline, si dovrebbe poter visualizzare i dati visualizzati nel contenitore selezionato per il blob, un file al giorno.

Si noti che non abbiamo utilizzato la funzionalità fornita da ADF per dirigere i dati in modo incrementale. Per ulteriori informazioni su come eseguire questa e altre funzionalità fornite da ADF, vedere la documentazione ADF.