Copiare e trasformare i dati in Database di Azure per PostgreSQL usando Azure Data Factory o Synapse Analytics

APPLICABILE A: Azure Data Factory Azure Synapse Analytics

Questo articolo illustra come usare l'attività di copia nelle pipeline di Azure Data Factory e Synapse Analytics per copiare dati da e in Database di Azure per PostgreSQL. E come usare Flusso di dati per trasformare i dati in Database di Azure per PostgreSQL. Per altre informazioni, leggere gli articoli introduttivi per Azure Data Factory e Synapse Analytics.

Questo connettore è specializzato per il servizio Database di Azure per PostgreSQL. Per copiare i dati da un database PostgreSQL generico che si trova in locale o nel cloud, usare il connettore PostgreSQL.

Funzionalità supportate

Questo connettore Database di Azure per PostgreSQL è supportato per le funzionalità seguenti:

(1) Runtime di integrazione Azure (2) Runtime di integrazione self-hosted

Le tre attività funzionano su Database di Azure per PostgreSQL Single Server, Flexible Server e Azure Cosmos DB per PostgreSQL.

Iniziare

Per eseguire l'attività Copy con una pipeline, è possibile usare uno degli strumenti o degli SDK seguenti:

• Strumento Copia dati
• portale Azure
• .NET SDK
• Python SDK
• Azure PowerShell
• API REST
• modello Azure Resource Manager

Creare un servizio collegato per Database di Azure per PostgreSQL usando l'interfaccia utente

Usare la procedura seguente per creare un servizio collegato per Azure database per PostgreSQL nell'interfaccia utente del portale di Azure.

1. Passare alla scheda Gestisci nell'area di lavoro Azure Data Factory o Synapse e selezionare Servizi collegati, quindi selezionare Nuovo:

   • Azure Data Factory
   • Azure Synapse

   

2. Cercare PostgreSQL e selezionare il connettore Azure database per PostgreSQL.

   

3. Configurare i dettagli del servizio, testare la connessione e creare il nuovo servizio collegato.

   

Dettagli di configurazione del connettore

Le sezioni seguenti offrono informazioni dettagliate sulle proprietà usate per definire entità di Data Factory specifiche per Database di Azure per PostgreSQL connettore.

Proprietà del servizio collegato

La versione del connettore Database di Azure per PostgreSQL 2.0 supporta le modalità Transport Layer Security (TLS) 1.3 e SSL (Secure Socket Layer). Fare riferimento a questa section per aggiornare la versione del connettore database SQL di Azure dalla versione 1.0. Per informazioni dettagliate sulla proprietà, vedere le sezioni corrispondenti.

• Versione 2.0
• Versione 1.0

Versione 2.0

Quando si applica la versione 2.0, sono supportate le proprietà seguenti per il servizio collegato Database di Azure per PostgreSQL:

Autenticazione di base

Esempio:

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

Esempio:

Store password in 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>" 
            }
        }
    }
}

Autenticazione dell'identità gestita assegnata dal sistema

Una data factory o un'area di lavoro di Synapse può essere associata a una identità gestita assegnata dal sistema che rappresenta il servizio durante l'autenticazione ad altre risorse in Azure. È possibile usare questa identità gestita per Azure database per l'autenticazione PostgreSQL. L'area di lavoro designata factory o Synapse può accedere e copiare dati da o nel database usando questa identità.

Per usare l'identità gestita assegnata dal sistema, seguire questa procedura:

1. Una data factory o un'area di lavoro di Synapse può essere associata a un'identità gestita assegnata dal sistema. Altre informazioni, Generare un'identità gestita assegnata dal sistema

2. I dati Azure per PostgreSQL con identità gestita assegnata dal sistema On.

   

3. Nella risorsa Database di Azure per PostgreSQL nella sezione Security

   1. Seleziona Autenticazione

   2. Selezionare solo autenticazione Microsoft Entra o autenticazione PostgreSQL e Microsoft Entra come metodo di autenticazione.

   3. Selezionare + Aggiungi amministratori Microsoft Entra

   4. Aggiungere l'identità gestita dal sistema per la risorsa Azure Data Factory tra gli Microsoft Entra Administrators

      

4. Configurare un database Azure per il servizio collegato PostgreSQL.

Esempio:

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

Autenticazione dell'identità gestita assegnata dall'utente

Una data factory o un'area di lavoro di Synapse può essere associata a un'identità gestita assegnata dall'< utente> che rappresenta il servizio durante l'autenticazione ad altre risorse in Azure. È possibile usare questa identità gestita per Azure database per l'autenticazione PostgreSQL. L'area di lavoro designata factory o Synapse può accedere e copiare dati da o nel database usando questa identità.

Per usare l'autenticazione dell'identità gestita assegnata dall'utente, oltre alle proprietà generiche descritte nella sezione precedente, specificare le proprietà seguenti:

È anche necessario seguire i passaggi seguenti:

1. Assicurarsi di creare una risorsa Identity gestita assegnata dall'utente nel portale di Azure. Per altre informazioni, vedere Gestire le identità gestite assegnate dall'utente

2. Assegna l'Identità Gestita Assegnata dall'Utente User-assigned Managed Identity alla risorsa del database di Azure per PostgreSQL

   1. Nella risorsa del database Azure per il server PostgreSQL, sotto Security

   2. Seleziona Autenticazione

   3. Verificare se il metodo di autenticazione è autenticazione solo Microsoft Entra o autenticazione Microsoft Entra e PostgreSQL

   4. Selezionare il collegamento + Aggiungi amministratori Microsoft Entra e quindi seleziona l'identità gestita che hai assegnato.

      

3. Assegna l'identità gestita assegnata dall'utente alla risorsa Azure Data Factory

   1. Selezionare Impostazioni e quindi Identità gestite

   2. Nella scheda Assegnata dall'utente . Selezionare il collegamento + Aggiungi e selezionare l'identità gestita dall'utente

      

4. Configurare un database Azure per il servizio collegato PostgreSQL.

Esempio:

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

Autenticazione di un'entità servizio

Esempio:

chiave dell'entità servizio

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

Esempio:

Certificato dell'entità servizio

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

Versione 1.0

Quando si applica la versione 1.0, sono supportate le proprietà seguenti per il servizio collegato Database di Azure per PostgreSQL:

Un stringa di connessione tipico è host=<server>.postgres.database.azure.com;database=<database>;port=<port>;uid=<username>;password=<password>. Altre proprietà che è possibile impostare in base al caso sono riportate qui di seguito:

Esempio:

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

Esempio:

Store password in 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>" 
            }
        }
    }
}

Proprietà del set di dati

Per un elenco completo delle sezioni e delle proprietà disponibili per la definizione dei set di dati, vedere Set di dati. In questa sezione viene fornito un elenco di proprietà supportate Database di Azure per PostgreSQL nei set di dati.

Per copiare dati da Database di Azure per PostgreSQL, impostare la proprietà type del set di dati su AzurePostgreSqlTable. Sono supportate le proprietà seguenti:

Esempio:

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

proprietà dell'attività di copia

Per un elenco completo delle sezioni e delle proprietà disponibili per la definizione delle attività, vedere Pipeline e attività. In questa sezione viene fornito un elenco delle proprietà supportate da un'origine del database di Azure per PostgreSQL.

Azure Database per PostgreSQL come origine

Per copiare dati da Database di Azure per PostgreSQL, impostare il tipo di origine nell'attività di copia su AzurePostgreSqlSource. Nella sezione origine dell'attività di copia sono supportate le proprietà seguenti:

Esempio:

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

Database di Azure per PostgreSQL come destinazione

Per copiare dati in Database di Azure per PostgreSQL, impostare il tipo di sink nell'attività di copia su SqlSink. Nella sezione sink dell'attività di copia sono supportate le proprietà seguenti:

Esempio 1: Comando Copia

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

Esempio 2: Eseguire l'upsert dei dati

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

Aggiornare o inserire i dati

attività Copy supporta in modo nativo le operazioni upsert. Per eseguire un upsert, l'utente deve specificare le colonne chiave che sono chiavi primarie o colonne univoche. Se l'utente non fornisce le colonne chiave, vengono usate le colonne chiave primaria nella tabella sink. L'attività di copia aggiornerà le colonne non chiave nella tabella sink in cui i valori della colonna chiave corrispondono a quelli nella tabella di origine; in caso contrario, inserirà nuovi dati.

Copia parallela da Database di Azure per PostgreSQL

Il connettore Database di Azure per PostgreSQL nell'attività di copia fornisce il partizionamento dei dati predefinito per copiare i dati in parallelo. È possibile trovare le opzioni di partizionamento dei dati nella tabella Origine dell'attività di copia.

Quando si abilita la copia partizionata, l'attività di copia esegue query parallele sull'origine Database di Azure per PostgreSQL per caricare i dati in base alle partizioni. Il grado di parallelismo è controllato dall'impostazione parallelCopies sull'attività di copia. Ad esempio, se si imposta parallelCopies su quattro, il servizio genera e esegue simultaneamente quattro query in base all'opzione e alle impostazioni di partizione specificate e ogni query recupera una parte di dati dal Database di Azure per PostgreSQL.

È consigliabile abilitare la copia parallela con il partizionamento dei dati, soprattutto quando si caricano grandi quantità di dati dal Database di Azure per PostgreSQL. Di seguito sono riportate le configurazioni consigliate per i diversi scenari: Quando si copiano dati in un archivio dati basato su file, è consigliabile scrivere in una cartella come più file (specificare solo il nome della cartella), nel qual caso le prestazioni sono migliori rispetto alla scrittura in un singolo file.

Procedure consigliate per il caricamento di dati con opzione partizione:

1. Scegliere una colonna distintiva come colonna partizione (ad esempio, chiave primaria o chiave univoca) per evitare l'asimmetria dei dati.
2. Se la tabella include una partizione predefinita, usare l'opzione di partizione "Partizioni fisiche della tabella" per ottenere prestazioni migliori.
3. Se si usa Azure Integration Runtime per copiare dati, è possibile impostare "Data Integration Units (DIU)" (>4) per usare più risorse di calcolo. Controllare gli scenari applicabili.
4. "Grado di parallelismo di copia" controlla i numeri di partizione, impostando questo numero troppo grande qualche tempo fa male alle prestazioni. È consigliabile impostare questo numero come (DIU o numero di nodi del runtime di integrazione self-hosted) * (da 2 a 4).

Esempio: caricamento completo da una tabella di grandi dimensioni con partizioni fisiche

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

Esempio: query con partizionamento per intervalli dinamici

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

Proprietà di mappatura del flusso di dati

Quando si trasformano i dati nel flusso di dati di mappatura, è possibile leggere e scrivere su tabelle di Azure Database per PostgreSQL. Per altre informazioni, vedere la trasformazione origine e la trasformazione sink nei flussi di dati per mapping. È possibile scegliere di usare un insieme di dati Database di Azure per PostgreSQL o un insieme di dati inline come tipo di origine e destinazione.

Trasformazione della sorgente

Nella tabella seguente sono elencate le proprietà supportate da Database di Azure per PostgreSQL di origine. È possibile modificare queste proprietà nella scheda Opzioni origine.

Esempio di script sorgente per Database di Azure per PostgreSQL

Quando si usa Database di Azure per PostgreSQL come tipo di origine, lo script del flusso di dati associato è:

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

Trasformazione sink

Nella tabella seguente sono elencate le proprietà supportate da Database di Azure per PostgreSQL sink. È possibile modificare queste proprietà nella scheda Opzioni sink.

• Abilita estrazione incrementale: usare questa opzione per indicare ad ADF di elaborare solo le righe modificate dall'ultima esecuzione della pipeline.

• Colonna incrementale: quando si usa la funzionalità di estrazione incrementale, è necessario scegliere la colonna data/ora o numerica da usare come filigrana nella tabella di origine.

• Iniziare a leggere dall'inizio: l'impostazione di questa opzione con l'estrazione incrementale indica ad ADF di leggere tutte le righe alla prima esecuzione di una pipeline con l'estrazione incrementale attivata.

Esempio di script di destinazione per Database di Azure per PostgreSQL

Quando si usa Database di Azure per PostgreSQL come tipo di sink, lo script del flusso di dati associato è:

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

Attività dello script

Per altre informazioni sull'attività script, vedere Attività script.

Proprietà dell'attività Lookup

Per altre informazioni sulle proprietà, vedere Attività di Ricerca.

Aggiornare il connettore Database di Azure per PostgreSQL

Nella pagina Modifica servizio collegato selezionare 2.0 in Versione e configurare il servizio collegato facendo riferimento alle proprietà del servizio collegato versione 2.0.

Contenuto correlato

Per un elenco degli archivi dati supportati dall'attività di copia come origini e destinazioni, vedere Archivi dati supportati.