使用 Azure 数据工厂向/从 Azure Database for PostgreSQL 复制数据Copy data to and from Azure Database for PostgreSQL by using Azure Data Factory

适用于: Azure 数据工厂 Azure Synapse Analytics(预览版)

本文介绍了如何使用 Azure 数据工厂中的复制活动功能从 Azure Database for PostgreSQL 复制数据。This article describes how to use the copy activity feature in Azure Data Factory to copy data from Azure Database for PostgreSQL. 本文是在总体概述复制活动的 Azure 数据工厂中的复制活动的基础上编写的。It builds on the Copy activity in Azure Data Factory article, which presents a general overview of copy activity.

此连接器专用于 Azure Database for PostgreSQL 服务This connector is specialized for the Azure Database for PostgreSQL service. 若要从位于本地或云中的通用 PostgreSQL 数据库复制数据,请使用 PostgreSQL 连接器To copy data from a generic PostgreSQL database located on-premises or in the cloud, use the PostgreSQL connector.

支持的功能Supported capabilities

以下活动支持此 Azure Database for PostgreSQL 连接器:This Azure Database for PostgreSQL connector is supported for the following activities:

可将数据从 Azure Database for PostgreSQL 复制到任何支持的接收器数据存储。You can copy data from Azure Database for PostgreSQL to any supported sink data store. 或者,可将数据从任何支持的源数据存储复制到 Azure Database for PostgreSQL。Or, you can copy data from any supported source data store to Azure Database for PostgreSQL. 有关复制活动支持作为源和接收器的数据存储的列表,请参阅支持的数据存储表。For a list of data stores that the copy activity supports as sources and sinks, see the Supported data stores table.

Azure 数据工厂提供内置驱动程序以启用连接。Azure Data Factory provides a built-in driver to enable connectivity. 因此,无需手动安装驱动程序即可使用此连接器。Therefore, you don't need to manually install any driver to use this connector.

入门Getting started

若要使用管道执行复制活动,可以使用以下工具或 SDK 之一:To perform the Copy activity with a pipeline, you can use one of the following tools or SDKs:

对于特定于 Azure Database for PostgreSQL 连接器的数据工厂实体,以下部分提供有关用于定义这些实体的属性的详细信息。The following sections offer details about properties that are used to define Data Factory entities specific to Azure Database for PostgreSQL connector.

链接服务属性Linked service properties

Azure Database for PostgreSQL 链接服务支持以下属性:The following properties are supported for the Azure Database for PostgreSQL linked service:

属性Property 说明Description 必须Required
typetype type 属性必须设置为:AzurePostgreSqlThe type property must be set to: AzurePostgreSql. Yes
connectionStringconnectionString 用于连接到 Azure Database for PostgreSQL 的 ODBC 连接字符串。An ODBC connection string to connect to Azure Database for PostgreSQL.
还可以将密码放在 Azure 密钥保管库中,并从连接字符串中拉取 password 配置。You can also put a password in Azure Key Vault and pull the password configuration out of the connection string. 有关更多详细信息,请参阅以下示例和在 Azure 密钥保管库中存储凭据See the following samples and Store credentials in Azure Key Vault for more details.
Yes
connectViaconnectVia 此属性表示用于连接到数据存储的集成运行时This property represents the integration runtime to be used to connect to the data store. 如果数据存储位于专用网络,则可以使用 Azure Integration Runtime 或自承载集成运行时。You can use Azure Integration Runtime or Self-hosted Integration Runtime (if your data store is located in private network). 如果未指定,则使用默认 Azure Integration Runtime。If not specified, it uses the default Azure Integration Runtime. No

典型的连接字符串为 Server=<server>.postgres.database.chinacloudapi.cn;Database=<database>;Port=<port>;UID=<username>;Password=<Password>A typical connection string is Server=<server>.postgres.database.chinacloudapi.cn;Database=<database>;Port=<port>;UID=<username>;Password=<Password>. 以下是你可以根据具体情况设置的更多属性:Here are more properties you can set per your case:

属性Property 说明Description 选项Options 必须Required
EncryptionMethod (EM)EncryptionMethod (EM) 驱动程序用于加密在驱动程序和数据库服务器之间发送的数据的方法。The method the driver uses to encrypt data sent between the driver and the database server. 例如 EncryptionMethod=<0/1/6>;For example, EncryptionMethod=<0/1/6>; 0 (No Encryption) (Default) / 1 (SSL) / 6 (RequestSSL)0 (No Encryption) (Default) / 1 (SSL) / 6 (RequestSSL) No
ValidateServerCertificate (VSC)ValidateServerCertificate (VSC) 启用 SSL 加密后,确定驱动程序是否验证数据库服务器发送的证书(加密方法=1)。Determines whether the driver validates the certificate that's sent by the database server when SSL encryption is enabled (Encryption Method=1). 例如 ValidateServerCertificate=<0/1>;For example, ValidateServerCertificate=<0/1>; 0 (Disabled) (Default) / 1 (Enabled)0 (Disabled) (Default) / 1 (Enabled) No

示例Example:

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

示例Example:

在 Azure 密钥保管库中存储密码Store password in Azure Key Vault

{
    "name": "AzurePostgreSqlLinkedService",
    "properties": {
        "type": "AzurePostgreSql",
        "typeProperties": {
            "connectionString": "Server=<server>.postgres.database.chinacloudapi.cn;Database=<database>;Port=<port>;UID=<username>;",
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        }
    }
}

数据集属性Dataset properties

有关可用于定义数据集的各个部分和属性的完整列表,请参阅 Azure 数据工厂中的数据集For a full list of sections and properties available for defining datasets, see Datasets in Azure Data Factory. 本部分提供数据集中 Azure Database for PostgreSQL 支持的属性列表。This section provides a list of properties that Azure Database for PostgreSQL supports in datasets.

要从 Azure Database for PostgreSQL 复制数据,请将数据集的 type 属性设置为 AzurePostgreSqlTableTo copy data from Azure Database for PostgreSQL, set the type property of the dataset to AzurePostgreSqlTable. 支持以下属性:The following properties are supported:

属性Property 说明Description 必须Required
typetype 数据集的 type 属性必须设置为 AzurePostgreSqlTable The type property of the dataset must be set to AzurePostgreSqlTable Yes
tableNametableName 表名称Name of the table 否(如果指定了活动源中的“query”)No (if "query" in activity source is specified)

示例Example:

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

复制活动属性Copy activity properties

有关可用于定义活动的各部分和属性的完整列表,请参见 Azure 数据工厂中的管道和活动For a full list of sections and properties available for defining activities, see Pipelines and activities in Azure Data Factory. 本部分提供 Azure Database for PostgreSQL 源支持的属性列表。This section provides a list of properties supported by an Azure Database for PostgreSQL source.

用于 PostgreSql 的 Azure 数据库作为源Azure Database for PostgreSql as source

要从 Azure Database for PostgreSQL 复制数据,请将复制活动中的源类型设置为 AzurePostgreSqlSourceTo copy data from Azure Database for PostgreSQL, set the source type in the copy activity to AzurePostgreSqlSource. 复制活动source部分支持以下属性:The following properties are supported in the copy activity source section:

属性Property 说明Description 必须Required
typetype 复制活动源的 type 属性必须设置为 AzurePostgreSqlSourceThe type property of the copy activity source must be set to AzurePostgreSqlSource Yes
查询query 使用自定义 SQL 查询读取数据。Use the custom SQL query to read data. 例如: "SELECT * FROM MyTable"For example: "SELECT * FROM MyTable" 否(如果指定了数据集中的 tableName 属性)No (if the tableName property in the dataset is specified)

示例Example:

"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 作为接收器Azure Database for PostgreSQL as sink

将数据复制到 Azure Database for PostgreSQL 时,复制活动的 sink 节支持以下属性:To copy data to Azure Database for PostgreSQL, the following properties are supported in the copy activity sink section:

属性Property 说明Description 必须Required
typetype 复制活动接收器的 type 属性必须设置为 AzurePostgreSQLSinkThe type property of the copy activity sink must be set to AzurePostgreSQLSink. Yes
preCopyScriptpreCopyScript 每次运行时将数据写入 Azure Database for PostgreSQL 之前,为要执行的复制活动指定 SQL 查询。Specify a SQL query for the copy activity to execute before you write data into Azure Database for PostgreSQL in each run. 可以使用此属性清除预加载的数据。You can use this property to clean up the preloaded data. No
writeBatchSizewriteBatchSize 当缓冲区大小达到 writeBatchSize 时,会将数据插入 Azure Database for PostgreSQL 表。Inserts data into the Azure Database for PostgreSQL table when the buffer size reaches writeBatchSize.
允许的值是表示行数的整数。Allowed value is an integer that represents the number of rows.
否(默认值为 10,000)No (default is 10,000)
writeBatchTimeoutwriteBatchTimeout 超时之前等待批插入操作完成时的等待时间。Wait time for the batch insert operation to complete before it times out.
允许的值为 Timespan 字符串。Allowed values are Timespan strings. 示例为 00:30:00(30 分钟)。An example is 00:30:00 (30 minutes).
否(默认值为 00:00:30)No (default is 00:00:30)

示例Example:

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

查找活动属性Lookup activity properties

有关属性的详细信息,请参阅 Azure 数据工厂中的 Lookup 活动For more information about the properties, see Lookup activity in Azure Data Factory.

后续步骤Next steps

有关 Azure 数据工厂中复制活动支持作为源和接收器的数据存储的列表,请参阅支持的数据存储For a list of data stores supported as sources and sinks by the copy activity in Azure Data Factory, see Supported data stores.