Configure BigQuery ODBC Driver On Linux

BigQuery is a Serverless Data Warehouse service in GCP. You can run any kinds of massive workloads in BigQuery and get your analytics done within a few seconds. BigQuery provides the inbuilt interaction with other services like DataProc, DataPrep and etc. Also BigQuery provides the feasibility to integrate with any BI tools using JDBC and ODBC drivers. Here we are going to configure BigQuery ODBC driver on Linux.

Authentication:

BigQuery ODBC supports two types of authentications. You can use anyone of them to access BQ from ODBC driver.

  1. Service Authentication.
  2. User Authentication.

Download and Install the Driver:

Simba is the provider of the BigQuery ODBC driver. Click the below link to download the BQ ODBC driver for both 32bit and 64bit.

Im downloading the 64bit version and install it on /opt directory.

wget https://storage.googleapis.com/simba-bq-release/odbc/SimbaODBCDriverforGoogleBigQuery64_2.1.11.1011.tar.gz
tar --directory=/opt -zxvf SimbaODBCDriverforGoogleBigQuery64_2.1.11.1011.tar.gz

Configure ODBC with Service Authentication:

In this authentication, we need to create a service account(or choose an existing one) in GCP and assign BigQuery role. Then download the credentials in JSON format or P12 Key.

Create the Service Account:

  • Go to API → Credentials.
  • Click Service Account Key.
  • If you want to create a new one, then select New Service Account from the drop down list or you can choose existing which has BQ access.
  • Take a note of your Service Account ID, We will use this ID as Client Email in the ODBC.
  • Then create the Account.

Configuration:

  • Open the odbc.ini file.
vi /opt/simba/googlebigqueryodbc/Setup/odbc.ini
  • If you are configuring this on for 64bit then you have to make all the changes below the [Google BigQuery 64-bit]
  1. Description → Description for your driver (Optional)
  2. Driver → Location your ODBC installation.
  3. Catalog → Your BigQuery project name.
  4. SQLDialect → Legacy SQL or Standard SQL.
  5. OAuthMechanism → 0 (0 is service authentication).
  6. EMail → Service Account ID.
  7. KeyFilePath → Your JSON or P12. ( I have uploaded my p12 key to /opt/simba/googlebigqueryodbc/)

Configure ODBC with User Authentication:

This authentication method will use any existing GCP account user’s privilege. We need to generate an Authentication and then generate a refresh token.

  • Go to the below URL.
https://accounts.google.com/o/oauth2/auth?scope=https://www.googleapis.com/auth/bigquery&response_type=code&redirect_uri=urn:ietf:wg:oauth:2.0:oob&client_id=977385342095.apps.googleusercontent.com&hl=en&from_login=1&as=76356ac9e8ce640b&pli=1&authuser=0
  • Click allow then it’ll provide a code.
  • Generate the refresh token by executing the below command.
sh /opt/simba/googlebigqueryodbc/Tools/get_refresh_token.sh PASTE_YOUR_CODE
  • Now Edit the obdc.ini file.
vi /opt/simba/googlebigqueryodbc/Setup/odbc.ini
  • If you are configuring this on for 64bit then you have to make all the changes below the [Google BigQuery 64-bit]
  1. Description → Description for your driver (Optional)
  2. Driver → Location your ODBC installation.
  3. Catalog → Your BigQuery project name.
  4. SQLDialect → Legacy SQL or Standard SQL.
  5. OAuthMechanism → 1 (1 is for User authentication).

Test the ODBC connection settings:

  • To test the connectivity we are going to use unixodbc To install this just run this below command.
apt-get install unixodbc
  • Export the LIBRARY PATH to the Environment variables.
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/simba/googlebigqueryodbc/lib/
export ODBCINI=/opt/simba/googlebigqueryodbc/Setup/odbc.ini
export SIMBAGOOGLEBIGQUERYODBCINI=/opt/simba/googlebigqueryodbc/lib/64/simba.googlebigqueryodbc.ini
  • Your Driver DSN Name is Google BigQuery 64-bit (If you want to change the DSN name, just open the odbc.ini file and replace [Google BigQuery 64-bit] with the name you want.)
  • Run the below command with 64bit driver and it should return Connected!
isql "Google BigQuery 64-bit"

This will help you to integrate the BigQuery with your favorite BI tools.