How to use IBM® App Connect with Google Cloud BigQuery

Enter the pagination type. Google Cloud BigQuery is an analytical data warehousing solution that enables organizations to analyze data across clouds.

Supported product and API versions

To find out which product and API versions this connector supports, see Detailed System Requirements on the IBM Support page.

Connecting to Google Cloud BigQuery

Complete the connection fields that you see in the App Connect Designer Connect > Applications and APIs page (previously the Catalog page) or flow editor. If necessary, work with your Google Cloud BigQuery administrator to obtain these values.

Google Cloud BigQuery connection fields:
Connection field Description
Project ID Your Google Cloud BigQuery project ID.
Dataset ID The ID (schema name) of the dataset containing the tables and views that you want to work with in your Google Cloud BigQuery project. To work with tables or views from another dataset, you need to create a different account using that specific dataset ID.
Client email Also known as the service account ID. You can find or create this value in the APIs & Services or IAM & Admin section of the Google Cloud Console. For example, abc-222@bigqueryproject-22xxx.iam.gserviceaccount.com.
Private key A key used to establish the identity of the service account. Find or create this value in the Service Accounts page of the Google Cloud Console. For more information, see https://ibm.biz/googlebigquery_privatekey.
To obtain these values for Google Cloud BigQuery, complete the following steps.
  1. Log in to the Google Cloud Console.
  2. On the dashboard, select an existing project, or click NEW PROJECT.
  3. Enter a project name and location (organization), then click Create.

    When the project is created, the Project ID is shown on the dashboard.

  4. On the Getting Started tile, click Explore and enable APIs, then click + ENABLE APIS AND SERVICES.
  5. Search for and select BiqQuery API, then click Manage.
  6. Click CREDENTIALS, then click Credentials in APIs & Services.
  7. Click CREATE CREDENTIALS, then select Service account, and follow the on-screen instructions to create a service account.
    Important: On the Grant this service account access to project section, make sure to add the following roles:
    • BigQuery Admin
    • BigQuery Data Editor
    • BigQuery Data Owner
    • BigQuery Metadata Viewer
    • Owner
    • Viewer
  8. When the service appears on the Credentials page, click the service account, then go to KEYS > Add key > Create new key.
  9. Select JSON as the key type, then click CREATE.
  10. Save the JSON file to your local system.
  11. Open the JSON file using an editor of your choice. This file contains the Client ID and Private key values.

To connect to a Google Cloud BigQuery endpoint from the App Connect Designer Applications and APIs page for the first time, expand Google Cloud BigQuery, then click Connect. For more information, see Managing accounts.

General considerations

Before you use App Connect Designer with Google Cloud BigQuery, take note of the following considerations:

  • (General consideration) You can see lists of the trigger events and actions that are available on the Applications and APIs page of the App Connect Designer.

    For some applications, the events and actions depend on the environment and whether the connector supports configurable events and dynamic discovery of actions. If the application supports configurable events, you see a Show more configurable events link under the events list. If the application supports dynamic discovery of actions, you see a Show more link under the actions list.

  • (General consideration) If you are using multiple accounts for an application, the set of fields that is displayed when you select an action for that application can vary for different accounts. In the flow editor, some applications always provide a curated set of static fields for an action. Other applications use dynamic discovery to retrieve the set of fields that are configured on the instance that you are connected to. For example, if you have two accounts for two instances of an application, the first account might use settings that are ready for immediate use. However, the second account might be configured with extra custom fields.

Events and actions

Google Cloud BigQuery events

Note: Events are not available for changes in this application. You can trigger a flow in other ways, such as at a scheduled interval or at specific dates and times.

Google Cloud BigQuery actions

Your flow completes these actions on this application.

Object Action Description
Datasets Create dataset Creates a new empty dataset.
Retrieve datasets Retrieves the dataset specified by Dataset ID.
Update dataset Update a dataset in the Google Cloud BigQuery project
Delete dataset Deletes the dataset specified by the Dataset ID value.
Jobs Retrieve jobs Returns information about a specific job. Job information is available for a six month period after creation. Requires that you're the person who ran the job, or have the Is Owner project role.
Tables Retrieve tables

Retrieves the specified table resource by table ID. This method does not return the data in the table, it only returns the table resource, which describes the structure of this table.
Update table Updates information in an existing table.
Delete table Deletes the table specified by Table ID from the dataset. If the table contains data, all the data is deleted.
Restore deleted table Restores a table that was deleted but is still within the time travel window by copying the table to a new table.
Copy table Copy a table to a specified destination.
Export data Exports a table to .csv, .json, or .avo file.
Load data Load data into a Google Cloud BigQuery table.
Views Retrieve views Retrieve view from the Google Cloud BigQuery project
Delete view Delete a view from the Google Cloud BigQuery project

Examples

Screenshot of the dashboard tile for a template that uses Google Cloud BigQuery

Use templates to quickly create flows for Google Cloud BigQuery

Learn how to use App Connect templates to quickly create flows that perform actions on Google Cloud BigQuery. For example, open Discover, and then search for Google BigQuery.

Learn more
Screenshot of the dashboard tile for a template that uses Google Cloud BigQuery