你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站,请访问 https://docs.azure.cn。
使用 Azure 数据工厂或 Synapse 管道在 Azure Synapse Analytics 中复制和转换数据
适用于: Azure 数据工厂
Azure Synapse Analytics
提示
试用 Microsoft Fabric 中的数据工厂,这是一种适用于企业的一站式分析解决方案。 Microsoft Fabric 涵盖从数据移动到数据科学、实时分析、商业智能和报告的所有内容。 了解如何免费开始新的试用!
本文概述了如何使用 Azure 数据工厂或 Synapse 管道中的复制活动从/向 Azure Synapse Analytics 复制数据,以及如何使用数据流转换 Azure Data Lake Storage Gen2 中的数据。 若要了解 Azure 数据工厂,请阅读介绍性文章。
支持的功能
此 Azure Synapse Analytics 连接器支持以下功能:
支持的功能 | IR | 托管专用终结点 |
---|---|---|
复制活动(源/接收器) | ① ② | ✓ |
映射数据流源(源/接收器) | ① | ✓ |
Lookup 活动 | ① ② | ✓ |
GetMetadata 活动 | ① ② | ✓ |
脚本活动 | ① ② | ✓ |
存储过程活动 | ① ② | ✓ |
① Azure 集成运行时 ② 自承载集成运行时
对于复制活动,此 Azure Synapse Analytics 连接器支持以下功能:
- 使用 SQL 身份验证和 Microsoft Entra 应用程序令牌身份验证通过服务主体或 Azure 资源托管标识来复制数据。
- 作为源,使用 SQL 查询或存储过程检索数据。 还可选择从 Azure Synapse Analytics 源进行并行复制。有关详细信息,请参阅从 Azure Synapse Analytics 进行并行复制部分。
- 作为接收器,使用 COPY 语句 或 PolyBase 或者批量插入加载数据。 我们建议使用 COPY 语句或 PolyBase 以获得更好的复制性能。 连接器还支持使用 DISTRIBUTION = ROUND_ROBIN 基于源架构自动创建目标表(如果不存在)。
重要
如果使用 Azure Integration Runtime 复制数据,请配置服务器级防火墙规则,确保 Azure 服务可以访问逻辑 SQL 服务器。 如果使用自承载集成运行时复制数据,请将防火墙配置为允许合适的 IP 范围。 此范围包括用于连接 Azure Synapse Analytics 的计算机的 IP。
入门
提示
若要实现最佳性能,请使用 PolyBase 或 COPY 语句将数据载入 Azure Synapse Analytics。 使用 PolyBase 将数据加载到 Azure Synapse Analytics 和使用 COPY 语句将数据加载到 Azure Synapse Analytics 部分包含详细信息。 有关带有用例的演练,请参阅在不到 15 分钟的时间里通过 Azure 数据工厂将 1 TB 的数据载入 Azure Synapse Analytics。
若要使用管道执行复制活动,可以使用以下工具或 SDK 之一:
使用 UI 创建 Azure Synapse Analytics 链接服务
使用以下步骤在 Azure 门户 UI 中创建 Azure Synapse Analytics 链接服务。
浏览到 Azure 数据工厂或 Synapse 工作区中的“管理”选项卡并选择“链接服务”,然后单击“新建”:
搜索 Synapse 并选择 Azure Synapse Analytics 连接器。
配置服务详细信息、测试连接并创建新的链接服务。
连接器配置详细信息
以下部分提供了有关定义数据工厂和 Synapse 管道实体的属性的详细信息(特定于 Azure Synapse Analytics 连接器)。
链接服务属性
Azure Synapse Analytics 连接器推荐版本支持 TLS 1.3。 请参阅此部分,了解如何将 Azure Synapse Analytics 连接器版本从旧版升级。 有关属性详细信息,请参阅相应部分。
提示
从 Azure 门户为 Azure Synapse 中的无服务器 SQL 池创建链接服务时:
- 对于“帐户选择方法”,请选择“手动输入”。
- 粘贴无服务器终结点的完全限定的域名。 可以在 Azure 门户 Synapse 工作区的“概述”中找到此值,该值位于“无服务器 SQL 终结点”下的属性中。 例如,
myserver-ondemand.sql-azuresynapse.net
。 - 对于“数据库名称”,请提供无服务器 SQL 池中的数据库名称。
提示
如果遇到错误(错误代码为“UserErrorFailedToConnectToSqlServer”,且消息如“数据库的会话限制为 XXX 且已达到。”),请将 Pooling=false
添加到连接字符串中,然后重试。
建议的版本
应用推荐版本时,Azure Synapse Analytics 链接服务支持以下通用属性:
properties | 描述 | 必需 |
---|---|---|
type | type 属性必须设置为 AzureSqlDW。 | 是 |
服务器 | 要连接到的 SQL Server 实例的名称或网络地址。 | 是 |
database | 数据库的名称。 | 是 |
authenticationType | 用于身份验证的类型。 允许的值为 SQL(默认值)、ServicePrincipal、SystemAssignedManagedIdentity、UserAssignedManagedIdentity。 转到有关特定属性和先决条件的相关身份验证部分。 | 是 |
加密 | 指示客户端和服务器之间发送的所有数据是否需要 TLS 加密。 选项:必需(对于 true,默认值)/可选(对于 false)/严格。 | 否 |
trustServerCertificate | 指示在绕过用于验证信任的证书链时是否加密通道。 | 否 |
hostNameInCertificate | 验证连接的服务器证书时要使用的主机名。 如果未指定,则服务器名称用于证书验证。 | 否 |
connectVia | 用于连接到数据存储的集成运行时。 可使用 Azure 集成运行时或自承载集成运行时(如果数据存储位于专用网络)。 如果未指定,则使用默认 Azure Integration Runtime。 | 否 |
有关其他连接属性,请查看下表:
properties | 描述 | 必须 |
---|---|---|
applicationIntent | 连接到服务器时的应用程序工作负载类型。 允许的值为 ReadOnly 和 ReadWrite 。 |
否 |
connectTimeout | 在终止尝试并生成错误之前等待与服务器建立连接的时间(以秒为单位)。 | 否 |
connectRetryCount | 识别空闲连接失败后尝试的重新连接次数。 该值应为介于 0 到 255 之间的整数。 | 否 |
connectRetryInterval | 识别空闲连接失败后,每次重新连接尝试之间的时间(以秒为单位)。 该值应为介于 1 到 60 之间的整数。 | 否 |
loadBalanceTimeout | 在连接被断开之前,连接在连接池中存在的最短时间(以秒为单位)。 | 否 |
commandTimeout | 在终止尝试执行命令并生成错误之前的默认等待时间(以秒为单位)。 | 否 |
integratedSecurity | 允许的值为 true 或 false 。 指定 false 时,指示是否在连接中指定了 userName 和密码。 指定 true 时,指示当前 Windows 帐户凭据是否用于身份验证。 |
否 |
failoverPartner | 主服务器关闭时要连接到的伙伴服务器的名称或地址。 | 否 |
maxPoolSize | 特定连接的连接池中允许的最大连接数。 | 否 |
minPoolSize | 特定连接的连接池中允许的最小连接数。 | 否 |
multipleActiveResultSets | 允许的值为 true 或 false 。 指定 true 时,应用程序可维护多重活动结果集 (MARS)。 指定 false 时,应用程序必须先处理或取消从某一批处理生成的所有结果集,然后才能对该连接执行任何其他批处理。 |
否 |
multiSubnetFailover | 允许的值为 true 或 false 。 如果你的应用程序要连接到不同子网上的 AlwaysOn 可用性组 (AG),那么将此属性设置为 true 会加快检测和连接到当前活动服务器。 |
否 |
packetSize | 用于与服务器实例通信的网络数据包的大小(字节数)。 | 否 |
池 | 允许的值为 true 或 false 。 指定 true 时,连接将共用。 指定 false 时,每次请求连接时都会显式打开连接。 |
否 |
SQL 身份验证
若要使用 SQL 身份验证,除了前面部分所述的通用属性,还指定以下属性:
properties | 描述 | 必选 |
---|---|---|
userName | 用于连接到服务器的用户名。 | 是 |
password | 对应于用户名的密码。 将此字段标记为 SecureString 以安全存储它。 或者,可以引用 Azure Key Vault 中存储的机密。 | 是 |
示例:使用 SQL 身份验证
{
"name": "AzureSqlDWLinkedService",
"properties": {
"type": "AzureSqlDW",
"typeProperties": {
"server": "<name or network address of the SQL server instance>",
"database": "<database name>",
"encrypt": "<encrypt>",
"trustServerCertificate": false,
"authenticationType": "SQL",
"userName": "<user name>",
"password": {
"type": "SecureString",
"value": "<password>"
}
},
"connectVia": {
"referenceName": "<name of Integration Runtime>",
"type": "IntegrationRuntimeReference"
}
}
}
示例:Azure Key Vault 中的密码
{
"name": "AzureSqlDWLinkedService",
"properties": {
"type": "AzureSqlDW",
"typeProperties": {
"server": "<name or network address of the SQL server instance>",
"database": "<database name>",
"encrypt": "<encrypt>",
"trustServerCertificate": false,
"authenticationType": "SQL",
"userName": "<user name>",
"password": {
"type": "AzureKeyVaultSecret",
"store": {
"referenceName": "<Azure Key Vault linked service name>",
"type": "LinkedServiceReference"
},
"secretName": "<secretName>"
}
},
"connectVia": {
"referenceName": "<name of Integration Runtime>",
"type": "IntegrationRuntimeReference"
}
}
}
服务主体身份验证
要使用服务主体身份验证,除了上一部分中描述的通用属性外,还需要指定以下属性:
属性 | 描述 | 必选 |
---|---|---|
servicePrincipalId | 指定应用程序的客户端 ID。 | 是 |
servicePrincipalCredential | 服务主体凭据。 指定应用程序的密钥。 将此字段标记为 SecureString 以安全地存储它,或引用存储在 Azure Key Vault 中的机密。 | 是 |
tenant | 指定应用程序的租户信息(域名或租户 ID)。 可将鼠标悬停在 Azure 门户右上角进行检索。 | 是 |
azureCloudType | 对于服务主体身份验证,请指定 Microsoft Entra 应用程序注册到的 Azure 云环境的类型。 允许的值为 AzurePublic 、AzureChina 、AzureUsGovernment 和 AzureGermany 。 默认情况下,使用数据工厂或 Synapse 管道的云环境。 |
否 |
还需要执行以下步骤:
在 Azure 门户中创建 Microsoft Entra 应用程序。 记下应用程序名称,以及以下定义链接服务的值:
- 应用程序 ID
- 应用程序密钥
- 租户 ID
在 Azure 门户中为你的服务器预配 Microsoft Entra 管理员(如果尚未)。 Microsoft Entra 管理员可以是 Microsoft Entra 用户或 Microsoft Entra 组。 如果授予包含托管标识的组管理员角色,则可跳过步骤 3 和步骤 4。 管理员将拥有对数据库的完全访问权限。
为服务主体 创建包含的数据库用户 。 使用 SSMS 等工具连接到要从中复制数据或要将数据复制到其中的数据仓库,其 Microsoft Entra 标识至少具有 ALTER ANY USER 权限。 运行以下 T-SQL:
CREATE USER [your_application_name] FROM EXTERNAL PROVIDER;
像通常对 SQL 用户或其他用户所做的那样向服务主体授予所需的权限。 运行以下代码,或者参考此处的更多选项。 如果要使用 PolyBase 加载数据,请了解所需的数据库权限。
EXEC sp_addrolemember db_owner, [your application name];
在 Azure 数据工厂或 Synapse 工作区中配置 Azure Synapse Analytics 链接服务。
使用服务主体身份验证的链接服务示例
{
"name": "AzureSqlDWLinkedService",
"properties": {
"type": "AzureSqlDW",
"typeProperties": {
"connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;Connection Timeout=30",
"servicePrincipalId": "<service principal id>",
"servicePrincipalCredential": {
"type": "SecureString",
"value": "<application key>"
},
"tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>"
},
"connectVia": {
"referenceName": "<name of Integration Runtime>",
"type": "IntegrationRuntimeReference"
}
}
}
使用系统分配的托管标识进行 Azure 资源身份验证
可将数据工厂或 Synapse 工作区与表示资源的 Azure 资源系统分配的托管标识相关联。 可将此托管标识用于 Azure Synapse Analytics 身份验证。 指定资源可使用此标识从/向数据仓库访问和复制数据。
若要使用系统分配的托管标识身份验证,请指定上一部分所述的通用属性,然后按照以下步骤操作。
在 Azure 门户中为你的服务器预配 Microsoft Entra 管理员(如果尚未)。 Microsoft Entra 管理员可以是 Microsoft Entra 用户或 Microsoft Entra 组。 如果为具有系统分配的托管标识的组授予管理员角色,请跳过步骤 3 和 4。 管理员将拥有对数据库的完全访问权限。
为系统分配的托管标识创建包含的数据库用户。 使用 SSMS 等工具连接到要从中复制数据或要将数据复制到其中的数据仓库,其 Microsoft Entra 标识至少具有 ALTER ANY USER 权限。 运行以下 T-SQL。
CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
为系统分配的托管标识授予所需的权限,就像通常为 SQL 用户和其他用户所做的那样。 运行以下代码,或者参考此处的更多选项。 如果要使用 PolyBase 加载数据,请了解所需的数据库权限。
EXEC sp_addrolemember db_owner, [your_resource_name];
配置 Azure Synapse Analytics 链接服务。
示例:
{
"name": "AzureSqlDWLinkedService",
"properties": {
"type": "AzureSqlDW",
"typeProperties": {
"server": "<name or network address of the SQL server instance>",
"database": "<database name>",
"encrypt": "<encrypt>",
"trustServerCertificate": false,
"authenticationType": "SystemAssignedManagedIdentity"
},
"connectVia": {
"referenceName": "<name of Integration Runtime>",
"type": "IntegrationRuntimeReference"
}
}
}
用户分配的托管标识身份验证
可将数据工厂或 Synapse 工作区与表示资源的用户分配的托管标识关联。 可将此托管标识用于 Azure Synapse Analytics 身份验证。 指定资源可使用此标识从/向数据仓库访问和复制数据。
要使用用户分配的托管身份身份验证,除了上一节中描述的通用属性外,还要指定以下属性:
属性 | 描述 | 必需 |
---|---|---|
凭据 | 将用户分配的托管标识指定为凭据对象。 | 是 |
还需要执行以下步骤:
在 Azure 门户中为你的服务器预配 Microsoft Entra 管理员(如果尚未)。 Microsoft Entra 管理员可以是 Microsoft Entra 用户或 Microsoft Entra 组。 如果为具有用户分配的托管标识的组授予管理员角色,请跳过步骤 3。 管理员将拥有对数据库的完全访问权限。
为用户分配的托管标识创建包含的数据库用户。 使用 SSMS 等工具连接到要从中复制数据或要将数据复制到其中的数据仓库,其 Microsoft Entra 标识至少具有 ALTER ANY USER 权限。 运行以下 T-SQL。
CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
创建一个或多个用户分配的托管标识,并为用户分配的托管标识授予所需的权限,就像通常为 SQL 用户和其他用户所做的那样。 运行以下代码,或者参考此处的更多选项。 如果要使用 PolyBase 加载数据,请了解所需的数据库权限。
EXEC sp_addrolemember db_owner, [your_resource_name];
为数据工厂分配一个或多个用户分配的托管标识,并为每个用户分配的托管标识创建凭据。
配置 Azure Synapse Analytics 链接服务。
示例
{
"name": "AzureSqlDWLinkedService",
"properties": {
"type": "AzureSqlDW",
"typeProperties": {
"server": "<name or network address of the SQL server instance>",
"database": "<database name>",
"encrypt": "<encrypt>",
"trustServerCertificate": false,
"authenticationType": "UserAssignedManagedIdentity",
"credential": {
"referenceName": "credential1",
"type": "CredentialReference"
}
},
"connectVia": {
"referenceName": "<name of Integration Runtime>",
"type": "IntegrationRuntimeReference"
}
}
}
旧版本
应用旧版本时,Azure Synapse Analytics 链接服务支持以下通用属性:
properties | 描述 | 必需 |
---|---|---|
type | type 属性必须设置为 AzureSqlDW。 | 是 |
connectionString | 为 connectionString 属性指定连接到 Azure Synapse Analytics 实例所需的信息。 将此字段标记为 SecureString 以安全存储它。 还可以将密码/服务主体密钥放在 Azure 密钥保管库中,如果是 SQL 身份验证,则从连接字符串中拉取 password 配置。 有关更多详细信息,请参阅在 Azure 密钥保管库中存储凭据一文。 |
是 |
connectVia | 用于连接到数据存储的集成运行时。 可使用 Azure 集成运行时或自承载集成运行时(如果数据存储位于专用网络)。 如果未指定,则使用默认 Azure Integration Runtime。 | 否 |
有关各种身份验证类型,请参阅以下部分,分别了解特定属性和先决条件:
旧版的 SQL 身份验证
要使用 SQL 身份验证,请指定前面部分所述的通用属性。
旧版的服务主体身份验证
要使用服务主体身份验证,除了上一部分中描述的通用属性外,还需要指定以下属性:
属性 | 描述 | 必选 |
---|---|---|
servicePrincipalId | 指定应用程序的客户端 ID。 | 是 |
servicePrincipalKey | 指定应用程序的密钥。 将此字段标记为 SecureString 以安全地存储它,或引用存储在 Azure Key Vault 中的机密。 | 是 |
tenant | 指定应用程序所在的租户的信息(例如域名或租户 ID)。 将鼠标悬停在 Azure 门户右上角进行检索。 | 是 |
azureCloudType | 对于服务主体身份验证,请指定 Microsoft Entra 应用程序注册到的 Azure 云环境的类型。 允许的值为 AzurePublic、AzureChina、AzureUsGovernment 和 AzureGermany 。 默认情况下,使用数据工厂或 Synapse 管道的云环境。 |
否 |
还需要按照服务主体身份验证中的步骤授予相应的权限。
旧版本的系统分配的托管标识身份验证
若要使用系统分配的托管标识身份验证,请按照系统分配的托管标识身份验证,为推荐的版本执行相同的步骤。
旧版本的用户分配的托管标识身份验证
若要使用用户分配的托管标识身份验证,请按照用户分配的托管标识身份验证,为推荐的版本执行相同的步骤。
数据集属性
有关可用于定义数据集的各部分和属性的完整列表,请参阅数据集一文。
Azure Synapse Analytics 数据集支持以下属性:
属性 | 描述 | 必需 |
---|---|---|
type | 数据集的 type 属性必须设置为 AzureSqlDWTable。 | 是 |
schema | 架构的名称。 | 对于源为“No”,对于接收器为“Yes” |
表 | 表/视图的名称。 | 对于源为“No”,对于接收器为“Yes” |
tableName | 具有架构的表/视图的名称。 此属性支持后向兼容性。 对于新的工作负荷,请使用 schema 和 table 。 |
对于源为“No”,对于接收器为“Yes” |
数据集属性示例
{
"name": "AzureSQLDWDataset",
"properties":
{
"type": "AzureSqlDWTable",
"linkedServiceName": {
"referenceName": "<Azure Synapse Analytics linked service name>",
"type": "LinkedServiceReference"
},
"schema": [ < physical schema, optional, retrievable during authoring > ],
"typeProperties": {
"schema": "<schema_name>",
"table": "<table_name>"
}
}
}
复制活动属性
有关可用于定义活动的各部分和属性的完整列表,请参阅管道一文。 本部分提供 Azure Synapse Analytics 源和接收器支持的属性列表。
Azure Synapse Analytics 用作源
提示
若要使用数据分区从 Azure Synapse Analytics 高效加载数据,请参阅从 Azure Synapse Analytics 进行并行复制以了解详细信息。
若要从 Azure Synapse Analytics 复制数据,请将复制活动源中的 type 属性设置为 SqlDWSource。 复制活动 source 节支持以下属性:
属性 | 描述 | 必需 |
---|---|---|
type | 复制活动源的 type 属性必须设置为 SqlDWSource。 | 是 |
sqlReaderQuery | 使用自定义 SQL 查询读取数据。 示例:select * from MyTable 。 |
否 |
sqlReaderStoredProcedureName | 从源表读取数据的存储过程的名称。 最后一个 SQL 语句必须是存储过程中的 SELECT 语句。 | 否 |
storedProcedureParameters | 存储过程的参数。 允许的值为名称或值对。 参数的名称和大小写必须与存储过程参数的名称和大小写匹配。 |
否 |
isolationLevel | 指定 SQL 源的事务锁定行为。 允许的值为:ReadCommitted、ReadUncommitted、RepeatableRead、Serializable、Snapshot 。 如果未指定,则使用数据库的默认隔离级别。 有关详细信息,请参阅 system.data.isolationlevel。 | 否 |
partitionOptions | 指定用于从 Azure Synapse Analytics 加载数据的数据分区选项。 允许值包括:None(默认值)、PhysicalPartitionsOfTable 和 DynamicRange 。 启用分区选项(且选项不是 None )时,用于从 Azure Synapse Analytics 并行加载数据的并行度由复制活动上的 parallelCopies 设置控制。 |
否 |
partitionSettings | 指定数据分区的设置组。 当分区选项不是 None 时适用。 |
否 |
在 partitionSettings 下: |
||
partitionColumnName | 以整数类型、日期类型或日期/时间类型(int 、smallint 、bigint 、date 、smalldatetime 、datetime 、datetime2 或 datetimeoffset )指定源列的名称,范围分区将使用它进行并行复制。 如果未指定,系统会自动检测表的索引或主键并将其用作分区列。当分区选项是 DynamicRange 时适用。 如果使用查询来检索源数据,请在 WHERE 子句中挂接 ?DfDynamicRangePartitionCondition 。 有关示例,请参阅从 SQL 数据库进行并行复制部分。 |
否 |
partitionUpperBound | 分区范围拆分的分区列的最大值。 此值用于决定分区步幅,不用于筛选表中的行。 将对表或查询结果中的所有行进行分区和复制。 如果未指定,复制活动会自动检测该值。 当分区选项是 DynamicRange 时适用。 有关示例,请参阅从 SQL 数据库进行并行复制部分。 |
否 |
partitionLowerBound | 分区范围拆分的分区列的最小值。 此值用于决定分区步幅,不用于筛选表中的行。 将对表或查询结果中的所有行进行分区和复制。 如果未指定,复制活动会自动检测该值。 当分区选项是 DynamicRange 时适用。 有关示例,请参阅从 SQL 数据库进行并行复制部分。 |
否 |
请注意以下几点:
- 在源中使用存储过程检索数据时,请注意,如果存储过程旨在当传入不同的参数值时返回不同的架构,则从 UI 导入架构时,或通过自动创建表的功能将数据复制到 SQL 数据库时,可能会遇到故障或出现意外的结果。
示例:使用 SQL 查询
"activities":[
{
"name": "CopyFromAzureSQLDW",
"type": "Copy",
"inputs": [
{
"referenceName": "<Azure Synapse Analytics input dataset name>",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "<output dataset name>",
"type": "DatasetReference"
}
],
"typeProperties": {
"source": {
"type": "SqlDWSource",
"sqlReaderQuery": "SELECT * FROM MyTable"
},
"sink": {
"type": "<sink type>"
}
}
}
]
示例:使用存储过程
"activities":[
{
"name": "CopyFromAzureSQLDW",
"type": "Copy",
"inputs": [
{
"referenceName": "<Azure Synapse Analytics input dataset name>",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "<output dataset name>",
"type": "DatasetReference"
}
],
"typeProperties": {
"source": {
"type": "SqlDWSource",
"sqlReaderStoredProcedureName": "CopyTestSrcStoredProcedureWithParameters",
"storedProcedureParameters": {
"stringData": { "value": "str3" },
"identifier": { "value": "$$Text.Format('{0:yyyy}', <datetime parameter>)", "type": "Int"}
}
},
"sink": {
"type": "<sink type>"
}
}
}
]
示例存储过程:
CREATE PROCEDURE CopyTestSrcStoredProcedureWithParameters
(
@stringData varchar(20),
@identifier int
)
AS
SET NOCOUNT ON;
BEGIN
select *
from dbo.UnitTestSrcTable
where dbo.UnitTestSrcTable.stringData != stringData
and dbo.UnitTestSrcTable.identifier != identifier
END
GO
Azure Synapse Analytics 用作接收器
Azure 数据工厂和 Synapse 管道支持三种将数据加载到 Azure Synapse Analytics 的方法。
- 使用 COPY 语句
- 使用 PolyBase
- 使用批量插入
加载数据最快且最具扩展性的方法是通过 COPY 语句 或 PolyBase。
要将数据复制到 Azure Synapse Analytics,请将复制活动中的接收器类型设置为 SqlDWSink。 复制活动 sink 节支持以下属性:
属性 | 描述 | 必需 |
---|---|---|
type | 复制活动接收器的 type 属性必须设置为 SqlDWSink。 | 是 |
allowPolyBase | 指示是否使用 PolyBase 将数据加载到 Azure Synapse Analytics。 allowCopyCommand 和 allowPolyBase 不能同时为 true。 有关约束和详细信息,请参阅使用 PolyBase 将数据加载到 Azure Synapse Analytics 部分。 允许的值为 True 和 False(默认值)。 |
否。 使用 PolyBase 时适用。 |
polyBaseSettings | allowPolybase 属性设置为 true 时可以指定的一组属性。 |
否。 使用 PolyBase 时适用。 |
allowCopyCommand | 指示是否使用 COPY 语句将数据加载到 Azure Synapse Analytics。 allowCopyCommand 和 allowPolyBase 不能同时为 true。 有关约束和详细信息,请参阅使用 COPY 语句将数据加载到 Azure Synapse Analytics 部分。 允许的值为 True 和 False(默认值)。 |
不是。 使用 COPY 时适用。 |
copyCommandSettings | allowCopyCommand 属性设置为 TRUE 时可以指定的一组属性。 |
不是。 使用 COPY 时适用。 |
writeBatchSize | 每批要插入到 SQL 表中的行数。 允许的值为 integer(行数)。 默认情况下,该服务根据行大小动态确定适当的批大小。 |
否。 使用批量插入时适用。 |
writeBatchTimeout | 插入、更新插入和存储过程操作在超时之前完成的等待时间。 允许的值是指时间跨度。 例如,“00:30:00”表示 30 分钟。 如果未指定值,则超时默认为“00:30:00”。 |
否。 使用批量插入时适用。 |
preCopyScript | 指定在每次运行中将数据写入到 Azure Synapse Analytics 之前要由复制活动运行的 SQL 查询。 使用此属性清理预加载的数据。 | 否 |
tableOption | 指定是否根据源架构自动创建接收器表(如果接收器表不存在)。 允许的值为:none (默认值)、autoCreate 。 |
否 |
disableMetricsCollection | 该服务收集指标(如 Azure Synapse Analytics DWU),用于进行复制性能优化和提供建议,从而引入了额外的主数据库访问权限。 如果你担心此行为,请指定 true 将其关闭。 |
否(默认值为 false ) |
maxConcurrentConnections | 活动运行期间与数据存储建立的并发连接的上限。 仅在要限制并发连接时指定一个值。 | 无 |
WriteBehavior | 指定复制活动的写入行为,以将数据加载到 Azure Synapse Analytics。 允许的值为 Insert 和 Upsert。 默认情况下,服务使用 insert 来加载数据。 |
否 |
upsertSettings | 指定写入行为的设置组。 当 WriteBehavior 选项为 Upsert 时应用。 |
否 |
在 upsertSettings 下: |
||
密钥 | 指定唯一行标识的列名称。 可使用单个键,也可使用一系列键。 如果未指定,将使用主键。 | 否 |
interimSchemaName | 指定用于创建临时表的临时架构。 注意:用户需要具有创建和删除表的权限。 默认情况下,临时表将与接收器表共享相同的架构。 | 否 |
示例 1:Azure Synapse Analytics 接收器
"sink": {
"type": "SqlDWSink",
"allowPolyBase": true,
"polyBaseSettings":
{
"rejectType": "percentage",
"rejectValue": 10.0,
"rejectSampleValue": 100,
"useTypeDefault": true
}
}
示例 2:更新插入数据
"sink": {
"type": "SqlDWSink",
"writeBehavior": "Upsert",
"upsertSettings": {
"keys": [
"<column name>"
],
"interimSchemaName": "<interim schema name>"
},
}
从 Azure Synapse Analytics 进行并行复制
复制活动中的 Azure Synapse Analytics 连接器提供内置的数据分区,用于并行复制数据。 可以在复制活动的“源”表中找到数据分区选项。
启用分区复制时,复制活动将对 Azure Synapse Analytics 源运行并行查询,以按分区加载数据。 可通过复制活动中的 parallelCopies
设置控制并行度。 例如,如果将 parallelCopies
设置为 4,则该服务会根据指定的分区选项和设置并行生成并运行 4 个查询,每个查询从 Azure Synapse Analytics 检索一部分数据。
建议同时启用并行复制和数据分区,尤其是从 Azure Synapse Analytics 加载大量数据时。 下面是适用于不同方案的建议配置。 将数据复制到基于文件的数据存储中时,建议将数据作为多个文件写入文件夹(仅指定文件夹名称),在这种情况下,性能优于写入单个文件。
方案 | 建议的设置 |
---|---|
从包含物理分区的大型表进行完整加载。 | 分区选项:表的物理分区。 在执行期间,该服务将自动检测物理分区并按分区复制数据。 若要检查表是否有物理分区,可参考此查询。 |
从不包含物理分区但包含用于数据分区的整数或日期时间列的大型表进行完整加载。 | 分区选项:动态范围分区。 分区列(可选):指定用于对数据进行分区的列。 如果未指定,将使用索引或主键列。 分区上限和分区下限(可选) :指定是否要确定分区步幅。 这不适用于筛选表中的行,表中的所有行都将进行分区和复制。 如果未指定,复制活动会自动检测这些值。 例如,如果分区列“ID”的值范围为 1 至 100,并且将此值的下限设置为 20、上限设置为 80,并行复制设置为 4,服务将按 4 个分区(分区的 ID 范围分别为 <=20、[21, 50]、[51, 80] 和 >=81)检索数据。 |
使用自定义查询从不包含物理分区但包含用于数据分区的整数或日期/日期时间列的表加载大量数据。 | 分区选项:动态范围分区。 查询: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause> 。分区列:指定用于对数据进行分区的列。 分区上限和分区下限(可选) :指定是否要确定分区步幅。 这不适用于筛选表中的行,查询结果中的所有行都将进行分区和复制。 如果未指定,复制活动会自动检测该值。 例如,如果分区列“ID”的值范围为 1 至 100,并且将此值的下限设置为 20、上限设置为 80,并行复制设置为 4,服务将按 4 个分区(分区的 ID 范围分别为 <=20、[21, 50]、[51, 80] 和 >=81)检索数据。 下面是针对不同场景的更多示例查询: 1.查询整个表: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition 2.使用列选择和附加的 where 子句筛选器从表中查询: SELECT <column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause> 3.使用子查询进行查询: SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause> 4.在子查询中使用分区查询: SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition) AS T |
使用分区选项加载数据的最佳做法:
- 选择独特的列作为分区列(如主键或唯一键),以避免数据倾斜。
- 如果表具有内置分区,请使用名为“表的物理分区”分区选项来提升性能。
- 如果使用 Azure Integration Runtime 复制数据,则可设置较大的“数据集成单元 (DIU)”(>4) 以利用更多计算资源。 检查此处适用的方案。
- “复制并行度”可控制分区数量,将此数字设置得太大有时会损害性能,建议将此数字设置按以下公式计算的值:(DIU 或自承载 IR 节点数)*(2 到 4)。
- 请注意:Azure Synapse Analytics 一次最多可执行 32 个查询,将“复制并行度”设置得太大可能会导致 Synapse 限制问题。
示例:从包含物理分区的大型表进行完整加载
"source": {
"type": "SqlDWSource",
"partitionOption": "PhysicalPartitionsOfTable"
}
示例:使用动态范围分区进行查询
"source": {
"type": "SqlDWSource",
"query": "SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition 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>"
}
}
检查物理分区的示例查询
SELECT DISTINCT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, CASE WHEN c.name IS NULL THEN 'no' ELSE 'yes' END AS HasPartition
FROM sys.tables AS t
LEFT JOIN sys.objects AS o ON t.object_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.index_columns AS ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id
LEFT JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN sys.types AS y ON c.system_type_id = y.system_type_id
WHERE s.name='[your schema]' AND t.name = '[your table name]'
如果表具有物理分区,则会看到“HasPartition”为“是”。
使用 COPY 语句将数据加载到 Azure Synapse Analytics
使用 COPY 语句是一种简单且灵活的方法,以高吞吐量将数据加载到 Azure Synapse Analytics。 若要了解更多详细信息,请查看使用 COPY 语句大容量加载数据
- 如果源数据位于 Azure Blob 或 Azure Data Lake Storage Gen2 中,并且格式与 COPY 语句兼容,则可以使用复制活动直接调用 COPY 语句,使 Azure Synapse Analytics 从源中拉取数据 。 有关详细信息,请参阅 使用 COPY 语句直接复制 。
- 如果 COPY 语句最初不支持源数据存储和格式,请改用 使用 COPY 语句暂存复制 功能。 暂存复制功能也能提供更高的吞吐量。 它自动将数据转换为与 COPY 语句兼容的格式,将数据存储在 Azure Blob 存储中,然后调用 COPY 语句将数据加载到 Azure Synapse Analytics。
提示
在 Azure Integration Runtime 中使用 COPY 语句时,有效的数据集成单元 (DIU) 数始终为 2。 优化 DIU 不会影响性能,因为从存储加载数据的功能由 Azure Synapse 引擎提供支持。
使用 COPY 语句直接复制
Azure Synapse Analytics COPY 语句直接支持 Azure Blob 和 Azure Data Lake Storage Gen2。 如果你的源数据满足本部分中所述的条件,请使用 COPY 语句从源数据存储直接复制到 Azure Synapse Analytics。 否则,请使用使用 COPY 语句的暂存复制。 该服务会检查设置,如果不满足条件,复制活动运行将会失败。
源链接服务和格式使用以下类型和身份验证方法:
支持的源数据存储类型 支持的格式 支持的源身份验证类型 Azure Blob 带分隔符的文本 帐户密钥身份验证、共享访问签名身份验证、服务主体身份验证(使用 ServicePrincipalKey)、系统分配的托管标识身份验证 Parquet 帐户密钥身份验证、共享访问签名身份验证 ORC 帐户密钥身份验证、共享访问签名身份验证 Azure Data Lake Storage Gen2 带分隔符的文本
Parquet
ORC帐户密钥身份验证、服务主体身份验证(使用 ServicePrincipalKey)、共享访问签名身份验证、系统分配的托管标识身份验证 重要
- 对存储链接服务使用托管标识身份验证时,请分别了解 Azure Blob 和 Azure Data Lake Storage Gen2 所需的配置。
- 如果 Azure 存储配置了 VNet 服务终结点,则必须在存储帐户上启用“允许受信任的 Microsoft 服务”并使用托管标识身份验证,详见将 VNet 服务终结点与 Azure 存储配合使用的影响。
格式设置如下:
- 对于“Parquet”:
compression
可以为“无压缩”、“Snappy”或GZip
。 - 对于 ORC:
compression
可以为“无压缩”、zlib
或“Snappy”。 - 对于“带分隔符的文本”:
rowDelimiter
显式设置为单字符或“\r\n”,不支持默认值。nullValue
保留默认值或设置为空字符串 ("")。encodingName
保留默认值或设置为 utf-8 或 utf-16。escapeChar
必须与quoteChar
相同,且不能为空。skipLineCount
保留默认值或设置为 0。compression
可以为“无压缩”或GZip
。
- 对于“Parquet”:
如果源是文件夹,则必须将复制活动中的
recursive
设置为 true,并且wildcardFilename
需要为*
或*.*
。wildcardFolderPath
、wildcardFilename
(*
或*.*
除外)、modifiedDateTimeStart
、modifiedDateTimeEnd
、prefix
、enablePartitionDiscovery
和additionalColumns
均未指定。
复制活动中的 allowCopyCommand
下支持以下 COPY 语句设置:
属性 | 描述 | 必需 |
---|---|---|
defaultValues | 为 Azure Synapse Analytics 中的每个目标列指定默认值。 属性中的默认值将覆盖数据仓库中设置的 DEFAULT 约束,标识列不能有默认值。 | 否 |
additionalOptions | 将直接在 COPY 语句的“With”子句中传递给 Azure Synapse Analytics COPY 语句的其他选项。 根据需要将值括在引号中,以符合 COPY 语句要求。 | 否 |
"activities":[
{
"name": "CopyFromAzureBlobToSQLDataWarehouseViaCOPY",
"type": "Copy",
"inputs": [
{
"referenceName": "ParquetDataset",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "AzureSQLDWDataset",
"type": "DatasetReference"
}
],
"typeProperties": {
"source": {
"type": "ParquetSource",
"storeSettings":{
"type": "AzureBlobStorageReadSettings",
"recursive": true
}
},
"sink": {
"type": "SqlDWSink",
"allowCopyCommand": true,
"copyCommandSettings": {
"defaultValues": [
{
"columnName": "col_string",
"defaultValue": "DefaultStringValue"
}
],
"additionalOptions": {
"MAXERRORS": "10000",
"DATEFORMAT": "'ymd'"
}
}
},
"enableSkipIncompatibleRow": true
}
}
]
使用 COPY 语句的暂存复制
如果源数据不与 COPY 语句本机兼容,请通过临时暂存 Azure Blob 或 Azure Data Lake Storage Gen2(不能为 Azure 高级存储)来启用数据复制。 在这种情况下,该服务会自动转换数据,以满足 COPY 语句的数据格式要求。 然后,它会调用 COPY 语句,将数据加载到 Azure Synapse Analytics。 最后,它会从存储中清理临时数据。 若要详细了解如何通过暂存方式复制数据,请参阅暂存复制。
若要使用此功能,请创建 Azure Blob 存储链接服务或 Azure Data Lake Storage Gen2 链接服务,使用帐户密钥或系统托管标识身份验证,Azure 存储帐户用作临时存储。
重要
- 对暂存链接服务使用托管标识身份验证时,请分别了解 Azure Blob 和 Azure Data Lake Storage Gen2 所需的配置。 还需要向暂存 Azure Blob 存储或 Azure Data Lake Storage Gen2 帐户中的 Azure Synapse Analytics 工作区托管标识授予权限。 要了解如何授予此权限,请参阅向工作区托管标识授予权限。
- 如果临时 Azure 存储配置了 VNet 服务终结点,则必须在存储帐户上启用“允许受信任的 Microsoft 服务”并使用托管标识身份验证,详见将 VNet 服务终结点与 Azure 存储配合使用的影响。
重要
如果你的暂存 Azure 存储配置了托管专用终结点并启用了存储防火墙,则必须使用托管标识身份验证并向 Synapse SQL Server 授予存储 Blob 数据读取器权限,以确保它可以在 COPY 语句加载期间访问暂存文件。
"activities":[
{
"name": "CopyFromSQLServerToSQLDataWarehouseViaCOPYstatement",
"type": "Copy",
"inputs": [
{
"referenceName": "SQLServerDataset",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "AzureSQLDWDataset",
"type": "DatasetReference"
}
],
"typeProperties": {
"source": {
"type": "SqlSource",
},
"sink": {
"type": "SqlDWSink",
"allowCopyCommand": true
},
"stagingSettings": {
"linkedServiceName": {
"referenceName": "MyStagingStorage",
"type": "LinkedServiceReference"
}
}
}
}
]
使用 PolyBase 将数据加载到 Azure Synapse Analytics
使用 PolyBase 是以高吞吐量将大量数据载入 Azure Synapse Analytics 的有效方法。 使用 PolyBase 而非默认 BULKINSERT 机制可以实现吞吐量的巨大增加。
- 如果源数据位于 Azure Blob 或 Azure Data Lake Storage Gen2 中,且格式与 PolyBase 兼容,则可使用复制活动直接调用 PolyBase,让 Azure Synapse Analytics 从源拉取数据。 有关详细信息,请参阅 使用 PolyBase 直接复制 。
- 如果 PolyBase 最初不支持源数据存储和格式,请改用 使用 PolyBase 的暂存复制 功能。 暂存复制功能也能提供更高的吞吐量。 它自动将数据转换为与 PolyBase 兼容的格式,将数据存储在 Azure Blob 存储中,然后调用 PolyBase 将数据加载到 Azure Synapse Analytics。
提示
详细了解有关如何使用 PolyBase 的最佳做法。 将 PolyBase 与 Azure Integration Runtime 一起使用时,Synapse 的直接或分段存储的有效数据集成单元 (DIU) 数始终为 2。 优化 DIU 不会影响性能,因为从存储加载数据的功能由 Synapse 引擎提供支持。
在复制活动中的 polyBaseSettings
下支持以下 PolyBase 设置:
属性 | 描述 | 必需 |
---|---|---|
rejectValue | 指定在查询失败之前可以拒绝的行数或百分比。 有关 PolyBase 的拒绝选项的详细信息,请参阅 CREATE EXTERNAL TABLE (Transact-SQL) 的“参数”部分。 允许的值为 0(默认值)、1、2 等。 |
否 |
rejectType | 指定 rejectValue 选项是文本值还是百分比。 允许的值为 Value(默认值)和 Percentage。 |
否 |
rejectSampleValue | 确定在 PolyBase 重新计算被拒绝行的百分比之前要检索的行数。 允许的值为 1、2 等。 |
如果 rejectType 是 percentage,则为“是” |
useTypeDefault | 指定在 PolyBase 从文本文件中检索数据时如何处理带分隔符的文本文件中的缺失值。 有关此属性的详细信息,请参阅创建外部文件格式 (Transact SQL) 中的参数部分。 允许的值为 True 和 False(默认值)。 |
否 |
使用 PolyBase 直接复制
Azure Synapse Analytics PolyBase 直接支持 Azure Blob 和 Azure Data Lake Storage Gen2。 如果源数据满足本部分所述的条件,请使用 PolyBase 从源数据存储直接复制到 Azure Synapse Analytics。 否则,请改用使用 PolyBase 的暂存复制。
提示
要将数据有效地复制到 Azure Synapse Analytics,请从将 Data Lake Store 与 Azure Synapse Analytics 配合使用时,Azure 数据工厂能够更轻松且方便地观察数据中了解详细信息。
如果不满足要求,该服务会检查设置,并自动回退到 BULKINSERT 机制以进行数据移动。
源链接的服务使用以下类型和身份验证方法:
支持的源数据存储类型 支持的源身份验证类型 Azure Blob 帐户密钥身份验证、系统分配的托管标识身份验证 Azure Data Lake Storage Gen2 帐户密钥身份验证、系统分配的托管标识身份验证 重要
- 对存储链接服务使用托管标识身份验证时,请分别了解 Azure Blob 和 Azure Data Lake Storage Gen2 所需的配置。
- 如果 Azure 存储配置了 VNet 服务终结点,则必须在存储帐户上启用“允许受信任的 Microsoft 服务”并使用托管标识身份验证,详见将 VNet 服务终结点与 Azure 存储配合使用的影响。
源数据格式为 Parquet、ORC 或带分隔符的文本,具有以下配置 :
- 文件夹路径不包含通配符筛选器。
- 文件名为空或指向单个文件。 如果在复制活动中指定通配符文件名,该通配符只能是
*
或*.*
。 rowDelimiter
为 default、 \n、 \r\n 或 \r。- 将
nullValue
保留为默认值或设置为“空字符串”(“”),并将treatEmptyAsNull
保留为默认值或设置为 true。 encodingName
保留为默认值或设置为 utf-8。quoteChar
、escapeChar
和skipLineCount
未指定。 PolyBase 支持跳过标头行,可将其配置为firstRowAsHeader
。compression
可以为“无压缩”、GZip
或“Deflate”。
如果源是文件夹,则必须将复制活动中的
recursive
设置为 true。未指定
wildcardFolderPath
、wildcardFilename
、modifiedDateTimeStart
、modifiedDateTimeEnd
、prefix
、enablePartitionDiscovery
和additionalColumns
。
注意
如果源是一个文件夹,请注意,PolyBase 将从该文件夹及其所有子文件夹中检索文件,并且它不会从文件名以下划线 (_) 或句点 (.) 开头的文件中检索数据,如此处 - LOCATION 参数所述。
"activities":[
{
"name": "CopyFromAzureBlobToSQLDataWarehouseViaPolyBase",
"type": "Copy",
"inputs": [
{
"referenceName": "ParquetDataset",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "AzureSQLDWDataset",
"type": "DatasetReference"
}
],
"typeProperties": {
"source": {
"type": "ParquetSource",
"storeSettings":{
"type": "AzureBlobStorageReadSettings",
"recursive": true
}
},
"sink": {
"type": "SqlDWSink",
"allowPolyBase": true
}
}
}
]
使用 PolyBase 的暂存复制
如果源数据不与 PolyBase 本机兼容,请通过临时暂存 Azure Blob 或 Azure Data Lake Storage Gen2(不能为 Azure 高级存储)来启用数据复制。 在这种情况下,该服务会自动转换数据,以满足 PolyBase 的数据格式要求。 然后,它调用 PolyBase 将数据加载到 Azure Synapse Analytics。 最后,它会从存储中清理临时数据。 若要详细了解如何通过暂存方式复制数据,请参阅暂存复制。
若要使用此功能,请创建 Azure Blob 存储链接服务或 Azure Data Lake Storage Gen2 链接服务,这两个服务使用帐户密钥或托管标识身份验证来引用 Azure 存储帐户作为临时存储。
重要
- 对暂存链接服务使用托管标识身份验证时,请分别了解 Azure Blob 和 Azure Data Lake Storage Gen2 所需的配置。 还需要向暂存 Azure Blob 存储或 Azure Data Lake Storage Gen2 帐户中的 Azure Synapse Analytics 工作区托管标识授予权限。 要了解如何授予此权限,请参阅向工作区托管标识授予权限。
- 如果临时 Azure 存储配置了 VNet 服务终结点,则必须在存储帐户上启用“允许受信任的 Microsoft 服务”并使用托管标识身份验证,详见将 VNet 服务终结点与 Azure 存储配合使用的影响。
重要
如果你的暂存 Azure 存储配置了托管专用终结点并启用了存储防火墙,则必须使用托管标识身份验证并向 Synapse SQL Server 授予存储 Blob 数据读取器权限,以确保它可以在 PolyBase 加载期间访问暂存文件。
"activities":[
{
"name": "CopyFromSQLServerToSQLDataWarehouseViaPolyBase",
"type": "Copy",
"inputs": [
{
"referenceName": "SQLServerDataset",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "AzureSQLDWDataset",
"type": "DatasetReference"
}
],
"typeProperties": {
"source": {
"type": "SqlSource",
},
"sink": {
"type": "SqlDWSink",
"allowPolyBase": true
},
"enableStaging": true,
"stagingSettings": {
"linkedServiceName": {
"referenceName": "MyStagingStorage",
"type": "LinkedServiceReference"
}
}
}
}
]
有关使用 PolyBase 的最佳做法
除了 Azure Synapse Analytics 的最佳做法中提到的做法以外,以下部分还提供了其他最佳做法。
所需数据库权限
若要使用 PolyBase,将数据加载到 Azure Synapse Analytics 的用户必须对目标数据库拥有“CONTROL”权限。 一种实现方法是将该用户添加为 db_owner 角色的成员。 在 Azure Synapse Analytics 概述中了解如何执行此操作。
行大小和数据类型限制
PolyBase 负载限制为小于 1 MB 的行。 不能用它加载到 VARCHR(MAX)、NVARCHAR 或 VARBINARY(MAX)。 有关详细信息,请参阅 Azure Synapse Analytics 服务容量限制。
如果数据源中的行大于 1 MB,可能需要将源表垂直拆分为多个小型表。 确保每行的最大大小不超过该限制。 然后,可以使用 PolyBase 加载这些小型表,并在 Azure Synapse Analytics 中将它们合并在一起。
或者,对于具有此类较大列的数据,可以通过关闭“允许 PolyBase”设置,来使用非 PolyBase 加载数据。
Azure Synapse Analytics 资源类
若要实现最佳吞吐量,请将更大的资源类分配给通过 PolyBase 将数据加载到 Azure Synapse Analytics 的用户。
排查 PolyBase 问题
加载到“小数”列
如果源数据为文本格式,或者位于其他与 PolyBase 不兼容的存储(使用暂存复制和 PolyBase)中,并且包含需加载到 Azure Synapse Analytics“小数”列中的空值,则可能出现以下错误:
ErrorCode=FailedDbOperation, ......HadoopSqlException: Error converting data type VARCHAR to DECIMAL.....Detailed Message=Empty string can't be converted to DECIMAL.....
解决方法是在复制活动接收器 -> PolyBase 设置中取消选择“使用类型默认值”选项(设为 false)。 “USE_TYPE_DEFAULT”是一项 PolyBase 本地设置,它指定 PolyBase 从文本文件中检索数据时,如何处理带分隔符的文本文件中的缺失值。
查看 Azure Synapse Analytics 中的 tableName 属性
下表举例说明如何在 JSON 数据集中指定 tableName 属性。 其中显示了架构和表名称的多个组合。
DB 架构 | 表名称 | tableName JSON 属性 |
---|---|---|
dbo | MyTable | MyTable 或 dbo.MyTable 或 [dbo].[MyTable] |
dbo1 | MyTable | dbo1.MyTable 或 [dbo1].[MyTable] |
dbo | My.Table | [My.Table] 或 [dbo].[My.Table] |
dbo1 | My.Table | [dbo1].[My.Table] |
如果看到以下错误,问题可能与为 tableName 属性指定的值有关。 有关为 tableName JSON 属性指定值的正确方法,请参阅上表。
Type=System.Data.SqlClient.SqlException,Message=Invalid object name 'stg.Account_test'.,Source=.Net SqlClient Data Provider
具有默认值的列
目前,PolyBase 功能只接受与目标表中数量相同的列。 例如,某个表包含四列,其中一列定义了默认值。 输入数据仍需包含四列。 包含三列的输入数据集生成类似于以下消息的错误:
All columns of the table must be specified in the INSERT BULK statement.
NULL 值是特殊形式的默认值。 如果列可为 null,则该列的 Blob 中的输入数据可能为空。 但输入数据集中不能缺少该数据。 PolyBase 在 Azure Synapse Analytics 中插入 NULL 来表示缺少的值。
外部文件访问失败
如果收到以下错误,请确保你使用的是托管标识身份验证,并已向 Azure Synapse 工作区的托管标识授予存储 Blob 数据读取者权限。
Job failed due to reason: at Sink '[SinkName]': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:\r\nHdfsBridge::isDirExist
有关详细信息,请参阅创建工作区后向托管标识授予权限。
映射数据流属性
在映射数据流中转换数据时,可以从 Azure Synapse Analytics 读取表和写入到表中。 有关详细信息,请参阅映射数据流中的源转换和接收器转换。
源转换
特定于 Azure Synapse Analytics 的设置可在源转换的“源选项”选项卡中找到。
输入 选择将源指向某个表(等效于 Select * from <table-name>
),还是输入自定义 SQL 查询。
启用暂存 强烈建议你在 Azure Synapse Analytics 源的生产工作负载中使用此选项。 使用管道中的 Azure Synapse Analytics 源执行数据流活动时,系统会提示输入暂存位置存储帐户,并将使用该帐户进行暂存数据加载。 它是从 Azure Synapse Analytics 加载数据的最快机制。
- 对存储链接服务使用托管标识身份验证时,请分别了解 Azure Blob 和 Azure Data Lake Storage Gen2 所需的配置。
- 如果 Azure 存储配置了 VNet 服务终结点,则必须在存储帐户上启用“允许受信任的 Microsoft 服务”并使用托管标识身份验证,详见将 VNet 服务终结点与 Azure 存储配合使用的影响。
- 使用 Azure Synapse“无服务器”SQL 池作为源时,不支持启用暂存。
查询:如果在“输入”字段中选择“查询”,请为源输入 SQL 查询。 此设置会替代在数据集中选择的任何表。 此处不支持 Order By 子句,但你可以设置完整的 SELECT FROM 语句。 还可以使用用户定义的表函数。 select * from udfGetData() 是 SQL 中可返回表的 UDF。 此查询将生成可以在数据流中使用的源表。 使用查询也是减少进行测试或查找的行的好方法。
SQL 示例:Select * from MyTable where customerId > 1000 and customerId < 2000
批大小:输入批大小,以将大型数据分成多个读取操作。 在数据流中,会使用此设置来设置 Spark 分栏式缓存。 这是选项字段,如果留空,它将使用 Spark 默认值。
隔离级别: 映射数据流中 SQL 源的默认设置为“读取未提交的内容”。 你可以将此处的隔离级别更改为以下值之一:
- 读取已提交的内容
- 读取未提交的内容
- 可重复的读取
- 可序列化
- 无(忽略隔离级别)
接收器转换
特定于 Azure Synapse Analytics 的设置可在接收器转换的“设置”选项卡中找到。
更新方法: 确定数据库目标上允许哪些操作。 默认设置为仅允许插入。 若要更新、更新插入或删除行,需要进行 alter-row 转换才能标记这些操作的行。 对于更新、更新插入和删除操作,必须设置一个或多个键列,以确定要更改的行。
表操作: 确定在写入之前是否从目标表重新创建或删除所有行。
- 无:不会对表进行任何操作。
- 重新创建:将删除表并重新创建表。 如果以动态方式创建表,则是必需的。
- 截断:将删除目标表中的所有行。
启用暂存:这样可以使用 copy 命令加载到 Azure Synapse Analytics SQL 池中,建议用于大多数 Synapse 接收器。 暂存存储是在执行数据流活动中配置的。
- 对存储链接服务使用托管标识身份验证时,请分别了解 Azure Blob 和 Azure Data Lake Storage Gen2 所需的配置。
- 如果 Azure 存储配置了 VNet 服务终结点,则必须在存储帐户上启用“允许受信任的 Microsoft 服务”并使用托管标识身份验证,详见将 VNet 服务终结点与 Azure 存储配合使用的影响。
批大小:控制每个 Bucket 中写入的行数。 较大的批大小可提高压缩比并改进内存优化,但在缓存数据时可能会导致内存不足异常。
使用接收器架构:默认情况下,将在接收器架构下创建临时表作为过渡。 可以取消选中“使用接收器架构”选项,在“选择用户数据库架构”中指定架构名称,在该架构名称下,数据工厂将创建一个临时表来加载上游数据,并在完成后进行自动清理。 请确保已在数据库中创建表权限,并更改对架构的权限。
预处理和后处理 SQL 脚本:输入将在数据写入接收器数据库之前(预处理)和之后(后处理)执行的多行 SQL 脚本
提示
- 建议将包含多个命令的单个批处理脚本拆分为多个批处理。
- 只有返回简单更新计数的数据定义语言 (Data Definition Language, DDL) 和数据操作语言 (Data Manipulation Language, DML) 语句可作为批处理的一部分运行。 在执行批量操作中了解详情
行处理时出错
写入到 Azure Synapse Analytics 时,某些数据行可能会由于目标设置的约束而出错。 一些常见错误包括:
- 字符串或二进制数据在表中会被截断
- 无法在列中插入 NULL 值
- 在将值转换成数据类型时失败
默认情况下,遇到第一个错误时,数据流运行会失败。 你可以选择“出错时继续”,确保即使各行存在错误,也可以完成数据流。 该服务提供了不同的选项来处理这些错误行。
事务提交:选择是在单个事务中写入数据,还是分批写入数据。 单个事务将提供更好的性能,且事务完成之前,其他人将看不到任何写入的数据。 批处理事务的性能较差,但适用于大型数据集。
输出已拒绝的数据:如果已启用,则可将错误行输出到 Azure Blob 存储或所选 Azure Data Lake Storage Gen2 帐户中的 csv 文件。 这会写入包含三个附加列的错误行:SQL 操作(例如插入或更新)、数据流错误代码,以及有关行的错误消息。
出错时报告成功:如果已启用,则即使发现了错误行,也会将数据流标记为成功。
查找活动属性
若要了解有关属性的详细信息,请查看 Lookup 活动。
GetMetadata 活动属性
若要了解有关属性的详细信息,请查看 GetMetadata 活动
Azure Synapse Analytics 的数据类型映射
从/向 Azure Synapse Analytics 复制数据时,以下映射用于从 Azure Synapse Analytics 数据类型映射到 Azure 数据工厂临时数据类型。 在使用 Synapse 管道从 Azure Synapse Analytics 复制数据或向 Azure Synapse Analytics 复制数据时,也会使用这些映射,因为管道还在 Azure Synapse 中实现了 Azure 数据工厂。 若要了解复制活动如何将源架构和数据类型映射到接收器,请参阅架构和数据类型映射。
提示
请参阅 Azure Synapse Analytics 中的表数据类型一文,了解 Azure Synapse Analytics 支持的数据类型以及针对不支持的数据类型的解决方法。
Azure Synapse Analytics 数据类型 | 数据工厂临时数据类型 |
---|---|
bigint | Int64 |
binary | Byte[] |
bit | Boolean |
char | String, Char[] |
date | DateTime |
datetime | DateTime |
datetime2 | DateTime |
Datetimeoffset | DateTimeOffset |
Decimal | Decimal |
FILESTREAM attribute (varbinary(max)) | Byte[] |
Float | Double |
image | Byte[] |
int | Int32 |
money | Decimal |
nchar | String, Char[] |
numeric | Decimal |
nvarchar | String, Char[] |
real | Single |
rowversion | Byte[] |
smalldatetime | DateTime |
smallint | Int16 |
smallmoney | Decimal |
time | TimeSpan |
tinyint | Byte |
uniqueidentifier | Guid |
varbinary | Byte[] |
varchar | String, Char[] |
升级 Azure Synapse Analytics 版本
要升级 Azure Synapse Analytics 版本,请在“编辑链接服务”页中,选择“版本”下的“推荐”,并参考推荐版本的链接服务属性来配置链接服务。
建议版本和旧版本之间的差异
下表显示了使用建议版本和旧版本的 Azure Synapse Analytics 之间的功能差异。
建议的版本 | 旧版本 |
---|---|
支持通过 encrypt 作为 strict 的 TLS 1.3。 |
不支持 TLS 1.3。 |
相关内容
有关复制活动支持作为源和接收器的数据存储的列表,请参阅支持的数据存储和格式。
反馈
https://aka.ms/ContentUserFeedback。
即将发布:在整个 2024 年,我们将逐步淘汰作为内容反馈机制的“GitHub 问题”,并将其取代为新的反馈系统。 有关详细信息,请参阅:提交和查看相关反馈