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

APLICA-SE A: Azure Data Factory Azure Synapse Analytics

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

Esse conector é especializado para o serviço Banco 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 do Banco de Dados do Azure para PostgreSQL tem suporte para os seguintes recursos:

(1) Infraestrutura de integração do Azure (2) Infraestrutura de integração auto-hospedada

As três atividades funcionam no Banco de Dados do Azure para PostgreSQL Single Server, Servidor Flexível 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
• SDK do .NET
• Python SDK
• Azure PowerShell
• API REST
• Modelo Azure Resource Manager

Criar um serviço vinculado ao Banco de Dados do Azure para PostgreSQL usando a interface do usuário

Use as etapas a seguir para criar um serviço vinculado ao banco de dados do Azure para PostgreSQL na interface do usuário do portal do Azure.

1. Navegue até a guia Gerenciar em seu espaço de trabalho do Azure Data Factory ou Synapse e selecione Serviços Vinculados e, em seguida, selecione Novo:

   • Fábrica de Dados do Azure
   • Sinapse do Azure

   

2. Pesquise PostgreSQL e selecione o banco de dados do Azure para 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 seções a seguir oferecem detalhes sobre as propriedades usadas para definir entidades do Data Factory específicas para o conector do Banco de Dados do Azure para PostgreSQL.

Propriedades do serviço vinculado

O conector do Banco de Dados do Azure para PostgreSQL versão 2.0 dá suporte ao Transport Layer Security (TLS) 1.3 e a vários modos SSL (Secured Socket Layer). Consulte esta seção para atualizar sua versão do conector do Banco 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 Azure Database for 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:

Armazenar palavra-passe no Cofre da Chave do Azure

{
    "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 gerenciada atribuída pelo Sistema que representa o serviço ao autenticar outros recursos no Azure. Você pode usar essa identidade gerenciada para o banco de dados do 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 PostgreSQL com identidade gerida atribuída pelo sistema On.

   

3. No seu recurso Banco de Dados do Azure para PostgreSQL em Segurança

   1. Selecione Autenticação

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

   3. Selecione + Adicionar administradores do Microsoft Entra

   4. Adicione a identidade gerida atribuída pelo sistema ao recurso Azure Data Factory, tornando-a um dos Administradores do Microsoft Entra.

      

4. Configure um banco 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

Um Data Factory ou espaço de trabalho Synapse pode ser associado a uma identidade gerida atribuída pelo utilizador que representa o serviço ao autenticar-se junto a outros recursos no Azure. Você pode usar essa identidade gerenciada para o banco de dados do 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 de Identidade Gerenciada Atribuída pelo Usuário no portal do Azure. Para saber mais, vá para Gerenciar identidades gerenciadas atribuídas pelo usuário

2. Atribuir a Identidade Gerenciada atribuída pelo Usuário ao seu banco de dados do Azure para o recurso PostgreSQL

   1. Em seu banco de dados do Azure para recurso de servidor PostgreSQL, em Segurança

   2. Selecione Autenticação

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

   4. Selecione + Adicionar link de administradores do Microsoft Entra e selecione sua identidade gerenciada atribuída pelo usuário

      

3. Atribuir a Identidade Gerenciada atribuída pelo Usuário ao seu recurso do 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 um banco 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": "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 têm suporte para o serviço vinculado Banco de Dados do Azure para PostgreSQL quando você aplica a versão 1.0:

Uma cadeia de conexão típica é 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:

Armazenar palavra-passe no Cofre da Chave do Azure

{
    "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 seção fornece uma lista de propriedades que o Banco de Dados do Azure para PostgreSQL oferece suporte em conjuntos de dados.

Para copiar dados do Banco de Dados do Azure para PostgreSQL, defina a propriedade type 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 seção fornece uma lista de propriedades suportadas por um Banco de Dados do Azure para fonte PostgreSQL.

Banco de Dados do Azure para PostgreSql como origem

Para copiar dados do Banco de Dados do Azure para PostgreSQL, defina o tipo de origem na atividade de cópia como 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>"
            }
        }
    }
]

Banco de Dados do Azure para PostgreSQL como coletor

Para copiar dados para o Banco de Dados do Azure para PostgreSQL, defina o tipo de coletor na atividade de cópia como 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 atividade 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 Banco de Dados do Azure para PostgreSQL

O conector do Banco de Dados do Azure para PostgreSQL na atividade de cópia fornece particionamento de dados integrado para copiar dados em paralelo. Você pode encontrar opções de particionamento de dados na guia Origem da atividade de cópia.

Quando você habilita a cópia particionada, a atividade de cópia executa consultas paralelas no Banco de Dados do Azure para a fonte PostgreSQL para carregar dados por partições. O grau de paralelismo é controlado pela configuração parallelCopies na atividade de cópia. Por exemplo, se você definir parallelCopies como quatro, o serviço gerará e executará simultaneamente quatro consultas com base na opção e nas configurações de partição especificadas, e cada consulta recuperará uma parte dos dados do Banco de Dados do Azure para PostgreSQL.

Sugere-se que você habilite a cópia paralela com particionamento de dados, especialmente quando carrega uma grande quantidade de dados do Banco 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 você usar o Tempo de Execução de Integração do Azure para copiar dados, poderá definir "Unidades de Integração de Dados (DIU)" (>4) maiores para utilizar mais recursos de computação. 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 num fluxo de dados de mapeamento, é possível ler e gravar em tabelas do Azure Database para PostgreSQL. Para obter mais informações, consulte a transformação de origem e transformação de destino no mapeamento de fluxos de dados. Você pode optar por usar um banco de dados do Azure para conjunto de dados PostgreSQL ou um conjunto de dados embutido como tipo de fonte e coletor.

Transformação da fonte

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

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

Quando você usa o Banco de Dados do Azure para PostgreSQL como tipo de origem, 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 coletor do Banco de Dados do Azure para PostgreSQL. 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 coletor do Banco de Dados do Azure para PostgreSQL

Ao utilizar o Azure Database for PostgreSQL como tipo de destino, 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 Banco de Dados do Azure para o conector 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.