Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
ADF Copy activity unable to write the JSON data in snowflake table
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(169.60000000000002, 98%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EPC%3C/text%3E%3C/svg%3E)
pankaj chaturvedi
91
Reputation points
Hi Team,
I am extracting JSON data from blob container and loading to snowflake table using Copy Activity in ADF.
I created table CREATE TABLE API_TEST(JSONDATA VARIANT) in snowflake and loading data into API_TEST table . I am not passing any schema mapping. When I passed schema mapping then data is loaded to snowflake but that's not the way to load the JSON data in snowflake as we know Snowflake does support VARIANT Data type feature which hold all the JSON data.
Could you please let me know does ADF support that feature which can load the JSON data in snowflake that table has VARIANT data type field.
I am looking forward your response.
Many thanks,
Azure Data Factory
{count} votes
-
pankaj chaturvedi 91 Reputation points
2023-02-05T10:15:50.97+00:00 Hi Team, Can you please respond. I am looking forward for your response.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(134.4, 9%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKM%3C/text%3E%3C/svg%3E)
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator2023-02-07T01:02:26.2066667+00:00 Thanks for using Microsoft Q&A forum and posting your query.
As per ADF Snowflake connector documentation, it clearly states that for JSON format, direct copy only supports the case that sink Snowflake table only has
single columnand the data type of this column isVARIANT,OBJECT, orARRAY.-
compressioncan be no compression, gzip, bzip2, or deflate. -
encodingNameis left as default or set to utf-8. - Column mapping is not specified.
Ref doc: Direct copy to Snowflake
Hope this info helps.
-
-
pankaj chaturvedi 91 Reputation points
2023-02-07T11:38:21.64+00:00 Hi @KranthiPakala-MSFT ,Thanks for your response. As I mentioned above I tried in the same way, But could not do the data load. Can you please try from your side and let me know what I am missing here.Thanks.
-
pankaj chaturvedi 91 Reputation points
2023-02-07T11:40:57.7933333+00:00 My copy activity is not failing it is succeeding but not loading any data to snowflake table
-
pankaj chaturvedi 91 Reputation points
2023-02-07T13:18:14.35+00:00 Hi @KranthiPakala-MSFT Please find sample REST API
SOURCE: https://dummy.restapiexample.com/api/v1/employeesTARGET: CREATE TABLE SAMPLE*API(JSON_*DATA VARIANT)
Now you can please try using Copy activity in ADF and let me know if you are able to achieve.
-
pankaj chaturvedi 91 Reputation points
2023-02-07T14:04:59.8566667+00:00 Hi @KranthiPakala-MSFT and Team, I am looking forward you response.
-
pankaj chaturvedi 91 Reputation points
2023-02-10T09:42:41.1066667+00:00 Hi Team, I did not get any response. Could you please look into it and help me out.
-
pankaj chaturvedi 91 Reputation points
2023-02-13T05:33:07.05+00:00 Hi Team,
I didn't get any response since long. Could you please respond someone and help me out on this request. Thanks!
-
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator
2023-02-14T08:25:21.98+00:00 Sorry for the delay. Just wanted to let you know that I have reached out to the product team to confirm the behavior of this connector w.r.t your scenario and awaiting their response. I'll keep you posted as soon as I hear back from the team.
We appreciate your patience.
-
pankaj chaturvedi 91 Reputation points
2023-02-16T06:54:06.47+00:00 HI @KranthiPakala-MSFT ,Did you get any update on this.? Did you get chance to implement using SAMPLE API to Snowflake VARIANT Column using ADF copy activity.
Can you please try and let me know if you are able to achieve.
-
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator
2023-02-17T00:59:41.4033333+00:00 Hi @pankaj chaturvedi , Sorry I haven't heard back from the product team yet and I do not have a snowflake instance to test this scenario.
But as per the product documentation, to load JSON data into Snowflake using the VARIANT data type, you need not pass a schema mapping in the copy activity.
As this seems be a sort of urgency for you, I recommend filing a support ticket for deeper investigation and immediate assistance. In case if you don't have a support plan, please let me know so that I can check on alternative options.
If you have already filed a support ticket, please do share the SR# so that we can track it internally.
Thank you
-
pankaj chaturvedi 91 Reputation points
2023-02-17T08:46:58.7866667+00:00 Hi @KranthiPakala-MSFT , I believe you can use free trial account for snowflake and try to implement if it is possible.Thanks.
-
pankaj chaturvedi 91 Reputation points
2023-02-21T07:48:44.2+00:00 Hi @KranthiPakala-MSFT ,Did you get any update on this or did you get chance to implement this. Could you please let me know.Thanks!
-
pankaj chaturvedi 91 Reputation points
2023-02-24T10:55:25.5233333+00:00 Hi @KranthiPakala-MSFT and Team, Can you please respond. Thanks.
-
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator
2023-02-25T01:10:36.0633333+00:00 Hi @pankaj chaturvedi ,
Sorry we haven't received the response form the internal team yet. But if this is a blocker, I would recommend filing a support ticket for immediate assistance. In case if you don't have a support plan, please let me know so that we can enable a onetime free support ticket to closely work on this issue.
Thank you
-
pankaj chaturvedi 91 Reputation points
2023-02-28T07:41:37.7033333+00:00 Hi @KranthiPakala-MSFT , I have already raised a support ticket and team is still working on this(since 2 weeks).But didn't get the solution yet. Could you please try implementing from your side if you have some bandwidth. thanks.
-
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator
2023-03-01T23:43:23.91+00:00 @pankaj chaturvedi Thanks for confirming. I am trying to spin up a Snowflake trial instance and will keep you posted on my findings soon.
In the meantime, could you please share the Support ticket number here so that I can double check internally on the current status and if needed, I can tag that in my escalation to product team.
Thank you
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(19.2, 1%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESM%3C/text%3E%3C/svg%3E)
Sowjanya Mandalapu 0 Reputation points2023-04-13T19:35:08.36+00:00 Hi @KranthiPakala-MSFT and Team, I have the same requirement. Did you get any solution from support or Do you guys have any solution.
-
pankaj chaturvedi 91 Reputation points
2023-09-05T12:38:37.59+00:00 Hi Team and @KranthiPakala-MSFT , any update on this question??
Sign in to comment
Sign in to answer