Copy Activity - Big Query to Azure SQL Database - GHighThroughputApiError

Question

Copy Activity - Big Query to Azure SQL Database - GHighThroughputApiError

Gabriel Levaillant 35

I am using a Copy Activity to copy data from Big Query to Azure SQL Database and I get this error :

Failure happened on 'Source' side. ErrorCode=User Error Odbc Operation Failed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][DSI] An error occurred while attempting to retrieve the error message for key 'GHighThroughputApiError' with message parameters ['Error: { code: UNKNOWN, message: Stream removed }'] and component ID 100: Message not found in file "D:\batch\tasks\shared\ODBC Drivers\Microsoft Google BigQuery ODBC Driver_2.4.5.1014\lib..\ErrorMessages\en-US\SimbaBigQueryODBCMessages.xml",Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][DSI] An error occurred while attempting to retrieve the error message for key 'GHighThroughputApiError' with message parameters ['Error: { code: UNKNOWN, message: Stream removed }'] and component ID 100: Message not found in file "D:\batch\tasks\shared\ODBC Drivers\Microsoft Google BigQuery ODBC Driver_2.4.5.1014\lib..\ErrorMessages\en-US\SimbaBigQueryODBCMessages.xml",Source=Microsoft ODBC Driver for Google BigQuery,'

Any idea where it could come from ?

PS : I can copy from BigQuery to Azure DL then Azure DL to SQL, it works 100% of the time this way.

Best,

Gabriel

Konstantinos Passadis 19,586 Reputation points MVP

2023-09-16T22:30:34.16+00:00

Hello @Gabriel Levaillant !

Are there any news regarding this issue ?

In case any answer helped kindly mark it as Accepted and upvote so others can benefit as well!

Regards !
Konstantinos Passadis 19,586 Reputation points MVP

2023-10-07T21:36:36.98+00:00

Hello @Gabriel Levaillant !

Do you have any feedback on your issue?

Kindly mark any answer that helped you as Accepted and Upvote or post your feedback to provide additional help!

Regards
Gabriel Levaillant 35 Reputation points

2023-10-09T12:03:03.5766667+00:00

Hey, no precise news from google no. I sticked to my BQ -> DL -> SQL order for my process,

Accepted answer

4 additional answers

Your answer

Konstantinos Passadis 19,586 Reputation points MVP

2023-09-16T22:30:34.16+00:00

Hello @Gabriel Levaillant !

Are there any news regarding this issue ?

In case any answer helped kindly mark it as Accepted and upvote so others can benefit as well!

Regards !
Konstantinos Passadis 19,586 Reputation points MVP

2023-10-07T21:36:36.98+00:00

Hello @Gabriel Levaillant !

Do you have any feedback on your issue?

Kindly mark any answer that helped you as Accepted and Upvote or post your feedback to provide additional help!

Regards
Gabriel Levaillant 35 Reputation points

2023-10-09T12:03:03.5766667+00:00

Hey, no precise news from google no. I sticked to my BQ -> DL -> SQL order for my process,

Answer 1

ShadowWalker 90

Hi
I have been facing same issue and it started to appear more often (almost daily on our BQ copy jobs)

GCP Support: After talking to GCP support, they pointed out that the ODBC drivers used by BigQuery connector in ADF are outdated. As per them, new updates were introduced in July-2023 (3.0.2.1005) whereas Microsoft is still using 2.4.5.1014. This maybe the cause. I don't know how often Microsoft updates the drivers for serverless-instances. On top of that, Microsoft doesn't let us create a support ticket from within the portal. Somehow the automatic troubleshooter finds no problem against pipeline RunID and terminates the request. This is absolutely bizarre.

For context: Copying data from BigQuery to Azure is done through the ODBC drivers, which is a piece of software that is written by Google as an efficient interface to BigQuery. However, this piece of software must be run on a server, which uses the ODBC driver to load data and then writes it into the destination. This is done by Microsoft Azure.

James McCann 5 Reputation points

2023-10-23T15:05:32.17+00:00

@ShadowWalker - Thanks for this feedback.

We have the same issue with our pipelines connected Big Query.

I have sent a support ticket with the error message attached and linked this conversation.

I'll update any useful information here.

Best
James McCann 5 Reputation points

2023-11-13T09:29:27.07+00:00

After a lot of back and forth with Microsoft support.

They insist that I create a self-hosted integration runtime with the correct drivers installed to connect to Big Query. The Product Team seem steadfast on not updating the drivers on their linked service to BQ.

This is ridiculous.

I have yet to action the self-hosted integration runtime for our pipelines, so I cannot confirm if this is a solution just yet

Answer 2

Konstantinos Passadis 19,586 MVP

Hello @Gabriel Levaillant !

Welcome to Microsoft QnA!

I understand that you are facing an issue to Copy Data from Big Query to Azure SQL Database

Please review this items :

Verify Azure SQL allows Big Query Connections , also verify Firewalls on Azure SQL

Once you are 100% sure for Authentication & Authorization try with smaller amounts of Data

Limitations on the API could probably lead to this message

Verify the Data itself is matching Source & Destnation configs , avoid using special characters

Lets start with these and send us your feedback

The fact that Datalake works , points to Firewall and/or Auth issues

Links :

https://learn.microsoft.com/en-us/azure/data-factory/connector-google-bigquery?tabs=data-factory

https://www.sqlshack.com/link-google-bigquery-resources-into-an-azure-sql-resource/

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Gabriel Levaillant 35 Reputation points

2023-07-09T13:50:36.0066667+00:00
Hi Konstantinos,

Thanks a lot for your quick answer.

For the next items :

Verify Azure SQL allows Big Query Connections , also verify Firewalls on Azure SQL -> It does. The same exact activity was also working everyday, twice a day until the 28/06. It is now working still, but most of the time it fails (about 90% of the time, including manual triggers).

Once you are 100% sure for Authentication & Authorization try with smaller amounts of Data -> It does not change, the status of the activity (failed or success) seems random. The amount of data/rows, the Azure SQL instance (dev or prod) do not seem to change the outcome.

Limitations on the API could probably lead to this message -> Could be, but it does work if I load it first into the Data Lake then to Azure SQL.

Verify the Data itself is matching Source & Destnation configs , avoid using special characters -> Same here, I checked the mapping from one source to another. It is coherent with what I need and want to do. Moreover, it was the exact same one when it was working about 2 weeks ago :(

I need to stress the fact, that it does work... Only sometimes

Thanks a lot for your help,

Gab
ShadowWalker 90 Reputation points

2023-10-09T10:32:18.4766667+00:00

Hello @Konstantinos Passadis !

I am unsure if Gabriel is active or not but the issue still persists on our side. As my question will be duplicate so Microsoft will remove my post if I post the same. Therefore if your could please follow up and check what version of ODBC drivers are being used by ADF ? Maybe its helpful for everyone if the drivers are up to date.

Your answer above is not applicable in this case and doesn't solve the problem.
Gabriel Levaillant 35 Reputation points

2023-10-09T12:05:26.3133333+00:00

I believe the issue comes from the Drivers used by ADF.

As I said in my post, not going through BQ to SQL directly but using DL as a middle layer solved my problem,
ShadowWalker 90 Reputation points

2023-10-09T12:21:56.2866667+00:00

@Gabriel Levaillant you are right, though this is a workaround to use a staging in between but its not feasible when we are talking about around 90 tables with millions of rows. If there is a connector to copy data directly from BQ to SQL with parametrization, it should work properly. I stress on the matter that Microsoft should investigate their driver versions and update them accordingly.
Gabriel Levaillant 35 Reputation points

2023-10-09T12:30:58.4333333+00:00

I completely agree, Microsoft should take the matter into their own hands.
ShadowWalker 90 Reputation points

2023-10-10T11:16:52.16+00:00

@Konstantinos Passadis @SSingh-MSFT any updates ? does MSFT has something to say ? apart from "In case any answer helped kindly mark it as Accepted and upvote so others can benefit as well" The accepted answer is probable cause of the problem and not the solution itself.

Answer 3

Hello @Gabriel Levaillant !

Thank you for updating

I have found the link below which refrences the Simba Drivers

D:\batch\tasks\shared\ODBC Drivers\Microsoft Google BigQuery ODBC Driver_2.4.5.1014\lib..\ErrorMessages\en-US*SimbaBigQueryODBCMessages.xml*

https://stackoverflow.com/questions/59368276/errors-when-using-bigquery-storage-api#:~:text=Do%20you%20set%20the%20values%20of%20Minimum%20Query,number%20of%20table%20operations%20per%20day%20is%201%2C000.

ALSO from the BigQuery Docs regarding ODBC

Known issues and FAQ

Can I use these drivers to ingest or export data between BigQuery and my existing environment?

These drivers leverage the query interface for BigQuery and don't provide functionality to leverage BigQuery's large scale ingestion mechanisms or export functionality.

While you can use DML to issue small volumes of INSERT requests, it is subject to the limits on DML.

ALSO :

https://community.qlik.com/t5/Official-Support-Articles/Random-GHighThroughputApiError-error-when-reloading-data-from/ta-p/1983458

I think we are looking into BigQuery problems with these Drivers for ODBC

In case you can export more logs please do ,otherwise open a case into GCP , my suggestion!

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Gabriel Levaillant 35 Reputation points

2023-07-10T16:58:45.5+00:00

Hi Konstantinos, I took your advice and opened an issue ticket with GCP here : https://issuetracker.google.com/issues/290502766

I will keep everyone updated on this question.
Konstantinos Passadis 19,586 Reputation points MVP

2023-07-11T10:56:08.0433333+00:00

Hello @Gabriel Levaillant !

Thanks for the update

Please do !

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 4

SSingh-MSFT 16,371 Moderator

Hi
Gabriel Levaillant •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

We are sorry about the inconvenience you are facing.

In addition to the suggestions by Konstantinos Passadis, I would recommend you to raise request with the GCP team as the error on the Source side that is GCP Big Query 'GHighThroughputApiError' seems to be a known intermittent issue as mentioned here:

https://issuetracker.google.com/issues/240305081?pli=1

https://community.qlik.com/t5/Official-Support-Articles/Random-GHighThroughputApiError-error-when-reloading-data-from/ta-p/1983458

You may try creating the issue in the GCP forum https://issuetracker.google.com/issues/new to get assistance.

Hope this helps. Do let us know if you have any further questions regarding the pipeline or Azure, we are happy to help.

Thank you.

Gabriel Levaillant 35 Reputation points

2023-07-10T16:59:08.2666667+00:00

Hi ShaktiSingh, I took your advice and opened an issue ticket with GCP here : https://issuetracker.google.com/issues/290502766

I will keep everyone updated on this question.
SSingh-MSFT 16,371 Reputation points Moderator

2023-07-13T04:03:42.68+00:00

Hi
Gabriel Levaillant •,

Appreciate your effort.

Sharing the solution here on Microsoft Q&A forum helps entire community.

Hope you get the solution soon. Thank you.

Answer 5

Hi,

I have the same problem

I am using a Copy Activity to copy data from Big Query to Azure SQL Database

I am using AutoResolveIntegrationRuntime (Azure Public Type)

I get this error:

Operation on target ... failed: Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][DSI] An error occurred while attempting to retrieve the error message for key 'GHighThroughputApiError' with message parameters ['Error: { code: UNKNOWN, message: Stream removed }'] and component ID 100: Message not found in file "D:\batch\tasks\shared\ODBC Drivers\Microsoft Google BigQuery ODBC Driver_2.4.5.1014\lib..\ErrorMessages\en-US\SimbaBigQueryODBCMessages.xml",Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][DSI] An error occurred while attempting to retrieve the error message for key 'GHighThroughputApiError' with message parameters ['Error: { code: UNKNOWN, message: Stream removed }'] and component ID 100: Message not found in file "D:\batch\tasks\shared\ODBC Drivers\Microsoft Google BigQuery ODBC Driver_2.4.5.1014\lib..\ErrorMessages\en-US\SimbaBigQueryODBCMessages.xml",Source=Microsoft ODBC Driver for Google BigQuery,'

I will add that a task within the pipeline is usually not executed, but when started manually it is usually executed

What could be the problem?

Mirek Chorąży 0 Reputation points

2023-09-08T12:27:00.62+00:00
SSingh-MSFT 16,371 Reputation points Moderator

2023-09-12T03:53:25.0633333+00:00

Hi Mirek Chorąży •,

You may try creating the issue in the GCP(Google Cloud Platform) forum https://issuetracker.google.com/issues/new to get assistance.

Thanks

Share via

Copy Activity - Big Query to Azure SQL Database - GHighThroughputApiError

4 additional answers

Your answer