Azure Cosmos DB
An Azure NoSQL database service for app development.
982 questions
A community member has associated this post with a similar question:
filter data from json
Only moderators can edit this content.
creating etl by filter rows from cosmos db api
Abedin, Masood
20
Reputation points
I am new to azure cloud platform and my task is to create ETL pipeline to populate data from cosmos db into snowflake. I have Azure Cosmos DB for MongoDB API connection string and so far I have created a linked service in the ADF. I am not sure on how to do an incremental load. I am only interested in picking up rows that are inserted or changed.
Someone suggested to look at this article but I don't want to write code and would like to work inside Azure Data Factory.
I can output the data in JSON format and I know how to upload into Snowflake from azure blob storage.
I was thinking of only grab records where UpdateDate = getdate() - 1 but not sure if it's possible from the following Sample data:
[{
"_id": {
"$binary": "H82Maue1G0y+3hmOnKYNoQ==",
"$type": "03"
},
"Type": 0,
"NoteId": 1518722702,
"Number": 9,
"PinnedInfo": null,
"CreatedDate": [
638071336835472472,
180
],
"UpdatedDate": [
638137843666973190,
60
],
"ScheduledDate": [
638071228585440000,
0
],
"ModifiedDate": null,
"FollowUpDate": null,
"Visibility": 0,
"MailedTo": null,
"ReschedReason": null,
What should be my default value so it matches with UpdateDate?
I am stuck at this point due to lack of knowledge and would appreciate the assistance.
Thanks in advance
{count} votes
-
MartinJaffer-MSFT 25,571 Reputation points Microsoft Employee
2023-03-16T21:33:51.8966667+00:00 @Abedin, Masood Hello and welcome to Microsoft Q&A.
I was just helping Geetha respond in your other thread. She is an expert on Cosmos while I am an expert on Data Factory/ Synapse pipelines.
There are two answers depending upon whether you are using Tumbling WIndow Trigger or not. I saw "WindowStart" / "WindowEnd" , so I thought you might be doing tumbling window to define update periods for incremental loads.
The short answer of what went wrong, was pipeline parameters were not evaluating as expressions, so instead of passing a date, you passed the instructions, literally
formatDateTime(.... All pipeline expressions start with@or@{, otherwise they are just text. Hence the error message. Expression are not evaluated in pipeline parameter default values.I also noticed you were putting the same value for both the
less thanoperator and thegreater than or equal tooperator. A date cannot be both less than and greater than the same thing at the same time.Since both question threads are on the same topic, and a comprehensive answer was posted in the other, I'd like to mark this thread as duplicate. However this thread does have the better title.