Copiare dati da o nel database SQL di Azure tramite Azure Data FactoryCopy data to or from Azure SQL Database by using Azure Data Factory

Questo articolo illustra come copiare dati da e verso Database SQL di Azure.This article outlines how to copy data to and from Azure SQL Database. Per altre informazioni su Azure Data Factory, vedere l'articolo introduttivo.To learn about Azure Data Factory, read the introductory article.

Funzionalità supportateSupported capabilities

Questo connettore Database SQL di Azure è supportato per le attività seguenti:This Azure SQL Database connector is supported for the following activities:

In particolare, il connettore del database SQL di Azure supporta queste funzioni:Specifically, this Azure SQL Database connector supports these functions:

  • La copia di dati tramite l'autenticazione SQL e l'autenticazione token dell'applicazione Azure Active Directory (Azure AD) con entità servizio o identità gestite per le risorse di Azure.Copy data by using SQL authentication and Azure Active Directory (Azure AD) Application token authentication with a service principal or managed identities for Azure resources.
  • Come origine, il recupero di dati tramite query SQL o stored procedure.As a source, retrieve data by using a SQL query or stored procedure.
  • Come sink, aggiungere dati alla tabella di destinazione o richiamare una stored procedure con logica personalizzata durante la copia.As a sink, append data to a destination table or invoke a stored procedure with custom logic during the copy.

La funzionalità Always Encrypted del database SQL di Azure non è attualmente supportata.Azure SQL Database Always Encrypted is not supported now.

Importante

Se si copiano i dati tramite il runtime di integrazione di Azure Data Factory, configurare un firewall del server SQL di Azure per consentire ai servizi di Azure di accedere al server.If you copy data by using Azure Data Factory Integration Runtime, configure an Azure SQL server firewall so that Azure Services can access the server. Se si copiano dati usando un runtime di integrazione self-hosted, configurare il firewall del server SQL di Azure per consentire l'intervallo IP appropriato.If you copy data by using a self-hosted integration runtime, configure the Azure SQL server firewall to allow the appropriate IP range. Questo intervallo include l'indirizzo IP del computer usato per connettersi al database SQL di Azure.This range includes the machine's IP that is used to connect to Azure SQL Database.

Attività inizialiGet started

È possibile usare l'attività di copia con una pipeline tramite uno degli strumenti o degli SDK seguenti.You can use one of the following tools or SDKs to use the copy activity with a pipeline. Per istruzioni dettagliate, selezionare un collegamento:Select a link for step-by-step instructions:

Le sezioni seguenti riportano informazioni dettagliate sulle proprietà usate per definire entità di Data Factory specifiche in un connettore del database SQL di Azure.The following sections provide details about properties that are used to define Data Factory entities specific to an Azure SQL Database connector.

Proprietà del servizio collegatoLinked service properties

Per un servizio collegato al database SQL di Azure sono supportate queste proprietà:These properties are supported for an Azure SQL Database linked service:

ProprietàProperty DescrizioneDescription ObbligatoriaRequired
typetype La proprietà type deve essere impostata su AzureSqlDatabase.The type property must be set to AzureSqlDatabase. Yes
connectionStringconnectionString Specifica le informazioni necessarie per connettersi all'istanza del database SQL di Azure per la proprietà connectionString.Specify information needed to connect to the Azure SQL Database instance for the connectionString property.
Contrassegnare questo campo come SecureString per archiviare la chiave in modo sicuro in Data Factory.Mark this field as a SecureString to store it securely in Data Factory. È anche possibile inserire la password/chiave entità servizio in Azure Key Vault e, se si tratta dell'autenticazione SQL, estrarre la configurazione password dalla stringa di connessione.You can also put password/service principal key in Azure Key Vault,and if it's SQL authentication pull the password configuration out of the connection string. Vedere gli esempi JSON sotto la tabella e l'articolo Archiviare le credenziali in Azure Key Vault per altri dettagli.See the JSON example below the table and Store credentials in Azure Key Vault article with more details.
Yes
servicePrincipalIdservicePrincipalId Specificare l'ID client dell'applicazione.Specify the application's client ID. Sì, quando si usa l'autenticazione Azure AD con un'entità servizio.Yes, when you use Azure AD authentication with a service principal.
servicePrincipalKeyservicePrincipalKey Specificare la chiave dell'applicazione.Specify the application's key. Contrassegnare questo campo come SecureString per archiviarlo in modo sicuro in Data Factory oppure fare riferimento a un segreto archiviato in Azure Key Vault.Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault. Sì, quando si usa l'autenticazione Azure AD con un'entità servizio.Yes, when you use Azure AD authentication with a service principal.
tenanttenant Specificare le informazioni sul tenant (nome di dominio o ID tenant) in cui si trova l'applicazione.Specify the tenant information (domain name or tenant ID) under which your application resides. Recuperarle passando il cursore del mouse sull'angolo superiore destro del portale di Azure.Retrieve it by hovering the mouse in the top-right corner of the Azure portal. Sì, quando si usa l'autenticazione Azure AD con un'entità servizio.Yes, when you use Azure AD authentication with a service principal.
connectViaconnectVia Runtime di integrazione da usare per la connessione all'archivio dati.The integration runtime to be used to connect to the data store. È possibile usare il runtime di integrazione di Azure o il runtime di integrazione self-hosted se l'archivio dati si trova in una rete privata.You can use Azure Integration Runtime or a self-hosted integration runtime if your data store is located in a private network. Se non specificato, viene usato il runtime di integrazione di Azure predefinito.If not specified, it uses the default Azure Integration Runtime. NoNo

Per altri tipi di autenticazione, fare riferimento alle sezioni seguenti relative, rispettivamente, ai prerequisiti e agli esempi JSON:For different authentication types, refer to the following sections on prerequisites and JSON samples, respectively:

Suggerimento

Se viene restituito l'errore con codice "UserErrorFailedToConnectToSqlServer" e un messaggio quale "Il limite di sessioni per il database è XXX ed è stato raggiunto.", aggiungere Pooling=false alla stringa di connessione e riprovare.If you hit error with error code as "UserErrorFailedToConnectToSqlServer" and message like "The session limit for the database is XXX and has been reached.", add Pooling=false to your connection string and try again.

Autenticazione in SQLSQL authentication

Esempio di servizio collegato tramite l'autenticazione SQLLinked service example that uses SQL authentication

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Password in Azure Key Vault:Password in Azure Key Vault:

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
            },
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticazione di un'entità servizioService principal authentication

Per usare l'autenticazione token dell'applicazione di Azure AD basata sull'entità servizio, seguire questa procedura:To use a service principal-based Azure AD application token authentication, follow these steps:

  1. Creare un'applicazione di Azure Active Directory nel portale di Azure.Create an Azure Active Directory application from the Azure portal. Prendere nota del nome dell'applicazione e dei valori seguenti che definiscono il servizio collegato:Make note of the application name and the following values that define the linked service:

    • ID applicazioneApplication ID
    • Chiave applicazioneApplication key
    • ID tenantTenant ID
  2. Effettuare il provisioning di un amministratore di Azure Active Directory per il server SQL di Azure nel portale di Azure, se l'operazione non è già stata eseguita.Provision an Azure Active Directory administrator for your Azure SQL server on the Azure portal if you haven't already done so. L'amministratore di Azure AD deve essere un utente o un gruppo di Azure AD, ma non può essere un'entità servizio.The Azure AD administrator must be an Azure AD user or Azure AD group, but it can't be a service principal. Questo passaggio viene eseguito in modo che, nel passaggio successivo, sia possibile usare un'identità di Azure AD per creare un utente di database indipendente per l'entità servizio.This step is done so that, in the next step, you can use an Azure AD identity to create a contained database user for the service principal.

  3. Creare utenti del database indipendente per l'entità servizio.Create contained database users for the service principal. Connettersi al database dal quale o verso il quale si desidera copiare i dati usando strumenti come SSMS, con un'identità di Azure AD con almeno l'autorizzazione ALTER ANY USER.Connect to the database from or to which you want to copy data by using tools like SSMS, with an Azure AD identity that has at least ALTER ANY USER permission. Eseguire il T-SQL seguente:Run the following T-SQL:

    CREATE USER [your application name] FROM EXTERNAL PROVIDER;
    
  4. Concedere all'entità servizio le autorizzazioni necessarie, come si fa di norma per gli utenti SQL o altri utenti.Grant the service principal needed permissions as you normally do for SQL users or others. Eseguire il codice seguente, o fare riferimento alle opzioni ulteriori qui.Run the following code, or refer to more options here.

    EXEC sp_addrolemember [role name], [your application name];
    
  5. Configurare un servizio collegato al database SQL di Azure in Azure Data Factory.Configure an Azure SQL Database linked service in Azure Data Factory.

Esempio di servizio collegato tramite l'autenticazione basata su entità servizioLinked service example that uses service principal authentication

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;Connection Timeout=30"
            },
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalKey": {
                "type": "SecureString",
                "value": "<service principal key>"
            },
            "tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticazione di identità gestite per le risorse di AzureManaged identities for Azure resources authentication

Una data factory può essere associata a un'identità gestita per le risorse di Azure, che rappresenta la data factory specifica.A data factory can be associated with a managed identity for Azure resources that represents the specific data factory. È possibile usare questa identità gestita per l'autenticazione del Database SQL di Azure.You can use this managed identity for Azure SQL Database authentication. La factory designata può accedere ai dati e copiarli da o verso il database tramite questa identità.The designated factory can access and copy data from or to your database by using this identity.

Per usare l'autenticazione identità gestita, seguire questa procedura:To use managed identity authentication, follow these steps:

  1. Effettuare il provisioning di un amministratore di Azure Active Directory per il server SQL di Azure nel portale di Azure, se l'operazione non è già stata eseguita.Provision an Azure Active Directory administrator for your Azure SQL server on the Azure portal if you haven't already done so. L'amministratore di Azure AD può essere un utente o un gruppo di Azure AD.The Azure AD administrator can be an Azure AD user or Azure AD group. Se si concede al gruppo con identità gestita un ruolo di amministratore, ignorare i passaggi 3 e 4.If you grant the group with managed identity an admin role, skip steps 3 and 4. L'amministratore avrà accesso completo al database.The administrator will have full access to the database.

  2. Creare utenti del database indipendente per l'identità di gestiti di Data Factory.Create contained database users for the Data Factory Managed Identity. Connettersi al database dal quale o verso il quale si desidera copiare i dati usando strumenti come SSMS, con un'identità di Azure AD con almeno l'autorizzazione ALTER ANY USER.Connect to the database from or to which you want to copy data by using tools like SSMS, with an Azure AD identity that has at least ALTER ANY USER permission. Eseguire il comando in T-SQL seguente:Run the following T-SQL:

    CREATE USER [your Data Factory name] FROM EXTERNAL PROVIDER;
    
  3. Concedere le autorizzazioni necessarie di identità gestite di Data Factory come si farebbe normalmente per gli utenti SQL e altri utenti.Grant the Data Factory Managed Identity needed permissions as you normally do for SQL users and others. Eseguire il codice seguente, o fare riferimento alle opzioni ulteriori qui.Run the following code, or refer to more options here.

    EXEC sp_addrolemember [role name], [your Data Factory name];
    
  4. Configurare un servizio collegato al database SQL di Azure in Azure Data Factory.Configure an Azure SQL Database linked service in Azure Data Factory.

Esempio:Example:

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;Connection Timeout=30"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Proprietà del set di datiDataset properties

Per un elenco completo delle sezioni e delle proprietà disponibili per la definizione dei set di dati, vedere l'articolo Set di dati.For a full list of sections and properties available for defining datasets, see the Datasets article. Questa sezione presenta un elenco delle proprietà supportate dal set di dati del database SQL di Azure.This section provides a list of properties supported by the Azure SQL Database dataset.

Per copiare dati da o in Database SQL di Azure, sono supportate le proprietà seguenti:To copy data from or to Azure SQL Database, the following properties are supported:

ProprietàProperty DescrizioneDescription ObbligatoriaRequired
typetype La proprietà type del set di dati deve essere impostata su AzureSqlTable.The type property of the dataset must be set to AzureSqlTable. Yes
tableNametableName Il nome della tabella o della vista nell'istanza del database SQL di Azure a cui fa riferimento il servizio collegato.The name of the table or view in the Azure SQL Database instance that the linked service refers to. No per l'origine, Sì per il sinkNo for source, Yes for sink

Esempio di proprietà dei set di datiDataset properties example

{
    "name": "AzureSQLDbDataset",
    "properties":
    {
        "type": "AzureSqlTable",
        "linkedServiceName": {
            "referenceName": "<Azure SQL Database linked service name>",
            "type": "LinkedServiceReference"
        },
        "schema": [ < physical schema, optional, retrievable during authoring > ],
        "typeProperties": {
            "tableName": "MyTable"
        }
    }
}

Proprietà dell'attività di copiaCopy Activity properties

Per un elenco completo delle sezioni e delle proprietà disponibili per la definizione delle attività, vedere l'articolo sulle pipeline.For a full list of sections and properties available for defining activities, see the Pipelines article. Questa sezione presenta un elenco delle proprietà supportate dall'origine e dal sink del database SQL di Azure.This section provides a list of properties supported by the Azure SQL Database source and sink.

Database SQL di Azure come origineAzure SQL Database as the source

Per copiare dati da un database SQL di Azure, impostare la proprietà type nell'origine dell'attività di copia su SqlSource.To copy data from Azure SQL Database, set the type property in the Copy Activity source to SqlSource. Nella sezione source dell'attività di copia sono supportate le proprietà seguenti:The following properties are supported in the Copy Activity source section:

ProprietàProperty DescrizioneDescription ObbligatoriaRequired
typetype La proprietà type dell'origine dell'attività di copia deve essere impostata su SqlSource.The type property of the Copy Activity source must be set to SqlSource. Yes
sqlReaderQuerysqlReaderQuery Usare la query SQL personalizzata per leggere i dati.Use the custom SQL query to read data. Esempio: select * from MyTable.Example: select * from MyTable. NoNo
sqlReaderStoredProcedureNamesqlReaderStoredProcedureName Nome della stored procedure che legge i dati dalla tabella di origine.The name of the stored procedure that reads data from the source table. L'ultima istruzione SQL deve essere un'istruzione SELECT nella stored procedure.The last SQL statement must be a SELECT statement in the stored procedure. NoNo
storedProcedureParametersstoredProcedureParameters Parametri per la stored procedure.Parameters for the stored procedure.
I valori consentiti sono coppie nome-valore.Allowed values are name or value pairs. I nomi e le maiuscole e minuscole dei parametri devono corrispondere ai nomi e alle maiuscole e minuscole dei parametri della stored procedure.Names and casing of parameters must match the names and casing of the stored procedure parameters.
NoNo

Punti da notarePoints to note

  • Se la proprietà sqlReaderQuery è specificata per SqlSource, l'attività di copia esegue questa query nell'origine del database SQL di Azure per ottenere i dati.If the sqlReaderQuery is specified for the SqlSource, Copy Activity runs this query against the Azure SQL Database source to get the data. In alternativa è possibile specificare una stored procedure.Or you can specify a stored procedure. Indicare i parametri sqlReaderStoredProcedureName e storedProcedureParameters (se accettati dalla stored procedure).Specify sqlReaderStoredProcedureName and storedProcedureParameters if the stored procedure takes parameters.
  • Se non si specifica né sqlReaderQuerysqlReaderStoredProcedureName, le colonne definite nella sezione struttura del set di dati JSON vengono usate per creare una query.If you don't specify either sqlReaderQuery or sqlReaderStoredProcedureName, the columns defined in the structure section of the dataset JSON are used to construct a query. select column1, column2 from mytable viene eseguito nel database SQL di Azure.select column1, column2 from mytable runs against Azure SQL Database. Se la definizione del set di dati non include la struttura, vengono selezionate tutte le colonne della tabella.If the dataset definition doesn't have the structure, all columns are selected from the table.

Esempio di query SQLSQL query example

"activities":[
    {
        "name": "CopyFromAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Azure SQL Database input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlSource",
                "sqlReaderQuery": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Esempio di stored procedureStored procedure example

"activities":[
    {
        "name": "CopyFromAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Azure SQL Database input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlSource",
                "sqlReaderStoredProcedureName": "CopyTestSrcStoredProcedureWithParameters",
                "storedProcedureParameters": {
                    "stringData": { "value": "str3" },
                    "identifier": { "value": "$$Text.Format('{0:yyyy}', <datetime parameter>)", "type": "Int"}
                }
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Definizione della stored procedureStored procedure definition

CREATE PROCEDURE CopyTestSrcStoredProcedureWithParameters
(
    @stringData varchar(20),
    @identifier int
)
AS
SET NOCOUNT ON;
BEGIN
     select *
     from dbo.UnitTestSrcTable
     where dbo.UnitTestSrcTable.stringData != stringData
    and dbo.UnitTestSrcTable.identifier != identifier
END
GO

Database SQL di Azure come sinkAzure SQL Database as the sink

Suggerimento

Altre informazioni sui comportamenti di scrittura supportati, configurazioni e consigliata da procedure consigliate per il caricamento dei dati nel Database SQL di Azure.Learn more on the supported write behaviors, configurations and best practice from Best practice for loading data into Azure SQL Database.

Per copiare i dati in un database SQL di Azure, impostare la proprietà type dell'attività di copia su SqlSink.To copy data to Azure SQL Database, set the type property in the Copy Activity sink to SqlSink. Nella sezione sink dell'attività di copia sono supportate le proprietà seguenti:The following properties are supported in the Copy Activity sink section:

ProprietàProperty DescrizioneDescription ObbligatoriaRequired
typetype La proprietà type del sink dell'attività di copia deve essere impostata su SqlSink.The type property of the Copy Activity sink must be set to SqlSink. Yes
writeBatchSizewriteBatchSize Numero di righe nella tabella SQL inserimenti per ogni batch.Number of rows to inserts into the SQL table per batch.
Il valore consentito è integer (numero di righe).The allowed value is integer (number of rows). Per impostazione predefinita, Data Factory di determinare in modo dinamico le dimensioni del batch appropriato in base alla dimensione di riga.By default, Data Factory dynamically determine the appropriate batch size based on the row size.
NoNo
writeBatchTimeoutwriteBatchTimeout Tempo di attesa per il completamento dell'operazione di inserimento batch prima del timeout.The wait time for the batch insert operation to finish before it times out.
Il valore consentito è timespan.The allowed value is timespan. Esempio: "00:30:00" (30 minuti).Example: “00:30:00” (30 minutes).
NoNo
preCopyScriptpreCopyScript Specificare una query SQL per l'attività di copia da eseguire prima di scrivere i dati nel database SQL di Azure.Specify a SQL query for Copy Activity to run before writing data into Azure SQL Database. Viene richiamata solo una volta per esecuzione della copia.It's only invoked once per copy run. Usare questa proprietà per pulire i dati precaricati.Use this property to clean up the preloaded data. NoNo
sqlWriterStoredProcedureNamesqlWriterStoredProcedureName Il nome della stored procedure che definisce come applicare i dati di origine in una tabella di destinazione.The name of the stored procedure that defines how to apply source data into a target table.
Questa stored procedure viene richiamata per batch.This stored procedure is invoked per batch. Per le operazioni che solo eseguita una volta e non hanno niente a un'origine dati, ad esempio, delete o truncate, usare il preCopyScript proprietà.For operations that only run once and have nothing to do with source data, for example, delete or truncate, use the preCopyScript property.
NoNo
storedProcedureParametersstoredProcedureParameters Parametri per la stored procedure.Parameters for the stored procedure.
I valori consentiti sono coppie nome-valore.Allowed values are name and value pairs. I nomi e le maiuscole e minuscole dei parametri devono corrispondere ai nomi e alle maiuscole e minuscole dei parametri della stored procedure.Names and casing of parameters must match the names and casing of the stored procedure parameters.
NoNo
sqlWriterTableTypesqlWriterTableType Specificare il nome di un tipo di tabella da usare nella stored procedure.Specify a table type name to be used in the stored procedure. L'attività di copia fa in modo che i dati spostati siano disponibili in una tabella temporanea con questo tipo di tabella.Copy Activity makes the data being moved available in a temporary table with this table type. Il codice della stored procedure può quindi unire i dati copiati con i dati esistenti.Stored procedure code can then merge the data being copied with existing data. NoNo

Esempio 1: aggiungere datiExample 1: append data

"activities":[
    {
        "name": "CopyToAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Azure SQL Database output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlSink",
                "writeBatchSize": 100000
            }
        }
    }
]

Esempio 2: richiamare una stored procedure durante la copiaExample 2: invoke a stored procedure during copy

Altre informazioni sono contenute in Richiamo della stored procedure per SQL Sink.Learn more details from Invoking stored procedure from SQL Sink.

"activities":[
    {
        "name": "CopyToAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Azure SQL Database output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlSink",
                "sqlWriterStoredProcedureName": "CopyTestStoredProcedureWithParameters",
                "sqlWriterTableType": "CopyTestTableType",
                "storedProcedureParameters": {
                    "identifier": { "value": "1", "type": "Int" },
                    "stringData": { "value": "str1" }
                }
            }
        }
    }
]

Procedure consigliate per il caricamento dei dati nel Database SQL di AzureBest practice for loading data into Azure SQL Database

Quando si copiano dati in Database SQL di Azure, si potrebbe richiedere un comportamento scrittura diversi:When you copy data into Azure SQL Database, you may require different write behavior:

  • Accodare : i dati di origine contengono solo nuovi record;Append: my source data only has new records;
  • Upsert : i dati di origine contengono sia istruzioni inserts e updates;Upsert: my source data has both inserts and updates;
  • Sovrascrivere : Desidera ricaricare tabella intera dimensione ogni volta che;Overwrite: I want to reload entire dimension table each time;
  • Scrivere con logica personalizzata : Ho bisogno di ulteriore elaborazione prima dell'inserimento finale nella tabella di destinazione.Write with custom logic: I need extra processing before the final insertion into the destination table.

Vedere la sezioni rispettivamente su come configurare in Azure Data factory e le procedure consigliate.Refer to the respectively sections on how to configure in ADF and the best practices.

Aggiungere i datiAppend data

Si tratta del comportamento predefinito di questo connettore di sink di Database SQL di Azure e si di ADF bulk insert per scrivere in modo efficiente alla tabella.This is the default behavior of this Azure SQL Database sink connector, and ADF do bulk insert to write to your table efficiently. È possibile semplicemente configurare l'origine e di conseguenza di sink nell'attività di copia.You can simply configure the source and sink accordingly in Copy activity.

Eseguire l'upsert dei datiUpsert data

Opzione ho (consigliato soprattutto quando ci sono dati di grandi dimensioni da copiare): il approccio ad alte prestazioni la maggior parte delle eseguire upsert è il seguente:Option I (suggested especially when you have large data to copy): the most performant approach to do upsert is the following:

  • In primo luogo, utilizzare un tabelle temporanee con ambito database per eseguire il caricamento bulk tutti i record usando l'attività di copia.Firstly, leverage a database scoped temporary table to bulk load all records using Copy activity. Come operazioni nel database con ambito temporaneo tabelle non vengono registrate, è possibile caricare milioni di record in pochi secondi.As operations against database scoped temporary tables are not logged, you can load millions of records in seconds.
  • Eseguire un'attività Stored Procedure in Azure Data factory per applicare una MERGE (o inserimento/aggiornamento) istruzione e utilizzare temporanea di tabella come origine per eseguire tutte le aggiorna o inserisce come una singola transazione, riducendo la quantità di round trip e operazioni di log.Execute a Stored Procedure activity in ADF to apply a MERGE (or INSERT/UPDATE) statement, and use the temp table as source to perform all updates or inserts as a single transaction, reducing the amount of roundtrips and log operations. Al termine dell'attività Stored Procedure, tabelle temporanee possono essere troncate per la preparazione per il successivo ciclo di upsert.At the end of the Stored Procedure activity , temp table can be truncated to be ready for the next upsert cycle.

Ad esempio, in Azure Data Factory, è possibile creare una pipeline con un' attività di copia concatenato con un attività Stored Procedure esito è positivo.As an example, in Azure Data Factory, you can create a pipeline with a Copy activity chained with a Stored Procedure activity on success. La prima copia i dati dall'archivio di origine in una tabella temporanea di Database SQL di Azure, ad esempio " ##UpsertTempTable" come nome della tabella nel set di dati, quindi quest'ultimo richiama una Stored Procedure per unire i dati di origine dalla stessa tabella nella destinazione tabella e pulire la tabella temporanea.The former copies data from your source store into an Azure SQL Database temporary table, say "##UpsertTempTable" as table name in dataset, then the latter invokes a Stored Procedure to merge source data from the temp table into target table, and clean up temp table.

Upsert

Nel database, definire una Stored Procedure con logica di unione, simile alla seguente, che viene fatto riferimento dall'attività di Stored Procedure precedente.In your database, define a Stored Procedure with MERGE logic, like the following, which is pointed to from the above Stored Procedure activity. Supponendo che di destinazione Marketing tabella con tre colonne: ProfileID, stato, e categoria, e di eseguire l'operazione upsert in base il ProfileID colonna.Assuming target Marketing table with three columns: ProfileID, State, and Category, and do the upsert based on the ProfileID column.

CREATE PROCEDURE [dbo].[spMergeData]
AS
BEGIN
    MERGE TargetTable AS target
    USING ##UpsertTempTable AS source
    ON (target.[ProfileID] = source.[ProfileID])
    WHEN MATCHED THEN
        UPDATE SET State = source.State
    WHEN NOT matched THEN
        INSERT ([ProfileID], [State], [Category])
      VALUES (source.ProfileID, source.State, source.Category);
    
    TRUNCATE TABLE ##UpsertTempTable
END

Opzione II: in alternativa, è possibile effettuare chiamare una stored procedure all'interno di attività di copia, durante la nota di questo approccio viene eseguito per ogni riga nella tabella di origine invece di uso di bulk insert come l'approccio predefinito Nell'attività di copia, pertanto non può essere inclusa per l'operazione upsert su larga scala.Option II: alternatively, you can choose to Invoke stored procedure within Copy activity, while note this approach is executed for each row in the source table instead of leveraging bulk insert as the default approach in Copy activity, thus it doesn't fit for large scale upsert.

Sovrascrivere l'intera tabellaOverwrite entire table

È possibile configurare preCopyScript sink proprietà nell'attività di copia, nel qual caso per ogni esecuzione attività di copia, Azure Data Factory esegue lo script prima di tutto, quindi eseguire la copia per inserire i dati.You can configure preCopyScript property in Copy activity sink, in which case for each Copy activity run, ADF executes the script first, then run the copy to insert the data. Ad esempio, per sovrascrivere l'intera tabella con i dati più recenti, è possibile specificare uno script per eliminare tutti i record prima del caricamento bulk dei nuovi dati dall'origine.For example, to overwrite the entire table with the latest data, you can specify a script to first delete all records before bulk-loading the new data from the source.

Scrivere i dati con logica personalizzataWrite data with custom logic

Analogamente a come descritto in Upsert dati sezione quando è necessario applicare ulteriori operazioni di elaborazione prima dell'inserimento finale dei dati di origine nella tabella di destinazione, è possibile un) su larga scala, caricare in una tabella temporanea con ambito database quindi richiamare una stored procedure, o b) richiamare una stored procedure durante la copia.Similar as described in Upsert data section, when you need to apply extra processing before the final insertion of source data into the destination table, you can a) for large scale, load to a database scoped temporary table then invoke a stored procedure, or b) invoking a stored procedure during copy.

Chiamare una stored procedure da un sink SQLInvoke stored procedure from SQL sink

Quando si copiano dati in Database SQL di Azure, è anche possibile configurare e richiamare una specificato dall'utente stored procedure con parametri aggiuntivi.When you copy data into Azure SQL Database, you can also configure and invoke a user-specified stored procedure with additional parameters.

Suggerimento

Richiamo delle stored procedure elabora i dati row-by-row anziché operazione bulk, che non è consigliata per la copia su larga scala.Invoking stored procedure processes the data row-by-row instead of bulk operation, which is not suggested for large scale copy. Altre informazioni, vedere procedure consigliate per il caricamento dei dati nel Database SQL di Azure.Learn more from Best practice for loading data into Azure SQL Database.

È possibile usare una stored procedure quando meccanismi di copia predefiniti non svolgono la funzione, ad esempio, applicare ulteriori operazioni di elaborazione prima dell'inserimento finale dei dati di origine nella tabella di destinazione.You can use a stored procedure when built-in copy mechanisms don't serve the purpose, e.g. apply extra processing before the final insertion of source data into the destination table. Alcuni esempi di elaborazione extra sono l'unione di colonne, la ricerca di altri valori e l'inserimento in più di una tabella.Some extra processing examples are merge columns, look up additional values, and insertion into more than one table.

L'esempio seguente illustra come usare una stored procedure per eseguire un'operazione di upsert in una tabella del database SQL di Azure.The following sample shows how to use a stored procedure to do an upsert into a table in Azure SQL Database. Si presuppone che i dati di input e la tabella Marketing del sink abbiano tre colonne: ProfileID, State e Category.Assume that input data and the sink Marketing table each have three columns: ProfileID, State, and Category. Eseguire l'operazione di upsert nella colonna ProfileID e applicarla solo a una categoria specifica.Do the upsert based on the ProfileID column, and only apply it for a specific category.

Set di dati di output: "tableName" deve essere lo stesso nome parametro di tipo tabella della stored procedure (vedere di seguito script di stored procedure).Output dataset: the "tableName" should be the same table type parameter name in your stored procedure (see below stored procedure script).

{
    "name": "AzureSQLDbDataset",
    "properties":
    {
        "type": "AzureSqlTable",
        "linkedServiceName": {
            "referenceName": "<Azure SQL Database linked service name>",
            "type": "LinkedServiceReference"
        },
        "typeProperties": {
            "tableName": "Marketing"
        }
    }
}

Definire le sink SQL sezione nell'attività di copia come indicato di seguito.Define the SQL sink section in copy activity as follows.

"sink": {
    "type": "SqlSink",
    "SqlWriterTableType": "MarketingType",
    "SqlWriterStoredProcedureName": "spOverwriteMarketing",
    "storedProcedureParameters": {
        "category": {
            "value": "ProductA"
        }
    }
}

Nel database definire la stored procedure con lo stesso nome di SqlWriterStoredProcedureName,In your database, define the stored procedure with the same name as the SqlWriterStoredProcedureName. che gestisce i dati di input dell'origine specificata e li unisce nella tabella di output.It handles input data from your specified source and merges into the output table. Il nome del parametro del tipo di tabella nella stored procedure deve essere identico al valore tableName definito nel set di dati.The parameter name of the table type in the stored procedure should be the same as the tableName defined in the dataset.

CREATE PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY, @category varchar(256)
AS
BEGIN
  MERGE [dbo].[Marketing] AS target
  USING @Marketing AS source
  ON (target.ProfileID = source.ProfileID and target.Category = @category)
  WHEN MATCHED THEN
      UPDATE SET State = source.State
  WHEN NOT MATCHED THEN
      INSERT (ProfileID, State, Category)
      VALUES (source.ProfileID, source.State, source.Category);
END

Nel database definire il tipo di tabella con lo stesso nome di sqlWriterTableType.In your database, define the table type with the same name as the sqlWriterTableType. Lo schema del tipo di tabella deve essere identico allo schema restituito dai dati di input.The schema of the table type should be same as the schema returned by your input data.

CREATE TYPE [dbo].[MarketingType] AS TABLE(
    [ProfileID] [varchar](256) NOT NULL,
    [State] [varchar](256) NOT NULL,
    [Category] [varchar](256) NOT NULL
)

La funzionalità di stored procedure sfrutta i parametri valutati a livello di tabella.The stored procedure feature takes advantage of Table-Valued Parameters.

Mapping delle proprietà del flusso di datiMapping Data Flow properties

Informazioni dettagliate dal trasformazione sorgente e sink trasformazione nel Mapping di flusso di dati.Learn details from source transformation and sink transformation in Mapping Data Flow.

Mapping dei tipi di dati per il database SQL di AzureData type mapping for Azure SQL Database

Quando si copiano i dati da o verso il database SQL di Azure, vengono usati i mapping seguenti dai tipi di dati del database SQL di Azure ai tipi di dati provvisori di Azure Data Factory.When you copy data from or to Azure SQL Database, the following mappings are used from Azure SQL Database data types to Azure Data Factory interim data types. Vedere Mapping dello schema e del tipo di dati per informazioni su come l'attività di copia esegue il mapping dello schema di origine e del tipo di dati al sink.See Schema and data type mappings to learn how Copy Activity maps the source schema and data type to the sink.

Tipi di dati del database SQL di AzureAzure SQL Database data type Tipo di dati provvisorio di Data FactoryData Factory interim data type
bigintbigint Int64Int64
binarybinary Byte[]Byte[]
bitbit BooleanBoolean
charchar String, Char[]String, Char[]
datedate DateTimeDateTime
DatetimeDatetime DateTimeDateTime
datetime2datetime2 DateTimeDateTime
DatetimeoffsetDatetimeoffset DateTimeOffsetDateTimeOffset
DecimalDecimal DecimalDecimal
FILESTREAM attribute (varbinary(max))FILESTREAM attribute (varbinary(max)) Byte[]Byte[]
FloatFloat DoubleDouble
imageimage Byte[]Byte[]
intint Int32Int32
moneymoney DecimalDecimal
ncharnchar String, Char[]String, Char[]
ntextntext String, Char[]String, Char[]
numericnumeric DecimalDecimal
nvarcharnvarchar String, Char[]String, Char[]
realreal SingleSingle
rowversionrowversion Byte[]Byte[]
smalldatetimesmalldatetime DateTimeDateTime
smallintsmallint Int16Int16
smallmoneysmallmoney DecimalDecimal
sql_variantsql_variant ObjectObject
texttext String, Char[]String, Char[]
timetime TimeSpanTimeSpan
timestamptimestamp Byte[]Byte[]
tinyinttinyint ByteByte
uniqueidentifieruniqueidentifier GuidGuid
varbinaryvarbinary Byte[]Byte[]
varcharvarchar String, Char[]String, Char[]
xmlxml xmlXml

Nota

Per i tipi di dati associati al tipo provvisorio Decimal, Azure Data Factory supporta attualmente la precisione fino a 28.For data types maps to Decimal interim type, currently ADF support precision up to 28. Se si hanno dati che richiedono una precisione maggiore di 28, è consigliabile convertirli in una stringa in una query SQL.If you have data with precision larger than 28, consider to convert to string in SQL query.

Passaggi successiviNext steps

Per un elenco degli archivi dati supportati come origini o sink dall'attività di copia in Azure Data Factory, vedere Archivi dati e formati supportati.For a list of data stores supported as sources and sinks by Copy Activity in Azure Data Factory, see Supported data stores and formats.