Configuration for Transferring JSON from Azure Cosmos DB to Snowflake Using Azure Data Factory

Question

Configuration for Transferring JSON from Azure Cosmos DB to Snowflake Using Azure Data Factory

Sowndar Kumaresan 0

We need to transfer an entire JSON object from Azure Cosmos DB to a Snowflake table. We have created a table in Snowflake with a single column named emp_details of type VARIANT to store the JSON data.

How can we configure the mapping in Azure Data Factory (ADF) to ensure that the entire JSON object, such as the following, is pushed into the single variant emp_details column in Snowflake?

{ "employee": { "id": 1, "name": "John Doe", "position": "Software Engineer", "salary": 75000, "location": { "city": "Seattle", "state": "Washington" } } }

we are not able to roll up the source as single JSON object in the mapping

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-16T04:31:04.45+00:00
@Sowndar Kumaresan

Thanks for reaching out to Microsoft Q&A

To transfer an entire JSON object from Azure Cosmos DB to a Snowflake table using Azure Data Factory (ADF), you can follow these steps.

Step 1: Create Linked Services

Set up a linked service to connect to your Azure Cosmos DB.

Create a linked service for Snowflake, ensuring you have the necessary credentials and permissions.

Step 2: Create Datasets

Create a dataset for your source (Cosmos DB). Ensure that the dataset is configured to read the JSON data.

Create a dataset for your destination (Snowflake). Set the type to SnowflakeV2Table and specify the table name where you want to store the JSON.

Step 3: Configure the Copy Activity

In ADF, create a new pipeline and add a Copy Data activity.

Select your Cosmos DB dataset as the source.

In the Source Options, ensure that you select the option to read the entire JSON object. You may need to set the JSON Path to $ to capture the entire object.

Sink Configuration:

Select your Snowflake dataset as the sink.

In the Mapping tab, map the source to the emp_details column in Snowflake. You should map the entire JSON object to this column.

Step 4: JSON Mapping

In the mapping configuration, ensure that the source is set to output the entire JSON object. You might need to use a Data Flow if the direct mapping does not work as expected.

If using Data Flow, add a Select transformation to ensure the JSON structure is preserved, and then map it to the emp_details column.

Step 5: Testing

Run the pipeline to test the configuration. Check the Snowflake table to ensure that the JSON object is stored correctly in the emp_details column.

Hope this helps. Do let us know if you any further queries.
Sowndar Kumaresan 0 Reputation points

2024-10-16T06:04:23.6733333+00:00
thanks for your response ,we tried the flow you referred above (NOTE- we are using Cosmos NoSQL as source)

Can you be more specific on the below point ? As a Copy Data, In the Source Options, ensure that you select the option to read the entire JSON object. You may need to set the JSON Path to $ to capture the entire object. where can I see source options and also there is no way to set JSON Path as $ ?

As a Data Flow , add a Select transformation to ensure the JSON structure is preserved, and then map it to the emp_details column.

for the above point can you give screen shot reference for entire JSON mapping process ?
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-18T10:54:05.7666667+00:00
@Sowndar Kumaresan

thanks for addressing

Go to the Mapping tab of the Copy Data activity.

Here, you need to create an explicit mapping. You can do this by specifying the JSON path to the entire object you want to push into the Snowflake column.

{ "source": { "path": "$" }, "sink": { "name": "emp_details" } }

This mapping indicates that you want to take the entire JSON object (denoted by $) and map it to the emp_details column in Snowflake.

please go through link for your follow up questions which has complete details a

https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-21T08:06:04.7133333+00:00

@Sowndar Kumaresan We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sowndar Kumaresan 0 Reputation points

2024-10-22T09:38:40.6166667+00:00

sorry for delay in response @phemanth

this is the warning that we are getting, is this the right way that we have added or else let me know the required steps

And also one more error throws when we try to publish with import schema option, we found error as below
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-23T05:56:09.93+00:00
@Sowndar KumaresanEnable staging: If you're unable to convert your data to a supported format or if you're using a different source, you can enable staging in Snowflake. Staging allows you to upload your data to a temporary location in Snowflake before loading it into your target table.

For the error you're seeing when using the "Import Schema" option, we need more information to troubleshoot it effectively. Please provide the following:

The exact error message: Include the full text of the error message, including any error codes.

Your answer

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-16T04:31:04.45+00:00

@Sowndar Kumaresan

Thanks for reaching out to Microsoft Q&A

To transfer an entire JSON object from Azure Cosmos DB to a Snowflake table using Azure Data Factory (ADF), you can follow these steps.

Step 1: Create Linked Services

Set up a linked service to connect to your Azure Cosmos DB.

Create a linked service for Snowflake, ensuring you have the necessary credentials and permissions.

Step 2: Create Datasets

Create a dataset for your source (Cosmos DB). Ensure that the dataset is configured to read the JSON data.

Create a dataset for your destination (Snowflake). Set the type to SnowflakeV2Table and specify the table name where you want to store the JSON.

Step 3: Configure the Copy Activity

In ADF, create a new pipeline and add a Copy Data activity.

Select your Cosmos DB dataset as the source.

In the Source Options, ensure that you select the option to read the entire JSON object. You may need to set the JSON Path to $ to capture the entire object.

Sink Configuration:

Select your Snowflake dataset as the sink.

In the Mapping tab, map the source to the emp_details column in Snowflake. You should map the entire JSON object to this column.

Step 4: JSON Mapping

In the mapping configuration, ensure that the source is set to output the entire JSON object. You might need to use a Data Flow if the direct mapping does not work as expected.

If using Data Flow, add a Select transformation to ensure the JSON structure is preserved, and then map it to the emp_details column.

Step 5: Testing

Run the pipeline to test the configuration. Check the Snowflake table to ensure that the JSON object is stored correctly in the emp_details column.

Hope this helps. Do let us know if you any further queries.
Sowndar Kumaresan 0 Reputation points

2024-10-16T06:04:23.6733333+00:00

thanks for your response ,we tried the flow you referred above (NOTE- we are using Cosmos NoSQL as source)

Can you be more specific on the below point ? As a Copy Data, In the Source Options, ensure that you select the option to read the entire JSON object. You may need to set the JSON Path to $ to capture the entire object. where can I see source options and also there is no way to set JSON Path as $ ?

As a Data Flow , add a Select transformation to ensure the JSON structure is preserved, and then map it to the emp_details column.

for the above point can you give screen shot reference for entire JSON mapping process ?
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-18T10:54:05.7666667+00:00

@Sowndar Kumaresan

thanks for addressing

Go to the Mapping tab of the Copy Data activity.

Here, you need to create an explicit mapping. You can do this by specifying the JSON path to the entire object you want to push into the Snowflake column.

{ "source": { "path": "$" }, "sink": { "name": "emp_details" } }

This mapping indicates that you want to take the entire JSON object (denoted by $) and map it to the emp_details column in Snowflake.

please go through link for your follow up questions which has complete details a

https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-21T08:06:04.7133333+00:00

@Sowndar Kumaresan We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sowndar Kumaresan 0 Reputation points

2024-10-22T09:38:40.6166667+00:00

sorry for delay in response @phemanth

this is the warning that we are getting, is this the right way that we have added or else let me know the required steps

And also one more error throws when we try to publish with import schema option, we found error as below
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-10-23T05:56:09.93+00:00

@Sowndar KumaresanEnable staging: If you're unable to convert your data to a supported format or if you're using a different source, you can enable staging in Snowflake. Staging allows you to upload your data to a temporary location in Snowflake before loading it into your target table.

For the error you're seeing when using the "Import Schema" option, we need more information to troubleshoot it effectively. Please provide the following:

The exact error message: Include the full text of the error message, including any error codes.

Share via

Configuration for Transferring JSON from Azure Cosmos DB to Snowflake Using Azure Data Factory

Your answer