Google BigQuery Connector
Documentation
Version: 12
Documentation

Authentication Service Account


Description

Service accounts are accounts that do not represent a human user. They provide a way to manage authentication and authorization when a human is not directly involved, such as when an application needs to access Google Cloud resources. Service accounts are managed by IAM. [API reference]

Instructions

Follow these steps on how to create Service Account to authenticate and access BigQuery API in SSIS package or ODBC data source:

Step-1: Create project

This step is optional, if you already have a project in Google Cloud and can use it. However, if you don't, proceed with these simple steps to create one:

  1. First of all, go to Google API Console.

  2. Then click Select a project button and then click NEW PROJECT button:

    Start creating a new project in Google Cloud
  3. Name your project and click CREATE button:

    Create a new project in Google Cloud
  4. Wait until the project is created:

    Wait until project is created in Google Cloud
  5. Done! Let's proceed to the next step.

Step-2: Enable Google Cloud APIs

In this step we will enable BigQuery API and Cloud Resource Manager API:

  1. Select your project on the top bar:

    Select project in Google Cloud
  2. Then click the "hamburger" icon on the top left and access APIs & Services:

    Access APIs and services in Google Cloud
  3. Now let's enable several APIs by clicking ENABLE APIS AND SERVICES button:

    Enable API for project in Google Cloud
  4. In the search bar search for bigquery api and then locate and select BigQuery API:

    Search for API in Google Cloud
  5. If BigQuery API is not enabled, enable it:

    Enable Google BigQuery API
  6. Then repeat the step and enable Cloud Resource Manager API as well:

    Enable Cloud Resource Manager API
  7. Done! Let's proceed to the next step and create a service account.

Step-3: Create Service Account

Use the steps below to create a Service Account in Google Cloud:

  1. First of all, go to IAM & Admin in Google Cloud console:

    Access IAM & Admin in Google Cloud
  2. Once you do that, click Service Accounts on the left side and click CREATE SERVICE ACCOUNT button:

    Start creating service account in Google Cloud
  3. Then name your service account and click CREATE AND CONTINUE button:

    Create service account in Google Cloud
  4. Continue by clicking Select a role dropdown and start granting service account BigQuery Admin and Project Viewer roles:

    Start granting service account project roles in Google Cloud
  5. Find BigQuery group on the left and then click on BigQuery Admin role on the right:

    Grant service account BigQuery Admin role
  6. Then click ADD ANOTHER ROLE button, find Project group and select Viewer role:

    Grant service account project viewer role
  7. Finish adding roles by clicking CONTINUE button:

    Finish granting service account project roles in Google Cloud
    You can always add or modify permissions later in IAM & Admin.
  8. Finally, in the last step, just click button DONE:

    Finish configuring service account in Google Cloud
  9. Done! We are ready to add a Key to this service account in the next step.

Step-4: Add Key to Service Account

We are ready to add a Key (P12 certificate) to the created Service Account:

  1. In Service Accounts open newly created service account:

    Open service account in Google Cloud
  2. Next, copy email address of your service account for the later step:

    Copy service account email address in Google Cloud
  3. Continue by selecting KEYS tab, then press ADD KEY dropdown, and click Create new key menu item:

    Start creating key for service account in Google Cloud
  4. Finally, select P12 option and hit CREATE button:

    Create P12 key for service account in Google Cloud
  5. P12 certificate downloads into your machine. We have all the data needed for authentication, let's proceed to the last step!

Step-5: Configure connection

  1. Now go to SSIS package or ODBC data source and configure these fields in Service Account authentication configuration:

    • In the Service Account Email field paste the service account Email address value you copied in the previous step.
    • In the Service Account Private Key Path (i.e. *.p12) field use downloaded certificate's file path.
  2. Done! Now you are ready to use Google BigQuery Connector!

Parameters

Parameter Label Required Default value Options Description
ClientId Service Account Email YES This is service account email ID (e.g. some_name@my_project.iam.gserviceaccount.com)
PrivateKeyPath Service Account Private Key Path (i.e. *.p12) YES File path for p12 file (i.e. Private Key file for service account). Keep this key file secure
ProjectId ProjectId YES Login to https://console.cloud.google.com/bigquery and choose Project dropdown at the top to see list of Projects. Over there you will find ProjectID next to ProjectName. You need to get ProjectID which has BigQuery API support enabled.
DatasetId DatasetId (Choose after ProjectId) YES Default Dataset Name you like to use when listing tables (e.g. MyDataset).
Scope Scope https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/bigquery.insertdata https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/cloud-platform.read-only https://www.googleapis.com/auth/devstorage.full_control https://www.googleapis.com/auth/devstorage.read_only https://www.googleapis.com/auth/devstorage.read_write Permission(s) you like to request
RetryMode RetryMode RetryWhenStatusCodeMatch
Name Value
None None
RetryAny RetryAny
RetryWhenStatusCodeMatch RetryWhenStatusCodeMatch
RetryStatusCodeList RetryStatusCodeList 429
RetryCountMax RetryCountMax 5
RetryMultiplyWaitTime RetryMultiplyWaitTime True
Location Job Location
Name Value
System Default
Data centers in the United States US
Data centers in the European Union EU
Columbus, Ohio us-east5
Iowa us-central1
Las Vegas us-west4
Los Angeles us-west2
Montréal northamerica-northeast1
Northern Virginia us-east4
Oregon us-west1
Salt Lake City us-west3
São Paulo southamerica-east1
Santiago southamerica-west1
South Carolina us-east1
Toronto northamerica-northeast2
Delhi asia-south2
Hong Kong asia-east2
Jakarta asia-southeast2
Melbourne australia-southeast2
Mumbai asia-south1
Osaka asia-northeast2
Seoul asia-northeast3
Singapore asia-southeast1
Sydney australia-southeast1
Taiwan asia-east1
Tokyo asia-northeast1
Belgium europe-west1
Finland europe-north1
Frankfurt europe-west3
London europe-west2
Madrid europe-southwest1
Milan europe-west8
Netherlands europe-west4
Paris europe-west9
Warsaw europe-central2
Zürich europe-west6
AWS - US East (N. Virginia) aws-us-east-1
Azure - East US 2 azure-eastus2
Custom Name (Type your own) type-region-id-here
The geographic location where the job should run. For Non-EU and Non-US datacenters we suggest you to supply this parameter to avoid any error.
ImpersonateAs Impersonate As (Enter Email Id)