Switch from Legacy BigQuery to new BigQuery Linked Service

ShilpaGopal 105

I have a BigQuery routine that takes start date and end date as input parameter and both input parameter is declared of type String in routine. This routine is working fine without errors.

I want to invoke the BigQuery routine from Azure Data Factory. I use “Copy data” to invoke BQ Routine from Data Factory. Using the old Big Query Linked Service (Legacy) the below invocation of BQ Routine from ADF works without errors.

@concat('

CALL mycompany-gcp.analytics_266606666.get_appointment_report(

''',

variables('BQStartDate'),

''', ''',

variables('BQEndDate'),

'''

)

But, when I use the new Big Query Linked Service, the routine fails with below error code. The old and the new Big Query Linked service configuration are the same.

User's image

I have gone through the ADF documentation where there is a comparison between legacy and new connector.

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

Is there any change in the format of input parameters passed to the BigQuery routine in hte new BigQuery LinkedService?

User's image

What change must be made when we switch from old legacy BiqQuery to new BigQuery linked service?

2 answers

Amira Bedhiafi 24,556 Reputation points

2024-09-24T11:04:25.4033333+00:00
Ensure that the parameters passed from Azure Data Factory (ADF) to BigQuery are correctly formatted for the new service. The new connector might require a different way of escaping or quoting parameters. For example, ensure that the single quotes are correctly formatted and variables are passed in the expected way.

Temporarily replace variables('BQStartDate') and variables('BQEndDate') with hardcoded values to check if the issue persists. This can help determine if the issue lies in how ADF is resolving the variables or how they are passed to BigQuery.

CALL mycompany-gcp.analytics_266606666.get_appointment_report( '2023-09-01', '2023-09-30' );

The new connector might have updated rules for handling stored procedures and routines. Double-check the Microsoft Documentation on the new BigQuery connector to see if there are changes in how routines and input parameters are managed.

Ensure that the data types for the input parameters are correctly declared and match what the new Linked Service expects. The routine takes String as input, but sometimes the connector may implicitly handle types differently, especially with dates.
Please sign in to rate this answer.
ShilpaGopal 105 Reputation points

2024-09-24T15:54:14.5633333+00:00

Thanks for the update @Amira Bedhiafi

I tried with a simple Big Query Routine invocation with no input parameters.

@concat('CALL `mycompany.analytics_222200222`.get_bq_test_data1()')

Still i get the same error.

Failure happened on 'Source' side. 'Type=Microsoft.DI.Connector.GoogleBigQuery.ExceptionUtils.GoogleBigQueryConnectorException,Message=Object reference not set to an instance of an object., ErrorCode: InternalError,Source=Microsoft.DI.Connector.GoogleBigQuery,''Type=System.NullReferenceException,Message=Object reference not set to an instance of an object.,Source=Microsoft.DI.Connector.GoogleBigQuery,'

Testing the BigQuery LinkedService "Test Connection" is successful. But the invocation of Routine results in error.

Any pointers on the error?

ShilpaGopal 105 Reputation points

2024-09-25T13:32:41.33+00:00

@Amira Bedhiafi Any pointers on the issue?

Smaran Thoomu 15,765 Reputation points Microsoft Vendor

2024-09-25T18:54:28.52+00:00

ShilpaGopal The error message "Object reference not set to an instance of an object" usually occurs when a null object reference is used in the code. It is difficult to determine the exact cause of the issue without more information. However, you can try the following steps to resolve the issue:

Check if the BigQuery Linked Service is configured correctly with the required credentials.

Check if the input parameters are of the correct data type.

Check if all the required parameters are passed to the BigQuery routine.

Check if the BigQuery routine is working fine without errors when executed directly in the BigQuery console.

If the issue persists, you can try the following steps:

Check if the BigQuery API is enabled in your Google Cloud Platform (GCP) project.

Check if the service account used to create the BigQuery Linked Service has the required permissions to access the BigQuery API.

Check if the BigQuery Linked Service is using the correct GCP project ID and dataset ID.

Check if the BigQuery Linked Service is using the correct authentication method (OAuth or service account key).

I hope this helps! Let me know if you have any further questions.

ShilpaGopal 105 Reputation points

2024-09-26T09:02:54.0033333+00:00

@Smaran Thoomu

Thanks for the update.

Big query routine is working fine because I can execute the routine from the old Big Query Legacy Linked Service. The routine has no input parameters. The moment i switch to new BQ Linked Service, the pipeline fails. So, i guess there is something wrong with the newly created BQ linked service only.

The newly created BQ linked service passes the "Test Connection".

I have copied the same configuration details when creating new Big Query linked service as that of legacy Linked service(Integration runtime, project id, same client id & secret, refresh token)

When we switch from old to new BQ Linked Service, what exact changes are required ? If it the issue with BQ routine, then how can the routine return successful results when using BQ Legacy linked service?

Smaran Thoomu 15,765 Reputation points Microsoft Vendor

2024-09-26T20:39:58.84+00:00

ShilpaGopal When switching from the old BigQuery Legacy Linked Service to the new BigQuery Linked Service, there might be some changes required in the way input parameters are passed to the BigQuery routine. The new connector might require a different way of escaping or quoting parameters. For example, ensure that the single quotes are correctly formatted and variables are passed in the expected way.

You can try temporarily replacing the input parameters with hardcoded values to check if the issue persists. This can help determine if the issue lies in how ADF is resolving the variables or how they are passed to BigQuery.

Additionally, the new connector might have updated rules for handling stored procedures and routines.

If the BigQuery routine is working fine without errors when executed directly in the BigQuery console, then the issue might be with the newly created BigQuery Linked Service. You can try checking if the newly created BigQuery Linked Service is using the correct GCP project ID and dataset ID, and if it is using the correct authentication method (OAuth or service account key).

I hope this helps! Let me know if you have any further questions.

Smaran Thoomu 15,765 Reputation points Microsoft Vendor

2024-09-27T21:23:05.3033333+00:00

ShilpaGopal We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

ShilpaGopal 105 Reputation points

2024-10-01T15:42:45.32+00:00

Hi Smaran @Smaran Thoomu

Thanks for the update. The issue is not resolved yet.

I am invoking a simple BQ routine with no input parameters. The Routine itself is working fine on BQ side. When i "Debug" or "Preview data" from ADF, i can see that the call is going to BQ and BQ is executing the data (I could validate at BQ side that the routine call from ADF is logged and Routine is executed successfully). But I get error on ADF side.

I also tried with a query in GoogleSQL instead of routine like "select * from BQ_table" This works fine as well as i get a table with BQ results as preview table.

This indicates that BQ linked service itself is working fine.

The Sink is configured with Azure Blob storage and this works fine as well with old BQ Linked Service.

Could there be an issue with the syntax (GoogleSQL syntax to call routine) when invoking the BQ routine in ADF using new BQ Linked Service? (the same syntax works fine with the old legacy BQ Linked Service.) Can you suggest what could be the possible BQ syntax changes to the parameter less Routine?

As the call is successfully logged in BQ but the ADF is not able to process the response, I doubt if the GoogleSQL syntax is different to normal SQL syntax?

CALL `mycompany.222200222.get_bq_data1`();

Omar Rose 0 Reputation points

2024-10-02T17:15:07.2733333+00:00

I am having the same issue. I think its because of how the result set is presented in BQ. I.e. if you call the stored proc in the UI on BQ, you need to click into see the result set. I think that the Synapse/ADF connector can't handle that.

The least worst option for me was converting my stored proc to a view in BQ, and then selecting from that instead. I just can't use any parameters. Pretty frustrating when the old connector worked just fine.

Microsoft, please can you fix this?!

Smaran Thoomu 15,765 Reputation points Microsoft Vendor

2024-10-02T17:43:50.45+00:00

ShilpaGopal I agree that this issue looks strange and I wasn't able to reproduce this issue. If you have a support plan, could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan, please let us know here.

Omar Rose 0 Reputation points

2024-10-02T18:43:10.6366667+00:00

@Smaran Thoomu You should be able to reproduce the issue if you just try and call a stored procedure that exists BigQuery in the "GoogleSQL" box in Synapse/ADF - it doesn't even need to use parameters to throw the error.

E.g:

CALL project.dataset.mystoredproc
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

Switch from Legacy BigQuery to new BigQuery Linked Service

2 answers

Your answer