Azure数据工厂-按日期过滤Mongodb源数据集 [英] Azure Data Factory - filter Mongodb source dataset by date

问题描述

这种情况非常简单，如ADFv2文档和示例中所述，我创建了一个复制管道来从MongoDB集合中获取数据，并将其写入Azure SQL数据库.

This scenario is pretty straightforward, as described in ADFv2 docs and samples, I've created a copy pipeline to get the data from MongoDB collection, and write it to Azure SQL database.

已成功传输完整的收集数据，并且正确设置了所有映射.当我尝试过滤源数据集以仅从MongoDB获取最近的 n 天时，问题就开始了.我尝试了几个查询，并与MongoDB Compass进行了交叉检查，以查看它们是否实际上在执行Mongo方面.归结为以下过滤查询:

Full collection data is successfully transfered and all the mappings are set correctly. The problem starts when I try to filter the source dataset to get only the last n days from MongoDB. I've tried several queries, and cross-checked with MongoDB Compass to see if they're actually executing Mongo side, which they are. It has come down to this filter query:

{"CacheTime":{$ gt:new Date(ISODate().getTime()-1000 * 60 * 60 * 24 * 5)}}

它在MongoDB上成功执行，但在ADF上失败，并显示错误:

which executes successfully on MongoDB, but fails on ADF with error:

属性值'{"CacheTime":{$ gt:new Date(ISODate().getTime()-1000 * 60 * 60 * 24 * 5)}}'无效..活动ID:xxxxx

所以我尝试使用ADF动态内容以ISODate格式设置当前日期，并将整个过滤器作为字符串传递:

So I tried using ADF dynamic content to set the current date in ISODate format and pass the whole filter as a string:

@concat('{"CacheTime":{$ gt:ISODate('，formatDateTime(utcnow()，'yyyy-MM-ddThh:mm:ssZ')，'"}}}'))

此失败并显示类似错误:

This one fails with a similar error:

属性值'{"CacheTime":{$ gt:new Date(ISODate("2019-11-15T10:45:16Z")-1000 * 60 * 60 * 24 * 2)}}''是无效的..活动ID:xxxxx

属性值本身({"CacheTime":{$ gt:new Date(ISODate("2019-11-15T10:45:16Z")-1000 * 60 * 60 * 24 * 2)}})仅执行在MongoDB上很好.而且我找不到它不能在ADF上运行的原因.检查MongoDB文档是否与受支持的MongoDB版本有关.我的MongoDB版本是4.0，但是查询中的函数是简单的日期函数，因此我认为过滤器查询应该不会失败.

The property value itself ({"CacheTime": {$gt: new Date(ISODate("2019-11-15T10:45:16Z")-1000*60*60*24*2)}}) executes just fine on MongoDB. And I can't find the reason why it doesn't work on ADF. Checked the MongoDB documentation if this has something to do with the supported MongoDB version. My MongoDB version is 4.0, but the functions in my query is simple date functions so I believe filter query should not fail.

是否有人将带有日期的成功查询过滤器传递给MongoDB并得以幸存?

Has anyone passed a successful query filter with dates to MongoDB and survived?

推荐答案

尝试一下.

{"CacheTime":{$ gt:ISODate("@ {adddays(utcnow()，-5)}")}}}

{"CacheTime":{$gt: ISODate("@{adddays(utcnow(),-5)}")}}

这篇关于Azure数据工厂-按日期过滤Mongodb源数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！