Kopiera och transformera data i Azure Database for PostgreSQL med Hjälp av Azure Data Factory eller Synapse Analytics

GÄLLER FÖR: Azure Data Factory Azure Synapse Analytics

Dricks

Prova Data Factory i Microsoft Fabric, en allt-i-ett-analyslösning för företag. Microsoft Fabric omfattar allt från dataflytt till datavetenskap, realtidsanalys, business intelligence och rapportering. Lär dig hur du startar en ny utvärderingsversion kostnadsfritt!

Den här artikeln beskriver hur du använder kopieringsaktivitet i Azure Data Factory- och Synapse Analytics-pipelines för att kopiera data från och till Azure Database for PostgreSQL och använder Dataflöde för att transformera data i Azure Database for PostgreSQL. Mer information finns i introduktionsartiklarna för Azure Data Factory och Synapse Analytics.

Den här anslutningsappen är specialiserad för Azure Database for PostgreSQL-tjänsten. Om du vill kopiera data från en allmän PostgreSQL-databas som finns lokalt eller i molnet använder du PostgreSQL-anslutningsappen.

Funktioner som stöds

Den här Azure Database for PostgreSQL-anslutningsappen stöds för följande funktioner:

Funktioner som stöds IR Hanterad privat slutpunkt
aktiviteten Kopiera (källa/mottagare) ① ②
Mappa dataflöde (källa/mottagare)
Sökningsaktivitet ① ②

(1) Azure Integration Runtime (2) Lokalt installerad integrationskörning

De tre aktiviteterna fungerar på alla distributionsalternativ för Azure Database for PostgreSQL:

Komma igång

Om du vill utföra aktiviteten Kopiera med en pipeline kan du använda något av följande verktyg eller SDK:er:

Skapa en länkad tjänst till Azure Database for PostgreSQL med hjälp av användargränssnittet

Använd följande steg för att skapa en länkad tjänst till Azure Database for PostgreSQL i Användargränssnittet för Azure-portalen.

  1. Bläddra till fliken Hantera i Din Azure Data Factory- eller Synapse-arbetsyta och välj Länkade tjänster och klicka sedan på Ny:

  2. Sök efter PostgreSQL och välj Azure-databasen för PostgreSQL-anslutningsprogrammet.

    Select the Azure database for PostgreSQL connector.

  3. Konfigurera tjänstinformationen, testa anslutningen och skapa den nya länkade tjänsten.

    Configure a linked service to Azure database for PostgreSQL.

Anslut eller konfigurationsinformation

Följande avsnitt innehåller information om egenskaper som används för att definiera Data Factory-entiteter som är specifika för Azure Database for PostgreSQL-anslutningstjänsten.

Länkade tjänstegenskaper

Följande egenskaper stöds för den länkade tjänsten Azure Database for PostgreSQL:

Property Beskrivning Obligatoriskt
type Typegenskapen måste anges till: AzurePostgreSql. Ja
Connectionstring En ODBC-anslutningssträng för att ansluta till Azure Database for PostgreSQL.
Du kan också placera ett lösenord i Azure Key Vault och hämta konfigurationen password från anslutningssträng. Mer information finns i följande exempel och Lagra autentiseringsuppgifter i Azure Key Vault .
Ja
connectVia Den här egenskapen representerar den integrationskörning som ska användas för att ansluta till datalagret. Du kan använda Azure Integration Runtime eller lokalt installerad integrationskörning (om ditt datalager finns i ett privat nätverk). Om den inte anges använder den standardkörningen för Azure-integrering. Nej

En typisk anslutningssträng är Server=<server>.postgres.database.azure.com;Database=<database>;Port=<port>;UID=<username>;Password=<Password>. Här är fler egenskaper som du kan ange per ditt ärende:

Property beskrivning Alternativ Obligatoriskt
EncryptionMethod (EM) Den metod som drivrutinen använder för att kryptera data som skickas mellan drivrutinen och databasservern. Till exempel: EncryptionMethod=<0/1/6>; 0 (Ingen kryptering) (standard) / 1 (SSL) / 6 (RequestSSL) Nej
ValidateServerCertificate (VSC) Avgör om drivrutinen validerar certifikatet som skickas av databasservern när SSL-kryptering är aktiverat (krypteringsmetod=1). Till exempel: ValidateServerCertificate=<0/1>; 0 (inaktiverad) (standard) /1 (aktiverad) Nej

Exempel:

{
    "name": "AzurePostgreSqlLinkedService",
    "properties": {
        "type": "AzurePostgreSql",
        "typeProperties": {
            "connectionString": "Server=<server>.postgres.database.azure.com;Database=<database>;Port=<port>;UID=<username>;Password=<Password>"
        }
    }
}

Exempel:

Lagra lösenord i Azure Key Vault

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

Egenskaper för datauppsättning

En fullständig lista över avsnitt och egenskaper som är tillgängliga för att definiera datauppsättningar finns i Datauppsättningar. Det här avsnittet innehåller en lista över egenskaper som Azure Database for PostgreSQL stöder i datauppsättningar.

Om du vill kopiera data från Azure Database for PostgreSQL anger du datauppsättningens typegenskap till AzurePostgreSqlTable. Följande egenskaper stöds:

Property Beskrivning Obligatoriskt
type Typegenskapen för datamängden måste anges till AzurePostgreSqlTable Ja
tableName Namn på tabellen Nej (om "fråga" i aktivitetskällan har angetts)

Exempel:

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

Kopiera egenskaper för aktivitet

En fullständig lista över avsnitt och egenskaper som är tillgängliga för att definiera aktiviteter finns i Pipelines och aktiviteter. Det här avsnittet innehåller en lista över egenskaper som stöds av en Azure Database for PostgreSQL-källa.

Azure Database for PostgreSql som källa

Om du vill kopiera data från Azure Database for PostgreSQL anger du källtypen i kopieringsaktiviteten till AzurePostgreSqlSource. Följande egenskaper stöds i avsnittet kopieringsaktivitetskälla:

Property Beskrivning Obligatoriskt
type Typegenskapen för kopieringsaktivitetskällan måste anges till AzurePostgreSqlSource Ja
query Använd den anpassade SQL-frågan för att läsa data. Till exempel: SELECT * FROM mytable eller SELECT * FROM "MyTable". Obs! I PostgreSQL behandlas entitetsnamnet som skiftlägesokänsligt om det inte anges. Nej (om egenskapen tableName i datauppsättningen har angetts)
partitionOptions Anger de datapartitioneringsalternativ som används för att läsa in data från Azure SQL Database.
Tillåtna värden är: Ingen (standard), PhysicalPartitionsOfTable och DynamicRange.
När ett partitionsalternativ är aktiverat (dvs. inte None) styrs graden av parallellitet för samtidig inläsning av data från en Azure SQL Database av parallelCopies inställningen för kopieringsaktiviteten.
Nej
partition Inställningar Ange gruppen med inställningarna för datapartitionering.
Använd när partitionsalternativet inte Noneär .
Nej
Under partitionSettings:
partitionNames Listan över fysiska partitioner som måste kopieras.
Använd när partitionsalternativet är PhysicalPartitionsOfTable. Om du använder en fråga för att hämta källdata kopplar ?AdfTabularPartitionName du in WHERE-satsen. Ett exempel finns i avsnittet Parallell kopia från Azure Database for PostgreSQL .
Nej
partitionColumnName Ange namnet på källkolumnen i heltal eller datum/datetime-typ (int, , bigintsmallint, date, timestamp without time zonetimestamp with time zone eller time without time zone) som ska användas av intervallpartitionering för parallell kopiering. Om den inte anges identifieras den primära nyckeln i tabellen automatiskt och används som partitionskolumn.
Använd när partitionsalternativet är DynamicRange. Om du använder en fråga för att hämta källdata kopplar ?AdfRangePartitionColumnName du in WHERE-satsen. Ett exempel finns i avsnittet Parallell kopia från Azure Database for PostgreSQL .
Nej
partitionUpperBound Det maximala värdet för partitionskolumnen för att kopiera ut data.
Använd när partitionsalternativet är DynamicRange. Om du använder en fråga för att hämta källdata kopplar ?AdfRangePartitionUpbound du in WHERE-satsen. Ett exempel finns i avsnittet Parallell kopia från Azure Database for PostgreSQL .
Nej
partitionLowerBound Det minsta värdet för partitionskolumnen för att kopiera ut data.
Använd när partitionsalternativet är DynamicRange. Om du använder en fråga för att hämta källdata kopplar ?AdfRangePartitionLowbound du in WHERE-satsen. Ett exempel finns i avsnittet Parallell kopia från Azure Database for PostgreSQL .
Nej

Exempel:

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

Azure Database for PostgreSQL som mottagare

Om du vill kopiera data till Azure Database for PostgreSQL stöds följande egenskaper i avsnittet kopieringsaktivitetsmottagare:

Property Beskrivning Obligatoriskt
type Typegenskapen för kopieringsaktivitetsmottagaren måste anges till AzurePostgreSQLSink. Ja
preCopyScript Ange en SQL-fråga för kopieringsaktiviteten som ska köras innan du skriver data till Azure Database for PostgreSQL i varje körning. Du kan använda den här egenskapen för att rensa inlästa data. Nej
writeMethod Den metod som används för att skriva data till Azure Database for PostgreSQL.
Tillåtna värden är: CopyCommand (standard, vilket är mer högpresterande), BulkInsert.
Nej
writeBatchSize Antalet rader som läses in i Azure Database for PostgreSQL per batch.
Tillåtet värde är ett heltal som representerar antalet rader.
Nej (standardvärdet är 1 000 000)
writeBatchTimeout Vänta tills batchinfogningsåtgärden har slutförts innan tidsgränsen uppnås.
Tillåtna värden är Tidsintervallsträngar. Ett exempel är 00:30:00 (30 minuter).
Nej (standardvärdet är 00:30:00)

Exempel:

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

Parallell kopia från Azure Database for PostgreSQL

Azure Database for PostgreSQL-anslutningsappen i kopieringsaktiviteten tillhandahåller inbyggd datapartitionering för att kopiera data parallellt. Du hittar alternativ för datapartitionering på fliken Källa i kopieringsaktiviteten.

Screenshot of partition options

När du aktiverar partitionerad kopiering kör kopieringsaktiviteten parallella frågor mot din Azure Database for PostgreSQL-källa för att läsa in data efter partitioner. Den parallella graden styrs av parallelCopies inställningen för kopieringsaktiviteten. Om du till exempel anger parallelCopies till fyra genererar och kör tjänsten samtidigt fyra frågor baserat på det angivna partitionsalternativet och inställningarna, och varje fråga hämtar en del data från Azure Database for PostgreSQL.

Du rekommenderas att aktivera parallell kopiering med datapartitionering, särskilt när du läser in stora mängder data från din Azure Database for PostgreSQL. Följande är föreslagna konfigurationer för olika scenarier. När du kopierar data till filbaserat datalager rekommenderar vi att du skriver till en mapp som flera filer (anger endast mappnamn), i vilket fall prestandan är bättre än att skriva till en enda fil.

Scenario Föreslagna inställningar
Full belastning från en stor tabell med fysiska partitioner. Partitionsalternativ: Fysiska partitioner i tabellen.

Under körningen identifierar tjänsten automatiskt de fysiska partitionerna och kopierar data efter partitioner.
Full belastning från stor tabell, utan fysiska partitioner, medan med en heltalskolumn för datapartitionering. Partitionsalternativ: Partition med dynamiskt intervall.
Partitionskolumn: Ange den kolumn som används för att partitionera data. Om den inte anges används primärnyckelkolumnen.
Läs in en stor mängd data med hjälp av en anpassad fråga med fysiska partitioner. Partitionsalternativ: Fysiska partitioner i tabellen.
Fråga: SELECT * FROM ?AdfTabularPartitionName WHERE <your_additional_where_clause>.
Partitionsnamn: Ange partitionsnamnen som du vill kopiera data från. Om det inte anges identifierar tjänsten automatiskt de fysiska partitionerna i tabellen som du angav i PostgreSQL-datauppsättningen.

Under körningen ersätter tjänsten med det faktiska partitionsnamnet ?AdfTabularPartitionName och skickar till Azure Database for PostgreSQL.
Läs in en stor mängd data med hjälp av en anpassad fråga, utan fysiska partitioner, medan du har en heltalskolumn för datapartitionering. Partitionsalternativ: Partition med dynamiskt intervall.
Fråga: SELECT * FROM ?AdfTabularPartitionName WHERE ?AdfRangePartitionColumnName <= ?AdfRangePartitionUpbound AND ?AdfRangePartitionColumnName >= ?AdfRangePartitionLowbound AND <your_additional_where_clause>.
Partitionskolumn: Ange den kolumn som används för att partitionera data. Du kan partitionera mot kolumnen med heltal eller datatyp för datum/datetime.
Partitionens övre gräns och partitionens nedre gräns: Ange om du vill filtrera mot partitionskolumnen för att hämta data endast mellan det nedre och det övre intervallet.

Under körningen ersätter ?AdfRangePartitionColumnNametjänsten , ?AdfRangePartitionUpboundoch ?AdfRangePartitionLowbound med det faktiska kolumnnamnet och värdeintervallen för varje partition och skickar till Azure Database for PostgreSQL.
Om till exempel partitionskolumnen "ID" har angetts med den nedre gränsen som 1 och den övre gränsen som 80, med parallell kopiering inställd som 4, hämtar tjänsten data med 4 partitioner. Deras ID:n är mellan [1,20], [21, 40], [41, 60] respektive [61, 80].

Metodtips för att läsa in data med partitionsalternativet:

  1. Välj distinkt kolumn som partitionskolumn (till exempel primärnyckel eller unik nyckel) för att undvika datasnedvridning.
  2. Om tabellen har inbyggd partition använder du partitionsalternativet "Fysiska partitioner av tabellen" för att få bättre prestanda.
  3. Om du använder Azure Integration Runtime för att kopiera data kan du ange större "Dataintegration Units (DIU)" (>4) för att använda mer databehandlingsresurser. Kontrollera tillämpliga scenarier där.
  4. "Grad av kopieringsparallellitet" styr partitionsnumren, anger det här talet för stort ibland skadar prestandan, rekommenderar att du anger det här talet som (DIU eller antalet lokalt installerade IR-noder) * (2 till 4).

Exempel: fullständig belastning från en stor tabell med fysiska partitioner

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

Exempel: fråga med partition för dynamiskt intervall

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

Mappa dataflödesegenskaper

När du transformerar data i dataflödet för mappning kan du läsa och skriva till tabeller från Azure Database for PostgreSQL. Mer information finns i källtransformering och mottagartransformation i mappning av dataflöden. Du kan välja att använda en Azure Database for PostgreSQL-datauppsättning eller en infogad datauppsättning som käll- och mottagartyp.

Källtransformering

I tabellen nedan visas de egenskaper som stöds av Azure Database for PostgreSQL-källan. Du kan redigera dessa egenskaper på fliken Källalternativ .

Name beskrivning Obligatoriskt Tillåtna värden Egenskap för dataflödesskript
Register Om du väljer Tabell som indata hämtar dataflödet alla data från tabellen som anges i datauppsättningen. Nej - (endast för infogad datamängd)
tableName
Fråga Om du väljer Fråga som indata anger du en SQL-fråga för att hämta data från källan, vilket åsidosätter alla tabeller som du anger i datauppsättningen. Att använda frågor är ett bra sätt att minska antalet rader för testning eller sökningar.

Order By-satsen stöds inte, men du kan ange en fullständig SELECT FROM-instruktion. Du kan också använda användardefinierade tabellfunktioner. select * from udfGetData() är en UDF i SQL som returnerar en tabell som du kan använda i dataflödet.
Frågeexempel: select * from mytable where customerId > 1000 and customerId < 2000 eller select * from "MyTable". Obs! I PostgreSQL behandlas entitetsnamnet som skiftlägesokänsligt om det inte anges.
Nej String query
Schemanamn Om du väljer Lagrad procedur som indata anger du ett schemanamn för den lagrade proceduren eller väljer Uppdatera för att be tjänsten att identifiera schemanamnen. Nej String schemaName
Lagrad procedur Om du väljer Lagrad procedur som indata anger du ett namn på den lagrade proceduren för att läsa data från källtabellen eller väljer Uppdatera för att be tjänsten att identifiera procedurnamnen. Ja (om du väljer Lagrad procedur som indata) String procedureName
Procedurparametrar Om du väljer Lagrad procedur som indata anger du eventuella indataparametrar för den lagrade proceduren i orderuppsättningen i proceduren eller väljer Importera för att importera alla procedureparametrar med hjälp av formuläret @paraName. Nej Matris Ingångar
Batchstorlek Ange en batchstorlek för att segmentera stora data i batchar. Nej Integer batchSize
Isoleringsnivå Välj någon av följande isoleringsnivåer:
- Läs bekräftad
– Läs ej genererad (standard)
– Repeterbar läsning
-Serialiseras
– Ingen (ignorera isoleringsnivå)
Nej READ_COMMITTED
READ_UNCOMMITTED
REPEATABLE_READ
SERIALISERAS
INGET
isolationLevel

Exempel på Azure Database for PostgreSQL-källskript

När du använder Azure Database for PostgreSQL som källtyp är det associerade dataflödesskriptet:

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

Transformering av mottagare

I tabellen nedan visas de egenskaper som stöds av Azure Database for PostgreSQL-mottagare. Du kan redigera dessa egenskaper på fliken Alternativ för mottagare.

Name beskrivning Obligatoriskt Tillåtna värden Egenskap för dataflödesskript
Uppdatera metod Ange vilka åtgärder som tillåts på databasmålet. Standardvärdet är att endast tillåta infogningar.
För att uppdatera, öka eller ta bort rader krävs en Alter row-transformering för att tagga rader för dessa åtgärder.
Ja true eller false Deletable
infogningsbar
Uppdaterbar
upsertable
Nyckelkolumner För uppdateringar, upserts och borttagningar måste nyckelkolumner anges för att avgöra vilken rad som ska ändras.
Kolumnnamnet som du väljer som nyckel används som en del av den efterföljande uppdateringen, upsert, delete. Därför måste du välja en kolumn som finns i mappningen mottagare.
Nej Matris keys
Hoppa över att skriva nyckelkolumner Om du inte vill skriva värdet till nyckelkolumnen väljer du "Hoppa över att skriva nyckelkolumner". Nej true eller false skipKeyWrites
Tabellåtgärd Avgör om du vill återskapa eller ta bort alla rader från måltabellen innan du skriver.
- Ingen: Ingen åtgärd utförs i tabellen.
- Återskapa: Tabellen tas bort och återskapas. Krävs om du skapar en ny tabell dynamiskt.
- Trunkera: Alla rader från måltabellen tas bort.
Nej true eller false Återskapa
trunkera
Batchstorlek Ange hur många rader som skrivs i varje batch. Större batchstorlekar förbättrar komprimering och minnesoptimering, men riskerar att få slut på minnesfel vid cachelagring av data. Nej Integer batchSize
Välj användar-DB-schema Som standard skapas en tillfällig tabell under mottagarschemat som mellanlagring. Du kan också avmarkera alternativet Använd mottagarschema och i stället ange ett schemanamn under vilket Data Factory skapar en mellanlagringstabell för att läsa in uppströmsdata och automatiskt rensa dem när de är klara. Kontrollera att du har behörigheten skapa tabell i databasen och ändra behörigheten för schemat. Nej String stagingSchemaName
Pre- och Post SQL-skript Ange sql-skript med flera rader som ska köras före (förbearbetning) och efter att (efterbearbetning) data har skrivits till mottagardatabasen. Nej String preSQLs
postSQLs

Dricks

  1. Vi rekommenderar att du bryter enskilda batchskript med flera kommandon i flera batchar.
  2. Det går endast att köra instruktioner av typen Data Definition Language (DDL) och Data Manipulation Language (DML), som returnerar ett enda uppdateringsvärde, som del av en batch. Läs mer om att utföra batchåtgärder
  • Aktivera inkrementell extrahering: Använd det här alternativet för att be ADF att endast bearbeta rader som har ändrats sedan den senaste gången pipelinen kördes.

  • Inkrementell kolumn: När du använder funktionen för inkrementell extrahering måste du välja den datum/tid eller numeriska kolumn som du vill använda som vattenstämpel i källtabellen.

  • Börja läsa från början: Om du anger det här alternativet med inkrementellt extrahering instrueras ADF att läsa alla rader vid första körningen av en pipeline med inkrementellt extrahering aktiverat.

Exempel på Azure Database for PostgreSQL-mottagarskript

När du använder Azure Database for PostgreSQL som mottagartyp är det associerade dataflödesskriptet:

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

Egenskaper för uppslagsaktivitet

Mer information om egenskaperna finns i Sökningsaktivitet.

En lista över datalager som stöds som källor och mottagare av kopieringsaktiviteten finns i Datalager som stöds.