Data Factory: Copying Large SQL Database to REST API

Question

Data Factory: Copying Large SQL Database to REST API

Anonymous

Hello Microsoft,

I am currently attempting to move data from a large SQL database into a REST API using Azure Data Factory. This table has 100,000+ Rows. My Initial attempt was to use a look up activity to get the data into a JSON format, and then use a for each activity to load the data into the REST API.

This API Can only accept one record at a time, so parallelization is key.

However, this implementation has a limitation, lookUp can only pull around 5000 records at a time, so I would need to nest that flow within another forEach Activity to iterate through the entirety of my table. Is this optimal?

I noticed that the copy Activity recently supports copying to REST API but I am running to issues pushing records to the API with this method. I am currently getting this Error:

Using a similar connection Method works fine in a web activity so I am left to assume that the payload from the copy activity is being provided in an obscure way. The body for the record needs to be in this format {Column1 : Value, Column2 : Value,.......}

Can I get some assistance debugging this?

Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-08-24T20:35:36.99+00:00

Hi @Anonymous ,

Thanks for using Microsoft Q&A !!
I suspect you are passing the entire source resultset to your REST API, however you mentioned that your API handles only record at a time. Can you please check by passing only one single row in Copy Activity by using Query instead of table in your source and check if this works.

Also, you can compare your debug runs of Lookup vs Copy activity to see what exactly is getting passed to the REST API.

Thanks
Saurabh
Carlos Mario Villadiego Arroyo 1 Reputation point

2021-08-24T21:25:36.49+00:00

Hello,

Why not just use a copy activity with source to your table database and sink with a REST dataset. on the sink side you can set Write batch size parameter to 1 in order to send just 1 record per request, you can set your custom mapping to adapt it to your backend contract. Also you can set parallellism with "degree of copy parallellism" option.
Anonymous

2021-08-24T22:47:34.157+00:00

This is my current implementation. However, I am blocked because the only error response I receive back from the copy activity is a bad request. My plane was to try and move 1 record across using the batch size set to 1, and then scale the degree of parallelism parameter.
Anonymous

2021-08-24T22:49:22.243+00:00

I am currently only passing 1 record using the batch size of 1 parameter, but even altering the query as you mentioned resulted in the same error. In the lookup activity i am able to pass the body as { column1: value, column2 : value, column3: value}.

IIs there a way to see what the POST request looks like coming out of ADF?
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-08-26T18:42:35.49+00:00

@Anonymous Sorry, I was not clear, you can try to send the data to storage location as sink (as Json file) and check if json is correct.

Also, do you have any authentication setup at REST API endpoint ? What authentication type you have chosen in the linked service ?

Thanks
Saurabh
Anonymous

2021-08-26T18:51:06.973+00:00

I have sent the output of the copy to a blob storage and the JSON file looks right, it appears to be what I need passed.

I am using an Auth_Token so in the linked service I have set that to anonymous.
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-08-26T19:07:40.677+00:00

@Anonymous ok. Are you using any mappings ? Does your REST api require any headers to be passed ?
Anonymous

2021-08-26T19:33:57.477+00:00

I have tried that activity with and without mappings, and the only header I pass is my auth_token.
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-08-26T20:05:21.23+00:00

@Anonymous
Is it possible to provide runid so that I can try checking in backend.

Thanks
Saurabh
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-09-01T18:35:20.87+00:00

@Anonymous ,
If you are unable to share the runid and still having issues, then I suggest you to open a support ticket so that someone could look into your environment. In case you have a limitation, please let me know and I will help providing a free support ticket for you.

Thanks
Saurabh