Google BigQuery ODBC.ini configuration

Navigate to /opt/simba/googlebigqueryodbc/Setup and Configure odbc.ini

Make sure to add DefaultDataset as mentioned below:

[GBQ]

_ # Description: DSN Description._

# This key is not necessary and is only to give a description of the data source.

Description=Simba ODBC Driver for Google BigQuery (64-bit) DSN

__

# Driver: The location where the ODBC driver is installed to.

Driver=/opt/simba/googlebigqueryodbc/lib/64/libgooglebigqueryodbc_sb64.so

__

# These values can be set here, or on the connection string.

# Catalog: The catalog to connect to. This is a required setting.

Catalog=<Project_Name>

DefaultDataset=<Dataset_TOBE_Used>

__

# SQLDialect: The SQL Dialect to use. There are two SQL dialects:

# 0 = BigQuery Legacy SQL

# 1 = BigQuery Standard SQL (SQL 11)

SQLDialect=1

__

# OAuth Mechanism: The OAuth mechanism to use. There are two choices:

# 0 = Service Authentication

# 1 = User Authentication

# This is a required setting.

OAuthMechanism=0

__

# RefreshToken: The Refresh Token used. This can be generated from the Windows connection dialog.

# It can also be generated by executing the following steps:

  1. Get an Authentication by logging into Google from the following 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_

  2. Run the get_refresh_token.sh shell script and pass in the Authentication Token received in step 1
  3. Copy the Refresh Token (the text on the right-side of the colon, without the trailing or leading spaces) from the output of the script.

    This is a required setting.

    RefreshToken=<REFRESH_TOKEN_GENERATED>

    __

Email: For Service Authentication, this is a required setting. It is your GENERATED service account email (not a typical Gmail account).

It is unique and associated with at least one public/private key pair.

Email=<EMAIL_FROM_GBQ_Project>

__

KeyFile Path: For Service Authentication, this is a required setting. This is the path to the stored keyfile (.p12).

KeyFilePath=<.JSON or .P12 PATH Downloaded from your GBQ project>

__

Used to specify the full path of the PEM formatted file containing trusted SSL CA certificates.

If an empty string is passed in for the configuration, the driver expects the trusted SSL CA

Certificates can be found in the file named cacerts.pem located in the same directory as the

driver's shared library.

TrustedCerts=/opt/simba/googlebigqueryodbc/lib/64/cacerts.pem

__

AllowLargeResults: When set to 1, the driver allows for result sets in responses to be larger than 128 MB.

AllowLargeResults=0

__

LargeResultsDataSetId: DatasetId to store temporary tables created. This is a required setting if AllowLargeResults is set to 1.

LargeResultsDataSetId=_bqodbc_temp_tables

__

LargeResultsTempTableExpirationTime: Time in milliseconds before the temporary tables created expire. This is a required setting if AllowLargeResults is set to 1.

LargeResultsTempTableExpirationTime=3600000

Configure odbcinst.ini

[Simba ODBC Driver for Google BigQuery 64-bit]

Description=Simba ODBC Driver for Google BigQuery(64-bit)

Driver=/opt/simba/googlebigqueryodbc/lib/64/libgooglebigqueryodbc_sb64.so

  • Copy “SimbaODBCDriverforGoogleBigQuery.lic” from the mail you would have received while downloading simba driver and copy it to “/opt/simba/googlebigqueryodbc/lib/64”

Update Setenv.sh

LD_LIBRARY_PATH=<CAMPAIGN_HOME>/bin:<SYSTEM_DB_LIB_PATH>/:/opt/simba/googlebigqueryodbc/lib/64:/usr/lib64/:/lib64
export LD_LIBRARY_PATH
ODBCINI=/opt/simba/googlebigqueryodbc/Setup/odbc.ini
Export ODBCINI

Configure Google BigQuery datasource

Campaign has provided new template BigQueryTemplate.xml to use to connect Google Bigquery user datasource.
  1. Import BigQueryTemplate.xml from <CAMPAIGN_HOME>/conf using configtool.
  2. Create data source using BigQueryTemplate.xml and specify below properties:
    ASMUserForDBCredentials <Platform_User>
    DeleteAsRecreate TRUE
    DeleteAsTruncate FALSE
    BulkInsertRequiresColumnType TRUE

    Assign Data source created in step 2 to platform user specified in ASMuserDBCredentials and provide service account id as user and path of .json file as password.

Google BigQuery - Not supported features with Campaign
  1. Campaign will not be able to support any new functions (Like Struct, UNNEST, ARRAY Data type, ARRAY_Length(), etc) available in Big Query in any form be it SQL Advance query, Derived field , Pre- Post SQL , RAW SQL or custom macros.
  2. Campaign can support View and user can take input of view in Campaign and do segmentation, sampling, etc but please make a note - View created based on any new functions like Struct, UNNEST, ARRAY_LENGTH(), etc will not be supported and will not work as expected.
  3. Arithmetic functions such as – (minus) , + (Plus) ,etc are not supported for Date or date related functions if compared with Date data type column by GBQ itself , hence it will not be supported via Campaign as well , GBQ support various date functions like DATE_ADD , DATE_SUB , DATE_DIFF , etc all this will be supported in Campaign as RAW SQL query.

    For Example – In campaign below query will not work.

    Current_DATE() + 30 instead user can use below :

    DATE_ADD(CURRENT_DATE(), INTERVAL 30 day)

    OR

    As Point and Click method as well :

    DATE_STRING(CURRENT_JULIAN()+30 ,YYYYMMDD)