Copiare e trasformare i dati in Istanza gestita SQL di Azure usando Azure Data FactoryCopy and transform data in Azure SQL Managed Instance by using Azure Data Factory

SI APPLICA A: Azure Data Factory Azure Synapse Analytics

Questo articolo illustra come usare l'attività di copia in Azure Data Factory per copiare dati da e in Azure SQL Istanza gestita e usare il flusso di dati per trasformare i dati in Istanza gestita SQL di Azure.This article outlines how to use Copy Activity in Azure Data Factory to copy data from and to Azure SQL Managed Instance, and use Data Flow to transform data in Azure SQL Managed Instance. 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 SQL Istanza gestita è supportato per le attività seguenti:This SQL Managed Instance connector is supported for the following activities:

Per l'attività di copia, questo connettore del database SQL di Azure supporta le funzioni seguenti:For Copy activity, this Azure SQL Database connector supports these functions:

  • Copia dei dati usando l'autenticazione SQL e l'autenticazione del token dell'applicazione Azure Active Directory (Azure AD) con un'entità servizio o identità gestite per le risorse di Azure.Copying 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 dei dati tramite una query SQL o un stored procedure.As a source, retrieving data by using a SQL query or a stored procedure. È anche possibile scegliere di eseguire la copia parallela da SQL MI source, vedere la sezione copia parallela da SQL mi per informazioni dettagliate.You can also choose to parallel copy from SQL MI source, see the Parallel copy from SQL MI section for details.
  • Come sink, creazione automatica della tabella di destinazione se non esiste in base allo schema di origine. Aggiunta di dati a una tabella o richiamo di una stored procedure con la logica personalizzata durante la copia.As a sink, automatically creating destination table if not exists based on the source schema; appending data to a table or invoking a stored procedure with custom logic during copy.

Nota

SQL Istanza gestita Always Encrypted non è attualmente supportato da questo connettore.SQL Managed Instance Always Encrypted isn't supported by this connector now. Per aggirare il contenuto, è possibile usare un connettore ODBC generico e un driver ODBC SQL Server tramite un runtime di integrazione self-hosted.To work around, you can use a generic ODBC connector and a SQL Server ODBC driver via a self-hosted integration runtime. Per altre informazioni, vedere l'articolo relativo all' uso always Encrypted .Learn more from Using Always Encrypted section.

PrerequisitiPrerequisites

Per accedere all' endpoint pubblicodi SQL istanza gestita, è possibile usare una Azure Data Factory runtime di integrazione di Azure gestito.To access the SQL Managed Instance public endpoint, you can use an Azure Data Factory managed Azure integration runtime. Assicurarsi di abilitare l'endpoint pubblico e consentire anche il traffico dell'endpoint pubblico nel gruppo di sicurezza di rete in modo che Azure Data Factory possa connettersi al database.Make sure that you enable the public endpoint and also allow public endpoint traffic on the network security group so that Azure Data Factory can connect to your database. Per ulteriori informazioni, vedere le linee guida.For more information, see this guidance.

Per accedere all'endpoint privato di SQL Istanza gestita, configurare un runtime di integrazione self-hosted in grado di accedere al database.To access the SQL Managed Instance private endpoint, set up a self-hosted integration runtime that can access the database. Se si effettua il provisioning del runtime di integrazione self-hosted nella stessa rete virtuale dell'istanza gestita, verificare che il computer del runtime di integrazione si trovi in una subnet diversa da quella dell'istanza gestita.If you provision the self-hosted integration runtime in the same virtual network as your managed instance, make sure that your integration runtime machine is in a different subnet than your managed instance. Se si effettua il provisioning del runtime di integrazione self-hosted in una rete virtuale diversa da quella dell'istanza gestita, è consigliabile usare un peering di rete virtuale o stabilire una connessione tra reti virtuali.If you provision your self-hosted integration runtime in a different virtual network than your managed instance, you can use either a virtual network peering or a virtual network to virtual network connection. Per altre informazioni, vedere Connettere l'applicazione a Istanza gestita di SQL di Azure.For more information, see Connect your application to SQL Managed Instance.

IntroduzioneGet started

Per eseguire l'attività di copia con una pipeline, è possibile usare uno degli strumenti o SDK seguenti:To perform the Copy activity with a pipeline, you can use one of the following tools or SDKs:

Le sezioni seguenti riportano informazioni dettagliate sulle proprietà che vengono usate per definire Azure Data Factory entità specifiche del connettore SQL Istanza gestita.The following sections provide details about properties that are used to define Azure Data Factory entities specific to the SQL Managed Instance connector.

Proprietà del servizio collegatoLinked service properties

Per il servizio collegato SQL Istanza gestita sono supportate le proprietà seguenti:The following properties are supported for the SQL Managed Instance linked service:

ProprietàProperty DescrizioneDescription ObbligatoriaRequired
typetype La proprietà Type deve essere impostata su AzureSqlMI.The type property must be set to AzureSqlMI. Yes
connectionStringconnectionString Questa proprietà specifica le informazioni di ConnectionString necessarie per connettersi a SQL istanza gestita usando l'autenticazione di SQL.This property specifies the connectionString information that's needed to connect to SQL Managed Instance by using SQL authentication. Per altre informazioni, vedere gli esempi seguenti.For more information, see the following examples.
La porta predefinita è 1433.The default port is 1433. Se si usa Istanza gestita SQL con un endpoint pubblico, specificare in modo esplicito la porta 3342.If you're using SQL Managed Instance with a public endpoint, explicitly specify port 3342.
È anche possibile inserire una password in Azure Key Vault.You also can put a password in Azure Key Vault. Se si tratta dell'autenticazione SQL, estrarre la password configurazione dalla stringa di connessione.If it's SQL authentication, pull the password configuration out of the connection string. Per ulteriori informazioni, vedere l'esempio JSON che segue la tabella e archivia le credenziali in Azure Key Vault.For more information, see the JSON example following the table and Store credentials in Azure Key Vault.
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à servizioYes, 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 Azure Data Factory o fare riferimento a un segreto archiviato nel Azure Key Vault.Mark this field as SecureString to store it securely in Azure Data Factory or reference a secret stored in Azure Key Vault. Sì, quando si usa l'autenticazione Azure AD con un'entità servizioYes, when you use Azure AD authentication with a service principal
tenanttenant Specificare le informazioni sul tenant, ad esempio il nome di dominio o l'ID tenant, in cui risiede l'applicazione.Specify the tenant information, like the domain name or tenant ID, under which your application resides. Recuperarlo passando il cursore del mouse sull'angolo superiore destro del portale di Azure.Retrieve it by hovering the mouse in the upper-right corner of the Azure portal. Sì, quando si usa l'autenticazione Azure AD con un'entità servizioYes, when you use Azure AD authentication with a service principal
azureCloudTypeazureCloudType Per l'autenticazione dell'entità servizio, specificare il tipo di ambiente cloud di Azure in cui è registrata l'applicazione Azure AD.For service principal authentication, specify the type of Azure cloud environment to which your Azure AD application is registered.
I valori consentiti sono AzurePublic, AzureChina, AzureUsGovernment e AzureGermany.Allowed values are AzurePublic, AzureChina, AzureUsGovernment, and AzureGermany. Per impostazione predefinita, viene usato l'ambiente cloud del data factory.By default, the data factory's cloud environment is used.
NoNo
connectViaconnectVia Questo runtime di integrazione viene usato per connettersi all'archivio dati.This integration runtime is used to connect to the data store. È possibile usare un runtime di integrazione self-hosted o un runtime di integrazione di Azure se l'istanza gestita ha un endpoint pubblico e consente a Azure Data Factory di accedervi.You can use a self-hosted integration runtime or an Azure integration runtime if your managed instance has a public endpoint and allows Azure Data Factory to access it. Se non specificato, viene usato il runtime di integrazione di Azure predefinito.If not specified, the default Azure integration runtime is used. Yes

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:

Autenticazione SQLSQL authentication

Esempio 1: usare l'autenticazione SQLExample 1: use SQL authentication

{
    "name": "AzureSqlMILinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "connectionString": "Data Source=<hostname,port>;Initial Catalog=<databasename>;Integrated Security=False;User ID=<username>;Password=<password>;"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Esempio 2: usare l'autenticazione SQL con una password in Azure Key VaultExample 2: use SQL authentication with a password in Azure Key Vault

{
    "name": "AzureSqlMILinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "connectionString": "Data Source=<hostname,port>;Initial Catalog=<databasename>;Integrated Security=False;User ID=<username>;",
            "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. Seguire i passaggi per eseguire il provisioning di un amministratore Azure Active Directory per il istanza gestita.Follow the steps to Provision an Azure Active Directory administrator for your Managed Instance.

  2. Creare un'applicazione 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
  3. Creare gli account di accesso per l'identità gestita Azure Data Factory.Create logins for the Azure Data Factory managed identity. In SQL Server Management Studio (SSMS) connettersi all'istanza gestita usando un account SQL Server che è un amministratore di sistema.In SQL Server Management Studio (SSMS), connect to your managed instance using a SQL Server account that is a sysadmin. Eseguire il codice T-SQL seguente nel database master:In master database, run the following T-SQL:

    CREATE LOGIN [your application name] FROM EXTERNAL PROVIDER
    
  4. Creare utenti di database indipendente per l'identità gestita del Azure Data Factory.Create contained database users for the Azure Data Factory managed identity. Connettersi al database da cui o in cui si vuole copiare i dati ed eseguire il codice T-SQL seguente:Connect to the database from or to which you want to copy data, run the following T-SQL:

    CREATE USER [your application name] FROM EXTERNAL PROVIDER
    
  5. Concedere le autorizzazioni necessarie per la Data Factory identità gestita come si fa normalmente per gli utenti SQL e altri.Grant the Data Factory managed identity needed permissions as you normally do for SQL users and others. Eseguire il codice seguente.Run the following code. Per altre opzioni, vedere questo documento.For more options, see this document.

    ALTER ROLE [role name e.g. db_owner] ADD MEMBER [your application name]
    
  6. Configurare un servizio collegato SQL Istanza gestita in Azure Data Factory.Configure a SQL Managed Instance linked service in Azure Data Factory.

Esempio: usare l'autenticazione basata su entità servizioExample: use service principal authentication

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "connectionString": "Data Source=<hostname,port>;Initial Catalog=<databasename>;",
            "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 di SQL Istanza gestita.You can use this managed identity for SQL Managed Instance authentication. La factory specificata può accedere e copiare i dati dal database o nel database usando questa identità.The designated factory can access and copy data from or to your database by using this identity.

Per utilizzare l'autenticazione di identità gestita, attenersi alla seguente procedura.To use managed identity authentication, follow these steps.

  1. Seguire i passaggi per eseguire il provisioning di un amministratore Azure Active Directory per il istanza gestita.Follow the steps to Provision an Azure Active Directory administrator for your Managed Instance.

  2. Creare gli account di accesso per l'identità gestita Azure Data Factory.Create logins for the Azure Data Factory managed identity. In SQL Server Management Studio (SSMS) connettersi all'istanza gestita usando un account SQL Server che è un amministratore di sistema.In SQL Server Management Studio (SSMS), connect to your managed instance using a SQL Server account that is a sysadmin. Eseguire il codice T-SQL seguente nel database master:In master database, run the following T-SQL:

    CREATE LOGIN [your Data Factory name] FROM EXTERNAL PROVIDER
    
  3. Creare utenti di database indipendente per l'identità gestita del Azure Data Factory.Create contained database users for the Azure Data Factory managed identity. Connettersi al database da cui o in cui si vuole copiare i dati ed eseguire il codice T-SQL seguente:Connect to the database from or to which you want to copy data, run the following T-SQL:

    CREATE USER [your Data Factory name] FROM EXTERNAL PROVIDER
    
  4. Concedere le autorizzazioni necessarie per la Data Factory identità gestita come si fa normalmente per gli utenti SQL e altri.Grant the Data Factory managed identity needed permissions as you normally do for SQL users and others. Eseguire il codice seguente.Run the following code. Per altre opzioni, vedere questo documento.For more options, see this document.

    ALTER ROLE [role name e.g. db_owner] ADD MEMBER [your Data Factory name]
    
  5. Configurare un servizio collegato SQL Istanza gestita in Azure Data Factory.Configure a SQL Managed Instance linked service in Azure Data Factory.

Esempio: usa l'autenticazione dell'identità gestitaExample: uses managed identity authentication

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "connectionString": "Data Source=<hostname,port>;Initial Catalog=<databasename>;"
        },
        "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 definire set di dati, vedere l'articolo sui set di dati.For a full list of sections and properties available for use to define datasets, see the datasets article. Questa sezione presenta un elenco delle proprietà supportate dal set di dati di SQL Istanza gestita.This section provides a list of properties supported by the SQL Managed Instance dataset.

Per copiare dati da e verso Istanza gestita SQL, sono supportate le proprietà seguenti:To copy data to and from SQL Managed Instance, the following properties are supported:

ProprietàProperty DescrizioneDescription ObbligatoriaRequired
typetype La proprietà Type del set di dati deve essere impostata su AzureSqlMITable.The type property of the dataset must be set to AzureSqlMITable. Yes
schemaschema Nome dello schema.Name of the schema. No per l'origine, Sì per il sinkNo for source, Yes for sink
tabellatable Nome della tabella/vista.Name of the table/view. No per l'origine, Sì per il sinkNo for source, Yes for sink
tableNametableName Nome della tabella/vista con schema.Name of the table/view with schema. Questa proprietà è supportata per garantire la compatibilità con le versioni precedenti.This property is supported for backward compatibility. Per i nuovi carichi di lavoro, usare schema e table.For new workload, use schema and table. No per l'origine, Sì per il sinkNo for source, Yes for sink

EsempioExample

{
    "name": "AzureSqlMIDataset",
    "properties":
    {
        "type": "AzureSqlMITable",
        "linkedServiceName": {
            "referenceName": "<SQL Managed Instance linked service name>",
            "type": "LinkedServiceReference"
        },
        "schema": [ < physical schema, optional, retrievable during authoring > ],
        "typeProperties": {
            "schema": "<schema_name>",
            "table": "<table_name>"
        }
    }
}

Proprietà dell'attività di copiaCopy activity properties

Per un elenco completo delle sezioni e delle proprietà disponibili per definire le attività, vedere l'articolo sulle pipeline.For a full list of sections and properties available for use to define activities, see the Pipelines article. Questa sezione presenta un elenco delle proprietà supportate dall'origine e dal sink di SQL Istanza gestita.This section provides a list of properties supported by the SQL Managed Instance source and sink.

Istanza gestita SQL come origineSQL Managed Instance as a source

Suggerimento

Per caricare i dati da SQL MI in modo efficiente usando il partizionamento dei dati, vedere altre informazioni da copia parallela da SQL mi.To load data from SQL MI efficiently by using data partitioning, learn more from Parallel copy from SQL MI.

Per copiare dati da SQL Istanza gestita, nella sezione origine dell'attività di copia sono supportate le proprietà seguenti:To copy data from SQL Managed Instance, 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 SqlMISource.The type property of the copy activity source must be set to SqlMISource. Yes
sqlReaderQuerysqlReaderQuery Questa proprietà usa la query SQL personalizzata per leggere i dati.This property uses the custom SQL query to read data. Un esempio è select * from MyTable.An example is select * from MyTable. NoNo
sqlReaderStoredProcedureNamesqlReaderStoredProcedureName Questa proprietà definisce il nome della stored procedure che legge i dati dalla tabella di origine.This property is 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 Questi parametri sono relativi alla stored procedure.These parameters are for the stored procedure.
I valori consentiti sono coppie nome-valore.Allowed values are name or value pairs. I nomi e l'uso di maiuscole e minuscole dei parametri devono corrispondere a quelli dei parametri della stored procedure.The names and casing of the parameters must match the names and casing of the stored procedure parameters.
NoNo
isolationLevelisolationLevel Specifica il comportamento di blocco della transazione per l'origine SQL.Specifies the transaction locking behavior for the SQL source. I valori consentiti sono: ReadCommitted, READUNCOMMITTED, RepeatableRead, Serializable, snapshot.The allowed values are: ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, Snapshot. Se non specificato, viene usato il livello di isolamento predefinito del database.If not specified, the database's default isolation level is used. Per altre informazioni dettagliate, vedere questo documento.Refer to this doc for more details. NoNo
partitionOptionspartitionOptions Specifica le opzioni di partizionamento dei dati utilizzate per caricare i dati da SQL MI.Specifies the data partitioning options used to load data from SQL MI.
I valori consentiti sono: None (impostazione predefinita), PhysicalPartitionsOfTable e DynamicRange.Allowed values are: None (default), PhysicalPartitionsOfTable, and DynamicRange.
Quando è abilitata un'opzione di partizione (ovvero non None ), il grado di parallelismo per caricare simultaneamente i dati da SQL mi è controllato dall' parallelCopies impostazione dell'attività di copia.When a partition option is enabled (that is, not None), the degree of parallelism to concurrently load data from SQL MI is controlled by the parallelCopies setting on the copy activity.
NoNo
partitionSettingspartitionSettings Specifica il gruppo di impostazioni per il partizionamento dei dati.Specify the group of the settings for data partitioning.
Applicare quando l'opzione partition non è None .Apply when the partition option isn't None.
NoNo
In partitionSettings :Under partitionSettings:
partitionColumnNamepartitionColumnName Specificare il nome della colonna di origine in un tipo Integer o data/ora ( int ,, smallint bigint , date , smalldatetime , datetime , datetime2 o datetimeoffset ) che verrà utilizzato dal partizionamento dell'intervallo per la copia parallela.Specify the name of the source column in integer or date/datetime type (int, smallint, bigint, date, smalldatetime, datetime, datetime2, or datetimeoffset) that will be used by range partitioning for parallel copy. Se non è specificato, l'indice o la chiave primaria della tabella vengono rilevati automaticamente e utilizzati come colonna della partizione.If not specified, the index or the primary key of the table is auto-detected and used as the partition column.
Si applica quando l'opzione di partizione è DynamicRange.Apply when the partition option is DynamicRange. Se si utilizza una query per recuperare i dati di origine, associare ?AdfDynamicRangePartitionCondition la clausola WHERE.If you use a query to retrieve the source data, hook ?AdfDynamicRangePartitionCondition in the WHERE clause. Per un esempio, vedere la sezione copia parallela da database SQL .For an example, see the Parallel copy from SQL database section.
NoNo
partitionUpperBoundpartitionUpperBound Valore massimo della colonna di partizione per la suddivisione dell'intervallo di partizioni.The maximum value of the partition column for partition range splitting. Questo valore viene usato per decidere lo stride della partizione, non per filtrare le righe nella tabella.This value is used to decide the partition stride, not for filtering the rows in table. Tutte le righe della tabella o del risultato della query verranno partizionate e copiate.All rows in the table or query result will be partitioned and copied. Se non è specificato, l'attività di copia rileva automaticamente il valore.If not specified, copy activity auto detect the value.
Si applica quando l'opzione di partizione è DynamicRange.Apply when the partition option is DynamicRange. Per un esempio, vedere la sezione copia parallela da database SQL .For an example, see the Parallel copy from SQL database section.
NoNo
partitionLowerBoundpartitionLowerBound Valore minimo della colonna di partizione per la suddivisione dell'intervallo di partizioni.The minimum value of the partition column for partition range splitting. Questo valore viene usato per decidere lo stride della partizione, non per filtrare le righe nella tabella.This value is used to decide the partition stride, not for filtering the rows in table. Tutte le righe della tabella o del risultato della query verranno partizionate e copiate.All rows in the table or query result will be partitioned and copied. Se non è specificato, l'attività di copia rileva automaticamente il valore.If not specified, copy activity auto detect the value.
Si applica quando l'opzione di partizione è DynamicRange.Apply when the partition option is DynamicRange. Per un esempio, vedere la sezione copia parallela da database SQL .For an example, see the Parallel copy from SQL database section.
NoNo

Tenere presente quanto segue:Note the following points:

  • Se viene specificato sqlReaderQuery per SqlMISource, l'attività di copia esegue la query sull'origine SQL istanza gestita per ottenere i dati.If sqlReaderQuery is specified for SqlMISource, the copy activity runs this query against the SQL Managed Instance source to get the data. In alternativa, è possibile specificare una stored procedure indicando i parametri sqlReaderStoredProcedureName e storedProcedureParameters, se la stored procedure accetta parametri.You also can specify a stored procedure by specifying sqlReaderStoredProcedureName and storedProcedureParameters if the stored procedure takes parameters.
  • Quando si usa stored procedure nell'origine per recuperare i dati, tenere presente che se il stored procedure è stato progettato per restituire uno schema diverso quando viene passato un valore di parametro diverso, potrebbe verificarsi un errore o un risultato imprevisto durante l'importazione dello schema dall'interfaccia utente o durante la copia dei dati nel database SQL con la creazione automatica della tabella.When using stored procedure in source to retrieve data, note if your stored procedure is designed as returning different schema when different parameter value is passed in, you may encounter failure or see unexpected result when importing schema from UI or when copying data to SQL database with auto table creation.

Esempio: usare una query SQLExample: Use a SQL query

"activities":[
    {
        "name": "CopyFromAzureSqlMI",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<SQL Managed Instance input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlMISource",
                "sqlReaderQuery": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Esempio: usare un stored procedureExample: Use a stored procedure

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

Definizione della stored procedureThe stored 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

Istanza gestita SQL come sinkSQL Managed Instance as a sink

Suggerimento

Scopri di più sui comportamenti di scrittura supportati, sulle configurazioni e sulle procedure consigliate, dalla procedura consigliata per il caricamento di dati in SQL istanza gestita.Learn more about the supported write behaviors, configurations, and best practices from Best practice for loading data into SQL Managed Instance.

Per copiare dati in SQL Istanza gestita, nella sezione sink dell'attività di copia sono supportate le proprietà seguenti:To copy data to SQL Managed Instance, 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 SqlMISink.The type property of the copy activity sink must be set to SqlMISink. Yes
preCopyScriptpreCopyScript Questa proprietà specifica una query SQL per l'attività di copia da eseguire prima di scrivere i dati in SQL Istanza gestita.This property specifies a SQL query for the copy activity to run before writing data into SQL Managed Instance. Viene richiamata solo una volta per ogni esecuzione della copia.It's invoked only once per copy run. È possibile usare questa proprietà per pulire i dati precaricati.You can use this property to clean up preloaded data. NoNo
tableOptiontableOption Specifica se creare automaticamente la tabella di sink se non esiste in base allo schema di origine.Specifies whether to automatically create the sink table if not exists based on the source schema. La creazione automatica della tabella non è supportata quando il sink specifica stored procedure.Auto table creation is not supported when sink specifies stored procedure. I valori consentiti sono: none (impostazione predefinita), autoCreate.Allowed values are: none (default), autoCreate. 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 vengono eseguite una sola volta e non hanno nulla a che fare con i dati di origine, ad esempio DELETE o TRUNCATE, usare la preCopyScript Proprietà.For operations that run only once and have nothing to do with source data, for example, delete or truncate, use the preCopyScript property.
Vedere l'esempio da richiamare un stored procedure da un sink SQL.See example from Invoke a stored procedure from a SQL sink.
NoNo
storedProcedureTableTypeParameterNamestoredProcedureTableTypeParameterName Nome del parametro del tipo di tabella specificato nell'stored procedure.The parameter name of the table type specified in the stored procedure. NoNo
sqlWriterTableTypesqlWriterTableType Nome del tipo di tabella da utilizzare nel stored procedure.The table type name to be used in the stored procedure. Nel corso dell'attività di copia, i dati spostati vengono resi disponibili in una tabella temporanea di questo tipo.The copy activity makes the data being moved available in a temp table with this table type. Il codice della stored procedure può quindi unire i dati di cui è in corso la copia con i dati esistenti.Stored procedure code can then merge the data that's being copied with existing data. 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
writeBatchSizewriteBatchSize Numero di righe da inserire nella tabella SQL per batch.Number of rows to insert into the SQL table per batch.
I valori consentiti sono integer per il numero di righe.Allowed values are integers for the number of rows. Per impostazione predefinita, Azure Data Factory determina in modo dinamico le dimensioni del batch appropriate in base alle dimensioni della riga.By default, Azure Data Factory dynamically determines the appropriate batch size based on the row size.
NoNo
writeBatchTimeoutwriteBatchTimeout Questa proprietà specifica il tempo di attesa per l'operazione di inserimento batch da completare prima del timeout.This property specifies the wait time for the batch insert operation to complete before it times out.
I valori consentiti sono per l'intervallo di tempo.Allowed values are for the timespan. Un esempio è "00:30:00", ovvero 30 minuti.An example is "00:30:00," which is 30 minutes.
NoNo
maxConcurrentConnectionsmaxConcurrentConnections Limite massimo di connessioni simultanee stabilite all'archivio dati durante l'esecuzione dell'attività.The upper limit of concurrent connections established to the data store during the activity run. Specificare un valore solo quando si desidera limitare le connessioni simultanee.Specify a value only when you want to limit concurrent connections. NoNo

Esempio 1: accodare i datiExample 1: Append data

"activities":[
    {
        "name": "CopyToAzureSqlMI",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Managed Instance output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlMISink",
                "tableOption": "autoCreate",
                "writeBatchSize": 100000
            }
        }
    }
]

Esempio 2: richiamare un stored procedure durante la copiaExample 2: Invoke a stored procedure during copy

Per ulteriori informazioni, vedere richiamare un stored procedure da un sink SQL mi.Learn more details from Invoke a stored procedure from a SQL MI sink.

"activities":[
    {
        "name": "CopyToAzureSqlMI",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Managed Instance output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlMISink",
                "sqlWriterStoredProcedureName": "CopyTestStoredProcedureWithParameters",
                "storedProcedureTableTypeParameterName": "MyTable",
                "sqlWriterTableType": "MyTableType",
                "storedProcedureParameters": {
                    "identifier": { "value": "1", "type": "Int" },
                    "stringData": { "value": "str1" }
                }
            }
        }
    }
]

Copia parallela da SQL MIParallel copy from SQL MI

Il connettore Istanza gestita di Azure SQL nell'attività di copia fornisce il partizionamento dei dati predefinito per copiare i dati in parallelo.The Azure SQL Managed Instance connector in copy activity provides built-in data partitioning to copy data in parallel. È possibile trovare le opzioni di partizionamento dei dati nella scheda origine dell'attività di copia.You can find data partitioning options on the Source tab of the copy activity.

Screenshot delle opzioni di partizione

Quando si Abilita la copia partizionata, l'attività di copia esegue query parallele sull'origine SQL MI per caricare i dati in base alle partizioni.When you enable partitioned copy, copy activity runs parallel queries against your SQL MI source to load data by partitions. Il grado di parallelismo è controllato dall'impostazione parallelCopies sull'attività di copia.The parallel degree is controlled by the parallelCopies setting on the copy activity. Se, ad esempio, si imposta parallelCopies su quattro, data factory genera ed esegue contemporaneamente quattro query in base all'opzione di partizione specificata e alle impostazioni e ogni query recupera una porzione di dati da SQL mi.For example, if you set parallelCopies to four, Data Factory concurrently generates and runs four queries based on your specified partition option and settings, and each query retrieves a portion of data from your SQL MI.

Si consiglia di abilitare la copia parallela con il partizionamento dei dati, specialmente quando si caricano grandi quantità di dati da SQL MI.You are suggested to enable parallel copy with data partitioning especially when you load large amount of data from your SQL MI. Di seguito sono riportate le configurazioni consigliate per i diversi scenari:The following are suggested configurations for different scenarios. Quando si copiano dati in un archivio dati basato su file, è consigliabile scrivere in una cartella come più file (specificare solo il nome della cartella), nel qual caso le prestazioni sono migliori rispetto alla scrittura in un singolo file.When copying data into file-based data store, it's recommended to write to a folder as multiple files (only specify folder name), in which case the performance is better than writing to a single file.

ScenarioScenario Impostazioni consigliateSuggested settings
Caricamento completo da tabelle di grandi dimensioni, con partizioni fisiche.Full load from large table, with physical partitions. Opzione partition: partizioni fisiche della tabella.Partition option: Physical partitions of table.

Durante l'esecuzione, Data Factory rileva automaticamente le partizioni fisiche e copia i dati in base alle partizioni.During execution, Data Factory automatically detects the physical partitions, and copies data by partitions.

Per verificare se la tabella contiene o meno una partizione fisica, è possibile fare riferimento a questa query.To check if your table has physical partition or not, you can refer to this query.
Caricamento completo da tabelle di grandi dimensioni, senza partizioni fisiche, con una colonna integer o DateTime per il partizionamento dei dati.Full load from large table, without physical partitions, while with an integer or datetime column for data partitioning. Opzioni di partizione: Partizione a intervalli dinamici.Partition options: Dynamic range partition.
Colonna partizione (facoltativo): specificare la colonna utilizzata per partizionare i dati.Partition column (optional): Specify the column used to partition data. Se non è specificato, viene utilizzata la colonna di chiave primaria o di indice.If not specified, the index or primary key column is used.
Limite superiore della partizione e limite inferiore della partizione (facoltativo): specificare se si vuole determinare lo stride della partizione.Partition upper bound and partition lower bound (optional): Specify if you want to determine the partition stride. Questa operazione non è relativa al filtraggio delle righe nella tabella. tutte le righe della tabella verranno partizionate e copiate.This is not for filtering the rows in table, all rows in the table will be partitioned and copied. Se non è specificato, l'attività di copia rileva automaticamente i valori.If not specified, copy activity auto detect the values.

Se, ad esempio, la colonna di partizione "ID" ha valori compresi tra 1 e 100 e si imposta il limite inferiore su 20 e il limite superiore come 80, con la copia parallela 4, Data Factory recupera i dati di 4 partizioni-ID nell'intervallo <= 20, [21, 50], [51, 80] e >= 81 rispettivamente.For example, if your partition column "ID" has values range from 1 to 100, and you set the lower bound as 20 and the upper bound as 80, with parallel copy as 4, Data Factory retrieves data by 4 partitions - IDs in range <=20, [21, 50], [51, 80], and >=81, respectively.
Caricare una grande quantità di dati tramite una query personalizzata, senza partizioni fisiche, con una colonna integer o date/DateTime per il partizionamento dei dati.Load a large amount of data by using a custom query, without physical partitions, while with an integer or date/datetime column for data partitioning. Opzioni di partizione: Partizione a intervalli dinamici.Partition options: Dynamic range partition.
Query: SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>.Query: SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>.
Colonna di partizione: Specificare la colonna usata per partizionare i dati.Partition column: Specify the column used to partition data.
Limite superiore della partizione e limite inferiore della partizione (facoltativo): specificare se si vuole determinare lo stride della partizione.Partition upper bound and partition lower bound (optional): Specify if you want to determine the partition stride. Non per filtrare le righe nella tabella, tutte le righe nel risultato della query verranno partizionate e copiate.This is not for filtering the rows in table, all rows in the query result will be partitioned and copied. Se non è specificato, l'attività di copia rileva automaticamente il valore.If not specified, copy activity auto detect the value.

Durante l'esecuzione, Data Factory sostituisce ?AdfRangePartitionColumnName con il nome della colonna e gli intervalli di valori effettivi per ogni partizione e invia a SQL mi.During execution, Data Factory replaces ?AdfRangePartitionColumnName with the actual column name and value ranges for each partition, and sends to SQL MI.
Se, ad esempio, la colonna di partizione "ID" ha valori compresi tra 1 e 100 e si imposta il limite inferiore su 20 e il limite superiore come 80, con la copia parallela 4, Data Factory recupera i dati di 4 partizioni-ID nell'intervallo <= 20, [21, 50], [51, 80] e >= 81 rispettivamente.For example, if your partition column "ID" has values range from 1 to 100, and you set the lower bound as 20 and the upper bound as 80, with parallel copy as 4, Data Factory retrieves data by 4 partitions- IDs in range <=20, [21, 50], [51, 80], and >=81, respectively.

Di seguito sono riportate altre query di esempio per scenari diversi:Here are more sample queries for different scenarios:
1. eseguire una query sull'intera tabella:1. Query the whole table:
SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition
2. eseguire una query da una tabella con la selezione delle colonne e i filtri aggiuntivi della clausola WHERE:2. Query from a table with column selection and additional where-clause filters:
SELECT <column_list> FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>
3. eseguire una query con sottoquery:3. Query with subqueries:
SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>
4. query con partizione in sottoquery:4. Query with partition in subquery:
SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition) AS T

Procedure consigliate per caricare i dati con l'opzione di partizione:Best practices to load data with partition option:

  1. Scegliere una colonna distinta come colonna di partizione, ad esempio chiave primaria o chiave univoca, per evitare l'asimmetria dei dati.Choose distinctive column as partition column (like primary key or unique key) to avoid data skew.
  2. Se la tabella include una partizione predefinita, usare l'opzione di partizione "partizioni fisiche della tabella" per ottenere prestazioni migliori.If the table has built-in partition, use partition option "Physical partitions of table" to get better performance.
  3. Se si usa Azure Integration Runtime per copiare i dati, è possibile impostare "Data Integration Unit (DIU)" di dimensioni maggiori (>4) per usare più risorse di elaborazione.If you use Azure Integration Runtime to copy data, you can set larger "Data Integration Units (DIU)" (>4) to utilize more computing resource. Controllare gli scenari applicabili.Check the applicable scenarios there.
  4. "Degree of copy parallelism" controlla i numeri di partizione, impostando questo numero su un valore troppo grande a volte, è consigliabile impostare questo numero come (Diu o numero di nodi IR self-hosted) * (da 2 a 4)."Degree of copy parallelism" control the partition numbers, setting this number too large sometime hurts the performance, recommend setting this number as (DIU or number of Self-hosted IR nodes) * (2 to 4).

Esempio: caricamento completo da tabelle di grandi dimensioni con partizioni fisicheExample: full load from large table with physical partitions

"source": {
    "type": "SqlMISource",
    "partitionOption": "PhysicalPartitionsOfTable"
}

Esempio: query con partizione a intervalli dinamiciExample: query with dynamic range partition

"source": {
    "type": "SqlMISource",
    "query": "SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>",
    "partitionOption": "DynamicRange",
    "partitionSettings": {
        "partitionColumnName": "<partition_column_name>",
        "partitionUpperBound": "<upper_value_of_partition_column (optional) to decide the partition stride, not as data filter>",
        "partitionLowerBound": "<lower_value_of_partition_column (optional) to decide the partition stride, not as data filter>"
    }
}

Query di esempio per controllare la partizione fisicaSample query to check physical partition

SELECT DISTINCT s.name AS SchemaName, t.name AS TableName, pf.name AS PartitionFunctionName, c.name AS ColumnName, iif(pf.name is null, 'no', 'yes') AS HasPartition
FROM sys.tables AS t
LEFT JOIN sys.objects AS o ON t.object_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id 
LEFT JOIN sys.index_columns AS ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id 
LEFT JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id 
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id 
LEFT JOIN sys.partition_functions pf ON pf.function_id = ps.function_id 
WHERE s.name='[your schema]' AND t.name = '[your table name]'

Se la tabella contiene una partizione fisica, verrà visualizzato "HasPartition" come "Yes" come indicato di seguito.If the table has physical partition, you would see "HasPartition" as "yes" like the following.

Risultato della query SQL

Procedura consigliata per il caricamento di dati in SQL Istanza gestitaBest practice for loading data into SQL Managed Instance

Quando si copiano dati in SQL Istanza gestita, potrebbe essere necessario un comportamento di scrittura diverso:When you copy data into SQL Managed Instance, you might require different write behavior:

  • Accoda: i dati di origine hanno solo i nuovi record.Append: My source data has only new records.
  • Upsert: i dati di origine sono inserimenti e aggiornamenti.Upsert: My source data has both inserts and updates.
  • Overwrite: si desidera ricaricare ogni volta l'intera tabella della dimensione.Overwrite: I want to reload the entire dimension table each time.
  • Scrivi 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 le rispettive sezioni per la configurazione in Azure Data Factory e procedure consigliate.See the respective sections for how to configure in Azure Data Factory and best practices.

Accodare datiAppend data

L'accodamento dei dati è il comportamento predefinito del connettore di sink di SQL Istanza gestita.Appending data is the default behavior of the SQL Managed Instance sink connector. Azure Data Factory esegue un inserimento bulk per scrivere in modo efficiente nella tabella.Azure Data Factory does a bulk insert to write to your table efficiently. È possibile configurare l'origine e il sink di conseguenza nell'attività di copia.You can configure the source and sink accordingly in the copy activity.

Eseguire l'upsert dei datiUpsert data

Opzione 1: Quando si dispone di una grande quantità di dati da copiare, è possibile eseguire il caricamento bulk di tutti i record in una tabella di staging usando l'attività di copia, quindi eseguire un'attività stored procedure per applicare un'istruzione merge o Insert/Update in un'unica immagine.Option 1: When you have a large amount of data to copy, you can bulk load all records into a staging table by using the copy activity, then run a stored procedure activity to apply a MERGE or INSERT/UPDATE statement in one shot.

L'attività di copia attualmente non supporta in modo nativo il caricamento dei dati in una tabella temporanea di database.Copy activity currently doesn't natively support loading data into a database temporary table. Esiste una modalità avanzata per configurarla con una combinazione di più attività. vedere ottimizzare gli scenari di Upsert bulk del database SQL.There is an advanced way to set it up with a combination of multiple activities, refer to Optimize SQL Database Bulk Upsert scenarios. Di seguito viene illustrato un esempio di utilizzo di una tabella permanente come gestione temporanea.Below shows a sample of using a permanent table as staging.

In Azure Data Factory, ad esempio, è possibile creare una pipeline con un' attività di copia concatenata a un' attività di stored procedure.As an example, in Azure Data Factory, you can create a pipeline with a Copy activity chained with a Stored Procedure activity. Il primo copia i dati dall'archivio di origine in una tabella di staging di SQL Istanza gestita di Azure, ad esempio UpsertStagingTable, come nome della tabella nel set di dati.The former copies data from your source store into an Azure SQL Managed Instance staging table, for example, UpsertStagingTable, as the table name in the dataset. Il secondo richiama quindi un stored procedure per unire i dati di origine dalla tabella di staging alla tabella di destinazione e pulire la tabella di staging.Then the latter invokes a stored procedure to merge source data from the staging table into the target table and clean up the staging table.

Upsert

Nel database definire un stored procedure con la logica di MERGE, come nell'esempio seguente, a cui fa riferimento l'attività stored procedure precedente.In your database, define a stored procedure with MERGE logic, like the following example, which is pointed to from the previous stored procedure activity. Si supponga che la destinazione sia la tabella Marketing con tre colonne: profileid, stato e categoria.Assume that the target is the Marketing table with three columns: ProfileID, State, and Category. Eseguire Upsert in base alla colonna profileid .Do the upsert based on the ProfileID column.

CREATE PROCEDURE [dbo].[spMergeData]
AS
BEGIN
    MERGE TargetTable AS target
    USING UpsertStagingTable 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 UpsertStagingTable
END

Opzione 2: È possibile scegliere di richiamare un stored procedure all'interno dell'attività di copia.Option 2: You can choose to invoke a stored procedure within the copy activity. Questo approccio esegue ogni batch, come regolato dalla writeBatchSize proprietà, nella tabella di origine anziché utilizzare BULK INSERT come approccio predefinito nell'attività di copia.This approach runs each batch (as governed by the writeBatchSize property) in the source table instead of using bulk insert as the default approach in the copy activity.

Sovrascrivi l'intera tabellaOverwrite the entire table

È possibile configurare la proprietà preCopyScript in un sink dell'attività di copia.You can configure the preCopyScript property in a copy activity sink. In questo caso, per ogni attività di copia eseguita, Azure Data Factory esegue prima lo script.In this case, for each copy activity that runs, Azure Data Factory runs the script first. Esegue quindi la copia per inserire i dati.Then it runs the copy to insert the data. Per sovrascrivere l'intera tabella con i dati più recenti, ad esempio, specificare uno script per eliminare prima tutti i record prima di eseguire il caricamento bulk dei nuovi dati dall'origine.For example, to overwrite the entire table with the latest data, specify a script to first delete all the records before you bulk load the new data from the source.

Scrivere dati con logica personalizzataWrite data with custom logic

I passaggi per scrivere i dati con la logica personalizzata sono simili a quelli descritti nella sezione relativa ai dati di Upsert .The steps to write data with custom logic are similar to those described in the Upsert data section. Quando è necessario applicare un'elaborazione aggiuntiva prima dell'inserimento finale dei dati di origine nella tabella di destinazione, è possibile caricare in una tabella di staging, quindi richiamare stored procedure attività o richiamare una stored procedure nel sink dell'attività di copia per applicare i dati.When you need to apply extra processing before the final insertion of source data into the destination table, you can load to a staging table then invoke stored procedure activity, or invoke a stored procedure in copy activity sink to apply data.

Richiamare una stored procedure da un sink SQLInvoke a stored procedure from a SQL sink

Quando si copiano dati in SQL Istanza gestita, è anche possibile configurare e richiamare una stored procedure specificata dall'utente con parametri aggiuntivi in ogni batch della tabella di origine.When you copy data into SQL Managed Instance, you also can configure and invoke a user-specified stored procedure with additional parameters on each batch of the source table. La funzionalità stored procedure sfrutta i parametri con valori di tabella.The stored procedure feature takes advantage of table-valued parameters.

È possibile usare una stored procedure quando non si possono usare i meccanismi di copia predefiniti.You can use a stored procedure when built-in copy mechanisms don't serve the purpose. Un esempio è quando si desidera applicare un'ulteriore elaborazione prima dell'inserimento finale dei dati di origine nella tabella di destinazione.An example is when you want to apply extra processing before the final insertion of source data into the destination table. Alcuni esempi di elaborazione aggiuntivi sono quando si desidera unire le colonne, cercare valori aggiuntivi e inserirli in più di una tabella.Some extra processing examples are when you want to merge columns, look up additional values, and insert into more than one table.

Nell'esempio seguente viene illustrato come usare una stored procedure per eseguire un'operazione upsert in una tabella del database SQL Server.The following sample shows how to use a stored procedure to do an upsert into a table in the SQL Server database. Si supponga che i dati di input e la tabella di Marketing sink includano tre colonne: profileid, state e Category.Assume that the input data and the sink Marketing table each have three columns: ProfileID, State, and Category. Eseguire Upsert in base alla colonna profileid e applicarla solo a una categoria specifica denominata "producta".Do the upsert based on the ProfileID column, and only apply it for a specific category called "ProductA".

  1. 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 sqlWriterTableType. Lo schema del tipo di tabella è identico allo schema restituito dai dati di input.The schema of the table type is the 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
    )
    
  2. Nel database definire il stored procedure con lo stesso nome di sqlWriterStoredProcedureName.In your database, define the stored procedure with the same name as 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 corrisponde a TableName definito nel set di dati.The parameter name of the table type in the stored procedure is the same as 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
    
  3. In Azure Data Factory definire la sezione sink SQL mi nell'attività di copia come indicato di seguito:In Azure Data Factory, define the SQL MI sink section in the copy activity as follows:

    "sink": {
        "type": "SqlMISink",
        "sqlWriterStoredProcedureName": "spOverwriteMarketing",
        "storedProcedureTableTypeParameterName": "Marketing",
        "sqlWriterTableType": "MarketingType",
        "storedProcedureParameters": {
            "category": {
                "value": "ProductA"
            }
        }
    }
    

Proprietà del flusso di dati per mappingMapping data flow properties

Quando si trasformano i dati nel flusso di dati di mapping, è possibile leggere e scrivere nelle tabelle da Azure SQL Istanza gestita.When transforming data in mapping data flow, you can read and write to tables from Azure SQL Managed Instance. Per altre informazioni, vedere la trasformazione origine e la trasformazione sink nei flussi di dati per mapping.For more information, see the source transformation and sink transformation in mapping data flows.

Nota

Il connettore Istanza gestita SQL di Azure nel flusso di dati di mapping è attualmente disponibile come anteprima pubblica.Azure SQL Managed Instance connector in Mapping Data Flow is currently available as public preview. È possibile connettersi all'endpoint pubblico di SQL Istanza gestita ma non ancora all'endpoint privato.You can connect to SQL Managed Instance public endpoint but not private endpoint yet.

Trasformazione origineSource transformation

La tabella seguente elenca le proprietà supportate dall'origine Istanza gestita SQL di Azure.The below table lists the properties supported by Azure SQL Managed Instance source. È possibile modificare queste proprietà nella scheda Opzioni di origine .You can edit these properties in the Source options tab.

NomeName DescrizioneDescription ObbligatoriaRequired Valori consentitiAllowed values Proprietà script flusso di datiData flow script property
TabellaTable Se si seleziona tabella come input, il flusso di dati recupera tutti i dati dalla tabella specificata nel set di dati.If you select Table as input, data flow fetches all the data from the table specified in the dataset. NoNo - -
QueryQuery Se si seleziona query come input, specificare una query SQL per recuperare i dati dall'origine, che esegue l'override di qualsiasi tabella specificata nel set di dati.If you select Query as input, specify a SQL query to fetch data from source, which overrides any table you specify in dataset. L'uso delle query è un ottimo modo per ridurre le righe per il test o le ricerche.Using queries is a great way to reduce rows for testing or lookups.

La clausola Order by non è supportata, ma è possibile impostare un'istruzione SELECT from completa.Order By clause is not supported, but you can set a full SELECT FROM statement. È possibile usare anche funzioni di tabella definite dall'utente.You can also use user-defined table functions. Select * from udfGetData () è una funzione definita dall'utente in SQL che restituisce una tabella che è possibile utilizzare nel flusso di dati.select * from udfGetData() is a UDF in SQL that returns a table that you can use in data flow.
Esempio di query: Select * from MyTable where customerId > 1000 and customerId < 2000Query example: Select * from MyTable where customerId > 1000 and customerId < 2000
NoNo stringString queryquery
Dimensioni dei batchBatch size Specificare le dimensioni del batch per suddividere i dati di grandi dimensioni in letture.Specify a batch size to chunk large data into reads. NoNo IntegerInteger batchSizebatchSize
Livello di isolamentoIsolation Level Scegliere uno dei seguenti livelli di isolamento:Choose one of the following isolation levels:
-Read Committed- Read Committed
-Read uncommitted (impostazione predefinita)- Read Uncommitted (default)
-Lettura ripetibile- Repeatable Read
-Serializzabile- Serializable
-None (ignora il livello di isolamento)- None (ignore isolation level)
NoNo READ_COMMITTEDREAD_COMMITTED
READ_UNCOMMITTEDREAD_UNCOMMITTED
REPEATABLE_READREPEATABLE_READ
SERIALIZABLESERIALIZABLE
NESSUNO
NONE
isolationLevelisolationLevel

Esempio di script di origine di Azure SQL Istanza gestitaAzure SQL Managed Instance source script example

Quando si usa Istanza gestita SQL di Azure come tipo di origine, lo script del flusso di dati associato è:When you use Azure SQL Managed Instance as source type, the associated data flow script is:

source(allowSchemaDrift: true,
    validateSchema: false,
    isolationLevel: 'READ_UNCOMMITTED',
    query: 'select * from MYTABLE',
    format: 'query') ~> SQLMISource

Trasformazione sinkSink transformation

La tabella seguente elenca le proprietà supportate dal sink di Istanza gestita SQL di Azure.The below table lists the properties supported by Azure SQL Managed Instance sink. È possibile modificare queste proprietà nella scheda Opzioni sink .You can edit these properties in the Sink options tab.

NomeName DescrizioneDescription ObbligatoriaRequired Valori consentitiAllowed values Proprietà script flusso di datiData flow script property
Update (metodo)Update method Specificare le operazioni consentite per la destinazione del database.Specify what operations are allowed on your database destination. Per impostazione predefinita, sono consentiti solo gli inserimenti.The default is to only allow inserts.
Per aggiornare, Upsert o eliminare righe, è necessaria una trasformazione alter Row per contrassegnare le righe per tali azioni.To update, upsert, or delete rows, an Alter row transformation is required to tag rows for those actions.
Yes true o falsetrue or false cancellabiledeletable
inseribileinsertable
aggiornabileupdateable
upsertableupsertable
Colonne chiaveKey columns Per gli aggiornamenti, Upsert ed eliminazioni, è necessario impostare le colonne chiave per determinare la riga da modificare.For updates, upserts and deletes, key column(s) must be set to determine which row to alter.
Il nome della colonna scelto come chiave verrà usato come parte del successivo aggiornamento, Upsert, DELETE.The column name that you pick as the key will be used as part of the subsequent update, upsert, delete. Pertanto, è necessario selezionare una colonna esistente nel mapping del sink.Therefore, you must pick a column that exists in the Sink mapping.
NoNo ArrayArray chiavikeys
Ignora scrittura colonne chiaveSkip writing key columns Se non si desidera scrivere il valore nella colonna chiave, selezionare "Ignora scrittura colonne chiave".If you wish to not write the value to the key column, select "Skip writing key columns". NoNo true o falsetrue or false skipKeyWritesskipKeyWrites
azione TabellaTable action Determina se ricreare o rimuovere tutte le righe dalla tabella di destinazione prima della scrittura.Determines whether to recreate or remove all rows from the destination table prior to writing.
- None: nessuna azione verrà eseguita nella tabella.- None: No action will be done to the table.
- Ricrea: la tabella viene eliminata e ricreata.- Recreate: The table will get dropped and recreated. Questa opzione è obbligatoria se si crea una nuova tabella in modo dinamico.Required if creating a new table dynamically.
- Truncate: tutte le righe della tabella di destinazione vengono rimosse.- Truncate: All rows from the target table will get removed.
NoNo true o falsetrue or false ricrearerecreate
truncatetruncate
Dimensioni dei batchBatch size Consente di specificare il numero di righe scritte in ogni batch.Specify how many rows are being written in each batch. Dimensioni batch più grandi migliorano l'ottimizzazione della compressione e della memoria, ma rischiano di causare eccezioni di memoria insufficiente durante la memorizzazione nella cache dei dati.Larger batch sizes improve compression and memory optimization, but risk out of memory exceptions when caching data. NoNo IntegerInteger batchSizebatchSize
Script pre e post SQLPre and Post SQL scripts Specificare gli script SQL a più righe che verrà eseguito prima (pre-elaborazione) e dopo (post-elaborazione) i dati vengono scritti nel database sink.Specify multi-line SQL scripts that will execute before (pre-processing) and after (post-processing) data is written to your Sink database. NoNo stringString preSQLspreSQLs
postSQLspostSQLs

Esempio di script di sink di Azure SQL Istanza gestitaAzure SQL Managed Instance sink script example

Quando si usa SQL di Azure Istanza gestita come tipo di sink, lo script del flusso di dati associato è:When you use Azure SQL Managed Instance as sink type, the associated data flow script is:

IncomingStream sink(allowSchemaDrift: true,
    validateSchema: false,
    deletable:false,
    insertable:true,
    updateable:true,
    upsertable:true,
    keys:['keyColumn'],
    format: 'table',
    skipDuplicateMapInputs: true,
    skipDuplicateMapOutputs: true) ~> SQLMISink

Proprietà dell'attività LookupLookup activity properties

Per altre informazioni sulle proprietà, vedere Attività Lookup.To learn details about the properties, check Lookup activity.

Proprietà dell'attività GetMetadataGetMetadata activity properties

Per altre informazioni sulle proprietà, vedere Attività GetMetadataTo learn details about the properties, check GetMetadata activity

Mapping dei tipi di dati per SQL Istanza gestitaData type mapping for SQL Managed Instance

Quando i dati vengono copiati in e da SQL Istanza gestita usando l'attività di copia, i mapping seguenti vengono usati dai tipi di dati SQL Istanza gestita per Azure Data Factory i tipi di dati provvisori.When data is copied to and from SQL Managed Instance using copy activity, the following mappings are used from SQL Managed Instance data types to Azure Data Factory interim data types. Per informazioni su come l'attività di copia esegue il mapping dello schema e del tipo di dati di origine al sink, vedere Mapping dello schema e del tipo di dati.To learn how the copy activity maps from the source schema and data type to the sink, see Schema and data type mappings.

Tipo di dati SQL Istanza gestitaSQL Managed Instance data type Tipo di dati provvisorio di Azure Data FactoryAzure Data Factory interim data type
bigintbigint Int64Int64
BINARYbinary Byte[]Byte[]
bitbit BooleanBoolean
charchar String, Char[]String, Char[]
Datadate 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 OggettoObject
texttext String, Char[]String, Char[]
timetime TimeSpanTimeSpan
timestamptimestamp Byte[]Byte[]
TINYINTtinyint Int16Int16
UNIQUEIDENTIFIERuniqueidentifier GuidGuid
varbinaryvarbinary Byte[]Byte[]
varcharvarchar String, Char[]String, Char[]
Xmlxml stringString

Nota

Per i tipi di dati che vengono mappati al tipo provvisorio decimale, attualmente l'attività di copia supporta la precisione fino a 28.For data types that map to the Decimal interim type, currently Copy activity supports 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 that requires precision larger than 28, consider converting to a string in a SQL query.

Uso di Always EncryptedUsing Always Encrypted

Quando si copiano dati da/in Azure SQL Istanza gestita con Always Encrypted, usare il connettore odbc generico e SQL Server driver ODBC tramite Integration Runtime self-hosted.When you copy data from/to Azure SQL Managed Instance with Always Encrypted, use generic ODBC connector and SQL Server ODBC driver via Self-hosted Integration Runtime. Questo connettore Azure SQL Istanza gestita non supporta ora Always Encrypted.This Azure SQL Managed Instance connector does not support Always Encrypted now.

Più in particolare:More specifically:

  1. Configurare un Integration Runtime self-hosted se non è disponibile.Set up a Self-hosted Integration Runtime if you don't have one. Per informazioni dettagliate, vedere l'articolo relativo alla Integration Runtime self-hosted .See Self-hosted Integration Runtime article for details.

  2. Scaricare il driver ODBC a 64 bit per SQL Server da quie installarlo nel computer Integration Runtime.Download the 64-bit ODBC driver for SQL Server from here, and install on the Integration Runtime machine. Per ulteriori informazioni sul funzionamento di questo driver, utilizzare always Encrypted con ODBC driver for SQL Server.Learn more about how this driver works from Using Always Encrypted with the ODBC Driver for SQL Server.

  3. Creare un servizio collegato con tipo ODBC per connettersi al database SQL, fare riferimento agli esempi seguenti:Create linked service with ODBC type to connect to your SQL database, refer to the following samples:

    • Per utilizzare l'autenticazione SQL: specificare la stringa di connessione ODBC come indicato di seguito e selezionare autenticazione di base per impostare il nome utente e la password.To use SQL authentication: Specify the ODBC connection string as below, and select Basic authentication to set the user name and password.

      Driver={ODBC Driver 17 for SQL Server};Server=<serverName>;Database=<databaseName>;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultClientSecret;KeyStorePrincipalId=<servicePrincipalKey>;KeyStoreSecret=<servicePrincipalKey>
      
    • Se si esegue Integration Runtime self-hosted in una macchina virtuale di Azure, è possibile usare l'autenticazione dell'identità gestita con l' identità della VM di Azure:If you run Self-hosted Integration Runtime on Azure Virtual Machine, you can use Managed Identity authentication with Azure VM's identity:

      1. Seguire gli stessi prerequisiti per creare l'utente del database per l'identità gestita e concedere il ruolo appropriato nel database.Follow the same prerequisites to create database user for the managed identity and grant the proper role in your database.
      2. In servizio collegato specificare la stringa di connessione ODBC come indicato di seguito e selezionare autenticazione anonima come indicato nella stringa di connessione stessa Authentication=ActiveDirectoryMsi .In linked service, specify the ODBC connection string as below, and select Anonymous authentication as the connection string itself indicatesAuthentication=ActiveDirectoryMsi.
      Driver={ODBC Driver 17 for SQL Server};Server=<serverName>;Database=<databaseName>;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultClientSecret;KeyStorePrincipalId=<servicePrincipalKey>;KeyStoreSecret=<servicePrincipalKey>; Authentication=ActiveDirectoryMsi;
      
  4. Creare il set di dati e l'attività di copia con il tipo ODBC di conseguenza.Create dataset and copy activity with ODBC type accordingly. Per altre informazioni, vedere connettore ODBC .Learn more from ODBC connector article.

Passaggi successiviNext steps

Per un elenco di archivi dati supportati come origini e sink dall'attività di copia in Azure Data Factory, vedere archivi dati supportati.For a list of data stores supported as sources and sinks by the copy activity in Azure Data Factory, see Supported data stores.