CODEWITHSUNDEEP
azureazure-cosmosdbazure-data-factoryazure-synapseazure-data-lake-gen2
tikiabbas
tikiabbas

Reputation: 307

Copy activitiy (from Cosmos SQL api to ADLS gen2) getting failed in Synapse

I am trying to run a pipeline which copies data from Cosmos (SQL API) to ADLS gen2 for multiple tables. Lookup Activity is passing list of queries and Copy Activity runs within ForEach, using self-hosted IR. However it keeps failing after 1st iteration with below error:

Operation on target Copy data1_copy1 failed: Failure happened on 'Sink' side. ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Upload file failed at path tfs/OU Cosmos Data/LATAM/fact\dl-br-prod.,Source=Microsoft.DataTransfer.Common,''Type=Microsoft.Azure.Documents.RequestTimeoutException,Message=Request timed out.

Also I'm sure it is not the issue with any one specific table since I have tried passing queries in different order, in each attempt first query passed completes successfully and for rest of iteration Copy Activity runs for sometime and eventually fails.

I have tried following so far:

  1. Running ForEach in sequential mode
  2. Changing Block Size (in MB) on Sink side to 20MB. By default it is 100MB

Upvotes: 0

Views: 540

Answers (2)

tikiabbas
tikiabbas

Reputation: 307

I was able to get response from Microsoft Cosmos product team:

Root cause:

The SDK client is configured with some Timeout value and the request is taking longer time.

Reason for the timeouts is an increase in Gateway latency (Gateway has no latency SLA) due to large result size. This is probably expected (more data tends longer to be read, sent, and received).

Resolution:

Increase the RequestTimeout used in the client.

The team owning the Synapse Data Transfer (which uses the .NET 2.5.1 SDK and owns the Microsoft.DataTransfer aplication) can increase the RequestTimeout used on the .NET SDK to a higher value. In newer SDK versions, this value is 65 seconds by default.

Though we have opted to bypass this route altogether and include either SynapseLink or Private Endpoint.

Upvotes: 0

KarthikBhyresh-MT
KarthikBhyresh-MT

Reputation: 5044

Can you check the workaround suggested in the official MS docs as this involves Self-Hosted IR.

Request to Azure Data Lake Storage Gen2 account caused a timeout error

Cause: The issue is caused by the Azure Data Lake Storage Gen2 sink timeout error, which usually occurs on the Self-hosted Integration Runtime (IR) machine.

Recommendation:

  1. Place your Self-hosted IR machine and target Azure Data Lake Storage Gen2 account in the same region, if possible. This can help avoid a random timeout error and produce better performance.

  2. Check whether there's a special network setting, such as ExpressRoute, and ensure that the network has enough bandwidth. We suggest that you lower the Self-hosted IR concurrent jobs setting when the overall bandwidth is low. Doing so can help avoid network resource competition across multiple concurrent jobs.

  3. If the file size is moderate or small, use a smaller block size for nonbinary copy to mitigate such a timeout error. For more information, see Blob Storage Put Block

Upvotes: 0

Related Questions