从cosmos db查询特定的时间范围数据,并通过azure数据工厂将其存储在sql数据库中 [英] Query a specific time range data from cosmos db and store it in sql database via azure data factory
问题描述
我正在使用Azure Data Factory V2,并希望使用SQL查询将存储为文档的json数据从Azure cosmos db复制到azure sql表.
I am using Azure Data Factory V2 and want to copy the json data stored as documents from Azure cosmos db to a azure sql table, using a SQL query.
情况:我只想从Cosmos db查询最后一小时的json文件,并将其存储在sql表中.为此,我在数据工厂中创建了一个新的触发器,该触发器每1小时重复一次sql查询.
Situation: I want to query just the last hour json files from Cosmos db and store it in sql table. To do that, I create a new Trigger in my Data factory which recurrence the sql query each 1 hour.
问题:最初,我编写了一个查询,该查询从cosmos db中读取所有数据,并且可以正常工作,但是我只想查询存储在cosmos db中最后一小时内的数据.
Problem: At first I wrote a query that read all data from cosmos db and it work fine, but I want to query just the data which stored within the last hour in cosmos db.
我需要什么?在Sql中,如何编写查询以仅获取最近一个小时存储在cosmos db中的新文件?
What i need? In Sql, how to write a query to get just the new files which stored within the last hour in the cosmos db ?
推荐答案
您可以在cosmos db集合中定义udf.
You could define an udf in your cosmos db collection.
function getLastHourTime(){
var date = new Date();
var a = date.setHours(-1);
return a;
}
将您的sql修改为:
SELECT * FROM c where c.time >= udf.getLastHourTime()
注意:udf.getLastHourTime()返回 Unix时间戳
,您需要匹配格式.
Note: udf.getLastHourTime() returns Unix time stamp
, you need to match the format.
希望它对您有帮助.
这篇关于从cosmos db查询特定的时间范围数据,并通过azure数据工厂将其存储在sql数据库中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!