Get Last value of timestamp in Azure Data Factory with Cosmos as source

Question

My pipeline has 2 activities for this test. A lookup activity and a stored procedure which simply takes the output of lookup and then uses it as a parameter to execute.

I want to the get the max value of _ts field (Comos DB Unix Timestamp) in a query that I'm writing.

This is my query on the source:

select max(c._ts) AS UnixTimestamp  from c 
where udf.convertTime(c._ts) >= '@{formatDateTime(addhours(pipeline().TriggerTime, -1), 'yyyy-MM-ddTHH:mm:ssZ' )}' 
AND udf.convertTime(c._ts) < '@{formatDateTime(pipeline().TriggerTime, 'yyyy-MM-ddTHH:mm:ssZ' )}'

When I debug this is the translated input:

{
    "source": {
        "type": "DocumentDbCollectionSource",
        "query": "select max(c._ts) AS UnixTimestamp  from c \nwhere udf.convertTime(c._ts) >= '2018-11-06T18:19:56Z' \nAND udf.convertTime(c._ts) < '2018-11-06T19:19:56Z'",
        "nestingSeparator": "."
    },
    "dataset": {
        "referenceName": "SourceComosDB",
        "type": "DatasetReference",
        "parameters": {}
    },
    "firstRowOnly": false
}

The output of the lookup activity is coming out to be:

{
    "count": 18,
    "value": [
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {}
    ],
    "effectiveIntegrationRuntime": "DefaultIntegrationRuntime (East US 2)"
}

and hence the stored procedure is not executed.

How can I get the max value of _ts within the query?

Jay Gong Jay Gong · Accepted Answer · 2018-11-07T03:18:25

I tested your code and did not reproduce your issue.

my sample code:

lookup activity output with your SQL:

One thing, it can't output 18 values if you used MAX query, supposed to be just one value.

The another thing, you should execute the SQK in cosmos db query editor to see if any results match the filter.

Get Last value of timestamp in Azure Data Factory with Cosmos as source

1 Answers