使用 Azure 数据工厂从/向 ODBC 数据存储复制数据Copy data from and to ODBC data stores using Azure Data Factory

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

本文概述了如何在 Azure 数据工厂中使用复制活动从/向 ODBC 数据存储复制数据。This article outlines how to use the Copy Activity in Azure Data Factory to copy data from and to an ODBC data store. 它是基于概述复制活动总体的复制活动概述一文。It builds on the copy activity overview article that presents a general overview of copy activity.

支持的功能Supported capabilities

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

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

具体而言,此 ODBC 连接器支持使用 BasicAnonymous 身份验证从/向任何与 ODBC 兼容的数据存储复制数据。Specifically, this ODBC connector supports copying data from/to any ODBC-compatible data stores using Basic or Anonymous authentication. 需要 64 位 ODBC 驱动程序A 64-bit ODBC driver is required. 对于 ODBC 接收器,ADF 支持 ODBC 版本 2.0 标准版。For ODBC sink, ADF support ODBC version 2.0 standard.

先决条件Prerequisites

要使用此 ODBC 连接器,需要:To use this ODBC connector, you need to:

  • 设置自承载集成运行时。Set up a Self-hosted Integration Runtime. 有关详细信息,请参阅自承载集成运行时一文。See Self-hosted Integration Runtime article for details.
  • 在集成运行时计算机上安装数据存储的 64 位 ODBC 驱动程序。Install the 64-bit ODBC driver for the data store on the Integration Runtime machine.

入门Getting started

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

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

链接服务属性Linked service properties

ODBC 链接服务支持以下属性:The following properties are supported for ODBC linked service:

属性Property 说明Description 必须Required
typetype type 属性必须设置为:OdbcThe type property must be set to: Odbc Yes
connectionStringconnectionString 不包括凭据部分的连接字符串。The connection string excluding the credential portion. 可以使用类似 "Driver={SQL Server};Server=Server.database.chinacloudapi.cn; Database=TestDatabase;" 的模式指定连接字符串,也可以利用在 Integration Runtime 计算机上使用 "DSN=<name of the DSN on IR machine>;" 设置的系统 DSN(数据源名称)(仍需要相应地指定链接服务中的凭据部分)。You can specify the connection string with pattern like "Driver={SQL Server};Server=Server.database.chinacloudapi.cn; Database=TestDatabase;", or use the system DSN (Data Source Name) you set up on the Integration Runtime machine with "DSN=<name of the DSN on IR machine>;" (you need still specify the credential portion in linked service accordingly).
还可以将密码放在 Azure 密钥保管库中,并从连接字符串中拉取  password  配置。You can also put a password in Azure Key Vault and pull the password configuration out of the connection string. 有关更多详细信息,请参阅 在 Azure 密钥保管库中存储凭据 。 Refer to Store credentials in Azure Key Vault with more details.
Yes
authenticationTypeauthenticationType 用于连接 ODBC 数据存储的身份验证类型。Type of authentication used to connect to the ODBC data store.
允许值包括:BasicAnonymousAllowed values are: Basic and Anonymous.
Yes
userNameuserName 如果使用基本身份验证,请指定用户名。Specify user name if you are using Basic authentication. No
passwordpassword 指定为 userName 指定的用户帐户的密码。Specify password for the user account you specified for the userName. 将此字段标记为 SecureString 以安全地将其存储在数据工厂中或引用存储在 Azure Key Vault 中的机密Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault. No
credentialcredential 连接字符串的访问凭据部分,采用特定于驱动程序的属性值格式指定。The access credential portion of the connection string specified in driver-specific property-value format. 示例:"RefreshToken=<secret refresh token>;"Example: "RefreshToken=<secret refresh token>;". 将此字段标记为 SecureString。Mark this field as a SecureString. No
connectViaconnectVia 用于连接到数据存储的集成运行时The Integration Runtime to be used to connect to the data store. 先决条件中所述,需要自承载集成运行时。A Self-hosted Integration Runtime is required as mentioned in Prerequisites. Yes

示例 1:使用 Basic 身份验证Example 1: using Basic authentication

{
    "name": "ODBCLinkedService",
    "properties": {
        "type": "Odbc",
        "typeProperties": {
            "connectionString": "<connection string>",
            "authenticationType": "Basic",
            "userName": "<username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

示例 2:使用 Anonymous 身份验证Example 2: using Anonymous authentication

{
    "name": "ODBCLinkedService",
    "properties": {
        "type": "Odbc",
        "typeProperties": {
            "connectionString": "<connection string>",
            "authenticationType": "Anonymous",
            "credential": {
                "type": "SecureString",
                "value": "RefreshToken=<secret refresh token>;"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

数据集属性Dataset properties

有关可用于定义数据集的各部分和属性的完整列表,请参阅数据集一文。For a full list of sections and properties available for defining datasets, see the datasets article. 本部分提供 ODBC 数据集支持的属性列表。This section provides a list of properties supported by ODBC dataset.

若要从 ODBC 兼容的数据存储复制数据/将数据复制到 ODBC 兼容的数据存储,支持以下属性:To copy data from/to ODBC-compatible data store, the following properties are supported:

属性Property 说明Description 必须Required
typetype 数据集的 type 属性必须设置为:OdbcTableThe type property of the dataset must be set to: OdbcTable Yes
tableNametableName ODBC 数据存储中表的名称。Name of the table in the ODBC data store. 源为否(如果指定了活动源中的“query”);No for source (if "query" in activity source is specified);
接收器为是Yes for sink

示例Example

{
    "name": "ODBCDataset",
    "properties": {
        "type": "OdbcTable",
        "schema": [],
        "linkedServiceName": {
            "referenceName": "<ODBC linked service name>",
            "type": "LinkedServiceReference"
        },
        "typeProperties": {
            "tableName": "<table name>"
        }
    }
}

如果使用 RelationalTable 类型数据集,该数据集仍按原样受支持,但我们建议今后使用新数据集。If you were using RelationalTable typed dataset, it is still supported as-is, while you are suggested to use the new one going forward.

复制活动属性Copy activity properties

有关可用于定义活动的各部分和属性的完整列表,请参阅管道一文。For a full list of sections and properties available for defining activities, see the Pipelines article. 本部分提供 ODBC 源支持的属性列表。This section provides a list of properties supported by ODBC source.

ODBC 作为源ODBC as source

从 ODBC 兼容的数据存储复制数据时,复制活动的 source 节支持以下属性:To copy data from ODBC-compatible data store, the following properties are supported in the copy activity source section:

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

示例:Example:

"activities":[
    {
        "name": "CopyFromODBC",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<ODBC input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "OdbcSource",
                "query": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

如果使用 RelationalSource 类型源,该源仍按原样受支持,但我们建议今后使用新源。If you were using RelationalSource typed source, it is still supported as-is, while you are suggested to use the new one going forward.

ODBC 作为接收器ODBC as sink

要向与 ODBC 兼容的数据存储复制数据,请将复制活动中的接收器类型设置为“OdbcSink”。To copy data to ODBC-compatible data store, set the sink type in the copy activity to OdbcSink. 复制活动接收器部分中支持以下属性:The following properties are supported in the copy activity sink section:

属性Property 说明Description 必须Required
typetype 复制活动接收器的 type 属性必须设置为:OdbcSinkThe type property of the copy activity sink must be set to: OdbcSink Yes
writeBatchTimeoutwriteBatchTimeout 超时之前等待批插入操作完成时的等待时间。Wait time for the batch insert operation to complete before it times out.
允许的值为:timespan。Allowed values are: timespan. 示例:“00:30:00”(30 分钟)。Example: “00:30:00” (30 minutes).
No
writeBatchSizewriteBatchSize 缓冲区大小达到 writeBatchSize 时会数据插入 SQL 表。Inserts data into the SQL table when the buffer size reaches writeBatchSize.
允许的值为:整数(行数)。Allowed values are: integer (number of rows).
否(默认值为 0 - 自动检测)No (default is 0 - auto detected)
preCopyScriptpreCopyScript 每次运行时,将数据写入到数据存储之前,指定复制活动要执行的 SQL 查询。Specify a SQL query for Copy Activity to execute before writing data into data store in each run. 此属性可用于清理预先加载的数据。You can use this property to clean up the pre-loaded data. No

备注

对于“writeBatchSize”,如果未设置(自动检测),则复制活动首先检测驱动程序是否支持批处理操作,如果支持,则将其设置为 10000,否则或将其设置为 1。For "writeBatchSize", if it's not set (auto-detected), copy activity first detects whether the driver supports batch operations, and set it to 10000 if it does, or set it to 1 if it doesn’t. 如果显式设置 0 以外的值,则复制活动遵循值,如果驱动程序不支持批处理操作,则会在运行时失败。If you explicitly set the value other than 0, copy activity honors the value and fails at runtime if the driver doesn’t support batch operations.

示例:Example:

"activities":[
    {
        "name": "CopyToODBC",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<ODBC output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "OdbcSink",
                "writeBatchSize": 100000
            }
        }
    }
]

Lookup 活动属性Lookup activity properties

若要了解有关属性的详细信息,请查看 Lookup 活动To learn details about the properties, check Lookup activity.

解决连接问题Troubleshoot connectivity issues

若要解决连接问题,请使用“集成运行时配置管理器”的“诊断”选项卡。To troubleshoot connection issues, use the Diagnostics tab of Integration Runtime Configuration Manager.

  1. 启动集成运行时配置管理器。Launch Integration Runtime Configuration Manager.
  2. 切换到“诊断”选项卡。Switch to the Diagnostics tab.
  3. 在“测试连接”部分下,选择数据存储的“类型”(链接服务)。Under the "Test Connection" section, select the type of data store (linked service).
  4. 指定用于连接数据存储的“连接字符串”,选择“身份验证”并输入“用户名”、“密码”和/或“凭据”。Specify the connection string that is used to connect to the data store, choose the authentication and enter user name, password, and/or credentials.
  5. 单击“测试连接”以测试数据存储的连接。Click Test connection to test the connection to the data store.

后续步骤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.