azure - Azure 数据工厂数据复制源中的查找活动 - 语法错误
问题描述
通过此https://docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-portal
我使用 sqlReaderQuery 进行了一项名为 GetCurrentWatermarkValue 的查找活动:
Select WaterMarkValue as CurrentWatermarkValue\nfrom WatermarkTable
我使用 sqlReaderQuery 进行了另一个名为 GetNewWatermarkValue 的活动:
select max(createdon) as NewWatermarkValue from shipment
然后我尝试在源中使用它们来进行数据复制活动,使用
select *
from Shipment
where CreatedOn > '@{activity('GetCurrentWatermarkValue').output.firstRow.CurrentWatermarkValue}'
and CreatedOn <= '@{activity('GetNewWatermarkValue').output.firstRow.NewWatermarkValue}'
预览数据按钮是灰色的(但当我删除 where 条件时启用)所以显然有问题
设置接收器后,我尝试设置映射。单击 Mapping 选项卡上的 Import schema 给出:
A database operation failed with the following error: 'Incorrect syntax near 'GetCurrentWatermarkValue'.'. Activity ID:98794aa9-c866-48d6-b9ff-9cb277bac6ed
我想也许我应该使用添加动态内容选项,但这只是给出
Query is required
我在某处读到,当在查找活动上设置 First Row Only 时,firstRow 之后的文本。应该是 [TableName] 但这似乎不对。
抬头:
{
"name": "GetCurrentWatermarkValue",
"type": "Lookup",
"policy": ...,
"typeProperties": {
"source": {
"type": "AzureSqlSource",
"sqlReaderQuery": "Select WaterMarkValue as CurrentWatermarkValue\nfrom WatermarkTable"
},
"dataset": {
"referenceName": "WatermarkTable",
"type": "DatasetReference"
}
}
}
抬头:
{
"name": "GetNewWatermarkValue",
"type": "Lookup",
"policy": ...,
"typeProperties": {
"source": {
"type": "AzureSqlSource",
"sqlReaderQuery": "select max(createdon) as NewWatermarkValue from shipment"
},
"dataset": {
"referenceName": "ShipmentsTable",
"type": "DatasetReference"
}
}
}
数据复制:
{
"name": "ArchiveShipments",
"type": "Copy",
"dependsOn": [
{
"activity": "GetCurrentWatermarkValue",
"dependencyConditions": [
"Succeeded"
]
},
{
"activity": "GetNewWatermarkValue",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": ...,
"typeProperties": {
"source": {
"type": "AzureSqlSource",
"sqlReaderQuery": {
"value": "select *\nfrom Shipment\nwhere CreatedOn > '@{activity('GetCurrentWatermarkValue').output.firstRow.CurrentWatermarkValue}' \nand CreatedOn <= '@{activity('GetNewWatermarkValue').output.firstRow.NewWatermarkValue}'",
"type": "Expression"
}
},
"sink": {
"type": "AzureSqlSink"
},
"enableStaging": false
},
"inputs": [
{
"referenceName": "ShipmentsTable",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "ShipmentArchiveTable",
"type": "DatasetReference"
}
]
}
解决方案
这看起来像一个语法问题,试试这是复制活动查询:
@{CONCAT('select * from Shipment where CreatedOn > ', activity('GetCurrentWatermarkValue').output.firstRow.CurrentWatermarkValue, ' and CreatedOn <= ', activity('GetNewWatermarkValue').output.firstRow.NewWatermarkValue}
除非您想手动设置它,否则不要使用映射,如果列名相同,则只需单击“清除”。
希望这有帮助!
推荐阅读
- java - RandomAccessFile 读取错误的整数
- excel - 将 D 列中的所有值从具有未知行数的未知工作表复制到主工作表中的单个 A 列值
- arrays - 保存阵列猫鼬
- python - Python:AttributeError:'builtin_function_or_method'对象没有属性'sleep'
- wordpress - 如何将文件附加到 WordPress 预订插件发送的电子邮件中?
- python - PySpark org.apache.spark.sql.AnalysisException:找不到表或视图:
- c# - Unity 按钮不起作用/突出显示/交互
- android - 即使数据不为空,onResponse 中的 Retrofit2 NULL 响应也是如此
- c# - 不了解异步等待概念
- c# - ASP MVC 的 NLog 未写入数据库