Connecting to Google BigQuery
Google BigQuery is a web service for querying massive datasets that take advantage of Google's infrastructure. The idea behind BigQuery is that you store your data on Google's Cloud Platform and then access that data via the BigQuery API.
This article shows you how to create a data connector in Dundas BI to extract data from your Google Developer project via the BigQuery API.
2.1. Developers Console Project
You need to create or use an existing project in the Google Developers Console as a first step. A project acts as a container in the Google Cloud Platform which stores information about billing and authorized users and also contains BigQuery data.
Go to https://console.developers.google.com/project and sign in to your Google account (e.g., a Gmail account).
Once signed in, you'll see a list of existing projects (if there are any) or you can click Create project to create a new one.
In the New Project dialog, enter a name for your project and then click Create.
2.2. Enable the BigQuery API
By default, new projects have the BigQuery API (along with other necessary APIs) enabled.
To check this, make sure you are logged into the Developers Console and your project is selected as the current project.
Click the menu button in the top-left corner. In the left navigation, click API Manager. Then click the Enabled APIs tab to see the list of enabled APIs for the project.
If you are using an existing project and need to enable the BigQuery API for it, go back to the Google APIs tab in the API Manager. Click the BigQuery API link under Google Cloud APIs.
Click Enable to enable to BigQuery API.
2.3. Service Account Credentials
This section shows you how to generate Service account credentials which will allow Dundas BI to access your Google Analytics data.
In the API Manager, click Credentials in the left navigation. On the right, click Create credentials and select the Service account key option.
Set the Service account dropdown to New service account. Then enter the Name for your service account.
Click Create to generate a certificate file. This file will be needed later to set up a data connector in Dundas BI.
2.3.1. P12 file type
If you require the P12 file type instead of the default JSON, you will require some additional information.
The Service account created dialog is displayed and shows the password for the private key. Copy and paste this password into Notepad for later use.
Close the dialog to see the list of service account keys which includes the one you just created.
Click Manage service accounts in the top-right corner.
Copy and paste the Service account email address into Notepad for later use. You'll need to enter this email address when setting up the data connector in Dundas BI.
2.4. Enable Billing
Billing must be enabled for your Google project in order to load data into the project. If you don't have this set up yet, you can sign up for a Free Trial (which requires you to enter credit card information). Go to https://cloud.google.com/bigquery/ and click the Free Trial button in the top right corner to sign up.
Next, go back to the Google Developers Console for your project. Click the menu button in the top-left corner and then click Billing.
Enable billing and then log out and log back in for the changes to take effect.
2.5. Public Datasets
Google provides public datasets which you can copy and query using BigQuery. See https://cloud.google.com/bigquery/sample-tables for more details.
For example, you can create a new dataset in your Google project, and copy tables from a public dataset such as shakespeare (which is one of the smaller datasets).
3. Data Connector
Log on to Dundas BI and create a new data connector.
Set the Data Provider dropdown to Google BigQuery.
Clock Choose File and select the certificate file which you downloaded (most likely in your Downloads folder).
If the certificate file is of a P12 file type, you now have to provide the client credential information that you generated previously:
- Paste the Service Account E-Mail Address that you recorded previously.
- Enter the private key password for the certificate file.
Leave the Catalog Projects field blank and select a Billed Project from the dropdown.
Click Submit to create the data connector and begin discovery.
You can now visualize your BigQuery data.
Create a new dashboard using the Blank template. Go to Explore and locate your newly created data connector. Expand it and drag a table to the dashboard canvas.
- The Catalog Projects field can be used instead of the billed project to specify free catalogs such as publicdata,gdelt-bq,fh-bigquery (comma separated). If you specify catalog projects but you also want to include the billed project, you must add the billed project to the Catalog Projects field.
- Under the Advanced options, there is a checkbox to Allow Large Results. Use this option to avoid data connector errors such as Response too large to return. But you also need to create a dataset for storing large results on the Google Cloud Platform. It is recommended to check the option to Expire new tables in one day when creating a dataset in the Google Cloud Platform.