none
ADF Copy Activity error mapping JSON to SQL RRS feed

  • Question

  • I have an Azure Data Factory Copy Activity that is using a REST request to elastic search as the Source and attempting to map the response to a SQL table as the Sink. Everything works fine except when it attempts to map the data field that contains the dynamic JSON. I get the following error:

    { "errorCode": "2200", "message": "ErrorCode=UserErrorUnsupportedHierarchicalComplexValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The retrieved type of data JObject with value {"name":"department"} is not supported yet, please either remove the targeted column or enable skip incompatible row to skip them.,Source=Microsoft.DataTransfer.Common,'", "failureType": "UserError", "target": "CopyContents_Paged", "details": [] }

    Here's an example of my mapping configuration:

    "translator": {
    	"type": "TabularTranslator",
    	"mappings": [
    		{
    			"source": {
    				"path": "['_source']['id']"
    			},
    			"sink": {
    				"name": "ContentItemId",
    				"type": "String"
    			}
    		},
    		{
    			"source": {
    				"path": "['_source']['status']"
    			},
    			"sink": {
    				"name": "Status",
    				"type": "Int32"
    			}
    		},
    		{
    			"source": {
    				"path": "['_source']['data']"
    			},
    			"sink": {
    				"name": "Data",
    				"type": "String"
    			}
    		}
    	],
    	"collectionReference": "$['hits']['hits']"
    }
    The JSON in the data object is dynamic so I'm unable to do an explicit mapping for the nested fields within it. That's why I'm trying to just store the entire JSON object under data in a column of a SQL table.

    I have also tried to do an explicit cast, but this throws a different error:
    "source": {
    	"path": "@string(['_source']['data'])"
    }
    How can I adjust my mapping configuration to allow this to work properly?

    Thursday, December 5, 2019 5:59 PM

Answers

  • Hello Keith.  I learned something new from the internal team.  Since you are using a tabular sink, there is an additional option you need to add.  "mapComplexValuesToString" .  

    I plan to experiment with this when I get a chance.

    • Marked as answer by Keith Wyss Monday, December 9, 2019 7:18 PM
    Monday, December 9, 2019 6:34 PM
    Moderator

All replies

  • Hello Keith Wyss and thank you for your question.  I will reach out internally to find out more information.
    Thursday, December 5, 2019 11:37 PM
    Moderator
  • First, can you share some sample data.  Redact / scrub if necessary.
    Thursday, December 5, 2019 11:46 PM
    Moderator
  • Yeah, below is a sample from the elasticsearch query that I'm attempting to map to SQL. You will see the fields referenced in the mapping I posted above also referenced in this sample data so you have some context. Let me know if there is anything else I can give you. Thanks for the help.

    [ 
       { 
          "took":12,
          "timed_out":false,
          "_shards":{ 
             "total":5,
             "successful":5,
             "skipped":0,
             "failed":0
          },
          "hits":{ 
             "total":94461,
             "max_score":1,
             "hits":[ 
                { 
                   "_index":"krakenqa_states_contents",
                   "_type":"elasticcontententity",
                   "_id":"316bfbff-face-4951-828c-fb339d598287",
                   "_score":1,
                   "_source":{ 
                      "id":"316bfbff-face-4951-828c-fb339d598287",
                      "documentId":"316bfbff-face-4951-828c-fb339d598287",
                      "status":10,
                      "lastModified":"2017-11-22T15:10:54Z",
                      "version":1,
                      "lastModifiedBy":"subject:043ec6a7-515e-4fef-93cd-c815b6447cdf",
                      "created":"2017-11-22T15:10:54Z",
                      "isDeleted":false,
                      "ai":"045c197f-ba36-4883-80b1-18f3e5f62fe3",
                      "appId":{ 
                         "id":"045c197f-ba36-4883-80b1-18f3e5f62fe3",
                         "name":"patternsupdate"
                      },
                      "si":"07b25b3e-251b-4cbf-92d3-4978ee4b767c",
                      "schemaId":{ 
                         "id":"07b25b3e-251b-4cbf-92d3-4978ee4b767c",
                         "name":"employee"
                      },
                      "createdBy":"subject:043ec6a7-515e-4fef-93cd-c815b6447cdf",
                      "dt":"",
                      "data":{ 
                         "stringFieldProperties":[ 
                            { 
                               "name":"firstname",
                               "fieldData":{ 
                                  "en":"Emilee"
                               }
                            },
                            { 
                               "name":"lastname",
                               "fieldData":{ 
                                  "en":"test"
                               }
                            },
                            { 
                               "name":"emailaddress",
                               "fieldData":{ 
                                  "en":"test@gmail.com"
                               }
                            }
                         ],
                         "referencesFieldProperties":[ 
                            { 
                               "name":"department",
                               "fieldData":{ 
                                  "en":[ 
    
                                  ]
                               }
                            }
                         ],
                         "assetsFieldProperties":[ 
                            { 
                               "name":"image",
                               "fieldData":{ 
                                  "en":[ 
    
                                  ]
                               }
                            }
                         ]
                      },
                      "rf":[ 
                         { 
                            "id":"9358f387-3cb3-4853-9e19-7cf5ee1f9b57"
                         }
                      ],
                      "rd":[ 
    
                      ],
                      "tg":[ 
    
                      ],
                      "tgd":[ 
    
                      ],
                      "cats":[ 
    
                      ],
                      "ps":null,
                      "tg_draft":[ 
    
                      ],
                      "tgd_draft":[ 
    
                      ],
                      "cats_draft":[ 
    
                      ],
                      "sj":null,
                      "sj2":null,
                      "isPending":false,
                      "nd":null,
                      "ag":null,
                      "ac":null
                   }
                }
             ]
          }
       }
    ]


    • Edited by Keith Wyss Friday, December 6, 2019 2:11 PM
    Friday, December 6, 2019 2:10 PM
  • Thank you.  I know in the past I tried to do almost the same thing and was unable to.  I have escalated the issue.

    This may be possible to do in Data Flow. ( REST -> BLOB -> Data Flow -> SQL to get around limitation ).  If so, would you be willing to try Data Flow?

    Friday, December 6, 2019 10:43 PM
    Moderator
  • I'm not as familiar with Data Flow but I am willing to try that route if we are unable to find a way to make this work using the Copy Data activity.
    Monday, December 9, 2019 1:17 PM
  • Hello Keith.  I learned something new from the internal team.  Since you are using a tabular sink, there is an additional option you need to add.  "mapComplexValuesToString" .  

    I plan to experiment with this when I get a chance.

    • Marked as answer by Keith Wyss Monday, December 9, 2019 7:18 PM
    Monday, December 9, 2019 6:34 PM
    Moderator
  • That did it! Thanks for pointing that out. I hadn't seen that option listed in any documentation I read so that was a big help!
    Monday, December 9, 2019 7:17 PM