Copy data from Snowflake to CosmosDB

Question

Copy data from Snowflake to CosmosDB

Raja 86

Hi Team,
For one of the requirement I need to copy data from Snowflake to Cosmos DB. Later I want to expose the data using Rest API on top of Cosmos DB (built in feature). But that's for later. Right now my problem is to copy the data.

Since data is simple and does not require much transformation I thought it should be a simple thing to do using ADF. So I plan to use a ADF pipeline and inside pipeline I plan to use Copy Data Activity.

The data in the Snowflake(The source) looks like,

And the data in the Cosmos DB should look like as below,

{
"id": "123",
"String Col": "Some String",
"Some Ints": [
123,
234
],

After configuring all the basic steps like creating linked service, mapping the columns etc. when I try to publish the pipeline it gives an error,

Direct copying data from Snowflake is only supported when sink dataset is DelimitedText, Parquet or JSON with Azure Blob Storage linked service, for other dataset or linked service, please enable staging

****Question 1:** Is the copying from Snowflake to Cosmos DB not supported?**

As I understood the answer to above question is "not supported" and to copy the data between these two we need to use an option "staging". Which means a blob storage would be used as temporary staging storage.

****Question 2:** Is my above understanding correct?**

If answer to question #2 is correct then I went ahead and thought to use a existing blob storage. It gives me other error,

****Question 3:** I really do not understand what should we do now? There's no concrete example as how to retrieve the Sas url to the blob. Does this url need to be pointing to an existing file location?**

****Question 4:** Doing all this to copy just a simple table, is it worth doing it all this? Is this a good solution?**

Helps is highly appreciated.

Accepted answer

0 additional answers

Your answer

Answer 1

Saurabh Sharma 23,846 Microsoft Employee Moderator

Hi @Raja ,

Question 1: Is the copying from Snowflake to Cosmos DB not supported? Question 2: Is my above understanding correct?
No, it is not supported currently and direct copying from Snowflake to sink is supported (With shared access signature authentication) if the sink is a Azure Blob Storage and sink dataset format is of Parquet, delimited text, or JSON. Please refer to the documentation.

Question 3: I really do not understand what should we do now? There's no concrete example as how to retrieve the Sas url to the blob. Does this url need to be pointing to an existing file location?
You need to enable staging as stated in the error message. In order to retrieve SAS URL for the blob you need to go to your blob storage account on azure portal and navigate to "Shared Access Signature" blade. You can configure the required set of permissions this SAS token could contain by selecting "Allowed Services" as Blob/File/Queue/Table. In your case you can select Blob as a service for staging.

You can also define which allowed resource types which could be Service/Container/Object. See below for details around the same.

Select Allowed Permissions as Read/Write/Delete/List etc.
You can select multiple options from above setting as per your requirement.

For staging (Read/Write) should be enough.
You can then generate the SAS tokens and use the "SAS Token" from the blade as highlighted above and use it in your linked service like below -

Hope this helps. Please let me know if you have any questions.

Please refer to the documentation - Staged Copy from Snowflake for any other additional details.

Thanks
Saurabh

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

4: https://learn.microsoft.com/en-us/azure/storage/common/storage-sas-overview [6]: https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake#staged-copy-from-snowflake

Raja 86 Reputation points

2021-06-30T09:35:02.807+00:00

Hi @Saurabh Sharma ,
I really appreciate that you provided detailed answer. Thanks for this!!.

Interestingly when I went through the documentation you suggested, I found an important point,

If your sink data store and format meet the criteria described in this section, you can use the Copy activity to directly copy from Snowflake to sink

For JSON format, direct copy only supports the case that source Snowflake table or query result only has single column and the data type of this column is VARIANT, OBJECT, or ARRAY.

Now if you look at the data that I want to copy(in my first post) has multiple columns (its actually a view resulting into say 10 columns).

!
Question 1: So in that case this 'Copy Activity' won't work?

Question 2: If no, what is the alternate solution to this?
a. Can we do something like : Query from Snowflake -> format-> dump to cosmosdb .... as 3 step pipeline? How can we achieve this?
b. Any better automated approach?

Appreciate any help!
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-06-30T21:47:02.68+00:00

Hi @Raja ,

The statement you are referring to in the document is meant for direct copy - when you are copying from Snowflake to Azure Blob storage. However, in your case when you are going to use the blob storage as staging then it should work fine with multiple columns. I have tried using the staging approach (using SAS token with Resource Type as (Container and object) and I could be able to see the row with all columns in CosmosDB. Please check the below screenshot.
Source data

Staging Settings

Pipeline Run Details

Target

Please try using the staging option and let me know if you see any issues and I would be happy to help you on the same.

Thanks
Saurabh
Raja 86 Reputation points

2021-07-01T17:23:28.617+00:00
Hi @Saurabh Sharma ,

Thank you very much for clarifying this. Probably I'll go ahead with the approach you have suggested.
But I feel if I'm going with this approach I'm using an extra storage resource. '

My scenario is simple :

I want to transfer data (1 view with say 10 columns) from Snowflake to CosmosDB. REST API to be used on top of Cosmos DB.

This batch we want to run say once in a day.

The data is not so huge

in this scenario what do you suggest is the best approach?
a. the approach I'm using copy activity?
b Using 'data flow' activity where I transfer the columnar data to JSON using expression language?
c. Any other approach .. may be using functions? or anything you want to suggest?
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-01T23:00:00.23+00:00

Hi @Raja ,

Another way to move data from Snowflake to CosmosDb is using Dataflow but you may be paying more compared copy data activity with storage as a staging.
It all depends the amount of data you are transferring from snowflake to CosmosDB. Please refer to this pricing page for for details. You could also use pricing calculator to get an approximation as well. You could refer to pricing concepts in terms examples by going through this documentation.

Also, if you are doing complex transformations then you can opt for dataflow, otherwise for basic data transfer I would suggest you to use copy data activity.
Hope this helps.
Please let me know if you have any other questions.

Thanks
Saurabh

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.
Raja 86 Reputation points

2021-07-05T04:10:03.66+00:00

Thanks a ton @Saurabh Sharma !!

This definitely answers my questions. As per our requirement we need to load data to Cosmos DB once in a day from snowflake. The data is not huge, some records say around 10k.

Meanwhile I tried option of 'Data Flow' and this did work for me. I want to reload the destination cosmosb db table(container) with each run. Means the cosmosb db would always be loaded with fresh data from snowflake. Going through the documentation I found that there is a mention of "truncate" but in UI its not really available.

So as I understand I need to re-create the container each time I need to load the data.

I have another question, I want to convert one column to integer , so I did toInteger("ColName") in the derived column transformation. In debug mode it shows it as integer but when it writes it writes as a string. Not suer why. This is the columns which is getting used as partition key. So I wanted it to be integer.
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-09T20:44:02.647+00:00

Hi @Raja ,
Which document you are looking at ? UI displays the settings based on the selected sink type and you may have checked it for Azure SQL sink.
Yes, are right, you need to re-create the container.
I am able to convert and write the string data in correct integer format to cosmos db.

.

Please verify if you are mapping the derived column correctly in the SINK. Please refer to the below Gif.

Thanks
Saurabh

Share via

Copy data from Snowflake to CosmosDB

0 additional answers

Your answer