BigQuery

Last Updated: 2022-01-20

Overview

BigQuery is a Google-run serverless, scalable, cloud-based data warehouse. It is built to support analysis and has built-in machine learning capabilities. Specifically, BigQuery is designed to help companies look for insights using very large datasets. Grouparoo's BigQuery Plugin enables you to import data from one or more BigQuery databases. You can use Grouparoo to sync data from BigQuery to any of the Destinations we provide plugins for.

Sources

Source NameDescription
BigQuery Import TableImport or update Records from a Bigquery database table.
BigQuery Import QueryImport or update records via a custom Bigquery query.

Setup

In order to use BigQuery, you’ll need to enable it as a service in your Google Cloud project. Create a service account which will be the source for a Client Email Address and Private key you will need to configure the initial connection for a BigQuery App in Grouparoo.

Find this information by going to the Service Accounts (IAM & Admin) page for Cloud Console, selecting a project, and choosing the email address for the service account you are using. Then, click the Keys tab and select Add Key from the Dropdown menu to create a new key. Select JSON as the Key type to create. This downloads a JSON file to your computer with the setup information you need.

BigQuery Setup

App

To connect BigQuery to Grouparoo, you create an App to tell Grouparoo how to connect to BigQuery. This specifies the connection details for the database you want to sync data from.

App Options

In Grouparoo, provide the options that best describe how you would like to connect to BigQuery.

BigQuery App Options

App OptionDescription
Project ID (required)Project id from Google.
Dataset (required)Default dataset id to use for sources.
Client Email Address (required)Email of service account. This will follow the pattern of the helper text pre-populated in the field and is generated by Google when you create a service account for this project.
Private Key (required)Private key of service account.
LocationRegion or multi-region code of your dataset. ("US", "EU", "asia-east1", etc.) This field is required for datasets outside the US.

BigQuery Import Table

This type of Source is a Table Source. It reads from a single schema or table in your BigQuery database using a primary or foreign key. Properties are generated from this table from row values or aggregations of row values.

Options

BigQuery Import Table Options

OptionDescription
Table Name (required)Name of the table to import.

Mapping

Mapping is the way you tell Grouparoo how Source properties relate to the fields in the Destination you are sending them to.

BigQuery Import Table Mapping

Schedule

Create a schedule to actively pull new data from this BigQuery table. If the recurring option is not selected, you are creating a one-time sync. There are also options to confirm records exist when running a schedule and to enable refresh. When Recurring is selected, you will see a field for entering the frequency, in minutes, of recurrence.

Options

BigQuery Import Table Schedule

Option NameDescription
column (required)Select which column Grouparoo should check to look for new data. Often, this would be a column like “UPDATED_AT” - Grouparoo remembers the most recent values so it can be based on an incremental count as well.

Filters

If there is a subset of rows data you want to specifically include or exclude from your Postgres database table, create a filter.

Properties

Properties populate your Records with data from your Postgres table based on table columns. Create a Property to be able to use row data in Grouparoo.

BigQuery Import Table Property Options

Options

Option NameDescription
Column Name (required)Choose the column that will be the source of data for this Grouparoo Property.
Aggregation Method (required)Aggregation methods determine how to use the row values in the table. Postgres supports the standard set of aggregation methods for table sources.

Filters

If there is a subset of data for a specific primary or foreign key you want to include from your database table, create a filter.

BigQuery Import Query

A Query Source works differently than a table-based source in that there are no options to choose in setting up the connection to this type of source. A Query Source is a more flexible way to build properties.

This type of Source provides the ability to write custom SQL code to extract data from one or more tables and import the result into Grouparoo.

Notes

We strive to keep our documentation accurate, easy to use, and up to date. If you notice that something has changed or is not addressed in the process of setting up your Grouparoo connection with BigQuery, please let us know by reaching out to help@grouparoo.com.

As always, if you need support using Grouparoo, reach out to community members in our Slack channel. We are always happy to help!

Join us on Slack