Articles in this section

Google BigQuery

Overview

Google BigQuery is Google’s fully managed, petabyte scale, low-cost enterprise data warehouse for analytics. Here are some things you can expect from the Google BigQuery/Grow integration.

Contents in This Article

Features

Acquisition Method

State
  Data Warehouse

  Beta

  Direct Query

   Supported

 

How to Connect

Before you get started you will need your Google BigQuery Project ID, Client Email and Private Key or you can upload a JSON file with your project ID, email, and private key.

  1. Click on the Data tab at the top left of the global navigation. Then click on "Connections" in the Data Library section.
  2. Click on the blue "Connect" button and search for Google BigQuery.
  3. Click on the Google BigQuery logo.
  4. The most straightforward way to connect your BigQuery account is to click “Fill in manually” on the authorization page. You’ll need the BigQuery project ID you want to connect, the email address associated with the BigQuery account, and your private key.
  5. If you don’t know what your private key is or how to get it, Google has a helpful article on how to do so. You have to do a bit of scrolling through the page to get to what you need, so we’ve copied the important details below:
  6.  
To generate a private key in JSON or PKCS12 format in Google BigQuery:
  1. Open the list of credentials in the Google Cloud Platform Console.
  2. Click Create credentials.
  3. Select Service account key. A Create service account key window opens.
  4. Click the drop-down box below Service account, then click New service account.
  5. Enter a name for the service account in Name.
  6. Use the default Service account ID or generate a different one.
  7. Select the Key type: JSON or P12.
  8. Click Create. A Service account created window is displayed and the private key for the Key type you selected is downloaded automatically. If you selected a P12 key, the private key's password ("notasecret") is displayed.
  9. Highlight and copy your private key.
  1. You can then come back to Grow and paste the key into the Private Key field. You'll need to also enter your email associated with BigQuery and your Project ID. Alternatively, if you have the know-how and would prefer to upload a JSON file with your project ID, email, and private key, that will work as well.
  2. When you've entered in this information, press the Connect button. You will see a green confirmation message that the connection has been made.

And that’s it! You are good to go.

Data Warehouse

Google BigQuery is on the Grow Data Warehouse, which we highly recommend you use. For more information about warehousing your data, visit the Data Warehouse Help Article.

Tables

The list of tables available will depend on your unique Google BigQuery project. You can expect to see one table per dataset associated with the project Id in your connection. Below is an example:

GoogleBigQuery.png

How to Warehouse your Data

  1. Starting with the Connections Overview Page, navigate to your Google BigQuery connection and click on it to open the Connection Details page.
  2. Select the “Manage Connection” button inside the Connection Details section to open the warehouse table selection flow.
  3. If your data source isn't already connected, refer to the How to Connect section of this article for more details. If your data source is already connected, you can immediately select which tables of data you want to warehouse.
  4. Once you have finished selecting the data that you want stored in Grow’s Data Warehouse, click the “Sync & Store” button at the bottom of the Manage Connection page. This will begin the initial population of your warehouse with the tables you defined. Populating your data warehouse for the first time may take a while, even up to several hours.
  5. While your data is "In Queue" or "Processing", you may continue working within the app.

Sync Interval

Full Sync

The default sync interval is 1 week.

Incremental Sync

Incremental Sync is available for all Google BigQuery warehoused tables. The default incremental sync interval is 1 hour.

To learn more about sync intervals and how to adjust the default settings, please visit this section of the Data Warehouse help article.

Direct Query

A direct query connection can be used to pull data into Grow. With abnormal usage, Direct Query can run into API limitations depending on the data source. For this reason, we recommend that you use Grow's Data Warehouse for a more performant, reliable experience.

Support Status

Direct Query

Currently supported with no projection to stop maintaining

 

Endpoints

Below is a list of the reports you can pull from BigQuery, along with a description of the fields available with each report.

  • Datasets: All datasets associated with projects under the connected BigQuery account, including kind, dataset ID, dataset reference, and project ID.
  • Tables: All tables for the selected dataset, including kind, table ID, project, dataset, table name, type, and unix epoch creation time.
  • Table Data: All table data for the selected dataset and table. The fields returned here will vary based on the specifics of the datasets created with BigQuery.
  • Custom SQL Query: Write custom SQL queries to hone in on specific fields when building a metric.

API Documentation

https://cloud.google.com/bigquery/docs/reference/rest

This is for reference only; you should not need to consult these to use the connection in Grow.

 

FAQs + Tips and Tricks

Permissions for Connecting to BigQuery using the Grow Warehouse

When connecting to Google BigQuery using the Grow Warehouse make sure you have the correct roles and permissions assigned. The user who is connecting BigQuery to Grow needs to have the assigned role of roles/bigquery.jobUser then also have the permission bigquery.jobs.create. Assigning this role and permission will allow you to run jobs including queries inside projects.

For more in-depth information on roles and permissions see "Predefined roles and permissions".

Unix Epoch Dates

Some datatable information fields include creation dates in unix epoch format. This will just look like a string of random numbers. Luckily, there’s a quick way to fix that, either through Grow’s table date format function in the edit metric screen, or with a relatively simple SQL query.

How can I make sure my data is secure?

We have several items in place to keep your data safe. Check out our security article to see what steps you can take to keep control of your data.

Related articles