Copiar e transformar dados no Base de Dados do Azure para PostgreSQL usando Azure Data Factory ou Synapse Analytics

APLICA-SE A: Azure Data Factory Azure Synapse Analytics

Este artigo descreve como utilizar a atividade "Copy Activity" nos pipelines do Azure Data Factory e do Synapse Analytics para copiar dados para e da Base de Dados do Azure para PostgreSQL. E como usar o Fluxo de Dados para transformar dados no Base de Dados do Azure para PostgreSQL. Para saber mais, leia os artigos introdutórios para Azure Data Factory e Synapse Analytics.

Este conector é especializado para o serviço Base de Dados do Azure para PostgreSQL. Para copiar dados de um banco de dados PostgreSQL genérico localizado no local ou na nuvem, use o conector PostgreSQL.

Capacidades suportadas

Este conector Base de Dados do Azure para PostgreSQL suporta as seguintes capacidades:

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

As três atividades funcionam em Base de Dados do Azure para PostgreSQL Single Server, Flexible Server e Azure Cosmos DB para PostgreSQL.

Como Começar

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

Criar um serviço ligado para a Base de Dados do Azure para PostgreSQL usando UI

Use os seguintes passos para criar um serviço ligado à base de dados Azure para PostgreSQL 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 selecione Novo:

   • Azure Data Factory
   • Azure Synapse

   

2. Procure por PostgreSQL e selecione a base de dados Azure para o conector PostgreSQL.

   

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 oferecem detalhes sobre propriedades usadas para definir entidades Data Factory específicas do conector Base de Dados do Azure para PostgreSQL.

Propriedades do serviço vinculado

A versão do conector Base de Dados do Azure para PostgreSQL 2.0 suporta Transport Layer Security (TLS) 1.3 e múltiplos modos Secured Socket Layer (SSL). Consulte esta secção para atualizar a versão do seu conector do Base de Dados SQL do Azure da versão 1.0. Para obter os detalhes da propriedade, consulte as seções correspondentes.

• Versão 2.0
• Versão 1.0

Versão 2.0

As seguintes propriedades são suportadas para o serviço associado Base de Dados do Azure para PostgreSQL ao aplicar a versão 2.0:

autenticação Básica

Exemplo:

{
    "name": "AzurePostgreSqlLinkedService",
    "properties": {
        "type": "AzurePostgreSql",
        "version": "2.0",
        "typeProperties": {
            "server": "<server name>",
            "port": "5432",
            "database": "<database name>",
            "sslMode": 2,
            "username": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        }
    }
}

Exemplo:

Guardar a palavra-passe em Azure Key Vault

{
    "name": "AzurePostgreSqlLinkedService",
    "properties": {
        "type": "AzurePostgreSql",
        "version": "2.0",
        "typeProperties": {
            "server": "<server name>",
            "port": "5432",
            "database": "<database name>",
            "sslMode": 2,
            "username": "<user name>",
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        }
    }
}

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 ao sistema que representa o serviço ao autenticar-se com outros recursos no Azure. Pode usar esta identidade gerida para a base de dados Azure para autenticação PostgreSQL. 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 identidade gerenciada atribuída ao sistema, siga as etapas:

1. Uma fábrica de dados ou um espaço de trabalho Synapse pode ser associado a uma identidade gerenciada atribuída ao sistema. Saiba mais, Gerar identidade gerenciada atribuída ao sistema

2. Os dados do Azure para o PostgreSQL com a identidade gerida atribuída pelo sistema estão Ativados.

   

3. No seu recurso Base de Dados do Azure para PostgreSQL na seção Segurança

   1. Selecione Autenticação

   2. Selecione apenas autenticação Microsoft Entra ou método de autenticação PostgreSQL e Microsoft Entra.

   3. Selecione + Adicionar administradores do Microsoft Entra

   4. Adicione a identidade gerida atribuída pelo sistema ao recurso Azure Data Factory como um dos administradores da Microsoft Entra

      

4. Configure uma base de dados do Azure para o serviço vinculado PostgreSQL.

Exemplo:

{
    "name": "AzurePostgreSqlLinkedService",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "annotations": [],
        "type": "AzurePostgreSql",
        "version": "2.0",
        "typeProperties": {
            "server": "<server name>",
            "port": 5432,
            "database": "<database name>",
            "sslMode": 2,
            "authenticationType": "SystemAssignedManagedIdentity"
        }
    }
}

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 uma identidade gerida Atribuída pelo utilizador que representa o serviço ao autenticar com outros recursos em Azure. Pode usar esta identidade gerida para a base de dados Azure para autenticação PostgreSQL. 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 as etapas:

1. Certifique-se de criar um recurso Identidade Gerida Atribuída pelo Utilizador no portal da Azure. Para saber mais, vá para Gerenciar identidades gerenciadas atribuídas pelo usuário

2. Atribui a User-assigned Managed Identity à tua base de dados de Azure para o recurso PostgreSQL

   1. Na sua base de dados Azure para o recurso de servidor PostgreSQL, em Security

   2. Selecione Autenticação

   3. Verifique se o método de autenticação é apenas autenticação Microsoft Entra ou autenticação PostgreSQL e Microsoft Entra

   4. Selecione o link + Adicionar Administradores Microsoft Entra e selecione a identidade gerida atribuída pelo utilizador.

      

3. Atribua a Identidade Gerida Atribuída pelo Utilizador ao teu recurso Azure Data Factory

   1. Selecione Configurações e, em seguida, Identidades gerenciadas

   2. Na guia Usuário atribuído . Selecione o link + Adicionar e selecione sua identidade gerenciada pelo usuário

      

4. Configure uma base de dados Azure para o serviço ligado PostgreSQL.

Exemplo:

{
    "name": "AzurePostgreSqlLinkedService",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "annotations": [],
        "type": "AzurePostgreSql",
        "version": "2.0",
        "typeProperties": {
            "server": "<server name>",
            "port": 5432,
            "database": "<database name>",
            "sslMode": 2,
            "authenticationType": "UserAssignedManagedIdentity",
            "credential": {
                "referenceName": "<your credential>",
                "type": "CredentialReference"
            }
        }
    }
}

Autenticação do Service Principal

Exemplo:

Chave da entidade de serviço

{
    "name": "AzurePostgreSqlLinkedService",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "annotations": [],
        "type": "AzurePostgreSql",
        "version": "2.0",
        "typeProperties": {
            "server": "<server name>",
            "port": 5432,
            "database": "<database name>",
            "sslMode": 2,
            "username": "<service principal name>",
            "authenticationType": "<authentication type>",
            "tenant": "<tenant>",
            "servicePrincipalId": "<service principal ID>",
            "azureCloudType": "<azure cloud type>",
            "servicePrincipalCredentialType": "<service principal type>",
            "servicePrincipalKey": "<service principal key>"
        }
    }
}

Exemplo:

Certificado de principal de serviço

{
    "name": "AzurePostgreSqlLinkedService",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "annotations": [],
        "type": "AzurePostgreSql",
        "version": "2.0",
        "typeProperties": {
            "server": "<server name>",
            "port": 5432,
            "database": "<database name>",
            "sslMode": 2,
            "username": "<service principal name>",
            "authenticationType": "<authentication type>",
            "tenant": "<tenant>",
            "servicePrincipalId": "<service principal ID>",
            "azureCloudType": "<azure cloud type>",
            "servicePrincipalCredentialType": "<service principal type>",
            "servicePrincipalEmbeddedCert": "<service principal certificate>",
            "servicePrincipalEmbeddedCertPassword": "<service principal embedded certificate password>"
        }
    }
}

Versão 1.0

As seguintes propriedades são suportadas para o serviço vinculado Base de Dados do Azure para PostgreSQL ao aplicar a versão 1.0:

Um cadeia de ligação típico é host=<server>.postgres.database.azure.com;database=<database>;port=<port>;uid=<username>;password=<password>. Aqui estão mais propriedades que você pode definir de acordo com o seu caso:

Exemplo:

{
    "name": "AzurePostgreSqlLinkedService",
    "properties": {
        "type": "AzurePostgreSql",
        "version": "1.0",
        "typeProperties": {
            "connectionString": "host=<server>.postgres.database.azure.com;database=<database>;port=<port>;uid=<username>;password=<password>"
        }
    }
}

Exemplo:

Guardar a palavra-passe em Azure Key Vault

{
    "name": "AzurePostgreSqlLinkedService",
    "properties": {
        "type": "AzurePostgreSql",
        "version": "1.0",
        "typeProperties": {
            "connectionString": "host=<server>.postgres.database.azure.com;database=<database>;port=<port>;uid=<username>;",
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        }
    }
}

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. Esta secção fornece uma lista de propriedades que o Base de Dados do Azure para PostgreSQL suporta em conjuntos de dados.

Para copiar dados de Base de Dados do Azure para PostgreSQL, defina a propriedade de tipo do conjunto de dados como AzurePostgreSqlTable. As seguintes propriedades são suportadas:

Exemplo:

{
    "name": "AzurePostgreSqlDataset",
    "properties": {
        "type": "AzurePostgreSqlTable",
        "linkedServiceName": {
            "referenceName": "<AzurePostgreSql linked service name>",
            "type": "LinkedServiceReference"
        },
        "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 e atividades. Esta secção fornece uma lista de propriedades suportadas por uma fonte do Base de Dados do Azure para PostgreSQL.

Azure Database for PostgreSQL como fonte

Para copiar dados de Base de Dados do Azure para PostgreSQL, defina o tipo de origem na atividade de cópia para AzurePostgreSqlSource. As seguintes propriedades são suportadas na seção de origem da atividade de cópia:

Exemplo:

"activities":[
    {
        "name": "CopyFromAzurePostgreSql",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<AzurePostgreSql input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "AzurePostgreSqlSource",
                "query": "<custom query e.g. SELECT * FROM mytable>",
                "queryTimeout": "00:10:00"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Base de Dados do Azure para PostgreSQL como alvo

Para copiar dados para Base de Dados do Azure para PostgreSQL, defina o tipo de sink na atividade de cópia para SqlSink. As seguintes propriedades são suportadas na seção coletor de atividade de cópia:

Exemplo 1: Comando Copiar

"activities":[
    {
        "name": "CopyToAzureDatabaseForPostgreSQL",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Azure PostgreSQL output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "AzurePostgreSqlSink",
                "preCopyScript": "<custom SQL script>",
                "writeMethod": "CopyCommand",
                "writeBatchSize": 1000000
            }
        }
    }
]

Exemplo 2: Inserir ou atualizar dados (Upsert)

"activities":[
    {
        "name": "CopyToAzureDatabaseForPostgreSQL",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Azure PostgreSQL output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "AzurePostgreSQLSink",
                "writeMethod": "Upsert",
                "upsertSettings": {
                    "keys": [
                        "<column name>"
                    ]
                },
            }
        }
    }
]

Atualizar ou inserir dados

A função de cópia suporta nativamente operações de upsert. Para executar um upsert, o usuário deve fornecer coluna(s) de chave que são chaves primárias ou colunas exclusivas. Se o usuário não fornecer coluna(s) de chave, então a(s) coluna(s) de chave primária na tabela de coletor serão usadas. A Atividade de Cópia atualizará a(s) coluna(s) não-chave(s) na tabela do coletor onde o(s) valor(es) da(s) coluna(s) chave(s) corresponde(m) aos da tabela de origem; caso contrário, inserirá novos dados.

Cópia paralela do Base de Dados do Azure para PostgreSQL

O conector do Base de Dados do Azure para PostgreSQL em atividades de cópia oferece particionamento de dados embutido para permitir a cópia de dados em paralelo. Você pode encontrar opções de particionamento de dados na guia Origem da atividade de cópia.

Quando ativas a cópia particionada, a atividade de cópia executa consultas paralelas na tua fonte do Base de Dados do Azure para PostgreSQL para carregar os dados por partições. O grau de paralelismo é controlado pela configuração parallelCopies na atividade de cópia. Por exemplo, se definir parallelCopies como 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 do Azure para PostgreSQL.

Recomenda-se ativar a cópia paralela com particionamento de dados, especialmente quando carrega grandes quantidades de dados do seu Base de Dados do Azure para PostgreSQL. A seguir estão sugeridas configurações para diferentes cenários. Ao copiar dados para o armazenamento de dados baseado em arquivo, a recomendação é 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; definir um número muito grande às vezes prejudica o desempenho." Recomendo definir este número como (DIU ou número de nós IR auto-hospedados) * (2 a 4).

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

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

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

"source": {
    "type": "AzurePostgreSqlSource",
    "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>"
    }
}

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 do Azure para PostgreSQL. Para obter mais informações, consulte a transformação de origem e transformação de destino no mapeamento de fluxos de dados. Pode escolher usar um conjunto de dados Base de Dados do Azure para PostgreSQL ou um conjunto de dados inline como tipo de origem e de destino.

Transformação da fonte

A tabela abaixo lista as propriedades suportadas pelo Base de Dados do Azure para PostgreSQL source. Você pode editar essas propriedades na guia Opções de origem .

Exemplo de script de origem do Base de Dados do Azure para PostgreSQL

Quando usa o Base de Dados do Azure para PostgreSQL como tipo de fonte, o script de fluxo de dados associado é:

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

Transformação do lavatório

A tabela abaixo lista as propriedades suportadas pelo Base de Dados do Azure para PostgreSQL sink. Você pode editar essas propriedades no separador Opções do Sink.

• Habilitar extração incremental: use esta opção para configurar o ADF para processar apenas as linhas que mudaram desde a última execução do pipeline.

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

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

Exemplo de script de saída do Azure Database para PostgreSQL

Quando usa o Base de Dados do Azure para PostgreSQL como tipo sink, o script de fluxo de dados associado é:

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

Atividade de script

Para obter mais informações sobre a atividade de script, consulte Atividade de script.

Propriedades da atividade de consulta

Para obter mais informações sobre as propriedades, consulte Atividade de pesquisa.

Atualizar o conector do Base de Dados do Azure para PostgreSQL

Na página Editar serviço vinculado , selecione 2.0 em Versão e configure o serviço vinculado consultando Propriedades do serviço vinculado versão 2.0.

Conteúdos relacionados

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