Setting up the BigQuery Environment

The BigQuery connector allows you to access the BigQuery REST API from an integration sequence.

To work with the BigQuery connector, you need to have a Google Cloud Platform account. If you do not have a Google Cloud Platform account, go to console.cloud.google.com, and create a Google Cloud Platform trial account.

BigQuery uses the OAuth 2.0 protocol for authorization. All requests to the BigQuery REST API will be authorized against a registered user. Developers can generate user credentials from the Google Cloud Platform using two different mechanisms. See the following sections for detail.

Obtaining user credentials

Follow the steps below to generate user credentials.

Obtaining a client ID and client secret

  1. Go to https://accounts.google.com/SignUphttps://accounts.google.com/SignUp and create a Google account.

  2. Go to https://console.developers.google.com/projectselector/apis/credentials, and sign in to your Google account.

    Bigquery-credentials-page

  3. If you do not already have a project, you can create a new project. Click Create credentials and then select OAuth client ID.

    Select OAuth client ID

  4. Next, select Web Application, and create a client.

    Select web application

  5. Add https://www.google.com as the redirect URI (you can add any URI that you need as redirect URI) under Authorized redirect URIs, and then click Create. This displays the client ID and client secret.

    Authorization-redirect-URI

  6. Make a note of the client ID and client secret that is displayed, and then click OK.

  7. Click Library on the left navigation pane.

    Select library

  8. Search BigQuery API under the Big data category.

    Pubsub API

  9. Click Enable. This enables the BigQuery API.

    Pubsub enable API

  10. Get the authorization code by sending a GET request to the following URL. Replace the <redirect_uri> and <client_ID> with the redirect URI and client ID values noted in the previous steps. Enter the following URL in your web browser.

    https://accounts.google.com/o/oauth2/auth?redirect_uri=<redirect_uri>&response_type=code&client_id=<client_ID>&scope=https://www.googleapis.com/auth/bigquery&approval_prompt=force&access_type=offline
    Note the authorization code for future use.

    Get authorization code

  11. Get the access token and refresh token by sending a POST request to the URL given below. Be sure to use an x-www-form-urlencoded body with the <authorization_code>, <client_id>, <client_secret>, and <redirect_uri> values noted before, and also set the grant_type to authorization_code. You will need them to configure the ESB Big Query Connector. 

    https://www.googleapis.com/oauth2/v3/token.
    Bigquery get token using postman

Obtaining credentials using the service account

  1. Open the Service Accounts page in the GCP console.

    Bigquery service account

  2. Select your project and click Open.

  3. Click Create Service Account.

    Bigquery create service account

  4. Enter Service account details, and then click Create.

    Bigquery enter service account

  5. Select a role you wish to grant to the service account and click Continue.

    Bigquery enter service account role

  6. Grant users access to this service account (optional) and click Done.

    Bigquery enter service account grant user access

  7. Go to the service account for which you wish to create a key and click the created Service account in that row.

    Bigquery enter service account grant user access

  8. Click Create key.

    Bigquery service account create key

  9. Select the key type as P12 and click Create. Then the created key will be downloaded.

Creating Project, Dataset and Table

Creating Project

  1. Open the BigQuery console.

  2. Click down arrow key shown in the following image.

    Bigquery create project step1

  3. Click New Project.

    Bigquery create project step2

  4. Enter new project details.

    Bigquery create project step3

Creating Dataset

  1. After creating the Project, click the created project. You can see the following details. Then click Create Dataset.

    Bigquery create Dataset step1

  2. Enter required Dataset details and click Create Dataset.

    Bigquery create Dataset step2

Creating Table

  1. After creating the Dataset, click the created Dataset. You can see the following details. Then click Create Table.

    Bigquery create Table step1

  2. Enter required Table details and click Create.

    Bigquery create Table step2

For more information about these operations, please refer to the BigQuery connector reference guide.

Top