How to Setup Google Big Query via ODBC on Linux?

[Selvaraj]

Hello All,

I am attempting to setup a connection to Google BigQuery via ODBC on a linux platform.

Steps completed:

1) Install ODBC driver provided by Simba

2) Configure LD_LIBRARY_PATH on sas env

3) DSN entry on the odbc.ini file

4) placed a .p12 file on the same directory.

when I isql - <DSN> I get a error that "unable to decode the .p12 file.

Please could you let me know the correct steps to setup Google BigQuery via odbc on linux platforms. Are there any pre-requisites on linux I need to check? 

Other points:

O/S - RHEL 6.9

SAS - 9.4M2

This is a Grid architecture and the linux servers do not have internet enabled. 

Advance thanks for your responses.

Regards,

Selva

[LinusH]

If I understand you correctly, you have not yet involved any SAS components?

For getting the ODBC driver to work from Linux, I would try to either go to Simba, or the one who delivers ODBC management service on your Linux box.

[Selvaraj]

True. I am trying to understand if anyone has done this already and am I missing anything basic. Thanks.

Regards,

Selva

[JBailey]

Hi @Selvaraj

If your Linux servers do not have Internet access then there is no way that you can query Google BigQuery.

The p12 message may be caused by the fact that the key can't be checked by Google.

Best wishes,
Jeff

[Selvaraj]

Thanks Jeff. I am trying to get a proxy server to be added to the odbc.ini. Are there steps I am missing or are there any other pre-requisites I am missing? Thanks.

Selva

[JBailey]

Hi @Selvaraj

Not that I know of. Are the Linux (SAS Grid nodes) going to be allowed to get to the internet at some point?

Best wishes,

Jeff

[Selvaraj]

Hi Jeff,

No, the linux servers may not get internet enabled. Alternatively, I am trying to use a proxy server by adding ProxyHost etc to the odbc.ini. Still getting the same error.

Regards,
Selva

[JBailey]

Hi @Selvaraj

I think your best bet at this point is to contact Simba tech support.

Best wishes,

Jeff

[Selvaraj]

Hi Jeff,

The install we have chosen is the free simba driver provided by Google. We may not be able to get their support unless we have a commercial support contract.

Regards,

Selva

[Selvaraj · Accepted solution]

All,

We have managed to resolve this. The issue was with the Proxy server authentication.

Out final odbc.ini file looks as below. Thanks.

Driver=</simba/googlebigqueryodbc/lib/64/libgooglebigqueryodbc_sb64.so>
Catalog=<Project ID"
OAuthMechanism=0
Email=<service account email id>
KeyFilePath=<full path to the /xxxxx.p12>
SQLDialect=1
ProxyHost=<proxy server url>

ProxyPort=8080
TrustedCerts=/simba/googlebigqueryodbc/lib/64/cacerts.pem
ProxyUid=<UID>
ProxyPwd=<pwd>

Note:

While setting up the library in SMC, ensure to provide the schema details to be able to register the tables.