Copy activity: "Map complex values to string" not working

Question

I'm using Copy activity to copy data from a REST Linked Service to my Snowflake database.

The REST source is this public endpoint: https://pokeapi.co/api/v2/pokemon?limit=5 . Sample JSON data:

{"count":1292,"next":"https://pokeapi.co/api/v2/pokemon?offset=5&limit=5","previous":null,"results":[{"name":"bulbasaur","url":"https://pokeapi.co/api/v2/pokemon/1/"},{"name":"ivysaur","url":"https://pokeapi.co/api/v2/pokemon/2/"},{"name":"venusaur","url":"https://pokeapi.co/api/v2/pokemon/3/"},{"name":"charmander","url":"https://pokeapi.co/api/v2/pokemon/4/"},{"name":"charmeleon","url":"https://pokeapi.co/api/v2/pokemon/5/"}]}

I want to put this entire response into a table in Snowflake with 1 column of STRING data type. I turned on "Map complex values to string" so the JSON will be converted to string.

Here is the error I get:

ErrorCode=JsonUnsupportedHierarchicalComplexValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The retrieved type of data JObject is not supported yet. Please either remove the targeted column 'xxx' or enable skip incompatible row to skip the issue rows.,Source=Microsoft.DataTransfer.Common

From the error, it seems that the JSON data is not converted to string like I expected. Could you please tell me where I did wrong?

Here is my mapping tab:

I tried getting the array inside by changing "$" to "$.results", but the same error happens.

Here is the complete JSON for the ADF pipeline

{
    "name": "sample_extraction",
    "properties": {
        "activities": [
            {
                "name": "Copy data1",
                "type": "Copy",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "RestSource",
                        "httpRequestTimeout": "00:01:40",
                        "requestInterval": "00.00:00:00.010",
                        "requestMethod": "GET"
                    },
                    "sink": {
                        "type": "SnowflakeSink",
                        "importSettings": {
                            "type": "SnowflakeImportCopyCommand"
                        }
                    },
                    "enableStaging": true,
                    "stagingSettings": {
                        "linkedServiceName": {
                            "referenceName": "staging_blob",
                            "type": "LinkedServiceReference"
                        },
                        "path": "mystagingpath"
                    },
                    "enableSkipIncompatibleRow": false,
                    "translator": {
                        "type": "TabularTranslator",
                        "mappings": [
                            {
                                "source": {
                                    "path": "$"
                                },
                                "sink": {
                                    "name": "response"
                                }
                            }
                        ],
                        "collectionReference": "",
                        "mapComplexValuesToString": true
                    }
                },
                "inputs": [
                    {
                        "referenceName": "toan_test_rest_pokemon",
                        "type": "DatasetReference",
                        "parameters": {
                            "endpoint": "pokemon",
                            "query_param": {
                                "limit": 5
                            }
                        }
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "SnowflakeTables",
                        "type": "DatasetReference"
                    }
                ]
            }
        ],
        "annotations": [],
        "lastPublishTime": "2023-09-29T04:31:45Z"
    },
    "type": "Microsoft.DataFactory/factories/pipelines"
}

Answer 1

Answering my own questions for those having the same issue: I eventually resigned to having 2 Copy activities: 1 to copy data from API source to a Blob storage, and another to copy data from that Blob storage directly to Snowflake. It's the same idea of staging copy through Blob storage that ADF already had, but this one works for copying data to a single VARIANT column in Snowflake.

Edit: can't mark my own answer as Accepted Answer.

Answer 2

Hi Brian Toan,

Thank you for posting query in Microsoft Q&A Platform.

You can consider making API call using web activity, and then take web activity response into string variable. And then, in Copy activity, under source use additional column field to pass this variable as column. And then, under Mappings of Copy activity, map this additional column to your sink table column.

Step1: Web activity to make API call.

Step2: Read output json of web activity, to save response in to variable. For this we can use set variable activity. Consider checking below video for passing output of one activity to another.

How to read JSON output of one Activity in to another Activity in Azure Data Factory

Step3: In copy activity, under source use some dummy dataset, pointing to some dummy file and then use additional columns field to create a new column and pass your variable as value. Check below video to know about adding additional columns.

Add additional columns during copy in Azure Data Factory

And then under Mappings, map this additional column with column in sink table.

Hope this helps. Please let me know how it goes or if any further queries.

---

Please consider hitting Accept Answer button. Accepted answers help community as well.