What Does This Article Cover?
Intelligence Hub includes a configurable Connection that allows Intelligence Hub solutions to write data to Google BigQuery tables. The Google BigQuery Connection supports writing payloads of data from Intelligence Hub creating new tables and new rows of data in Google BigQuery tables. The Connection also supports querying Google BigQuery tables for data. This article covers the following topics.
- What is Google BigQuery?
- Simba BigQuery JDBC Driver
- Google Configuration
- Intelligence Hub Connection Overview Configuration
- Intelligence Hub Connection Output Configuration
- Intelligence Hub Connection Input Configuration
What is Google BigQuery?
Google BigQuery is a managed, serverless data warehouse. It is a Platform as a Service (PaaS). BigQuery provides scalable analysis over large quantities of data and it supports querying using a dialect of SQL. It also has built-in machine learning capabilities.
Simba BigQuery JDBC Driver
The Google BigQuery connection uses the Simba BigQuery JDBC driver to enable reading, writing, and browsing BigQuery datasets. The driver must be installed to use the Intelligence Hub Google BigQuery Connection. Intelligence Hub 3.4 has been tested with version 1.5.2.1005 of the Simba driver. The following are the steps to install the Simba driver.
- Download the JDBC Driver linked to on this page: https://cloud.google.com/bigquery/docs/reference/odbc-jdbc-drivers
- Unzip the file and then copy the GoogleBigQueryJDBC42.jar into the Intelligence Hub runtime/lib directory. Only the GoogleBigQueryJDBC42.jar file needs to be installed. The Intelligence Hub installer contains all the required dependencies with the exception of the Simba driver. The Simba driver may not redistributed with applications.
- Restart the Intelligence Hub runtime.
Google Configuration
A project is required to use BigQuery. A project can be created in the Google Cloud Console.
A dataset must be created for the project in BigQuery.
Typically service account is created for use with BigQuery. The service account must be granted permission to the project.
To create a Service Account go to the Google Cloud Console and in the side menu click on 'IAM & Admin' -> 'Service Accounts'
Click on 'Create Service Account' and fill in the nescessary details and click 'Create'
Assign the Service account appropriate roles for BigQuery (e.g. BigQuery Admin or specific roles like BigQuery Data Viewer and BigQuery Job User), then click 'Continue'
Click on 'Create Key', choose JSON as the key type, and click create
The key must be generated for the Service Account in the form of a JSON file. The JSON file must be stored on disk, making it available to the runtime. The JSON file will be referenced in the JDBC connection string.
Intelligence Hub Connection Overview Configuration
In order to establish a connection between Google BigQuery and Intelligence Hub a JDBC Connection string must be configured.
The JDBC Connection String has the following format:
OAuthType=0;OAuthServiceAcctEmail=your-service-account-email;OAuthPvtKeyPath=path/to/your/service-account-key.json;
- Replace 'your-service-account-email' with the email address of the service account
- Replace 'path/to.your/service-account-key.json' with the full path to your service account JSON key file
The following is an example of a connection string.
Intelligence Hub Connection Output Configuration
Intelligence Hub Connection Outputs are configured to create and send data to tables in Google BigQuery. The configuration consists of indicating a table name and whether or not a new table will be created. Inserts, Updates, and Upserts are supported.
Intelligence Hub Connection Input Configuration
Intelligence Hub Connection Inputs are configured to obtain data from tables in Google BigQuery. A query can be written the the Query text box. The Object Explorer provides the ability to browse the tables in the dataset.