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:
- Get an Authentication by logging into Google from the following URL
- Run the get_refresh_token.sh shell script and pass in the Authentication Token received in step 1
- 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
- Import BigQueryTemplate.xml from <CAMPAIGN_HOME>/conf using configtool.
- 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.
- 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.
- 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.
- 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)