Connecting to Google BigQuery
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.
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.
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.
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.
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.
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).
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.
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.
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.
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.
4. See also
- Google Cloud: Introduction to BigQuery
- Google Cloud: BigQuery: Cloud Data Warehouse
- Connecting to Google Sheets
- Connecting to Google Analytics