Azure data pipeline fails when parsing array data from cosmos db

Question

Azure data pipeline fails when parsing array data from cosmos db

Francis Gesora 1

I use Aure data factory to run data pipelines from cosmosDB to mysql db. all datatypes work well, except for array data types which fail with the error message:

Operation on target Copy_XxxCollection failed: ErrorCode=UserErrorSchemaMappingCannotInferSinkColumnType,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Data type of column 'xxxx' can't be inferred from 1st row of data, please specify its data type in mappings of copy activity or structure of DataSet.,Source=Microsoft.DataTransfer.Common,'

I have enabled "map complex values to string" whcih doesnt seemt to fix. My arrays asically hold user defined values and the number of values in a single json object depends on the user - one useer could define 3 values, another could define 10 etc.

Is there any way to map the arrays successfully to a single sql field?

KranthiPakala-MSFT 46,827 Reputation points Moderator

2022-03-29T19:25:49.15+00:00

Hello @Francis Gesora ,

Thanks for the question and using MS Q&A platform.

As per my understanding, when you are trying to copy data from Cosmos DB to mySql DB, you are receiving above error when the source datatype is of Array type. Please correct me if I'm wrong.

Have you tried to mark the array as collection reference and was the check box for collection reference selected as shown below? If not, could you please try it and let us know if that helps to overcome the issue.

NOTE: Please note that for records where the array marked as collection reference is empty and the check box is selected, the entire record is skipped.

For more information about the schema mapping please refer to this document - Hierarchical source to tabular sink schema mapping

If you still have the issue, please do share a sample JSON of your source with dummy data and also the mapping settings image so that we can better assist you.

Do let us know how it goes.

Thank you
Francis Gesora 1 Reputation point

2022-03-30T10:04:54.767+00:00

Hello,

Yes have done as advised but getting same error : "
Operation on target CopyTags failed: ErrorCode=UserErrorSchemaMappingCannotInferSinkColumnType,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Data type of column 'skills' can't be inferred from 1st row of data, please specify its data type in mappings of copy activity or structure of DataSet.,Source=Microsoft.DataTransfer.Common,'"

Below is sample of data being pushed:
{
"userId" : ObjectId("6238e03b9352bf35d260accc"),
"skillTags" : [ "Farming", "Interior Design", "Videography", "Events Planning", "Art" ]
}

Mysl schema:
CREATE TABLE usertags (
id int(20) NOT NULL AUTO_INCREMENT,
userId varchar(100) DEFAULT NULL,
skills mediumtext,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Francis Gesora 1 Reputation point

2022-04-07T11:55:23.743+00:00

Hello @KranthiPakala-MSFT any thoughts on the feedback i gave?