Connecting to Google BigQuery

Contents[Hide]

1. Overview

Google BigQuery is a web service for querying massive datasets that take advantage of Google's cloud infrastructure.

This article shows you how to create a data connector in Dundas BI to extract data from your Google project via the BigQuery API.

Using Google BigQuery
Using Google BigQuery

2. Setup

2.1. Create or choose a project

Go to https://console.cloud.google.com/ and sign into your Google account.

Once signed in, you can create or choose an existing project to use. The current project is often listed at the top of the screen and you can click to see a list of projects and switch between them or to create a new one.

The cloud resource manager page should also list your projects and allow you create a new one. A project acts as a container in the Google Cloud Platform which stores information about billing and authorized users and contains BigQuery data.

Google cloud projects
Google cloud projects

2.2. Enable the BigQuery API

To check that the BigQuery API is enabled, click the menu button in the top-left corner, and go to the API & services dashboard.

List of enabled APIs for the project
List of enabled APIs for the project

Ensure your project is selected as the current project in the top-left corner. You can enable the BigQuery API on this page if not already listed.

2.3. Service account credentials

Service account credentials are needed to allow Dundas BI to access your data.

To create or manage service accounts, in the API Manager, click Credentials in the left navigation. On the right, click Create credentials and select the Service account option.

Add service account credentials
Add service account credentials

Enter a name and role for your service account.

When done, add a key for your service account. For example, click to edit the service account, go to the Keys tab if applicable, then click Add Key. Choose a JSON key type.

When finished, a certificate file should be downloaded. 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 key type instead of the default JSON, you will require some additional information.

A popup should be displayed showing the password for the private key. Copy and paste this password into Notepad or similar for later use.

You will also need the service account email address. Copy this from the service account details and paste it with your password.

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 do not have this set up yet, you can sign up for a free trial from https://cloud.google.com/bigquery/.

Next, go back to the Google Developers Console for your project. Click the menu button in the top-left corner and then click Billing.

Ensure billing is enabled, and you may need to 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. New data connector

In Dundas BI, create a new data connector from the main menu.

Set the Data Provider dropdown to Google BigQuery.

Click Choose File and select the certificate file that you downloaded.

Choose the certificate file
Choose the certificate file

If the certificate file is of a P12 file type, you now have to provide the Service Account E-Mail Address and Password For The .P12 File that you copied for later reference.

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

Click Submit to create the data connector and begin discovery.

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

3.1. Advanced

Expand the Advanced section for additional options.

Use the option Use Legacy SQL to switch the data connector from using standard SQL to legacy SQL.

Advanced options
Advanced options

Use the option Allow Large Results to avoid data connector errors such as Response too large to return. To use this option, you have to create a dataset for storing large results on the Google Cloud Platform. 

Tip
Check the option to Expire new tables in one day when creating a dataset in the Google Cloud Platform.

4. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

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