Partilhar via


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

Gorjeta

Experimente o Data Factory no Microsoft Fabric, uma solução de análise tudo-em-um para empresas. O Microsoft Fabric abrange tudo, desde a movimentação de dados até ciência de dados, análises em tempo real, business intelligence e relatórios. Saiba como iniciar uma nova avaliação gratuitamente!

Este artigo descreve como usar a Atividade de Cópia no Azure Data Factory e nos pipelines do Synapse Analytics para copiar dados de e para o Banco de Dados do Azure para PostgreSQL e 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:

Capacidades suportadas IR Ponto final privado gerido
Atividade de cópia (origem/coletor) (1) (2)
Mapeando o fluxo de dados (origem/coletor) (1)
Atividade de Pesquisa (1) (2)

(1) Tempo de execução de integração do Azure (2) Tempo de execução de integração auto-hospedado

As três atividades funcionam em todas as opções de implantação do Banco de Dados do Azure para PostgreSQL:

Introdução

Para executar a atividade Copiar com um pipeline, você pode usar uma das seguintes ferramentas ou SDKs:

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 clique em Novo:

  2. Pesquise PostgreSQL e selecione o banco de dados do Azure para conector PostgreSQL.

    Selecione o banco de dados do Azure para o conector PostgreSQL.

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

    Configure um serviço vinculado ao banco de dados do Azure para PostgreSQL.

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

As seguintes propriedades têm suporte para o serviço vinculado Banco de Dados do Azure para PostgreSQL:

Property Descrição Obrigatório
tipo A propriedade type deve ser definida como: AzurePostgreSql. Sim
connectionString Uma cadeia de conexão ODBC para se conectar ao Banco de Dados do Azure para PostgreSQL.
Você também pode colocar uma senha no Cofre de Chaves do Azure e extrair a password configuração da cadeia de conexão. Consulte os seguintes exemplos e Armazenar credenciais no Azure Key Vault para obter mais detalhes.
Sim
ConecteVia Essa propriedade representa o tempo de execução de integração a ser usado para se conectar ao armazenamento de dados. Você pode usar o Azure Integration Runtime ou o Self-hosted Integration Runtime (se seu armazenamento de dados estiver localizado em rede privada). Se não for especificado, ele usará o Tempo de Execução de Integração do Azure padrão. Não

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

Property Description Opções Necessário
Método de criptografia (EM) O método que o driver usa para criptografar dados enviados entre o driver e o servidor de banco de dados. Por exemplo, EncryptionMethod=<0/1/6>; 0 (Sem criptografia) (padrão) / 1 (SSL) / 6 (RequestSSL) Não
ValidateServerCertificate (VSC) Determina se o driver valida o certificado enviado pelo servidor de banco de dados quando a criptografia SSL está habilitada (Método de Criptografia=1). Por exemplo, ValidateServerCertificate=<0/1>; 0 (Desativado) (Padrão) / 1 (Habilitado) Não

Exemplo:

{
    "name": "AzurePostgreSqlLinkedService",
    "properties": {
        "type": "AzurePostgreSql",
        "typeProperties": {
            "connectionString": "Server=<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",
        "typeProperties": {
            "connectionString": "Server=<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:

Property Descrição Obrigatório
tipo A propriedade type do conjunto de dados deve ser definida como AzurePostgreSqlTable Sim
tableName Nome da tabela Não (se "consulta" na fonte da atividade for especificado)

Exemplo:

{
    "name": "AzurePostgreSqlDataset",
    "properties": {
        "type": "AzurePostgreSqlTable",
        "linkedServiceName": {
            "referenceName": "<AzurePostgreSql linked service name>",
            "type": "LinkedServiceReference"
        },
        "typeProperties": {}
    }
}

Propriedades da atividade Copy

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:

Property Descrição Obrigatório
tipo A propriedade type da fonte de atividade de cópia deve ser definida como AzurePostgreSqlSource Sim
query Use a consulta SQL personalizada para ler dados. Por exemplo: SELECT * FROM mytable ou SELECT * FROM "MyTable". Observação no PostgreSQL, o nome da entidade é tratado como insensível a maiúsculas e minúsculas se não for citado. Não (se a propriedade tableName no conjunto de dados for especificada)
partitionOptions Especifica as opções de particionamento de dados usadas para carregar dados do Banco de Dados SQL do Azure.
Os valores permitidos são: None (padrão), PhysicalPartitionsOfTable e DynamicRange.
Quando uma opção de partição é habilitada (ou seja, não None), o grau de paralelismo para carregar simultaneamente dados de um Banco de Dados SQL do Azure é controlado pela parallelCopies configuração na atividade de cópia.
Não
partitionSettings Especifique o grupo de configurações para particionamento de dados.
Aplique quando a opção de partição não Nonefor .
Não
Em partitionSettings:
partitionNames A lista de partições físicas que precisam ser copiadas.
Aplique quando a opção de partição for PhysicalPartitionsOfTable. Se você usar uma consulta para recuperar os dados de origem, conecte ?AdfTabularPartitionName a cláusula WHERE. Para obter um exemplo, consulte a seção Cópia paralela do Banco de Dados do Azure para PostgreSQL .
Não
partitionColumnName Especifique o nome da coluna de origem no tipo inteiro ou data/data/hora (int, smallint, bigint, timestamp with time zone datetimestamp without time zone, , ou time without time zone) que será usado pelo particionamento de intervalo para cópia paralela. Se não for especificado, a chave primária da tabela será detetada automaticamente e usada como coluna de partição.
Aplique quando a opção de partição for DynamicRange. Se você usar uma consulta para recuperar os dados de origem, conecte ?AdfRangePartitionColumnName a cláusula WHERE. Para obter um exemplo, consulte a seção Cópia paralela do Banco de Dados do Azure para PostgreSQL .
Não
partiçãoUpperBound O valor máximo da coluna de partição para copiar dados.
Aplique quando a opção de partição for DynamicRange. Se você usar uma consulta para recuperar os dados de origem, conecte ?AdfRangePartitionUpbound a cláusula WHERE. Para obter um exemplo, consulte a seção Cópia paralela do Banco de Dados do Azure para PostgreSQL .
Não
partiçãoLowerBound O valor mínimo da coluna de partição para copiar dados.
Aplique quando a opção de partição for DynamicRange. Se você usar uma consulta para recuperar os dados de origem, conecte ?AdfRangePartitionLowbound a cláusula WHERE. Para obter um exemplo, consulte a seção Cópia paralela do Banco de Dados do Azure para PostgreSQL .
Não

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>"
            },
            "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, as seguintes propriedades são suportadas na seção coletor de atividade de cópia:

Property Descrição Obrigatório
tipo A propriedade type do coletor de atividade de cópia deve ser definida como AzurePostgreSQLSink. Sim
pré-CopyScript Especifique uma consulta SQL para a atividade de cópia a ser executada antes de gravar dados no Banco de Dados do Azure para PostgreSQL em cada execução. Você pode usar essa propriedade para limpar os dados pré-carregados. Não
writeMethod O método usado para gravar dados no Banco de Dados do Azure para PostgreSQL.
Os valores permitidos são: CopyCommand (padrão, que é mais eficiente), BulkInsert.
Não
writeBatchSize O número de linhas carregadas no Banco de Dados do Azure para PostgreSQL por lote.
O valor permitido é um número inteiro que representa o número de linhas.
Não (o padrão é 1.000.000)
writeBatchTimeout Aguarde o tempo para que a operação de inserção em lote seja concluída antes que ela atinja o tempo limite.
Os valores permitidos são cadeias de caracteres Timespan. Um exemplo é 00:30:00 (30 minutos).
Não (o padrão é 00:30:00)

Exemplo:

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

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 interno para copiar dados em paralelo. Você pode encontrar opções de particionamento de dados na guia Origem da atividade de cópia.

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

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 paralelo é controlado pela parallelCopies configuração 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, é 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.

Cenário Configurações sugeridas
Carga completa a partir de uma mesa grande, com divisórias físicas. Opção de partição: Partições físicas da tabela.

Durante a execução, o serviço deteta automaticamente as partições físicas e copia os dados por partições.
Carga completa a partir de uma tabela grande, sem partições físicas, enquanto com uma coluna inteira para particionamento de dados. Opções de partição: Partição de intervalo dinâmico.
Coluna de partição: especifique a coluna usada para particionar dados. Se não for especificado, a coluna de chave primária será usada.
Carregue uma grande quantidade de dados usando uma consulta personalizada, com partições físicas. Opção de partição: Partições físicas da tabela.
Consulta: SELECT * FROM ?AdfTabularPartitionName WHERE <your_additional_where_clause>.
Nome da partição: especifique o(s) nome(s) da(s) partição(ões) de onde copiar os dados. Se não for especificado, o serviço detetará automaticamente as partições físicas na tabela especificada no conjunto de dados PostgreSQL.

Durante a execução, o serviço substitui ?AdfTabularPartitionName pelo nome real da partição e envia para o Banco de Dados do Azure para PostgreSQL.
Carregue uma grande quantidade de dados usando uma consulta personalizada, sem partições físicas, enquanto com uma coluna inteira para particionamento de dados. Opções de partição: Partição de intervalo dinâmico.
Consulta: SELECT * FROM ?AdfTabularPartitionName WHERE ?AdfRangePartitionColumnName <= ?AdfRangePartitionUpbound AND ?AdfRangePartitionColumnName >= ?AdfRangePartitionLowbound AND <your_additional_where_clause>.
Coluna de partição: especifique a coluna usada para particionar dados. Você pode particionar em relação à coluna com inteiro ou tipo de dados data/data/hora.
Limite superior da partição e limite inferior da partição: especifique se deseja filtrar a coluna da partição para recuperar dados apenas entre o intervalo inferior e superior.

Durante a execução, o serviço substitui ?AdfRangePartitionColumnName, ?AdfRangePartitionUpbounde ?AdfRangePartitionLowbound com o nome da coluna real e intervalos de valores para cada partição, e envia para o Banco de Dados do Azure para PostgreSQL.
Por exemplo, se a coluna de partição "ID" estiver definida com o limite inferior como 1 e o limite superior como 80, com cópia paralela definida como 4, o serviço recuperará dados por 4 partições. Os seus IDs situam-se entre [1,20], [21, 40], [41, 60] e [61, 80], respetivamente.

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" controlar os números de partição, definir este 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 a partir de uma mesa 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 em mapeamento de fluxo de dados, você pode ler e gravar em tabelas do Banco de Dados do Azure para PostgreSQL. Para obter mais informações, consulte a transformação de origem e a transformação de coletor 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 .

Nome Descrição Obrigatório Valores permitidos Propriedade do script de fluxo de dados
Tabela Se você selecionar Tabela como entrada, o fluxo de dados buscará todos os dados da tabela especificada no conjunto de dados. Não - (apenas para conjunto de dados embutido)
tableName
Query Se você selecionar Consulta como entrada, especifique uma consulta SQL para buscar dados da origem, que substituirá qualquer tabela especificada no conjunto de dados. Usar consultas é uma ótima maneira de reduzir linhas para testes ou pesquisas.

A cláusula Order By não é suportada, 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 que você pode usar no fluxo de dados.
Exemplo de consulta: select * from mytable where customerId > 1000 and customerId < 2000 ou select * from "MyTable". Observação no PostgreSQL, o nome da entidade é tratado como insensível a maiúsculas e minúsculas se não for citado.
Não String query
Nome do esquema Se você selecionar Procedimento armazenado como entrada, especifique um nome de esquema do procedimento armazenado ou selecione Atualizar para solicitar que o serviço descubra os nomes do esquema. Não String schemaName
Procedimento armazenado Se você selecionar Procedimento armazenado como entrada, especifique um nome do procedimento armazenado para ler dados da tabela de origem ou selecione Atualizar para solicitar que o serviço descubra os nomes dos procedimentos. Sim (se você selecionar Procedimento armazenado como entrada) String nome_procedimento
Parâmetros do procedimento Se você selecionar Procedimento armazenado como entrada, especifique quaisquer parâmetros de entrada para o procedimento armazenado na ordem definida no procedimento ou selecione Importar para importar todos os parâmetros do procedimento usando o formulário @paraName. Não Matriz Insumos
Tamanho do lote Especifique um tamanho de lote para fragmentar dados grandes em lotes. Não Número inteiro batchSize
Nível de isolamento Escolha um dos seguintes níveis de isolamento:
- Ler Comprometido
- Ler Não confirmado (padrão)
- Leitura repetível
- Serializável
- Nenhum (ignorar o nível de isolamento)
Não READ_COMMITTED
READ_UNCOMMITTED
REPEATABLE_READ
SERIALIZÁVEL
NENHUM
Nível de isolamento

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 na guia Opções do coletor .

Nome Descrição Obrigatório Valores permitidos Propriedade do script de fluxo de dados
Método de atualização Especifique quais operações são permitidas no destino do banco de dados. O padrão é permitir apenas inserções.
Para atualizar, atualizar ou excluir linhas, uma transformação de linha Alter é necessária para marcar linhas para essas ações.
Sim true ou false suprimido
inserível
atualizável
Atualizável
Colunas-chave Para atualizações, upserts e exclusões, a(s) coluna(s) chave(s) deve(m) ser definida(s) para determinar qual linha alterar.
O nome da coluna que você escolher como a chave será usado como parte da atualização subsequente, upsert, excluir. Portanto, você deve escolher uma coluna que existe no mapeamento de coletor.
Não Matriz chaves
Ignorar colunas de teclas de escrita Se desejar não escrever o valor na coluna de chave, selecione "Ignorar colunas de chave de escrita". Não true ou false skipKeyEscreve
Ação da tabela Determina se todas as linhas da tabela de destino devem ser recriadas ou removidas antes da gravação.
- Nenhuma: Nenhuma ação será feita para a mesa.
- Recriar: A tabela será descartada e recriada. Necessário se criar uma nova tabela dinamicamente.
- Truncate: Todas as linhas da tabela de destino serão removidas.
Não true ou false recriar
truncate
Tamanho do lote Especifique quantas linhas estão sendo escritas em cada lote. 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. Não Número inteiro batchSize
Selecionar esquema de banco de dados do usuário Por padrão, uma tabela temporária será criada sob o esquema de coletor como preparação. Como alternativa, você pode desmarcar a opção Usar esquema de coletor e, em vez disso, especificar um nome de esquema sob o qual o Data Factory criará uma tabela de preparo para carregar dados upstream e limpá-los automaticamente após a conclusão. Verifique se você tem permissão para criar tabela no banco de dados e alterar permissão no esquema. Não String stagingSchemaName
Scripts pré e pós SQL Especifique scripts SQL de várias linhas que serão executados antes (pré-processamento) e depois que os dados (pós-processamento) forem gravados no banco de dados do coletor. Não String pré-SQLs
postSQLs

Gorjeta

  1. Recomenda-se quebrar scripts de lote único com vários comandos em vários lotes.
  2. Somente instruções DDL (Data Definition Language) e DML (Data Manipulation Language) que retornam uma contagem de atualização simples podem ser executadas como parte de um lote. Saiba mais em Executando operações em lote
  • Habilitar extração incremental: use esta opção para informar ao ADF para processar apenas linhas que foram alteradas desde a última vez que o pipeline foi executado.

  • 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.

  • 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.

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

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

Propriedades da atividade de pesquisa

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

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