Connecting to Google BigQuery

Contents[Hide]

1. Overview

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.

Using Google BigQuery
Using Google BigQuery

2. Setup

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.

Create a new project
Create a new project

In the New Project dialog, enter a name for your project and then click Create.

Enter the project name
Enter the project name

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.

List of enabled APIs for the project
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.

API Library
API Library

Click Enable to enable to BigQuery API.

Click Enable
Click Enable

2.3. Service Account Credentials

This section shows you how to generate Service account credentials which will allow Dundas BI to access your BigQuery data.

In the API Manager, click Credentials in the left navigation. On the right, click Create credentials and select the Service account key option which applies to Google Cloud APIs such as BigQuery.

Add Service account credentials
Add Service account credentials

Set the Service account dropdown to New service account and enter a Name for your service account.

Under Key type, select the P12 option and click Create to generate a certificate file. This file will be needed later to set up a data connector in Dundas BI.

Create a P12 key
Create a P12 key

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.

If you're using Chrome browser, the generated certificate file which has the extension p12 should have been automatically downloaded to your computer.

P12 key file and password is generated
P12 key file and password is generated

Close the dialog and you should see the list of service account keys including the one you just created. Click the Manage service accounts link.

Click Manage service accounts
Click Manage service accounts

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.

Service account email address
Service account email address

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.

Project billing settings
Project billing settings

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).

Project has a dataset with a copy of the shakespeare dataset
Project has a dataset with a copy of the shakespeare dataset

3. Data Connector

Log on to Dundas BI and create a new data connector.

Set the Data Provider dropdown to Google BigQuery.

Enter the Service Account E-Mail Address which you recorded into Notepad previously.

Clock Choose File and select the P12 certificate file which you downloaded (most likely in your Downloads folder).

Enter the service account email address and choose the certificate file
Enter the service account email address and choose the certificate file

Enter the password for the certificate file which you also recorded into Notepad previously.

Leave the Catalog Projects field blank and select a Billed Project from the dropdown.

Click Submit to create the data connector and begin discovery.

Enter the password and select the billed project
Enter the password and select the billed project

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.

View your BigQuery data on a dashboard
View your BigQuery data on a dashboard

4. Notes

  • 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.

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri