Connecting to Google BigQuery

Prev Next

Overview

Google BigQuery is a serverless, highly scalable cloud data warehouse that enables you to analyze vast amounts of data. The Syniti Knowledge Platform (SKP) allows you to integrate and access data securely from Google BigQuery using REST APIs. The SKP connects to BigQuery through OAuth 2.0 authentication, allowing secure read and write operations on BigQuery datasets and tables.

Prerequisites

Before creating a Google BigQuery connection, ensure the following:

  • Google Cloud Project Details:

    • You must have an active Google Cloud Project with BigQuery API enabled.

    • Ensure that you have your Google BigQuery project details, specifically Project ID and Dataset IDs. Refer to Creating and managing projects and BigQuery Datasets for more information.

  • User Identity: Users do not need individual BigQuery permissions. The Syniti OAuth2 client is pre-configured with all required BigQuery permissions. However, your Google developer account (user identity) is required for authentication (tracking and audit) only, not authorization.

Creating a BigQuery Connection

When you are in the Create Connection page,

  1. Select HTTP API from the Type list and enter the following details:

    Property

    Description

    Notes

    Name (Required)

    Name of the Google BigQuery connection, unique in the SKP.

    Not applicable

    Description

    Enter a description that briefly states the purpose of this connection.

    Not applicable

    Connector Type

    Select Google BigQuery from the connector type list.

    This specifies the REST API connector type for BigQuery operations.

    Base URL (Required)

    Enter the Google BigQuery API base URL.

    Use https://bigquery.googleapis.com/bigquery/v2 as the base URL.

    Extended Properties

    Use the + Add Property button to add any additional connection properties.

    Not applicable

  2. From the Usage list, select the required usage to define how this connection is used.

  3. From the SKP Connector list, select the required SKP Connector component to communicate with your BigQuery project.

  4. Click Save.

At this point, the connection is saved but not yet authorized. You’ll be redirected to the newly created Google BigQuery connection details page.

Authenticate BigQuery Connection

Complete the following steps to authenticate the connection:

  1. On the newly created Google BigQuery connection details page, click Connect with Google to initiate the authorization process. You are redirected to Google's authorization page.

  2. Log in with your Google account. Google asks for your permission to allow Syniti to access your BigQuery datasets.

  3. Approve this permission request. You are redirected back to the connection details page with the following message: Successfully authenticated.

The SKP receives an authorization code from Google and exchanges it for a refresh token, which is securely stored to enable continued access to your BigQuery data.

Google BigQuery Datastore

Once the Google BigQuery connection is created, you can add this connection to the required datastore.

On the Add Datastore page, ensure you enter the following details:

  • Connection: Select the newly created Google BigQuery connection.

  • Database: Enter your Google Cloud Project ID.

  • Schema: Enter your BigQuery Dataset ID or name.

Add Tables

  1. In the newly created BigQuery datastore, select the Tables tab and click Add Tables. The Add Table dialog box is displayed.

  2. In the Name field, enter the required table name.

  3. Enter a description for this table.

  4. In the Schemas field, enter the dataset ID or name from your Google BigQuery project where the table exists.

  5. Click Save.

  6. Repeat Steps 1 through 5 to add more tables.

The tables are now added for your BigQuery datastore connection. After adding tables, a metadata scan is automatically triggered, importing tables and their metadata into your datastore.

Reauthenticate BigQuery Connection

If replication or metadata scanning fails due to expired or revoked authorization:

  1. Access the Google BigQuery connection page and click Reconnect with Google.

  2. Repeat the authorization process. A new refresh token is generated and stored.