Switch from Legacy BigQuery to new BigQuery Linked Service

ShilpaGopal 100

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://video2.skills-academy.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?

1 answer

Amira Bedhiafi 24,181 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 100 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 100 Reputation points

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

@Amira Bedhiafi Any pointers on the issue?

Smaran Thoomu 15,520 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 100 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,520 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,520 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.
Sign in to comment

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

Share via

Switch from Legacy BigQuery to new BigQuery Linked Service

1 answer

Your answer