Copiar e transformar dados no Azure SQL Instância Gerenciada usando Azure Data FactoryCopy and transform data in Azure SQL Managed Instance by using Azure Data Factory

APLICA-SE A: Azure Data Factory Azure Synapse Analytics

Este artigo descreve como usar a atividade de cópia em Azure Data Factory para copiar dados de e para o SQL Instância Gerenciada do Azure e usar o fluxo de dados para transformar dados no Instância Gerenciada SQL do 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. Para saber mais sobre o Azure Data Factory, leia as artigo introdutório.To learn about Azure Data Factory, read the introductory article.

Funcionalidades com suporteSupported capabilities

Este conector do SQL Instância Gerenciada tem suporte para as seguintes atividades:This SQL Managed Instance connector is supported for the following activities:

Para a atividade de cópia, este conector do banco de dados SQL do Azure dá suporte a essas funções:For Copy activity, this Azure SQL Database connector supports these functions:

  • Copiar dados usando a autenticação do SQL e a autenticação do token de aplicativo do Azure Active Directory (Azure AD) com uma entidade de serviço ou identidades gerenciadas para recursos do 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.
  • Como fonte, recuperar dados usando uma consulta SQL ou um procedimento armazenado.As a source, retrieving data by using a SQL query or a stored procedure. Você também pode optar por copiar paralelamente da fonte do SQL MI, consulte a seção cópia paralela do SQL mi para obter detalhes.You can also choose to parallel copy from SQL MI source, see the Parallel copy from SQL MI section for details.
  • Como um coletor, criar automaticamente a tabela de destino se não existir com base no esquema de origem; acrescentar dados a uma tabela ou invocar um procedimento armazenado com lógica personalizada durante a cópia.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.

Observação

O SQL Instância Gerenciada Always Encrypted não tem suporte neste conector agora.SQL Managed Instance Always Encrypted isn't supported by this connector now. Para solucionar o trabalho, você pode usar um conector ODBC genérico e um SQL Server driver ODBC por meio de um Integration Runtime auto-hospedado.To work around, you can use a generic ODBC connector and a SQL Server ODBC driver via a self-hosted integration runtime. Saiba mais usando Always Encrypted seção.Learn more from Using Always Encrypted section.

Pré-requisitosPrerequisites

Para acessar o ponto de extremidade públicodo SQL instância gerenciada, você pode usar um Azure data Factory tempo de execução de integração do Azure gerenciado.To access the SQL Managed Instance public endpoint, you can use an Azure Data Factory managed Azure integration runtime. Certifique-se de habilitar o ponto de extremidade público e também permitir o tráfego de ponto de extremidade público no grupo de segurança de rede para que Azure Data Factory possa se conectar ao seu banco de dados.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. Para obter mais informações, consulte este guia.For more information, see this guidance.

Para acessar o ponto de extremidade particular do SQL Instância Gerenciada, configure um tempo de execução de integração auto-hospedado que pode acessar o banco de dados.To access the SQL Managed Instance private endpoint, set up a self-hosted integration runtime that can access the database. Se você provisionar o tempo de execução de integração auto-hospedado na mesma rede virtual que sua instância gerenciada, certifique-se de que seu computador de tempo de execução de integração esteja em uma sub-rede diferente da instância gerenciada.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 você provisionar o tempo de execução de integração auto-hospedado em uma rede virtual diferente da instância gerenciada, você poderá usar um emparelhamento de rede virtual ou uma rede virtual para a conexão de rede virtual.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. Para obter mais informações, confira Conectar seu aplicativo à Instância Gerenciada de SQL.For more information, see Connect your application to SQL Managed Instance.

IntroduçãoGet started

Para executar a atividade de Cópia com um pipeline, será possível usar as ferramentas ou os SDKs abaixo:To perform the Copy activity with a pipeline, you can use one of the following tools or SDKs:

As seções a seguir fornecem detalhes sobre as propriedades que são usadas para definir Azure Data Factory entidades específicas para o conector do SQL Instância Gerenciada.The following sections provide details about properties that are used to define Azure Data Factory entities specific to the SQL Managed Instance connector.

Propriedades do serviço vinculadoLinked service properties

As propriedades a seguir têm suporte para o serviço vinculado do SQL Instância Gerenciada:The following properties are supported for the SQL Managed Instance linked service:

PropriedadeProperty DescriçãoDescription ObrigatórioRequired
typetype A propriedade Type deve ser definida como AzureSqlMI.The type property must be set to AzureSqlMI. SimYes
connectionStringconnectionString Essa propriedade especifica as informações de ConnectionString necessárias para se conectar ao SQL instância gerenciada usando a autenticação do SQL.This property specifies the connectionString information that's needed to connect to SQL Managed Instance by using SQL authentication. Para obter mais informações, consulte os exemplos a seguir.For more information, see the following examples.
A porta padrão é a 1433.The default port is 1433. Se você estiver usando o SQL Instância Gerenciada com um ponto de extremidade público, especifique explicitamente a porta 3342.If you're using SQL Managed Instance with a public endpoint, explicitly specify port 3342.
Você também pode colocar uma senha em Azure Key Vault.You also can put a password in Azure Key Vault. Se for a autenticação do SQL, extraia a password configuração da cadeia de conexão.If it's SQL authentication, pull the password configuration out of the connection string. Para obter mais informações, consulte o exemplo de JSON após a tabela e armazenar as credenciais em Azure Key Vault.For more information, see the JSON example following the table and Store credentials in Azure Key Vault.
SimYes
servicePrincipalIdservicePrincipalId Especifique a ID do cliente do aplicativo.Specify the application's client ID. Sim, quando você usa a autenticação do Azure AD com uma entidade de serviçoYes, when you use Azure AD authentication with a service principal
servicePrincipalKeyservicePrincipalKey Especifique a chave do aplicativo.Specify the application's key. Marque este campo como SecureString para armazená-lo com segurança em Azure data Factory ou fazer referência a um segredo armazenado em 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. Sim, quando você usa a autenticação do Azure AD com uma entidade de serviçoYes, when you use Azure AD authentication with a service principal
locatáriotenant Especifique as informações do locatário, como o nome de domínio ou a ID do locatário, sob a qual seu aplicativo reside.Specify the tenant information, like the domain name or tenant ID, under which your application resides. Recupere-as passando o mouse no canto superior direito do Portal do Azure.Retrieve it by hovering the mouse in the upper-right corner of the Azure portal. Sim, quando você usa a autenticação do Azure AD com uma entidade de serviçoYes, when you use Azure AD authentication with a service principal
azureCloudTypeazureCloudType Para autenticação de entidade de serviço, especifique o tipo de ambiente de nuvem do Azure no qual seu aplicativo do Azure AD está registrado.For service principal authentication, specify the type of Azure cloud environment to which your Azure AD application is registered.
Os valores permitidos são AzurePublic, AzureChina, AzureUsGovernment e AzureGermany.Allowed values are AzurePublic, AzureChina, AzureUsGovernment, and AzureGermany. Por padrão, o ambiente de nuvem do data factory é usado.By default, the data factory's cloud environment is used.
NãoNo
connectViaconnectVia Esse Integration Runtime é usado para se conectar ao armazenamento de dados.This integration runtime is used to connect to the data store. Você pode usar um tempo de execução de integração auto-hospedado ou um tempo de execução de integração do Azure se sua instância gerenciada tiver um ponto de extremidade público e permitir que Azure Data Factory o acesse.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 não especificado, o Azure Integration Runtime padrão será usado.If not specified, the default Azure integration runtime is used. SimYes

Para diferentes tipos de autenticação, consulte as seções a seguir sobre pré-requisitos e amostras JSON, respectivamente:For different authentication types, refer to the following sections on prerequisites and JSON samples, respectively:

Autenticação do SQLSQL authentication

Exemplo 1: usar a autenticação do 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"
        }
    }
}

Exemplo 2: usar a autenticação do SQL com uma senha no 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"
        }
    }
}

Autenticação de entidade de serviçoService principal authentication

Para usar uma autenticação de token de aplicativo do Azure AD baseada no serviço principal, siga estas etapas:To use a service principal-based Azure AD application token authentication, follow these steps:

  1. Siga as etapas para provisionar um administrador de Azure Active Directory para seu instância gerenciada.Follow the steps to Provision an Azure Active Directory administrator for your Managed Instance.

  2. Crie um aplicativo do Azure Active Directory no portal do Azure.Create an Azure Active Directory application from the Azure portal. Anote o nome do aplicativo e os seguintes valores que definem o serviço vinculado:Make note of the application name and the following values that define the linked service:

    • ID do aplicativoApplication ID
    • Chave do aplicativoApplication key
    • ID do locatárioTenant ID
  3. Crie logons para a identidade gerenciada Azure data Factory.Create logins for the Azure Data Factory managed identity. No SQL Server Management Studio (SSMS), conecte-se à sua instância gerenciada usando uma conta de SQL Server que seja um sysadmin.In SQL Server Management Studio (SSMS), connect to your managed instance using a SQL Server account that is a sysadmin. No banco de dados mestre, execute o seguinte T-SQL:In master database, run the following T-SQL:

    CREATE LOGIN [your application name] FROM EXTERNAL PROVIDER
    
  4. Crie usuários de banco de dados independente para a identidade gerenciada Azure data Factory.Create contained database users for the Azure Data Factory managed identity. Conecte-se ao banco de dados do ou para o qual você deseja copiar dados. Execute o seguinte T-SQL: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. Conceda à identidade gerenciada Data Factory permissões necessárias como faria normalmente para usuários do SQL e outros.Grant the Data Factory managed identity needed permissions as you normally do for SQL users and others. Execute o código a seguir.Run the following code. Para mais opções, confira este documento.For more options, see this document.

    ALTER ROLE [role name e.g. db_owner] ADD MEMBER [your application name]
    
  6. Configure um serviço vinculado do SQL Instância Gerenciada no Azure Data Factory.Configure a SQL Managed Instance linked service in Azure Data Factory.

Exemplo: usar a autenticação de entidade de serviçoExample: 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"
        }
    }
}

Identidades gerenciadas para autenticação de recursos do AzureManaged identities for Azure resources authentication

Um data factory pode ser associado a uma identidade gerenciada para recursos do Azure, que representa esse data factory específico.A data factory can be associated with a managed identity for Azure resources that represents the specific data factory. Você pode usar essa identidade gerenciada para autenticação do SQL Instância Gerenciada.You can use this managed identity for SQL Managed Instance authentication. O factory designado pode acessar dados do banco de dados e copiá-los para o banco de dados usando essa identidade.The designated factory can access and copy data from or to your database by using this identity.

Para usar a autenticação de identidade gerenciada, siga estas etapas.To use managed identity authentication, follow these steps.

  1. Siga as etapas para provisionar um administrador de Azure Active Directory para seu instância gerenciada.Follow the steps to Provision an Azure Active Directory administrator for your Managed Instance.

  2. Crie logons para a identidade gerenciada Azure data Factory.Create logins for the Azure Data Factory managed identity. No SQL Server Management Studio (SSMS), conecte-se à sua instância gerenciada usando uma conta de SQL Server que seja um sysadmin.In SQL Server Management Studio (SSMS), connect to your managed instance using a SQL Server account that is a sysadmin. No banco de dados mestre, execute o seguinte T-SQL:In master database, run the following T-SQL:

    CREATE LOGIN [your Data Factory name] FROM EXTERNAL PROVIDER
    
  3. Crie usuários de banco de dados independente para a identidade gerenciada Azure data Factory.Create contained database users for the Azure Data Factory managed identity. Conecte-se ao banco de dados do ou para o qual você deseja copiar dados. Execute o seguinte T-SQL: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. Conceda à identidade gerenciada Data Factory permissões necessárias como faria normalmente para usuários do SQL e outros.Grant the Data Factory managed identity needed permissions as you normally do for SQL users and others. Execute o código a seguir.Run the following code. Para mais opções, confira este documento.For more options, see this document.

    ALTER ROLE [role name e.g. db_owner] ADD MEMBER [your Data Factory name]
    
  5. Configure um serviço vinculado do SQL Instância Gerenciada no Azure Data Factory.Configure a SQL Managed Instance linked service in Azure Data Factory.

Exemplo: usa a autenticação de identidade gerenciadaExample: 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"
        }
    }
}

Propriedades do conjunto de dadosDataset properties

Para obter uma lista completa das seções e das propriedades disponíveis para uso para definir conjuntos de dados, confira o artigo sobre conjuntos de dados.For a full list of sections and properties available for use to define datasets, see the datasets article. Esta seção fornece uma lista das propriedades com suporte pelo conjunto de Instância Gerenciada do SQL.This section provides a list of properties supported by the SQL Managed Instance dataset.

Para copiar dados de e para o SQL Instância Gerenciada, há suporte para as seguintes propriedades:To copy data to and from SQL Managed Instance, the following properties are supported:

PropriedadeProperty DescriçãoDescription ObrigatórioRequired
typetype A propriedade Type do conjunto de conjuntos deve ser definida como AzureSqlMITable.The type property of the dataset must be set to AzureSqlMITable. SimYes
esquemaschema Nome do esquema.Name of the schema. Não para fonte, Sim para o coletorNo for source, Yes for sink
tabelatable Nome da tabela/exibição.Name of the table/view. Não para fonte, Sim para o coletorNo for source, Yes for sink
tableNametableName Nome da tabela/exibição com esquema.Name of the table/view with schema. Essa propriedade é compatível com versões anteriores.This property is supported for backward compatibility. Para uma nova carga de trabalho, use schema e table.For new workload, use schema and table. Não para fonte, Sim para o coletorNo for source, Yes for sink

ExemploExample

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

Propriedades da atividade de cópiaCopy activity properties

Para obter uma lista completa das seções e propriedades disponíveis para uso para definir atividades, confia o artigo Pipelines.For a full list of sections and properties available for use to define activities, see the Pipelines article. Esta seção fornece uma lista das propriedades com suporte pela fonte e pelo coletor do SQL Instância Gerenciada.This section provides a list of properties supported by the SQL Managed Instance source and sink.

SQL Instância Gerenciada como uma fonteSQL Managed Instance as a source

Dica

Para carregar dados do SQL de MI com eficiência usando o particionamento de dados, saiba mais em cópia paralela do SQL mi.To load data from SQL MI efficiently by using data partitioning, learn more from Parallel copy from SQL MI.

Para copiar dados do SQL Instância Gerenciada, as propriedades a seguir têm suporte na seção origem da atividade de cópia:To copy data from SQL Managed Instance, the following properties are supported in the copy activity source section:

PropriedadeProperty DescriçãoDescription ObrigatórioRequired
typetype A propriedade Type da fonte da atividade de cópia deve ser definida como SqlMISource.The type property of the copy activity source must be set to SqlMISource. SimYes
sqlReaderQuerysqlReaderQuery Essa propriedade usa a consulta SQL personalizada para ler dados.This property uses the custom SQL query to read data. Um exemplo é select * from MyTable.An example is select * from MyTable. NãoNo
sqlReaderStoredProcedureNamesqlReaderStoredProcedureName Essa propriedade é o nome do procedimento armazenado que lê dados da tabela de origem.This property is the name of the stored procedure that reads data from the source table. A última instrução SQL deve ser uma instrução SELECT no procedimento armazenado.The last SQL statement must be a SELECT statement in the stored procedure. NãoNo
storedProcedureParametersstoredProcedureParameters Esses parâmetros são para o procedimento armazenado.These parameters are for the stored procedure.
Valores permitidos são pares de nome ou valor.Allowed values are name or value pairs. Os nomes e o uso de maiúsculas e minúsculas dos parâmetros devem corresponder aos nomes e o uso de maiúsculas e minúsculas dos parâmetros do procedimento armazenado.The names and casing of the parameters must match the names and casing of the stored procedure parameters.
NãoNo
isolationLevelisolationLevel Especifica o comportamento de bloqueio de transação para a origem do SQL.Specifies the transaction locking behavior for the SQL source. Os valores permitidos são: ReadCommitted, READUNCOMMITTED, REPEATABLEREAD, Serializable, snapshot.The allowed values are: ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, Snapshot. Se não for especificado, o nível de isolamento padrão do banco de dados será usado.If not specified, the database's default isolation level is used. Veja este documento para obter mais detalhes.Refer to this doc for more details. NãoNo
partitionOptionspartitionOptions Especifica as opções de particionamento de dados usadas para carregar dados do SQL MI.Specifies the data partitioning options used to load data from SQL MI.
Os valores permitidos são: None (padrão), PhysicalPartitionsOfTable e DynamicRange.Allowed values are: None (default), PhysicalPartitionsOfTable, and DynamicRange.
Quando uma opção de partição está habilitada (ou seja, não None ), o grau de paralelismo para carregar dados simultaneamente do SQL mi é controlado pela parallelCopies configuração na atividade de cópia.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.
NãoNo
partitionSettingspartitionSettings Especifique o grupo de configurações para o particionamento de dados.Specify the group of the settings for data partitioning.
Aplicar quando a opção de partição não estiver None .Apply when the partition option isn't None.
NãoNo
*Em partitionSettings : _*Under partitionSettings: _
partitionColumnNamepartitionColumnName Especifique o nome da coluna de origem _ em inteiro ou data/hora tipo* ( int , smallint , bigint , date , smalldatetime , datetime , datetime2 ou datetimeoffset ) que será usado pelo particionamento de intervalo para cópia paralela.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 não for especificado, o índice ou a chave primária da tabela será detectado automaticamente e usado como a coluna de partição.If not specified, the index or the primary key of the table is auto-detected and used as the partition column.
Aplicar quando a opção de partição for DynamicRange.Apply when the partition option is DynamicRange. Se você usar uma consulta para recuperar os dados de origem, conecte-se ?AdfDynamicRangePartitionCondition à cláusula WHERE.If you use a query to retrieve the source data, hook ?AdfDynamicRangePartitionCondition in the WHERE clause. Para obter um exemplo, consulte a seção cópia paralela do banco de dados SQL .For an example, see the Parallel copy from SQL database section.
NãoNo
partitionUpperBoundpartitionUpperBound O valor máximo da coluna de partição para divisão de intervalo de partição.The maximum value of the partition column for partition range splitting. Esse valor é usado para decidir o stride da partição, não para filtrar as linhas na tabela.This value is used to decide the partition stride, not for filtering the rows in table. Todas as linhas da tabela ou do resultado da consulta serão particionadas e copiadas.All rows in the table or query result will be partitioned and copied. Se não for especificado, a atividade de cópia detectará o valor automaticamente.If not specified, copy activity auto detect the value.
Aplicar quando a opção de partição for DynamicRange.Apply when the partition option is DynamicRange. Para obter um exemplo, consulte a seção cópia paralela do banco de dados SQL .For an example, see the Parallel copy from SQL database section.
NãoNo
partitionLowerBoundpartitionLowerBound O valor mínimo da coluna de partição para divisão de intervalo de partição.The minimum value of the partition column for partition range splitting. Esse valor é usado para decidir o stride da partição, não para filtrar as linhas na tabela.This value is used to decide the partition stride, not for filtering the rows in table. Todas as linhas da tabela ou do resultado da consulta serão particionadas e copiadas.All rows in the table or query result will be partitioned and copied. Se não for especificado, a atividade de cópia detectará o valor automaticamente.If not specified, copy activity auto detect the value.
Aplicar quando a opção de partição for DynamicRange.Apply when the partition option is DynamicRange. Para obter um exemplo, consulte a seção cópia paralela do banco de dados SQL .For an example, see the Parallel copy from SQL database section.
NãoNo

Observe os seguintes pontos:Note the following points:

  • Se sqlReaderQuery for especificado para SqlMISource, a atividade de cópia executará essa consulta na fonte de instância gerenciada do SQL para obter os dados.If sqlReaderQuery is specified for SqlMISource, the copy activity runs this query against the SQL Managed Instance source to get the data. Você também pode especificar um procedimento armazenado especificando o sqlReaderStoredProcedureName e o storedProcedureParameters se o procedimento armazenado usa parâmetros.You also can specify a stored procedure by specifying sqlReaderStoredProcedureName and storedProcedureParameters if the stored procedure takes parameters.
  • Ao usar o procedimento armazenado na origem para recuperar dados, observe que, se o procedimento armazenado for projetado como retornando um esquema diferente quando um valor de parâmetro diferente for passado, você poderá encontrar uma falha ou conferir um resultado inesperado ao importar o esquema da interface do usuário ou ao copiar dados para o SQL Database com a criação automática de tabela.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.

Exemplo: usar uma consulta 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>"
            }
        }
    }
]

Exemplo: usar um procedimento armazenadoExample: 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>"
            }
        }
    }
]

A definição do procedimento armazenadoThe 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

SQL Instância Gerenciada como um coletorSQL Managed Instance as a sink

Dica

Saiba mais sobre os comportamentos de gravação com suporte, as configurações e as práticas recomendadas da prática recomendada para carregar dados no SQL instância gerenciada.Learn more about the supported write behaviors, configurations, and best practices from Best practice for loading data into SQL Managed Instance.

Para copiar dados para o SQL Instância Gerenciada, as propriedades a seguir têm suporte na seção coletor de atividade de cópia:To copy data to SQL Managed Instance, the following properties are supported in the copy activity sink section:

PropriedadeProperty DescriçãoDescription ObrigatórioRequired
typetype A propriedade Type do coletor da atividade de cópia deve ser definida como SqlMISink.The type property of the copy activity sink must be set to SqlMISink. SimYes
preCopyScriptpreCopyScript Esta propriedade especifica uma consulta SQL para que a atividade de cópia seja executada antes de gravar dados no SQL Instância Gerenciada.This property specifies a SQL query for the copy activity to run before writing data into SQL Managed Instance. É chamado apenas uma vez por execução de cópia.It's invoked only once per copy run. Você pode usar essa propriedade para limpar os dados previamente carregados.You can use this property to clean up preloaded data. NãoNo
tableOptiontableOption Especifica se a tabela do coletor deve ser criada automaticamente se não existir com base no esquema de origem.Specifies whether to automatically create the sink table if not exists based on the source schema. Não há suporte para a criação de tabela automática quando o coletor especifica o procedimento armazenado.Auto table creation is not supported when sink specifies stored procedure. Os valores permitidos são none (padrão) e autoCreate.Allowed values are: none (default), autoCreate. NãoNo
sqlWriterStoredProcedureNamesqlWriterStoredProcedureName O nome do procedimento armazenado que define como aplicar dados de origem em uma tabela de destino.The name of the stored procedure that defines how to apply source data into a target table.
Este procedimento armazenado é chamado por lote.This stored procedure is invoked per batch. Para operações que são executadas apenas uma vez e não têm nada a ver com os dados de origem, por exemplo, excluir ou truncar, use a preCopyScript propriedade.For operations that run only once and have nothing to do with source data, for example, delete or truncate, use the preCopyScript property.
Consulte o exemplo de invocar um procedimento armazenado de um coletor SQL.See example from Invoke a stored procedure from a SQL sink.
NãoNo
storedProcedureTableTypeParameterNamestoredProcedureTableTypeParameterName O nome do parâmetro do tipo de tabela especificado no procedimento armazenado.The parameter name of the table type specified in the stored procedure. NãoNo
sqlWriterTableTypesqlWriterTableType O nome do tipo de tabela a ser usado no procedimento armazenado.The table type name to be used in the stored procedure. A atividade de cópia disponibiliza aqueles dados sendo movidos em uma tabela temporária com esse tipo de tabela.The copy activity makes the data being moved available in a temp table with this table type. O código de procedimento armazenado pode mesclar os dados que estão sendo copiados com os dados existentes.Stored procedure code can then merge the data that's being copied with existing data. NãoNo
storedProcedureParametersstoredProcedureParameters Parâmetros para o procedimento armazenado.Parameters for the stored procedure.
Valores permitidos são pares de nome e valor.Allowed values are name and value pairs. Nomes e uso de maiúsculas e minúsculas de parâmetros devem corresponder aos nomes e o uso de maiúsculas e minúsculas dos parâmetros do procedimento armazenado.Names and casing of parameters must match the names and casing of the stored procedure parameters.
NãoNo
writeBatchSizewriteBatchSize Número de linhas a serem inseridas na tabela SQL por lote.Number of rows to insert into the SQL table per batch.
Os valores permitidos são inteiros para o número de linhas.Allowed values are integers for the number of rows. Por padrão, Azure Data Factory determina dinamicamente o tamanho do lote apropriado com base no tamanho da linha.By default, Azure Data Factory dynamically determines the appropriate batch size based on the row size.
NãoNo
writeBatchTimeoutwriteBatchTimeout Essa propriedade especifica o tempo de espera para a operação de inserção em lotes a ser concluída antes de atingir o tempo limite.This property specifies the wait time for the batch insert operation to complete before it times out.
Os valores permitidos são para o TimeSpan.Allowed values are for the timespan. Um exemplo é "00:30:00", que é de 30 minutos.An example is "00:30:00," which is 30 minutes.
NãoNo

Exemplo 1: acrescentar dadosExample 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
            }
        }
    }
]

Exemplo 2: invocar um procedimento armazenado durante a cópiaExample 2: Invoke a stored procedure during copy

Saiba mais detalhes em invocar um procedimento armazenado de um coletor de Mi do SQL.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" }
                }
            }
        }
    }
]

Cópia paralela do SQL MIParallel copy from SQL MI

O conector do Azure SQL Instância Gerenciada na atividade de cópia fornece particionamento de dados interno para copiar dados em paralelo.The Azure SQL Managed Instance connector in copy activity provides built-in data partitioning to copy data in parallel. Você pode encontrar opções de particionamento de dados na guia origem da atividade de cópia.You can find data partitioning options on the Source tab of the copy activity.

Captura de tela das opções de partição

Quando você habilita a cópia particionada, a atividade de cópia executa consultas paralelas em sua fonte de MI do SQL para carregar dados por partições.When you enable partitioned copy, copy activity runs parallel queries against your SQL MI source to load data by partitions. O grau paralelo é controlado pela configuração do parallelCopies na atividade de cópia.The parallel degree is controlled by the parallelCopies setting on the copy activity. Por exemplo, se você definir parallelCopies como quatro, data Factory gera e executa quatro consultas de maneira simultânea com base na opção de partição e nas configurações especificadas, e cada consulta recupera uma parte dos dados do Mi do SQL.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.

É recomendável habilitar a cópia paralela com o particionamento de dados, especialmente quando você carrega grandes quantidades de dados de sua MI SQL.You are suggested to enable parallel copy with data partitioning especially when you load large amount of data from your SQL MI. Veja a seguir as configurações sugeridas para cenários diferentes.The following are suggested configurations for different scenarios. Ao copiar dados para o armazenamento de dados baseado em arquivo, é recomendável gravar em uma pasta como vários arquivos (apenas especifique o nome da pasta). nesse caso, o desempenho é melhor do que gravar em um único arquivo.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.

CenárioScenario Configurações sugeridasSuggested settings
Carga total de uma tabela grande, com partições físicas.Full load from large table, with physical partitions. Opção de partição: partições físicas da tabela.Partition option: Physical partitions of table.

Durante a execução, Data Factory detecta automaticamente as partições físicas e copia os dados por partições.During execution, Data Factory automatically detects the physical partitions, and copies data by partitions.

Para verificar se a tabela tem uma partição física ou não, você pode fazer referência a essa consulta.To check if your table has physical partition or not, you can refer to this query.
Carga total de uma tabela grande, sem partições físicas, enquanto com uma coluna de número inteiro ou de data e hora para o particionamento de dados.Full load from large table, without physical partitions, while with an integer or datetime column for data partitioning. Opções de partição: partição de intervalo dinâmico.Partition options: Dynamic range partition.
Coluna de partição (opcional): Especifique a coluna usada para particionar dados.Partition column (optional): Specify the column used to partition data. Se não for especificado, a coluna índice ou chave primária será usada.If not specified, the index or primary key column is used.
Limite superior da partição e limite inferior da partição (opcional): especifique se você deseja determinar o stride da partição.Partition upper bound and partition lower bound (optional): Specify if you want to determine the partition stride. Isso não é para filtrar as linhas na tabela, todas as linhas da tabela serão particionadas e copiadas.This is not for filtering the rows in table, all rows in the table will be partitioned and copied. Se não for especificado, a atividade de cópia detectará automaticamente os valores.If not specified, copy activity auto detect the values.

Por exemplo, se a coluna de partição "ID" tiver valores varia de 1 a 100, e você definir o limite inferior como 20 e o limite superior como 80, com cópia paralela como 4, Data Factory recuperará dados por 4 partições-IDs no intervalo <= 20, [21, 50], [51, 80] e >= 81, respectivamente.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.
Carregue uma grande quantidade de dados usando uma consulta personalizada, sem partições físicas, enquanto com uma coluna de número inteiro ou data/hora para o particionamento de dados.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. Opções de partição: partição de intervalo dinâmico.Partition options: Dynamic range partition.
Consulta: SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>.Query: SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>.
Coluna de partição: especifique a coluna usada para particionar dados.Partition column: Specify the column used to partition data.
Limite superior da partição e limite inferior da partição (opcional): especifique se você deseja determinar o stride da partição.Partition upper bound and partition lower bound (optional): Specify if you want to determine the partition stride. Isso não é para filtrar as linhas na tabela, todas as linhas no resultado da consulta serão particionadas e copiadas.This is not for filtering the rows in table, all rows in the query result will be partitioned and copied. Se não for especificado, a atividade de cópia detectará o valor automaticamente.If not specified, copy activity auto detect the value.

Durante a execução, Data Factory substitui ?AdfRangePartitionColumnName pelo nome real da coluna e os intervalos de valores de cada partição e envia para o SQL mi.During execution, Data Factory replaces ?AdfRangePartitionColumnName with the actual column name and value ranges for each partition, and sends to SQL MI.
Por exemplo, se a coluna de partição "ID" tiver valores varia de 1 a 100, e você definir o limite inferior como 20 e o limite superior como 80, com cópia paralela como 4, Data Factory recuperará dados por 4 partições-IDs no intervalo <= 20, [21, 50], [51, 80] e >= 81, respectivamente.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.

Aqui estão mais exemplos de consultas para cenários diferentes:Here are more sample queries for different scenarios:
1. consulte a tabela inteira:1. Query the whole table:
SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition
2. consulta de uma tabela com seleção de coluna e filtros de cláusula WHERE adicionais: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. consultar com subconsultas:3. Query with subqueries:
SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>
4. consulta com partição na subconsulta:4. Query with partition in subquery:
SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition) AS T

Práticas recomendadas para carregar dados com a opção de partição:Best practices to load data with partition option:

  1. Escolha coluna distinta como coluna de partição (como chave primária ou chave exclusiva) para evitar a distorção de dados.Choose distinctive column as partition column (like primary key or unique key) to avoid data skew.
  2. Se a tabela tiver uma partição interna, use a opção de partição "partições físicas da tabela" para obter um melhor desempenho.If the table has built-in partition, use partition option "Physical partitions of table" to get better performance.
  3. Se você usar Azure Integration Runtime para copiar dados, poderá definir "unidades de integração de dados (DIU)" maiores (>4) para utilizar mais recursos de computação.If you use Azure Integration Runtime to copy data, you can set larger "Data Integration Units (DIU)" (>4) to utilize more computing resource. Verifique os cenários aplicáveis ali.Check the applicable scenarios there.
  4. O "grau de paralelismo de cópia" controla os números de partição, definindo esse número muito grande em algum momento que afeta o desempenho, recomenda definir esse número como (DIU ou número de nós de ir de hospedagem interna) * (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).

Exemplo: carregamento completo de uma tabela grande com partições físicasExample: full load from large table with physical partitions

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

Exemplo: consulta com a partição do intervalo dinâmicoExample: 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>"
    }
}

Exemplo de consulta para verificar a partição físicaSample 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 a tabela tiver uma partição física, você verá "HasPartition" como "Sim", como a seguir.If the table has physical partition, you would see "HasPartition" as "yes" like the following.

Resultado da consulta SQL

Prática recomendada para carregar dados no SQL Instância GerenciadaBest practice for loading data into SQL Managed Instance

Quando você copia dados para o SQL Instância Gerenciada, pode ser necessário um comportamento de gravação diferente:When you copy data into SQL Managed Instance, you might require different write behavior:

  • Append: meus dados de origem têm apenas registros novos.Append: My source data has only new records.
  • Upsert: meus dados de origem têm inserções e atualizações.Upsert: My source data has both inserts and updates.
  • Substituir: Eu quero recarregar toda a tabela de dimensões a cada vez.Overwrite: I want to reload the entire dimension table each time.
  • Gravar com lógica personalizada: preciso de processamento extra antes da inserção final na tabela de destino.Write with custom logic: I need extra processing before the final insertion into the destination table.

Consulte as respectivas seções sobre como configurar o em Azure Data Factory e as práticas recomendadas.See the respective sections for how to configure in Azure Data Factory and best practices.

Acrescentar dadosAppend data

Acrescentar dados é o comportamento padrão do conector do coletor de Instância Gerenciada do SQL.Appending data is the default behavior of the SQL Managed Instance sink connector. Azure Data Factory faz uma inserção em massa para gravar em sua tabela com eficiência.Azure Data Factory does a bulk insert to write to your table efficiently. Você pode configurar a origem e o coletor de acordo com a atividade de cópia.You can configure the source and sink accordingly in the copy activity.

Upsert dataUpsert data

Opção 1: Quando você tem uma grande quantidade de dados a serem copiados, você pode carregar em massa todos os registros em uma tabela de preparo usando a atividade de cópia e, em seguida, executar uma atividade de procedimento armazenado para aplicar uma instrução Merge ou Insert/Update em uma única imagem.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.

Atualmente, a atividade de cópia não dá suporte nativo ao carregamento de dados em uma tabela temporária de banco de dado.Copy activity currently doesn't natively support loading data into a database temporary table. Há uma maneira avançada de configurá-lo com uma combinação de várias atividades, consulte otimizar cenários de Upsert em massa do banco de dados SQL.There is an advanced way to set it up with a combination of multiple activities, refer to Optimize SQL Database Bulk Upsert scenarios. Abaixo mostra um exemplo de como usar uma tabela permanente como preparo.Below shows a sample of using a permanent table as staging.

Por exemplo, em Azure Data Factory, você pode criar um pipeline com uma atividade de cópia encadeada com uma atividade de procedimento armazenado.As an example, in Azure Data Factory, you can create a pipeline with a Copy activity chained with a Stored Procedure activity. O primeiro copia os dados do seu repositório de origem em uma tabela de preparo do SQL Instância Gerenciada do Azure, por exemplo, UpsertStagingTable, como o nome da tabela no conjunto de dados.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. Em seguida, o último invoca um procedimento armazenado para mesclar dados de origem da tabela de preparo na tabela de destino e limpar a tabela de preparo.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

No banco de dados, defina um procedimento armazenado com lógica de MESCLAgem, como o exemplo a seguir, que é apontado da atividade de procedimento armazenado anterior.In your database, define a stored procedure with MERGE logic, like the following example, which is pointed to from the previous stored procedure activity. Suponha que o destino seja a tabela de marketing com três colunas: ProfileId, estado e categoria.Assume that the target is the Marketing table with three columns: ProfileID, State, and Category. Faça o Upsert com base na coluna 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

Opção 2: Você pode optar por invocar um procedimento armazenado na atividade de cópia.Option 2: You can choose to invoke a stored procedure within the copy activity. Essa abordagem executa cada lote (conforme governado pela writeBatchSize Propriedade) na tabela de origem em vez de usar BULK INSERT como a abordagem padrão na atividade de cópia.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.

Substituir a tabela inteiraOverwrite the entire table

Você pode configurar a propriedade preCopyScript em um coletor de atividade de cópia.You can configure the preCopyScript property in a copy activity sink. Nesse caso, para cada atividade de cópia executada, Azure Data Factory executa o script primeiro.In this case, for each copy activity that runs, Azure Data Factory runs the script first. Em seguida, ele executa a cópia para inserir os dados.Then it runs the copy to insert the data. Por exemplo, para substituir a tabela inteira com os dados mais recentes, especifique um script para primeiro excluir todos os registros antes de carregar em massa os novos dados da origem.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.

Gravar dados com lógica personalizadaWrite data with custom logic

As etapas para gravar dados com lógica personalizada são semelhantes às descritas na seção de dados Upsert .The steps to write data with custom logic are similar to those described in the Upsert data section. Quando você precisa aplicar processamento extra antes da inserção final dos dados de origem na tabela de destino, você pode carregar em uma tabela de preparo e, em seguida, invocar a atividade de procedimento armazenado, ou invocar um procedimento armazenado no coletor de atividade de cópia para aplicar dados.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.

Invocar um procedimento armazenado de um coletor SQLInvoke a stored procedure from a SQL sink

Ao copiar dados para o SQL Instância Gerenciada, você também pode configurar e invocar um procedimento armazenado especificado pelo usuário com parâmetros adicionais em cada lote da tabela de origem.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. O recurso de procedimento armazenado aproveita os parâmetros com valor de tabela.The stored procedure feature takes advantage of table-valued parameters.

Você pode usar um procedimento armazenado quando os mecanismos internos de cópia não atendem à finalidade.You can use a stored procedure when built-in copy mechanisms don't serve the purpose. Um exemplo é quando você deseja aplicar processamento extra antes da inserção final dos dados de origem na tabela de destino.An example is when you want to apply extra processing before the final insertion of source data into the destination table. Alguns exemplos de processamento extra são quando você deseja mesclar colunas, Pesquisar valores adicionais e inserir em mais de uma tabela.Some extra processing examples are when you want to merge columns, look up additional values, and insert into more than one table.

O exemplo a seguir mostra como usar um procedimento armazenado para fazer um upsert em uma tabela no banco de dados do SQL Server.The following sample shows how to use a stored procedure to do an upsert into a table in the SQL Server database. Suponha que os dados de entrada e a tabela de marketing do coletor tenham três colunas: ProfileId, estado e categoria.Assume that the input data and the sink Marketing table each have three columns: ProfileID, State, and Category. Faça o Upsert com base na coluna ProfileId e aplique-o somente para uma categoria específica chamada "produtoA".Do the upsert based on the ProfileID column, and only apply it for a specific category called "ProductA".

  1. No banco de dados, defina o tipo de tabela com o mesmo nome que sqlWriterTableType.In your database, define the table type with the same name as sqlWriterTableType. O esquema do tipo de tabela é o mesmo que o esquema retornado pelos dados de entrada.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. No banco de dados, defina o procedimento armazenado com o mesmo nome que sqlWriterStoredProcedureName.In your database, define the stored procedure with the same name as sqlWriterStoredProcedureName. Ele manipula os dados de entrada da sua origem especificada e mescla na tabela de saída.It handles input data from your specified source and merges into the output table. O nome do parâmetro do tipo de tabela no procedimento armazenado é o mesmo que TableName definido no DataSet.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. Em Azure Data Factory, defina a seção coletor de Mi do SQL na atividade de cópia da seguinte maneira: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"
            }
        }
    }
    

Propriedades do fluxo de dados de mapeamentoMapping data flow properties

Ao transformar dados no fluxo de dados de mapeamento, você pode ler e gravar em tabelas do Azure SQL Instância Gerenciada.When transforming data in mapping data flow, you can read and write to tables from Azure SQL Managed Instance. Para obter mais informações, confira transformação de origem e transformação do coletor nos fluxos de dados de mapeamento.For more information, see the source transformation and sink transformation in mapping data flows.

Observação

O conector do SQL Instância Gerenciada do Azure no fluxo de dados de mapeamento está disponível atualmente como visualização pública.Azure SQL Managed Instance connector in Mapping Data Flow is currently available as public preview. Você pode se conectar ao ponto de extremidade público do SQL Instância Gerenciada, mas não ao ponto de extremidade privado ainda.You can connect to SQL Managed Instance public endpoint but not private endpoint yet.

Transformação de origemSource transformation

A tabela abaixo lista as propriedades com suporte pela fonte de Instância Gerenciada do SQL do Azure.The below table lists the properties supported by Azure SQL Managed Instance source. Você pode editar essas propriedades na guia Opções de origem .You can edit these properties in the Source options tab.

NomeName DescriçãoDescription NecessáriaRequired Valores permitidosAllowed values Propriedade de script de fluxo de dadosData flow script property
TabelaTable Se você selecionar tabela como entrada, o fluxo de dados buscará todos os dados da tabela especificada no conjunto.If you select Table as input, data flow fetches all the data from the table specified in the dataset. NãoNo - -
ConsultaQuery Se você selecionar consulta como entrada, especifique uma consulta SQL para buscar dados da origem, o que substitui qualquer tabela que você especificar no DataSet.If you select Query as input, specify a SQL query to fetch data from source, which overrides any table you specify in dataset. O uso de consultas é uma ótima maneira de reduzir linhas para teste ou pesquisas.Using queries is a great way to reduce rows for testing or lookups.

Não há suporte para a cláusula order by , mas você pode definir uma instrução SELECT FROM completa.Order By clause is not supported, but you can set a full SELECT FROM statement. Também pode usar funções de tabela definidas pelo usuário.You can also use user-defined table functions. Select * de udfGetData () é um UDF no SQL que retorna uma tabela que você pode usar no fluxo de dados.select * from udfGetData() is a UDF in SQL that returns a table that you can use in data flow.
Exemplo de consulta: Select * from MyTable where customerId > 1000 and customerId < 2000Query example: Select * from MyTable where customerId > 1000 and customerId < 2000
NãoNo StringString Consultaquery
Tamanho do loteBatch size Especifique um tamanho de lote para dividir dados grandes em leituras.Specify a batch size to chunk large data into reads. NãoNo IntegerInteger batchSizebatchSize
Nível de IsolamentoIsolation Level Escolha um dos seguintes níveis de isolamento:Choose one of the following isolation levels:
-Leitura confirmada- Read Committed
-Leitura não confirmada (padrão)- Read Uncommitted (default)
-Leitura repetida- Repeatable Read
-Serializável- Serializable
-Nenhum (ignorar nível de isolamento)- None (ignore isolation level)
NãoNo READ_COMMITTEDREAD_COMMITTED
READ_UNCOMMITTEDREAD_UNCOMMITTED
REPEATABLE_READREPEATABLE_READ
SERIALIZABLESERIALIZABLE
NONE
NONE
isolationLevelisolationLevel

Exemplo de script de origem do Azure SQL Instância GerenciadaAzure SQL Managed Instance source script example

Quando você usa o Azure SQL Instância Gerenciada como tipo de fonte, o script de fluxo de dados associado é: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

Transformação de coletorSink transformation

A tabela abaixo lista as propriedades com suporte pelo coletor de Instância Gerenciada do SQL do Azure.The below table lists the properties supported by Azure SQL Managed Instance sink. Você pode editar essas propriedades na guia Opções do coletor .You can edit these properties in the Sink options tab.

NomeName DescriçãoDescription NecessáriaRequired Valores permitidosAllowed values Propriedade de script de fluxo de dadosData flow script property
Método UpdateUpdate method Especifique quais operações são permitidas no destino do banco de dados.Specify what operations are allowed on your database destination. O padrão é permitir apenas inserções.The default is to only allow inserts.
Para atualizar, upsertr ou excluir linhas, uma transformação ALTER Row é necessária para marcar linhas para essas ações.To update, upsert, or delete rows, an Alter row transformation is required to tag rows for those actions.
SimYes true ou falsetrue or false pode ser excluídodeletable
Insertableinsertable
atualizávelupdateable
upsertableupsertable
Colunas de chavesKey columns Para atualizações, upserts e exclusões, coluna (s) de chave devem ser definidas para determinar qual linha alterar.For updates, upserts and deletes, key column(s) must be set to determine which row to alter.
O nome da coluna que você escolhe como a chave será usado como parte da atualização subsequente, Upsert, Delete.The column name that you pick as the key will be used as part of the subsequent update, upsert, delete. Portanto, você deve escolher uma coluna que exista no mapeamento do coletor.Therefore, you must pick a column that exists in the Sink mapping.
NãoNo ArrayArray chaveskeys
Ignorar colunas de chave de gravaçãoSkip writing key columns Se você não quiser gravar o valor na coluna de chave, selecione "ignorar a gravação de colunas de chave".If you wish to not write the value to the key column, select "Skip writing key columns". NãoNo true ou falsetrue or false skipKeyWritesskipKeyWrites
Ação tabelaTable action Determina se deve-se recriar ou remover todas as linhas da tabela de destino antes da gravação.Determines whether to recreate or remove all rows from the destination table prior to writing.
- Nenhum: nenhuma ação será feita para a tabela.- None: No action will be done to the table.
- Recriar: a tabela será descartada e recriada.- Recreate: The table will get dropped and recreated. Necessário ao criar uma tabela dinamicamente.Required if creating a new table dynamically.
- Truncar: todas as linhas da tabela de destino serão removidas.- Truncate: All rows from the target table will get removed.
NãoNo true ou falsetrue or false recriarrecreate
truncatetruncate
Tamanho do loteBatch size Especifique quantas linhas estão sendo gravadas em cada lote.Specify how many rows are being written in each batch. Tamanhos de lote maiores aprimoram a compactação e a otimização de memória, mas geram risco de exceções de memória insuficiente ao armazenar dados em cache.Larger batch sizes improve compression and memory optimization, but risk out of memory exceptions when caching data. NãoNo IntegerInteger batchSizebatchSize
Scripts SQL anteriores e posterioresPre and Post SQL scripts Especifique scripts SQL de várias linhas que serão executados antes (pré-processamento) e após (pós-processamento) os dados são gravados no banco de dado do coletor.Specify multi-line SQL scripts that will execute before (pre-processing) and after (post-processing) data is written to your Sink database. NãoNo StringString preSQLspreSQLs
postSQLspostSQLs

Exemplo de script de coletor de Instância Gerenciada do SQL do AzureAzure SQL Managed Instance sink script example

Quando você usa o SQL do Azure Instância Gerenciada como tipo de coletor, o script de fluxo de dados associado é: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

Pesquisar propriedades de atividadeLookup activity properties

Para saber detalhes sobre as propriedades, verifique Atividade de pesquisa.To learn details about the properties, check Lookup activity.

Propriedades de atividade GetMetadataGetMetadata activity properties

Para saber detalhes sobre as propriedades, verifique Atividade GetMetadataTo learn details about the properties, check GetMetadata activity

Mapeamento de tipo de dados para SQL Instância GerenciadaData type mapping for SQL Managed Instance

Quando os dados são copiados para e do SQL Instância Gerenciada usando a atividade de cópia, os seguintes mapeamentos são usados de tipos de dados do SQL Instância Gerenciada para Azure Data Factory tipos de dados provisórios.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. Para saber mais sobre como a atividade de cópia mapeia do tipo de dados e esquema de origem para o coletor, confira Mapeamentos de tipo de dados e esquema.To learn how the copy activity maps from the source schema and data type to the sink, see Schema and data type mappings.

Tipo de dados do SQL Instância GerenciadaSQL Managed Instance data type Tipo de dados provisório do Azure Data FactoryAzure Data 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 ObjetoObject
texttext String, Char[]String, Char[]
timetime TimeSpanTimeSpan
timestamptimestamp Byte[]Byte[]
TINYINTtinyint Int16Int16
UNIQUEIDENTIFIERuniqueidentifier GuidGuid
varbinaryvarbinary Byte[]Byte[]
varcharvarchar String, Char[]String, Char[]
Xmlxml StringString

Observação

Para tipos de dados que são mapeados para o tipo provisório decimal, a atividade de cópia atualmente dá suporte à precisão de até 28.For data types that map to the Decimal interim type, currently Copy activity supports precision up to 28. Se você tiver dados que exijam precisão maior que 28, considere converter para uma cadeia de caracteres em uma consulta SQL.If you have data that requires precision larger than 28, consider converting to a string in a SQL query.

Como usar o Always EncryptedUsing Always Encrypted

Ao copiar dados de/para o Azure SQL Instância Gerenciada com Always Encrypted, use o conector odbc genérico e SQL Server driver odbc por meio de Integration Runtime hospedados internamente.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. Este conector do Azure SQL Instância Gerenciada não oferece suporte a Always Encrypted agora.This Azure SQL Managed Instance connector does not support Always Encrypted now.

Mais especificamente:More specifically:

  1. Configure um Integration Runtime auto-hospedado se você não tiver um.Set up a Self-hosted Integration Runtime if you don't have one. Confira o artigo de Integration Runtime auto-hospedado para obter detalhes.See Self-hosted Integration Runtime article for details.

  2. Baixe o driver ODBC de 64 bits para SQL Server aquie instale na máquina Integration Runtime.Download the 64-bit ODBC driver for SQL Server from here, and install on the Integration Runtime machine. Saiba mais sobre como esse driver funciona usando Always Encrypted com o driver ODBC para SQL Server.Learn more about how this driver works from Using Always Encrypted with the ODBC Driver for SQL Server.

  3. Crie um serviço vinculado com o tipo ODBC para se conectar ao banco de dados SQL, consulte os exemplos a seguir:Create linked service with ODBC type to connect to your SQL database, refer to the following samples:

    • Para usar a autenticação do SQL: Especifique a cadeia de conexão ODBC como abaixo e selecione Autenticação básica para definir o nome de usuário e a senha.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>
      
    • Para usar Data Factory autenticação de identidade gerenciada:To use Data Factory Managed Identity authentication:

      1. Siga os mesmos pré-requisitos para criar um usuário de banco de dados para a identidade gerenciada e conceder a função apropriada em seu banco de dados.Follow the same prerequisites to create database user for the managed identity and grant the proper role in your database.
      2. Em serviço vinculado, especifique a cadeia de conexão ODBC, conforme mostrado abaixo, e selecione Autenticação anônima como a própria cadeia de conexão indica 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. Crie DataSet e copie a atividade com o tipo ODBC adequadamente.Create dataset and copy activity with ODBC type accordingly. Saiba mais no artigo conector ODBC .Learn more from ODBC connector article.

Próximas etapasNext steps

Para obter uma lista de armazenamentos de dados com suporte como fontes e coletores pela atividade de cópia no Azure Data Factory, consulte armazenamentos de dados com suporte.For a list of data stores supported as sources and sinks by the copy activity in Azure Data Factory, see Supported data stores.