topic Re: How to Setup Google Big Query via ODBC on Linux? in SAS Data Management

How to Setup Google Big Query via ODBC on Linux?

Selvaraj — Mon, 12 Feb 2018 13:44:02 GMT

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

Re: How to Setup Google Big Query via ODBC on Linux?

LinusH — Mon, 12 Feb 2018 13:52:05 GMT

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.

Re: How to Setup Google Big Query via ODBC on Linux?

Selvaraj — Mon, 12 Feb 2018 14:18:48 GMT

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

Regards,

Selva

Re: How to Setup Google Big Query via ODBC on Linux?

JBailey — Mon, 12 Feb 2018 19:09:33 GMT

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

Re: How to Setup Google Big Query via ODBC on Linux?

Selvaraj — Tue, 13 Feb 2018 15:16:53 GMT

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

Re: How to Setup Google Big Query via ODBC on Linux?

JBailey — Tue, 13 Feb 2018 18:02:55 GMT

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

Re: How to Setup Google Big Query via ODBC on Linux?

Selvaraj — Tue, 13 Feb 2018 18:13:51 GMT

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

Re: How to Setup Google Big Query via ODBC on Linux?

JBailey — Wed, 14 Feb 2018 15:41:43 GMT

Hi @Selvaraj

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

Best wishes,

Jeff

Re: How to Setup Google Big Query via ODBC on Linux?

Selvaraj — Tue, 20 Feb 2018 12:35:06 GMT

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

Re: How to Setup Google Big Query via ODBC on Linux?

Selvaraj — Tue, 13 Mar 2018 17:02:29 GMT

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.