KB316480: How to upgrade the Google Big Query Driver in MicroStrategy Secure Enterprise 10.4.x running on Linux Operating Systems

Overview

This document outlines the procedure to upgrade the Google Big Query Driver in  Strategy Secure Enterprise 10.4.x on Linux to the version of the driver shipped in Strategy Secure Enterprise 10.5.0. The updated driver libraries can be found here. This documentation does not apply to Strategy Secure Enterprise 10.4.x running on Window, AIX, or Solaris environments. Instructions for updating the driver on Windows are included in the link provided below: 
    KB316209: How to upgrade the Google Big Query Driver on Strategy Secure Enterprise 10.4.x
 
The upgrades driver helps overcome some limitations of the out-of-the-box driver provided in Strategy Secure Enterprise 10.4.x, including the ability to use BigQuery Standard SQL.
 

Installation

Follow the steps below to install the new Google BigQuery Driver on an existing Strategy Secure Enterprise 10.4.x environment running on Linux. This guide is written assuming that Strategy Intelligence Server and Strategy Web are on the same machine.
 
Initial Preparation

1. Download and copy the driver patch zip file (GoogleBigQuery_Linux_v2.0.8.1015.zip) onto the Linux machine hosting the Strategy Secure Enterprise 10.4.x installation
2. Extract the zip file to its own directory, separate from the existing Strategy installation files (example: /home/test/GoogleBigQueryDriverPatchLinux). This directory will be referred to as <MSTR_PATCH_PATH> for the remainder of this Knowledge Base article.
3. Close all current ODBC connections that are using the existing 10.4 GA Google BigQuery ODBC Driver. 

 
Backup

1. Navigate to the Strategy install directory (e.g. /opt/Strategy). This will be referred to as the <MSTR_INSTALL_PATH> for the remainder of this Knowledge Base article.
    
2. Navigate to <MSTR_INSTALL_PATH>/lib and create a directory such as GBQ_BACKUP. This will hold the original Google BigQuery files.
    
3. Copy the following files from the <MSTR_INSTALL_PATH>/lib folder to the GBQ_BACKUP location:
   
• libbigqueryodbc_sb64.so
• SimbaBigQueryODBC.did
   
4. Navigate to <MSTR_INSTALL_PATH>/locale/en_US/LC_MESSAGES/BigQuery/en-US and again create a backup directory (e.g. GBQ_BACKUP_LOCALE). 
    
5. Copy the following driver files from <MSTR_INSTALL_PATH>/locale/en_US/LC_MESSAGES/BigQuery/en-US to the backup directory created above.
   
• DSMessages.xml
• ODBCMessages.xml
• SimbaBigQueryODBCMessages.xml
• SQLEngineMessages.xml
6. Navigate to the Strategy Web Directory (e.g. <MSTR_INSTALL_PATH>/WebUniversal). This will be referred to as the <MSTR_WEB_PATH> for the remainder of this Knowledge Base article.
    
7. Navigate to <MSTR_WEB_PATH>/WEB-INF/xml and create a backup directory (e.g. GBQ_BACKUP_WEB).
    
8. Copy the file <MSTR_WEB_PATH>/WEB-INF/xml/DBProperties.xml to the backup directory created above.

Install

1. Copy the following files from <MSTR_PATCH_PATH>/lib to <MSTR_INSTALL_PATH/lib:
   
• GoogleBigQueryODBC.did
• libgooglebigqueryodbc_sb64.so
• third-party-licenses.txt
2. Copy the following files from <MSTR_PATCH_PATH>/locale/en_US/LC_MESSAGES/BigQuery/en-US to <MSTR_INSTALL_PATH>/locale/en_US/LC_MESSAGES/BigQuery/en-US:
   
• DSMessages.xml
• ODBCMessages.xml
• SimbaBigQueryODBCMessages.xml
• SQLEngineMessages.xml
3. Copy the DBProperties.xml file from <MSTR_PATCH_PATH>/WEB-INF/xml to <MSTR_WEB_PATH>/WEB-INF/xml.
    
4. Update the odbcinst.ini in the <MSTR_HOME_PATH>(eg; /var/opt/Strategy) by following the steps below.
    
   
• Backup the existing odbcinst.ini.
• Modify the entry for [Strategy Google BigQuery ODBC Driver] to update the driver file name as shown below:
  

  
  [Strategy Google BigQuery ODBC Driver]
  Driver=<MSTR_INSTALL_PATH>/lib/libgooglebigqueryodbc_sb64.so
  Description=Strategy BigQuery ODBC Driver

  
  
5. Update the odbc.ini.example file in the <MSTR_HOME_PATH>.
    
   
• Backup the existing odbc.ini.example file 
• Create a new entry [Google BigQuery] in the [ODBC Data Sources] section as shown below:
  

  
  [ODBC Data Sources]
  Google BigQuery=Strategy Google BigQuery ODBC Driver

  
  
• Add the following example entry to the end of the file:
  

  
  [Google BigQuery]
  Driver=<MSTR_INSTALL_PATH>/lib/libgooglebigqueryodbc_sb64.so
  Description=Strategy Google BigQuery ODBC Driver
  AllowLargeResults=1
  Catalog=
  DefaultStringColumnLength=65535
  Email=
  KeyFilePath=
  LargeResultsDataSetId=_bqodbc_temp_tables
  LargeResultsTempTableExpirationTime=3600000
  MaxRequestsPerSecond=0
  OAuthMechanism=1
  ProxyHost=
  ProxyPort=0
  ProxyPwd=
  ProxyUid=
  RefreshToken=
  RowsFetchedPerBlock=16384
  SQLDialect=1

  
  

 
Creating a DSN
Once installed, the following steps can be followed to create a new odbc entry for Google BigQuery using the updated driver.
 

1. Copy the Google Big Query DSN template from the <MSTR_HOME_PATH>/odbc.ini.example to the <MSTR_HOME_PATH>/odbc.ini.
    
2. Update the DSN name (e.g. [Google BigQuery] to reflect the correct name) and specify the Catalog to reflect the name of the Google BigQuery project being used.
    
3. Refresh the token
   
• Connect to the following site: 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
• Type in the credentials for accessing BigQuery data and select Sign In.
• Click Accept when prompted to allow BigQuery Client Tools to access data in Google BigQuery. Copy the provided code.
• To generate the refresh token, run the get_refresh_toke.sh script from <MSTR_INSTALL_HOME>/lib
  

  
  /get_refresh_token.sh <Refresh Token>

  
  
• Copy the refreshed token from the output of running the above script and set it as the RefreshToken parameter in the odbc.ini file for the Google BigQuery DSN.

Test connectivity

To confirm the ODBC DSN is working, users should first verify connectivity via DB Query Tool or ODBC Test Tool. These are both located within <MSTR_HOME_PATH>/bin. Users without a GUI will need to use ODBC Test Tool (./mstrtodbcx). 
 

Upgrading the Google BigQuery Database Object

Lastly, before using the upgraded driver within Strategy, the existing Database object needs to be upgraded. The following Knowledge Base article can be referenced using the GoogleBigQuery.PDS included in the <MSTR_PATCH_PATH>
 
    KB43537 : How to install DBMS objects provided by Strategy Technical Support.
             
When prompted with information about 'Google BigQuery' already existing, click yes to replace it with the new one as shown below:

KB316480