Error while copying Big Query to Data Lake in Azure Data Factory

Bharath R S 21

Hi All,

We are facing an issue while copying data from Google Analytics to Data Lake using Big Query connector in ADF. The linked services works fine, the preview of data also works fine. When we run the pipeline we get an error. Also I am running just one query so the problem of

{ "errorCode": "2200", "message": "ErrorCode=UserErrorFailedToConnectOdbcSource,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][DriverOAuthSupport] (8701) Error interacting with REST API: Couldn't connect to server\r\nERROR [HY000] [Microsoft][DriverOAuthSupport] (8701) Error interacting with REST API: Couldn't connect to server,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][DriverOAuthSupport] (8701) Error interacting with REST API: Couldn't connect to server\r\nERROR [HY000] [Microsoft][DriverOAuthSupport] (8701) Error interacting with REST API: Couldn't connect to server,Source=,'", "failureType": "UserError", "target": "Copy data1", "details": []

}

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-09-01T01:25:35.44+00:00

Hi @Anonymous ,

Thanks for your query and using this forum. As per my understanding your source is Google Analytics, Sink is ADLS and you are trying to copy data using ADF Google BigQuery connector. Please correct me if I misunderstood your ask.

To better assist on this query, please confirm if your source is Google BigQuery or Google Analytics?

Looking forward to your response.

Thank you
Bharath R S 21 Reputation points

2020-09-01T04:37:07.34+00:00

Hi,

The source is google BigQuery and the sink is ADLS Gen 2. I am getting the error while running the copy activity. The preview of data works fine in the Source setting of the copy activity.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-09-02T01:04:58.473+00:00

Hi @Anonymous ,

Thanks for your response and confirmation. Could you please confirm if it a trigger or debug run? - If it is a trigger run, please share failed pipeline run ID for further analysis? - Also could you please tell us what authentication type is used to establish the connection - UserAuthentication or ServiceAuthentication. If it is ServiceAuthentication what is the SHIR version used?

Additional info: Note that BigQuery limits the maximum rate of incoming requests and enforces appropriate quotas on a per-project basis, refer to Quotas & Limits - API requests. Make sure you do not trigger too many concurrent requests to the account. Analytics?

Looking forward to your response.

Thank you
Bharath R S 21 Reputation points

2020-09-02T04:45:07.04+00:00

Hi,

I am using User Authentication and using Azure runtime since the self hosted IR cannot connect to Big Query. The Linked service connection is successful and the preview of data is able to get the data. It is only when we run the pipeline it gives us this error. The query hardly returns 128 rows. I have tested this in Excel using the Simba 64 bit connector in DSN and it works absolutely fine.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-09-02T19:50:57.867+00:00

Hi @Anonymous ,

Thanks for your prompt response and sincere apologizes for your experience with this issue. I have escalated this issue to internal team and confirmed that this needs a deeper investigation to identify the root cause. As you confirmed that you were able to test this in Excel using Simba connector, this may also require the driver logs as well as driver support team involvement to troubleshoot further .

For deeper investigation and immediate assistance, If you have a support plan we request you to please file a support ticket, else could you please send an email to AzCommunity[at]Microsoft[dot]com with the below details, so that we can create a one-time-free support ticket for you to work closely on this matter.

Email subject : <Attn - Kranthi : Microsoft Q&A Thread title>
Thread URL : <Microsoft Q&A Thread link>
Subscription ID: <your subscription id>

Please see continuation comment below

Thank you.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-09-02T20:05:31.757+00:00

Continuation to above comment:

For ADF Google BigQuery Connector, ServiceAuthentication can be used only on Self-hosted Integration Runtime. This was the reason, I thought of checking if you were using UserAuthentication or ServiceAuthentication. Ref: ADF Google BigQuery Connector Linked service properties

Thank you for clarifying on this ask which confirms that issue is irrelevant to SHIR latest release.

Please do let me know here once the email is sent with the details requested in my previous comment. In case if you have support plan, please open a support request and do share the SR# with for so that I can work with support engineer internally.

Thank you
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-09-04T00:31:09.267+00:00

Hi @Anonymous ,

Following up to see if you have got a chance to file a support request or send an email as described in my previous comment? Please let us know, so that we can take a deeper look into the issue.

Thank you

1 answer

R S,Bharath,KOLKATA,GLOBE-BS-BA-Business Analytics 6 Reputation points

2020-09-08T06:28:57.053+00:00

Hi This is resolved now, it was quiet silly actually. The data sink was in SH IR mode and was not able to reach the google query. It is resolved now.
Please sign in to rate this answer.

1 person found this answer helpful.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-09-09T05:46:06.4+00:00

Hi @R S,Bharath,KOLKATA,GLOBE-BS-BA-Business Analytics ,

Glad to know that your issue was resolved and thanks much for sharing your findings as it would be helpful for others reading this post.

Ah, since your sink is using SHIR and Source is using Azure IR (UserAuthentication), as per the below statement, if either source or sink linked service points to a self-hosted IR, the copy activity is executed on that self-hosted Integration Runtime because of which this issue has happened.

Please feel free to Accept your answer as it would help others to find solution quickly.

Thank you and stay safe.
Sign in to comment