Copie e transforme dados no Base de Dados SQL do Azure usando Azure Data Factory ou Azure Synapse Analytics

APLICA-SE A: Azure Data Factory Azure Synapse Analytics

Este artigo descreve como usar o Copy Activity nos pipelines do Azure Data Factory ou Azure Synapse para copiar dados do e para o Base de Dados SQL do Azure, e usar o Fluxo de Dados para transformar dados no Base de Dados SQL do Azure. Para saber mais, leia o artigo introdutório para Azure Data Factory ou Azure Synapse Analytics.

Capacidades suportadas

Este conector Base de Dados SQL do Azure é suportado para as seguintes capacidades:

(1) Runtime de integração Azure (2) Runtime de integração auto-hospedado

Para atividade Copy, este conector Base de Dados SQL do Azure suporta estas funções:

• Cópia de dados utilizando autenticação SQL e autenticação de token da aplicação Microsoft Entra com um principal de serviço ou identidades geridas para recursos do Azure.
• Como origem, recuperando dados usando uma consulta SQL ou um procedimento armazenado. Também pode optar por copiar em paralelo de uma fonte Base de Dados SQL do Azure, veja a secção Cópia paralela da base de dados SQL para mais detalhes.
• Como um coletor, criando automaticamente a tabela de destino se não existir com base no esquema de origem; anexar dados a uma tabela ou invocar um procedimento armazenado com lógica personalizada durante a cópia.

Se usares Base de Dados SQL do Azure serverless tier, nota que quando o servidor está pausado, a atividade falha em vez de esperar que a retomada automática esteja pronta. Você pode adicionar repetição de atividade ou encadear atividades adicionais para garantir que o servidor esteja ativo após a execução real.

Introdução

Para executar a atividade de cópia com um pipeline, você pode usar uma das seguintes ferramentas ou SDKs:

• Ferramenta Copiar dados
• Portal do Azure
• .NET SDK
• Python SDK
• Azure PowerShell
• API REST
• Modelo do Azure Resource Manager

Crie um serviço ligado ao Base de Dados SQL do Azure usando UI

Use os seguintes passos para criar um serviço ligado ao Base de Dados SQL do Azure na interface do portal Azure.

1. Navegue até ao separador Gerir no seu espaço de trabalho Azure Data Factory ou Synapse e selecione Serviços Ligados, depois clique em Novo:

   • Azure Data Factory
   • Azure Synapse

   

2. Procure por SQL e selecione o conector Base de Dados SQL do Azure.

   

3. Configure os detalhes do serviço, teste a conexão e crie o novo serviço vinculado.

   

Detalhes de configuração do conector

As secções seguintes fornecem detalhes sobre as propriedades utilizadas para definir entidades do pipeline do Azure Data Factory ou do Synapse, específicas de um conector do Base de Dados SQL do Azure.

Propriedades do serviço vinculado

A versão do conector Base de Dados SQL do Azure Recommended suporta TLS 1.3. Consulte esta secção para atualizar a versão do seu conector de Base de Dados SQL do Azure a partir do Legacy. Para obter os detalhes da propriedade, consulte as seções correspondentes.

• Versão recomendada
• Versão legacy

Versão recomendada

Estas propriedades genéricas são suportadas para um serviço Base de Dados SQL do Azure ligado quando se aplica a versão Recomendado:

Para obter propriedades de conexão adicionais, consulte a tabela abaixo:

Autenticação do SQL

Para usar a autenticação SQL, além das propriedades genéricas descritas na seção anterior, especifique as seguintes propriedades:

Exemplo: usando a autenticação SQL

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Exemplo: palavra-passe em Azure Key Vault

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "<Azure Key Vault linked service name>",
                    "type": "LinkedServiceReference"
                },
                "secretName": "<secretName>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Exemplo: Usar sempre criptografado

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            },
            "alwaysEncryptedSettings": {
                "alwaysEncryptedAkvAuthType": "ServicePrincipal",
                "servicePrincipalId": "<service principal id>",
                "servicePrincipalKey": {
                    "type": "SecureString",
                    "value": "<service principal key>"
                }
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticação do principal de serviço

Para usar a autenticação do principal de serviço, além das propriedades genéricas descritas na seção anterior, especifique as seguintes propriedades:

Você também precisa seguir os passos abaixo:

1. Criar uma aplicação Microsoft Entra a partir do portal Azure. Anote o nome do aplicativo e os seguintes valores que definem o serviço vinculado:

   • ID da aplicação
   • Chave de aplicação
   • ID de Locatário

2. Provisione um administrador de Microsoft Entra para o seu servidor no portal Azure, caso ainda não o tenha feito. O administrador do Microsoft Entra deve ser um utilizador do Microsoft Entra ou do grupo Microsoft Entra, mas não pode ser um principal de serviço. Esta etapa é feita para que, na etapa seguinte, possa usar uma identidade Microsoft Entra para criar um utilizador de base de dados contido para o principal do serviço.

3. Crie utilizadores de banco de dados contidos para o principal de serviço. Conecta-te à base de dados da qual ou para a qual quiseres copiar os dados usando ferramentas como o SQL Server Management Studio, com uma identidade Microsoft Entra que tenha pelo menos a permissão ALTERAR QUALQUER UTILIZADOR. Execute o seguinte T-SQL:

   CREATE USER [your application name] FROM EXTERNAL PROVIDER;
   

4. Conceda as permissões necessárias à entidade de serviço, como normalmente faz para utilizadores SQL ou outros. Execute o código a seguir. Para mais opções, consulte este documento.

   ALTER ROLE [role name] ADD MEMBER [your application name];
   

5. Configure um serviço ligado ao Base de Dados SQL do Azure num espaço de trabalho Azure Data Factory ou Synapse.

Exemplo de serviço vinculado que usa a autenticação da entidade de serviço

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "hostNameInCertificate": "<host name>",
            "authenticationType": "ServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalCredential": {
                "type": "SecureString",
                "value": "<application key>"
            },
            "tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticação de identidade gerenciada atribuída pelo sistema

Uma fábrica de dados ou um espaço de trabalho Synapse pode ser associado a uma identidade gerida atribuída pelo sistema para recursos do Azure que representa o serviço ao autenticar com outros recursos no Azure. Pode usar esta identidade gerida para autenticação do Base de Dados SQL do Azure. A fábrica designada ou o espaço de trabalho Synapse pode acessar e copiar dados de ou para seu banco de dados usando essa identidade.

Para usar a autenticação de identidade gerenciada atribuída pelo sistema, especifique as propriedades genéricas descritas na seção anterior e siga estas etapas.

1. Provisione um administrador de Microsoft Entra para o seu servidor no portal Azure, caso ainda não o tenha feito. O administrador da Microsoft Entra pode ser um utilizador da Microsoft Entra ou um grupo da Microsoft Entra. Se você conceder ao grupo com identidade gerenciada uma função de administrador, ignore as etapas 3 e 4. O administrador tem acesso total à base de dados.

2. Crie usuários de banco de dados contidos para a identidade gerenciada. Conecta-te à base de dados da qual ou para a qual quiseres copiar os dados usando ferramentas como o SQL Server Management Studio, com uma identidade Microsoft Entra que tenha pelo menos a permissão ALTERAR QUALQUER UTILIZADOR. Execute o seguinte T-SQL:

   CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
   

3. Conceda à identidade gerida as permissões necessárias, como normalmente faz para utilizadores SQL e outros. Execute o código a seguir. Para mais opções, consulte este documento.

   ALTER ROLE [role name] ADD MEMBER [your_resource_name];
   

4. Configure um serviço ligado ao Base de Dados SQL do Azure.

Exemplo

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SystemAssignedManagedIdentity"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticação de identidade gerenciada atribuída pelo usuário

Uma fábrica de dados ou um espaço de trabalho Synapse pode ser associado a identidades geridas atribuídas pelo utilizador que representam o serviço ao autenticar-se com outros recursos no Azure. Pode usar esta identidade gerida para autenticação do Base de Dados SQL do Azure. A fábrica designada ou o espaço de trabalho Synapse pode acessar e copiar dados de ou para seu banco de dados usando essa identidade.

Para usar a autenticação de identidade gerenciada atribuída pelo usuário, além das propriedades genéricas descritas na seção anterior, especifique as seguintes propriedades:

Você também precisa seguir os passos abaixo:

1. Provisione um administrador de Microsoft Entra para o seu servidor no portal Azure, caso ainda não o tenha feito. O administrador da Microsoft Entra pode ser um utilizador da Microsoft Entra ou um grupo da Microsoft Entra. Se você conceder ao grupo com identidade gerenciada atribuída pelo usuário uma função de administrador, ignore as etapas 3. O administrador tem acesso total à base de dados.

2. Crie usuários de banco de dados contidos para a identidade gerenciada atribuída pelo usuário. Conecta-te à base de dados da qual ou para a qual quiseres copiar os dados usando ferramentas como o SQL Server Management Studio, com uma identidade Microsoft Entra que tenha pelo menos a permissão ALTERAR QUALQUER UTILIZADOR. Execute o seguinte T-SQL:

   CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
   

3. Crie uma ou várias identidades gerenciadas atribuídas pelo usuário e conceda à identidade gerenciada atribuída pelo usuário as permissões necessárias como você normalmente faz para usuários SQL e outros. Execute o código a seguir. Para mais opções, consulte este documento.

   ALTER ROLE [role name] ADD MEMBER [your_resource_name];
   

4. Atribua uma ou várias identidades gerenciadas atribuídas pelo usuário ao seu data factory e crie credenciais para cada identidade gerenciada atribuída pelo usuário.

5. Configure um serviço ligado ao Base de Dados SQL do Azure.

Exemplo

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "UserAssignedManagedIdentity",
            "credential": {
                "referenceName": "credential1",
                "type": "CredentialReference"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Versão antiga

Estas propriedades genéricas são suportadas para um serviço vinculado ao Base de Dados SQL do Azure quando aplicar a versão Legacy:

Para diferentes tipos de autenticação, consulte as seguintes seções sobre propriedades específicas e pré-requisitos, respectivamente:

• Autenticação SQL para a versão herdada
• Autenticação de service principal para a versão herdada
• Autenticação de identidade gerenciada atribuída pelo sistema para a versão herdada
• Autenticação de identidade gerenciada atribuída pelo usuário para a versão herdada

Autenticação SQL para a versão herdada

Para usar a autenticação SQL, especifique as propriedades genéricas descritas na seção anterior.

Autenticação do principal de serviço para a versão legada

Para usar a autenticação do principal de serviço, além das propriedades genéricas descritas na seção anterior, especifique as seguintes propriedades:

Você também precisa seguir as etapas em Autenticação da entidade de serviço para conceder a permissão correspondente.

Autenticação de identidade gerenciada atribuída pelo sistema para a versão herdada

Para usar a autenticação de identidade gerida atribuída pelo sistema, siga a mesma etapa para a versão recomendada em Autenticação de identidade gerida atribuída pelo sistema.

Autenticação de identidade gerida pelo usuário para versões antigas

Para usar a autenticação de identidade gerenciada atribuída pelo usuário, siga a mesma etapa para a versão recomendada em Autenticação de identidade gerenciada atribuída pelo usuário.

Propriedades do conjunto de dados

Para obter uma lista completa de seções e propriedades disponíveis para definir conjuntos de dados, consulte Conjuntos de dados.

As seguintes propriedades são suportadas para o conjunto de dados Base de Dados SQL do Azure:

Exemplo de propriedades de conjunto de dados

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

Propriedades da atividade de cópia

Para obter uma lista completa de seções e propriedades disponíveis para definir atividades, consulte Pipelines. Esta secção fornece uma lista de propriedades suportadas pelo Base de Dados SQL do Azure (código-fonte e sink).

Base de Dados SQL do Azure como fonte

Para copiar dados de Base de Dados SQL do Azure, as seguintes propriedades são suportadas na secção atividade de cópia fonte:

Tenha em atenção os seguintes pontos:

• Se sqlReaderQuery for definido para AzureSqlSource, a atividade de cópia irá executar essa consulta na fonte Base de Dados SQL do Azure para obter os dados. Você também pode especificar um procedimento armazenado especificando sqlReaderStoredProcedureName e storedProcedureParameters se o procedimento armazenado tiver parâmetros.
• Ao usar o procedimento armazenado na origem para recuperar dados, observe se o procedimento armazenado for projetado como retornando esquema diferente quando um valor de parâmetro diferente for passado, você poderá encontrar falha ou ver um resultado inesperado ao importar esquema da interface do usuário ou ao copiar dados para o banco de dados SQL com a criação automática de tabelas.

Exemplo de consulta SQL

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

Exemplo de procedimento armazenado

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

Definição de procedimento armazenado

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

Base de Dados SQL do Azure como destino

Para copiar dados para Base de Dados SQL do Azure, as seguintes propriedades são suportadas na secção de atividade de cópia sink:

Exemplo 1: Acrescentar dados

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

Exemplo 2: Invocar um procedimento armazenado durante a cópia

Saiba mais detalhes em Invocar um procedimento armazenado a partir de um destino SQL.

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

Exemplo 3: Inserir ou atualizar dados

"activities":[
    {
        "name": "CopyToAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Azure SQL Database output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "AzureSqlSink",
                "tableOption": "autoCreate",
                "writeBehavior": "upsert",
                "upsertSettings": {
                    "useTempDB": true,
                    "keys": [
                        "<column name>"
                    ]
                },
            }
        }
    }
]

Cópia paralela do banco de dados SQL

O conector Base de Dados SQL do Azure na atividade de cópia de dados fornece particionamento de dados incorporado para copiar dados paralelamente. Você pode encontrar opções de particionamento de dados na guia Origem da atividade de cópia.

Quando ativa a cópia particionada, a atividade de cópia executa consultas paralelas na sua fonte Base de Dados SQL do Azure para carregar dados por partições. O grau paralelo é controlado pela configuração parallelCopies na atividade de cópia. Por exemplo, se definir parallelCopies para quatro, o serviço gera e executa simultaneamente quatro consultas com base na opção de partição e definições especificadas, e cada consulta recupera uma parte dos dados do seu Base de Dados SQL do Azure.

Sugere-se ativar a cópia paralela com particionamento de dados, especialmente quando carrega grandes quantidades de dados do seu Base de Dados SQL do Azure. A seguir estão sugeridas configurações para diferentes cenários. Ao copiar dados para o armazenamento de dados baseado em arquivo, é recomendável gravar em uma pasta como vários arquivos (especifique apenas o nome da pasta), caso em que o desempenho é melhor do que gravar em um único arquivo.

Práticas recomendadas para carregar dados com a opção de partição:

1. Escolha uma coluna distinta como coluna de partição (como chave primária ou chave exclusiva) para evitar distorção de dados.
2. Se a tabela tiver partição incorporada, use a opção de partição "Partições físicas da tabela" para obter um melhor desempenho.
3. Se usar Azure Integration Runtime para copiar dados, pode definir "Unidades de Integração de Dados (DIU)" (>4) maiores para utilizar mais recursos computacionais. Verifique os cenários aplicáveis lá.
4. "Grau de paralelismo de cópia controla os números de partição, estabelecer este número demasiado grande pode por vezes prejudicar o desempenho, recomenda-se definir este número como (DIU ou número de nós IR auto-hospedados) * (2 a 4)."

Exemplo: carga completa a partir de uma tabela grande com partições físicas

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

Exemplo: consulta com partição de intervalo dinâmico

"source": {
    "type": "AzureSqlSource",
    "query": "SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition 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ísica

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 partição física, você verá "HasPartition" como "sim" como a seguir.

Melhor prática para carregar dados no Base de Dados SQL do Azure

Quando copias dados para o Base de Dados SQL do Azure, podes precisar de comportamentos de escrita diferentes:

• Acrescentar: Meus dados de origem têm apenas novos registros.
• Upsert: Meus dados de origem têm inserções e atualizações.
• Substituir: Quero recarregar uma tabela de dimensão inteira cada vez.
• Escrever com lógica personalizada: preciso de processamento extra antes da inserção final na tabela de destino.

Consulte as respetivas seções sobre como configurar no serviço e as práticas recomendadas.

Acrescentar dados

Adicionar dados é o comportamento padrão deste conector sink do Base de Dados SQL do Azure. O serviço faz uma inserção em massa para gravar na sua tabela de forma eficiente. Você pode configurar a origem e o coletor de acordo com a atividade de cópia.

Inserir ou atualizar dados

A atividade de cópia agora suporta carregar dados nativamente numa tabela temporária de base de dados e depois atualizar os dados na tabela de destino se a chave existir e, caso contrário, inserir novos dados. Para saber mais sobre as definições do upsert nas atividades de cópia, veja Base de Dados SQL do Azure como o sumidouro.

Substituir a tabela inteira

Você pode configurar a propriedade preCopyScript no coletor de atividade de cópia. Nesse caso, para cada atividade de cópia executada, o serviço executa o script primeiro. Em seguida, ele executa a cópia para inserir os dados. Por exemplo, para substituir a tabela inteira pelos dados mais recentes, especifique um script para primeiro excluir todos os registros antes de carregar em massa os novos dados da fonte.

Gravar dados com lógica personalizada

As etapas para gravar dados com lógica personalizada são semelhantes às descritas na seção Upsert dados. Quando precisar de aplicar processamento extra antes da inserção final dos dados de origem na tabela de destino, pode carregar para uma tabela de staging e depois invocar a atividade do procedimento armazenado, ou invocar um procedimento armazenado no sink de atividade de cópia para aplicar dados, ou usar o Mapping Fluxo de Dados.

Invocar um procedimento armazenado a partir de um coletor SQL

Quando copia dados para o Base de Dados SQL do Azure, também pode configurar e invocar um procedimento armazenado especificado pelo utilizador com parâmetros adicionais em cada lote da tabela de origem. O recurso de procedimento armazenado tira partido dos parâmetros com valor de tabela.

Você pode usar um procedimento armazenado quando os mecanismos de cópia internos não atendem à finalidade. Um exemplo é quando você deseja aplicar processamento extra antes da inserção final dos dados de origem na tabela de destino. Alguns exemplos de processamento extra são quando você deseja mesclar colunas, procurar valores adicionais e inserir em mais de uma tabela.

O exemplo seguinte mostra como usar um procedimento armazenado para fazer um upsert numa tabela no Base de Dados SQL do Azure. Suponha que os dados de entrada e a tabela de destino Marketing tenham três colunas: ProfileID, State e Category. Faça o upsert com base na coluna ProfileID e aplique-o apenas para uma categoria específica chamada "ProductA".

1. Em seu banco de dados, defina o tipo de tabela com o mesmo nome de sqlWriterTableType. O esquema do tipo de tabela é o mesmo que o esquema retornado pelos dados de entrada.

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

2. Em seu banco de dados, defina o procedimento armazenado com o mesmo nome de sqlWriterStoredProcedureName. Ele processa dados de entrada da sua fonte especificada e integra na tabela de saída. O nome do parâmetro do tipo de tabela no procedimento armazenado é o mesmo que tableName definido no conjunto de dados.

   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. No seu pipeline Azure Data Factory ou Synapse, defina a seção SQL sink na atividade de cópia da seguinte forma:

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

Ao escrever dados no Base de Dados SQL do Azure usando procedimento armazenado, o sink divide os dados de origem em mini lotes e depois faz a inserção, para que a consulta extra no procedimento armazenado possa ser executada várias vezes. Se tiver a consulta para a atividade de cópia a executar antes de gravar os dados no Base de Dados SQL do Azure, não é recomendado adicioná-la ao procedimento armazenado, mas sim adicioná-lo na caixa Pre-copy script.

Mapeando propriedades de fluxo de dados

Ao transformar dados no mapeamento do fluxo de dados, pode ler e escrever em tabelas do Base de Dados SQL do Azure. Para obter mais informações, consulte a transformação de origem e a transformação de coletor no mapeamento de fluxos de dados.

Transformação da fonte

Definições específicas para Base de Dados SQL do Azure estão disponíveis no separador Opções de Fonte da transformação de origem.

Entrada: selecione se você aponta sua fonte para uma tabela (equivalente a Select * from <table-name>) ou insere uma consulta SQL personalizada.

Consulta: Se você selecionar Consulta no campo de entrada, insira uma consulta SQL para sua fonte. Essa configuração substitui qualquer tabela escolhida no conjunto de dados. As cláusulas Order By não são suportadas aqui, mas você pode definir uma instrução SELECT FROM completa. Você também pode usar funções de tabela definidas pelo usuário. select * from udfGetData() é um UDF em SQL que retorna uma tabela. Essa consulta produzirá uma tabela de origem que você pode usar em seu fluxo de dados. Usar consultas também é uma ótima maneira de reduzir linhas para testes ou pesquisas.

CREATE PROC CTESP @query nvarchar(max)
AS
BEGIN
EXECUTE sp_executesql @query;
END

Procedimento armazenado: escolha esta opção se desejar gerar uma projeção e dados de origem a partir de um procedimento armazenado executado a partir do banco de dados de origem. Você pode digitar o esquema, o nome do procedimento e os parâmetros ou clicar em Atualizar para solicitar que o serviço descubra os esquemas e os nomes dos procedimentos. Em seguida, você pode clicar em Importar para importar todos os parâmetros do procedimento usando o formulário @paraName.

• Exemplo de SQL: Select * from MyTable where customerId > 1000 and customerId < 2000
• Exemplo de SQL parametrizado: "select * from {$tablename} where orderyear > {$year}"

Tamanho do lote: insira um tamanho de lote para fragmentar dados grandes em leituras.

Nível de isolamento: O padrão para fontes SQL no mapeamento do fluxo de dados é ler sem confirmação. Você pode alterar o nível de isolamento aqui para um destes valores:

• Leia Comprometido
• Ler Não Comprometido
• Leitura repetível
• Serializável
• Nenhum (ignorar o nível de isolamento)

Ativar extração incremental: use esta opção para indicar ao ADF que deve processar apenas as linhas que foram alteradas desde a última execução do pipeline. Para habilitar a extração incremental com desvio de esquema, escolha tabelas baseadas em colunas Incremental/Marca d'água em vez de tabelas habilitadas para Captura de Dados de Alteração Nativa.

Coluna incremental: Ao usar o recurso de extração incremental, você deve escolher a data/hora ou a coluna numérica que deseja usar como marca d'água na tabela de origem.

Ativar captura de alterações de dados nativa (Versão de Teste): Use esta opção para indicar ao ADF que processe apenas os dados de alteração capturados pela tecnologia SQL de captura de alterações desde a última vez que o pipeline foi executado. Com essa opção, os dados delta, incluindo inserção de linha, atualização e exclusão, serão carregados automaticamente sem a necessidade de qualquer coluna incremental. Tens de ativar a captura de dados alterados na SQL do Azure base de dados antes de usares esta opção no ADF. Para obter mais informações sobre esta opção no ADF, consulte captura de dados de alterações nativa.

Comece a ler desde o início: definir essa opção com extração incremental instruirá o ADF a ler todas as linhas na primeira execução de um pipeline com a extração incremental ativada.

Transformação do lavatório

Definições específicas para Base de Dados SQL do Azure estão disponíveis no separador Settings da transformação do sink.

Método de atualização: determina quais operações são permitidas no destino do banco de dados. O padrão é permitir apenas inserções. Para realizar a atualização, inserção ou eliminação de linhas, é necessário uma transformação de alteração de linha para marcar as linhas para essas ações. Para atualizações, upserts e exclusões, uma coluna ou colunas de chave devem ser definidas para determinar qual linha alterar.

O nome da coluna que você escolher como a chave aqui será usado pelo serviço como parte da atualização subsequente, upsert, excluir. Portanto, você deve escolher uma coluna que existe no mapeamento de coletor. Se não desejar escrever o valor nesta coluna de chave, clique em "Ignorar escrita em colunas de chave".

Pode parametrizar a coluna chave usada aqui para atualizar a sua tabela Base de Dados SQL do Azure de destino. Se você tiver várias colunas para uma chave composta, clique em "Expressão personalizada" e poderá adicionar conteúdo dinâmico usando a linguagem de expressão de fluxo de dados, que pode incluir uma matriz de cadeias de caracteres com nomes de coluna para uma chave composta.

Ação da tabela: Determina se devem ser recriadas ou removidas todas as linhas da tabela de destino antes da gravação.

• Nenhuma: Nenhuma ação será realizada na tabela.
• Recriar: A tabela será descartada e recriada. Necessário apenas ao criar uma nova tabela dinamicamente.
• Truncate: Todas as linhas da tabela de destino serão removidas.

Tamanho do lote: Controla quantas linhas são escritas em cada bucket. Lotes maiores melhoram a compactação e a otimização da memória, mas correm o risco de exceções de falta de memória ao armazenar dados em cache.

Usar TempDB: Por padrão, o serviço usará uma tabela temporária global para armazenar dados como parte do processo de carregamento. Como alternativa, você pode desmarcar a opção "Usar TempDB" e, em vez disso, pedir ao serviço para armazenar a tabela de retenção temporária em um banco de dados de usuário localizado no banco de dados que está sendo usado para esse coletor.

Scripts SQL Pre e Post: Insira scripts SQL de várias linhas que serão executados antes (pré-processamento) e depois (pós-processamento), quando os dados forem gravados no seu banco de dados Sink.

Processamento da linha de erro

Ao escrever no SQL do Azure DB, certas linhas de dados podem falhar devido a restrições definidas pelo destino. Alguns erros comuns:

• Dados binários ou de cadeia de caracteres seriam truncados na tabela
• Não é possível inserir o valor NULL na coluna
• A instrução INSERT entrou em conflito com a restrição CHECK

Por padrão, uma execução de fluxo de dados falhará no primeiro erro que receber. Você pode escolher Ignorar erros, permitindo que o fluxo de dados seja concluído mesmo que linhas individuais tenham erros. O serviço fornece diferentes opções para você lidar com essas linhas de erro.

Confirmação de transação: escolha se seus dados são gravados em uma única transação ou em lotes. Uma única transação proporcionará um desempenho pior, mas nenhum dado gravado será visível para outras pessoas até que a transação seja concluída.

Output rejected data: Se estiver ativado, pode exportar as linhas de erro para um ficheiro CSV no Armazenamento de Blobs do Azure ou numa conta Azure Data Lake Storage Gen2 à sua escolha. Isso gravará as linhas de erro com três colunas adicionais: a operação SQL como INSERT ou UPDATE, o código de erro de fluxo de dados e a mensagem de erro na linha.

Relatar sucesso em caso de erro: Se ativado, o fluxo de dados será marcado como um sucesso, mesmo se forem encontradas linhas de erro.

Mapeamento de tipos de dados para o Base de Dados SQL do Azure

Quando os dados são copiados do ou para o Base de Dados SQL do Azure, são usados os seguintes mapeamentos dos tipos de dados do Base de Dados SQL do Azure para os tipos de dados intermédios do Azure Data Factory. Os mesmos mapeamentos são usados pela funcionalidade pipeline Synapse, que implementa diretamente o Azure Data Factory. Para saber como a atividade de cópia mapeia o esquema de origem e o tipo de dados para o coletor, consulte Mapeamentos de esquema e tipo de dados.

Propriedades da atividade de consulta

Para saber detalhes sobre as propriedades, verifique Atividade de pesquisa.

Propriedades de atividade GetMetadata

Para saber detalhes sobre as propriedades, verifique a atividade GetMetadata

Utilização de Always Encrypted

Quando copiar dados de/para Base de Dados SQL do Azure com Sempre Encriptado, siga os passos abaixo:

1. Armazene a Column Master Key (CMK) em um Azure Key Vault. Saiba mais sobre como configurar o Always Encrypted usando Azure Key Vault

2. Certifique-se de obter acesso ao cofre de chaves onde está armazenada a Chave Mestra de Coluna (CMK). Consulte este artigo para obter as permissões necessárias.

3. Crie um serviço vinculado para se conectar ao seu banco de dados SQL e habilite a função 'Always Encrypted' usando a identidade gerida ou o principal de serviço.

Captura nativa de dados de alteração

O Azure Data Factory pode suportar capacidades nativas de captura de dados de alterações para SQL Server, SQL do Azure DB e SQL do Azure MI. Os dados alterados, incluindo inserção de linha, atualização e exclusão em repositórios SQL, podem ser detetados e extraídos automaticamente pelo fluxo de dados de mapeamento do ADF. Com a experiência sem código no mapeamento de fluxo de dados, os usuários podem facilmente obter o cenário de replicação de dados de repositórios SQL anexando um banco de dados como armazenamento de destino. Além disso, os utilizadores podem também compor qualquer lógica de transformação de dados para conseguir um cenário de ETL incremental utilizando repositórios SQL.

Certifique-se de manter o pipeline e o nome da atividade inalterados, para que o ponto de verificação possa ser registado pelo ADF e assim você obtenha automaticamente os dados alterados da última execução. Se alterar o nome do pipeline ou o nome da atividade, o ponto de verificação será redefinido, o que o obriga a começar do início ou a aplicar as alterações a partir de agora na próxima execução do pipeline. Caso deseje alterar o nome do pipeline ou o nome da atividade, mas queira manter o ponto de verificação para obter dados modificados automaticamente da última execução, utilize sua própria chave de ponto de verificação na atividade de fluxo de dados para alcançar isso.

Quando você depura o pipeline, esse recurso funciona da mesma forma. Lembre-se de que o ponto de verificação será redefinido quando você atualizar o navegador durante a execução de depuração. Depois de estar satisfeito com o resultado da execução de depuração do pipeline, pode publicar e acionar o pipeline. No momento em que você aciona pela primeira vez seu pipeline publicado, ele é reiniciado automaticamente desde o início ou recebe alterações a partir de agora.

Na seção de monitoramento, você sempre tem a chance de executar novamente um pipeline. Quando você está fazendo isso, os dados alterados são sempre capturados do ponto de verificação anterior da execução do pipeline selecionado.

Exemplo 1:

Quando você encadeia diretamente uma transformação de origem referenciada ao conjunto de dados habilitado para SQL CDC com uma transformação de coletor referenciada a um banco de dados em um fluxo de dados de mapeamento, as alterações ocorridas na origem SQL serão aplicadas automaticamente ao banco de dados de destino, para que você obtenha facilmente o cenário de replicação de dados entre bancos de dados. Você pode usar o método update na transformação do coletor para selecionar se deseja permitir inserir, permitir atualização ou permitir exclusão no banco de dados de destino. O script de exemplo no mapeamento de fluxo de dados é como abaixo.

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 sink(allowSchemaDrift: true,
	validateSchema: false,
	deletable:true,
	insertable:true,
	updateable:true,
	upsertable:true,
	keys:['id'],
	format: 'table',
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true,
	errorHandlingOption: 'stopOnFirstError') ~> sink1

Exemplo 2:

Se quiser habilitar o cenário de ETL em vez da replicação de dados entre bancos de dados via SQL CDC, você pode usar expressões no mapeamento de fluxo de dados, incluindo isInsert(1), isUpdate(1) e isDelete(1) para diferenciar as linhas com diferentes tipos de operação. A seguir está um dos scripts de exemplo para mapear o fluxo de dados na derivação de uma coluna com o valor: 1 para indicar linhas inseridas, 2 para indicar linhas atualizadas e 3 para indicar linhas excluídas para transformações downstream para processar os dados delta.

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 derive(operationType = iif(isInsert(1), 1, iif(isUpdate(1), 2, 3))) ~> derivedColumn1
derivedColumn1 sink(allowSchemaDrift: true,
	validateSchema: false,
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true) ~> sink1

Limitação conhecida:

• Somente as alterações líquidas do SQL CDC serão carregadas pelo ADF via cdc.fn_cdc_get_net_changes_.

Atualize a versão do Base de Dados SQL do Azure

Para atualizar a versão do Base de Dados SQL do Azure, na página Editar serviço ligado, selecione Recomendado em Versão e configure o serviço ligado consultando as Propriedades do serviço ligado para a versão recomendada.

Diferenças entre a versão recomendada e a versão herdada

A tabela abaixo mostra as diferenças entre o Base de Dados SQL do Azure usando a versão recomendada e a versão legada.

Conteúdos relacionados

Para obter uma lista de armazenamentos de dados suportados como fontes e destinos pela atividade de cópia, consulte Armazenamentos de dados e formatos suportados.