IBM Cognos Analytics - How to setup Google BigQuery JDBC data server connection (with SSL)

Answer

1. Download the Google BigQuery JDBC 4.1 drivers:
   https://cloud.google.com/bigquery/providers/simba-drivers/
    
2. Place the JDBC driver files (all jar files within the downloaded version) in the following path: <cognos_install>/analytics/drivers
    
3. Export the Google BigQuery certificates to enable SSL connection:
   https://cloud.google.com/load-balancing/docs/ssl-certificates
    
4. Import the Google BigQuery certificates in Cognos

    ./keytool -import -noprompt -alias GoogleInternetAuthorityG3.crt -file /tmp/GoogleInternetAuthorityG3.crt -keystore <cognos_install_location>/jre/lib/security/cacerts

   ./keytool -import -noprompt -alias GlobalSignRootCA-R2.crt -file /tmp/GlobalSignRootCA-R2.crt -keystore <cognos_install_location>/jre/lib/security/cacerts
5. Setup the JSON authentication file (Cognos supports OAuthType = 0):
   https://www.simba.com/products/BigQuery/doc/JDBC_InstallGuide/content/jdbc/bq/authenticating/serviceaccount.htm
    
6. Place the JSON file on a convenient folder accessible by the browser. In this example we placed the .json file in D:\temp.
    
7. Create the data server connection. Go to Manage > Data server connections.
    
8. Select "Google BigQuery" as the data server type.
    
9. Use the following JDBC url format:
   jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=your-project-123456;OAuthType=0;OAuthPvtKeyPath=D:\\temp\\yourproject-0d6a8d80b220.json;OAuthServiceAcctEmail=yourproject-111@yourproject-project-123456.iam.gserviceaccount.com