QlikReplicate to connect the Google Bigquery

[Aaron_Liu]

Hi Community,

I used the QlikReplicate to connect the Google Bigquery, I follow the user guide to install the 'SimbaODBCDriverforGoogleBigQuery32_2.3.3.1005.msi' and install the GoogleCloudSDK and downgrade it to 315.0.0
(attachment 'GoogleSDKVersion.jpg')

Then go to the ODBC setting(32-bit) , let the ODBC test sueecss(attachment 'ODBC_Test.jpg')

But at the QR web console endpoint , the test connection show error message 'SYS-E-HTTPFAIL, Cannot connect to ODBC provider.
SYS,GENERAL_EXCEPTION,Cannot connect to ODBC provider,RetCode: SQL_ERROR SqlState: IM002 NativeError: 0 Message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'

Please tell me what's wrong with me?
Thanks.
Aaron 2022-7-4

[john_wang · Accepted solution]

Hello @Aaron_Liu ,

The Simba ODBC Driver for Google BigQuery should be 64-bit.

you are using SimbaODBCDriverforGoogleBigQuery32_2.3.3.1005.msi.

the correct is: SimbaODBCDriverforGoogleBigQuery64_2.3.3.1005.msi

Let me know if you need any additional assistance,

John.

[john_wang]

Hello @Aaron_Liu ,

The download link in 2021.5 User Guide is inaccurate (it's correct in other versions of Replicate). You may download the ODBC Driver from:

https://storage.googleapis.com/simba-bq-release/odbc/SimbaODBCDriverforGoogleBigQuery64_2.3.3.1005.m...

Hope this helps.

Regards,

John.

[kishetty03]

Hi @john_wang ,

Just needed a confirmation on the GoogleCloudSDK part.

For latest Qlik Replicate Nov 2023 SR1 on Linux , in the guide compatible google sdk version is not mentioned, For May 2023 it is mention to use 402.00

Is there any suggested version or we should keep it 402.00 only for upgrade

[john_wang]

Hello @kishetty03 ,

Thanks for the feedback.

If my memory serves me well the GoogleCloudSDK is no need from Replicate 2023.11. Please allow me some time to confirm for you.

Regards,

John.

[john_wang]

Hello @kishetty03 ,

I've confirmed that Starting from Replicate 2023.11, Google Big Query will work with Qlik load utility for BQ (which is called arepbq), this utility will replace Google SDK and in particular bq command.
Therefore, for Google BigQuery endpoint the Google Cloud SDK is no need any more.

Hope this helps.

John.

[kishetty03]

Sure Thanks alot  !! @john_wang 

[Vaibhav_19]

Hi @john_wang ,

We are also facing the "Cannot connect to ODBC provider" error , While testing the Target Endpoint connection for Google BigQuery where Qlik Replicate installed on Linux.

we installed Simba driver from the link provided in user guide - https://storage.googleapis.com/simba-bq-release/odbc/SimbaODBCDriverforGoogleBigQuery_3.0.0.1001-Lin...

Getting the Error message --

Cannot connect to ODBC provider
RetCode: SQL_ERROR SqlState: IM005 NativeError: 0 Message: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed

Also can you please confirm, Qlik Replicate Installed on Linux server needs Internet connection for connecting to GoogleBigQuery and Salesforce endpoints.

please helps us with.

Regards,

Vaibhav

[john_wang]

Hello Vaibhav, @Vaibhav_19 

Thanks for reaching out to Qlik Community here!

This error indicates a connection configuration issue. The easiest way is creating a 64-bit ODBC DSN and use isql to access the DSN to troubleshoot the connectivity issue.

Here below is a sample of the file "/etc/odbc.ini":

[ODBC Data Sources]
GBQ_ODBC=Simba ODBC Driver for Google BigQuery 64-bit
[GBQ_ODBC]
Driver=/export/googlebigqueryodbc/lib/64/libgooglebigqueryodbc_sb64.so
Catalog=api-project-1243343
DefaultDataset=QATEST
SQLDialect=1
OAuthMechanism=0
Email=simbaaccount@api-project-1243343.iam.gserviceaccount.com
KeyFilePath=/export/Simba_GBQ_ODBC/API Project-c993e990af5.json

Hope it helps.

John.

[Vaibhav_19]

Hello @john_wang ,

Thank you for your quick response.

The solution you provided helped us resolve the error, and we successfully tested the endpoint connection.