Google BigQuery

Introduction

This guide takes you through setting up Google BigQuery for use with Cyclr, as well as installing a Google BigQuery Connector.

You will need a Google Cloud Platform account.

Get OAuth 2.0 credentials

To authenticate the connector, you need to create a web application inside your Google Cloud Platform account to get your OAuth 2.0 credentials:

From the Google Cloud Platform Credentials page, select CREATE CREDENTIALS > OAuth client ID. Create a project with the following settings:

Setting

Value

Application type

Web application

Authorised redirect URIs

A URL containing your Service Domain, e.g.

https://{ServiceDomain}/connector/callback

Your service domain can be found in your Cyclr Console under:
Settings > General Settings > Service Domain.

Make note of the Client ID and Client secret.

For more information, see Google's guide on how to create client credentials.

Get project ID

You need a project ID to use methods in the Tables method category. From the Google Cloud Platform dashboard:

Select the project name from drop down button in the navigation bar.
Make a note of the project ID listed under the ID heading. In this example, the project ID of the cyclr project is cyclr-389110.

You can also find the project ID if you install the Google BigQuery connector without a project ID or dataset ID set, then use the Utilities > Projects > List Projects method. After you use this method, you need to re-authenticate the connector.

Get dataset ID

You also need a dataset ID in order to use methods in the Tables method category. From the Google BigQuery dashboard:

In the Explorer pane, select the arrow to the left of the project ID to expand it.
Select the arrow to the left of each dataset to view the tables within it and make a note of the dataset ID. In this example, the dataset ID that contains table_1 is dataset_1. ![BigQuery dataset ID 1]((./images/google-bigquery-dataset-id-1.png)

You can also find the dataset ID if you install the Google BigQuery connector without a project ID or dataset ID set, then use the Utilities > Datasets > List Datasets method. After you use this method, you need to re-authenticate the connector.

Partner Setup

To set up the Google BigQuery connector in Cyclr, go to your Cyclr console:

Go to Connectors > Application Connector Library.
Use the search box to find the Google BigQuery connector.
Select the Setup Required icon.
Enter the below values:

Value

Description

Client ID

The default client ID to use.

Client Secret

The default client secret to use.

Scopes

The scopes you want to use.

Cyclr uses a default scope of https://www.googleapis.com/auth/bigquery if you don't set this value.

Select Save Changes.

If you leave any values blank, Cyclr asks for the value when you install the connector into an account. This means you can use different settings for different accounts.

Account setup

Cyclr also asks for the below values when you install the Google BigQuery connector into an account:

Value	Description
Project ID	The project ID of the project to use. This must be entered to use methods in the Tables method category.
Dataset ID	The dataset ID of the dataset to use. This must be entered to use methods in the Tables method category.

Additional Information

Create a Table Custom Object Category

You can create a Custom Object Category to access each table within a project and dataset as it's own method category. Create multiple Custom Object Categories to access multiple tables under a single connector installation.

You need to install the Google BigQuery connector with a Project ID and Dataset ID for table custom objects to function.

To create a table Custom Object, from the Edit Connector page of the Google BigQuery connector:

Under the Methods & Fields heading, select the Tables methods category.
Select the pink Copy this Category to create a Custom Category Object icon.
Select the Select object dropdown.
Select the table you want to create a Custom Object Category for.
Select Copy.

The methods within the new method category you created now target the selected table.

Create a Cycle to request a large set of data

Use the List New Table Data Incrementally or List Partial Table Data method to incrementally list data in a Cycle. You can use Generic Webhook Connector methods to allow the cycle to send a request to itself to continually run. This approach is useful for very large sets of data.

When you use the List Partial Table Data method, you need to track the last index of the table data returned in the current request and send it in the next request. To do this, add request fields for the Generic Webhook Connector then map the last index of the table data returned as additional steps, as explained below.

Prerequisites

Install the Google BigQuery connector.
Create a Table Custom Object that targets the required table.
Install the Generic Webhook Connector
- To install the Generic Webhook Connector, navigate to the Utility Connectors page:
  - From the console, select Templates > Template Connectors > + Install New Utility.
  - From an account, select Connectors > + Install New Utility.
- Select Install under the Generic Webhook Connector.
- Update the Name field to a recognisable name.
- Select Next.

Add request fields for the Generic Webhook Connector

You only need to do this step for the List Partial Table Data method.

To allow the next request to use the last index of the table data returned in the current request, you must add request fields to the Generic Webhook connector that track the index:

Navigate to the Edit Connector page for the Generic Webhook Connector:

From the console, select Templates > Template Connectors. Under the Installed Utility Connectors heading, select the Edit Connector icon next to the the Generic Webhook Connector.
From an account, select Connectors. Under the Installed Utility Connectors heading, select the Edit Connector icon next to the Generic Webhook Connector.

Add a request field to the POST method

Under the Methods and Fields heading, select HTTP Methods.
Select POST.
Under the Request Fields heading, select the Add Field icon.
Set the Field Location to lastIndex.
Set the Display Name to Last Index.
Set the Data Type to Integer.
Select Create.

Add a request field to the Webhook method

Under the Methods and Fields heading, select Webhooks.
Select Webhook.
Under the Request Fields heading, select the Add Field icon.
Set the Field Location to lastIndex.
Set the Display Name to Last Index.
Set the Data Type to Integer.
Select Create.

Create a Cycle

From the console:

Select Templates > Template Library.
Select Create New Template.
Enter a template name.
Select Create.

From an account:

Select Cycles.
Select Design New Cycle.
Enter a Cycle name.
Select Create.

Add methods to the Cycle

Add following methods to the Cycle:

Generic Webhook > HTTP Methods > POST
Generic Webhook > Webhooks > Webhook
Google BigQuery > Tables > List New Table Data Incrementally or List Partial Table Data
Tools > Delay: You can use the Delay tool to set the time between requests once all current table data has been retrieved.

Connect the methods

Connect the methods as follows:

In this example, add any additional Cycle methods between the true exit of the List Partial Table Data method and the POST method.

Configure a Cycle

Setup the POST to webhook loop

The POST method must target the Webhook method to allow the Cycle to send a request to itself to continually run:

Select the Step setup of the Webhook method.
Copy the webhook URL and close the window.
Select the Step setup of the POST method.
Select Select... next to the URL field.
Select Type a Value.
Enter the webhook URL into the text box and close the window.

Map the last index of the table data returned

This step is only required for the List Partial Table Data method.

Select the Step setup of the List Partial Table Data method.
Select Ignore next to the Start Index field.
Select Webhook.
Select Nothing Selected.
Select Last Index. This is the field added earlier in this process.
Close the window.
Select the Step setup icon of the POST method.
Select Ignore next to the Last Index field. This is the field added earlier in this process.
Select the List Partial Table Data method.
Select Nothing Selected.
Select Last Row Index.
Close the window.

Configure the Rows Per Execution setting

The rows per execution setting determines how many table rows are returned per Cycle execution. You need to manually set the rows per execution, which is dependant on the width of your table data. For example, a starting point of 10000 might be useful.

Select the Step setup of the List Partial Table Data or List New Table Data Incrementally methods.
Select Ignore next to the Rows Per Execution field.
Select Type a Value.
Enter the enter the rows per execution into the text box.
Close the window.

Configure the delay time

Use the delay step to set the time between cycle executions once the cycle retrieves all of the table data. When new table data is found, the cycle continues to run until it retrieves all table data and then reverts back to the delay.

For example, a delay duration of 12 hours makes the Cycle check for new table data once every 12 hours once it retrieves all of the table data.

Select the Step setup of the Delay method.
Set the duration and close the window.

Give dataset access to another user

You can give other users access to a dataset. For more information, see Google BigQuery's documentation on IAM access control.

Add access for another user from the Google BigQuery console:

From the Explorer pane, navigate and select the dataset.
From the dataset navigation menu bar, select Sharing > Permissions.
Select Add Principal.
Under the Add principals heading, in the New principals field, enter users, groups, domains, or service accounts to give access to.
Under the Assign roles heading, select the Select a role dropdown box to assign roles.
Select Save.

Insert table data delay

Because Google BigQuery needs to process and stream the data from Google’s internal servers to the table before you can access it, there might be a delay when you insert table data, before you can access the data.

If the insert call doesn't return an error, the data appears in your table within 24 hours.