Google describes BigQuery as “an enterprise data warehouse that provides super-fast SQL queries using the processing power of Google’s infrastructure.” Openbridge supports the delivery of data to a BigQuery warehouse destination. If you are not a BigQuery user, Google offers a free tier, perfect to kickstart your data efforts.
Getting Started
You will need to care for a few steps before we can get BigQuery set up within Openbridge. We need to get your Google Cloud configuration set up. Specifically:
You'll need to choose the project you want to use within your Google Cloud Console. This is typically a drop-down menu in the Google Cloud console nav
You need to enable your BigQuery API for the selected project
You need to create a Service Account and IAM policy that allows Openbridge to access to BigQuery within your project
The process should take at most 5 minutes. Let's get started!
Step 1: Enable The BigQuery API
The first step is to verify that you have the BigQuery API enabled. If not, then you can enable it.
p.s. Make sure you have selected the correct project in the nav.
Step 2: Create a Service Account
The next step is to configure a service account via IAM. You need to log in to your Google Cloud console:
After you login select "IAM & admin" in the navigation panel.
Next, you want to select "Service Accounts" and then "Create service account"
This will pop open the create service account window.
A: Give your account a unique name
B: Copy the "Service Account ID" as you will need this later. It will look something like this "openbridge-bigquery-data-1234567@appspot.gserviceaccount.com"
C: Select "JSON" as the Key type
p.s. Don't select a Role; we will do that later.
When you are ready, select "CREATE"
Your service account JSON will download to your computer. The content of the file will look something like this:
{
"type": "service_account",
"project_id": "foo-baseme-139217",
"private_key_id": "",
"private_key": "",
"client_email": "foo-baseme-139217@appspot.gserviceaccount.com",
"client_id": "122211111111",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://accounts.google.com/o/oauth2/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/foo-casing-139217%40appspot.gserviceaccount.com"
}
Keep this file in a safe place. Treat it like you would a password. You will need this later when you set up BigQuery within Openbridge.
NOTE: Google Cloud generally recognizes a service account JSON key as soon as it is uploaded. However, it is worth noting that it may take some time for the permissions associated with the service account to propagate across all Google Cloud services. This process typically takes a few minutes, but sometimes it may take up to 24 hours!
You should see your newly created Service Account listed:
Step 3: Adding Permissions To Your Service Account
Next, we need to add BigQuery permissions to your new service account.
In your Google Cloud console, select "IAM & admin"->"IAM"
You will see the "ADD" option. This will allow you to set permissions for your new Service Account. Select "ADD"
You will be presented with a new window:
A: Remember the Service Account ID we asked you to save? You enter it here. Paste the Service Account ID where it says "New members"
B: Select the BigQuery Admin role.
NOTE: Google also requires the Project Browser role to be set. If the Project Browser is not set it will result in an error due to the Google Cloud projects being empty. As a result, both BigQuery Admin and Project Browser must be set!
When you are done, select "SAVE"
Your new member and permissions for your BigQuery Project should be listed as:
Congrats! You configured a Service account to allow us to deliver data to a BigQuery warehouse destination.
One Last Thing...Google Cloud Billing!
Some Google Cloud projects do not have billing enabled. Please make sure your target project has it enabled. Without billing being active, Google prevents us from being able to connect to BigQuery!
First, go to "Billing" in the Google Cloud Console:
Next, please make sure Billing is active and configured. If it is not, turn Billing on.
Here are reference docs from Google about setting up billing: