Setting up Google BigQuery as a Target
  • Updated on 19 Mar 2020
  • 4 minutes to read
  • Contributors
  • Print
  • Share
  • Dark
    Light

Setting up Google BigQuery as a Target

  • Print
  • Share
  • Dark
    Light

Overview

Before you use this guide, please make sure you’ve signed up for the Google Cloud Platform and that you have a console admin user.

If you don’t have one of these prerequisites, click here .

This guide will walk through creating the following in order to create a connection to BigQuery within Rivery:

  • Google Cloud Service Account
  • Enable Cloud Storage and BigQuery APIs
  • Google Cloud Storage Bucket
  • Create a BigQuery dataset

At the end of this guide, you will be able to quickly connect your BigQuery account to Rivery from inside the tool.

Create a Service Account User for Rivery:

Rivery uploads your source data into a Google Cloud Storage bucket. Your BigQuery project then pulls in data from this storage bucket. To access the relevant storage bucket and BigQuery project, you will need to first create a user in the Google Cloud Platform Console that has access to the relevant bucket and the relevant BigQuery project.

In order to create a new service account in Google Cloud Platform,
please make sure your user has a ServiceAccountAdmin role.

Now, let’s create a service account in the GCP Console:

  1. Sign into Google Cloud Platform Console .

  2. Go to IAM & Admin -> Service account
    setting-up-google-bigquery-as-a-target_mceclip02.png

  3. Click on CREATE SERVICE ACCOUNT .
    setting-up-google-bigquery-as-a-target_mceclip1.png

  4. Set yourService Account name (i.e: RiveryUser)

  5. Set Roles

    Storage Admin
    BigQuery Admin

  6. Copy your Account Service ID / Email from the service account list. You will enter this later in a Rivery connection.

  7. Mark the Checkbox - Furnish a new private key andin the key type options, chooseP12.

  8. Click on the CREATE button. Your P12 secret key will download. Keep track of this key file as you will need to import it in a forthcoming step.

Enable Cloud Storage and BigQuery API

  1. Go to API's & Services on the Google Cloud Console menu pane -> Click on Library.

  2. Search for Google Cloud Storage JSON API and click on Enable . setting-up-google-bigquery-as-a-target_mceclip24.png

  3. Search for BigQuery API and click on Enable . setting-up-google-bigquery-as-a-target_mceclip33.png

Create a Google Cloud Storage Bucket

Rivery needs a Google Cloud Storage bucket to operate as a FileZone before your data is uploaded to BigQuery.

  1. Sign into the Google Cloud Platform Console .

  2. In the menu pane on the left, go to Storage -> Browser -> click on the CREATE BUCKET button.
    setting-up-google-bigquery-as-a-target_mceclip46.png

  3. In the wizard:

    • Set Bucket Name (example: project_name_rivery_file_zone)
    • Set your Bucket to Region and choose your preferred location
    • Click the CREATE button

Create a BigQuery Dataset

While Rivery will create your tables automatically, you need to choose your Dataset. You can leverage an existing dataset, or create a new one by following these steps:

  1. Sign into the Google Cloud Platform Console.

  2. On the menu pane to the left, go to BigQuery .The Google BigQuery editor will open.

  3. Go to the left sidebar -> Select your Project Name

  4. To the right of the page, in the bottom half of the editor, click on the CREATE DATASET buttonsetting-up-google-bigquery-as-a-target_mceclip53.png

  5. In the wizard:

    • Set the Dataset ID
    • Click the Create dataset button.

Configure Your BigQuery in Rivery

Next, we need to configure a connection to BigQuery within Rivery:

  1. Log into Rivery.

  2. Set your bucket as the default Google Cloud Storage bucket in Rivery:

    • On the left hand side of the screen, go to Variables
    • Set your {gcs_file_zone} variable value to the GCS bucket name that you’ve created. This will be saved automatically by clicking anywhere on the screen
    • If you don’t have a {gcs_file_zone} variable on your screen, you should add a new variable with that name and set the GCS bucket name that you’ve created as the value. Press on + Add Variable Screen_Shot_2017-11-26_at_6.56.36_PM.png
  3. Create a new connection for your BigQuery instance:

    • Go to Connections.
    • Select New Connection .
    • From the source list, choose Google BigQuerysetting-up-google-bigquery-as-a-target_mceclip010.png
  4. Next, enter the credentials information for your Google Platform Service Account:

    setting-up-google-bigquery-as-a-target_target_mceclip1_1.png

    1. Connection Name

    2. Description (optional)

    3. Project Id (can be found on the Google Cloud Platform Home section)

    4. Project Number (can be found on the Google Cloud Platform Home section)

    5. Service Account email - the Service Account Id that you copied from the Service Account Wizard

    6. Choose file - upload the P12 file that was generated when you created a Service Account

Click 'Test Connection' at the bottom to test! Once a valid connection is made, save the connection.

If you cannot get a valid connection set up, contact helpme@rivery.io for support.

Conclusion

This guide showed you how to create:

  • A Service Account user for Rivery
  • A Google Cloud Storage bucket
  • A BigQuery Dataset
  • A BigQuery connection in Rivery

You now have a Google BigQuery connection that you can use in any river where Google BigQuery is the end database target.

Was this article helpful?