Creating & Configuring Service Accounts

The aim of this document is to detail the steps required to create and configure a Google Cloud (GCP) Service Account to access BigQuery resources within your own project and additionally request access to Dimensions datasets.

Before starting the primary prerequisite is to have already configured a GCP project that you have administrative access over. This tutorial will use the Google Cloud web-console, but it could also be accomplished using the GCloud CLI. Further, before diving in and setting up a Service Account it may be useful to revise Google’s own documentation regarding these accounts and what they are used for along with some details regarding best practices. Google’s documentation can be found here.

1. Setup Google BigQuery access

The first step is accessing the Google Cloud environment using the web-console. The following link can be used as a starting point: https://console.cloud.google.com/.

There are two important things to check:

  • First, that you are using the correct Google Account. This is especially true if you are logged into multiple Google Accounts on the same web browser. You can click the circle icon in the top-right hand corner of the web page to verify your current Google Account and switch between accounts if necessary.

  • Secondly, that you have the correct GCP project selected. Service Accounts are created within a GCP project and belong to a single project. The image below shows you where to view what your current GCP project is set to. Clicking on the highlighted button will allow you to select a different project if required.

Check current GCP project.

For this tutorial the selected project is ds-test-gbq.

2. IAM and Accounts - Service Accounts

The next step is to navigate to the IAM administration section, specifically the “Service Accounts” sub-section.

This can be done using the “hamburger” menu in the top-left hand corner, selecting “IAM and admin” and then selecting the sub-section “Service accounts”.

Navigate to IAM and admin, Service accounts.

3. Create the Service Account

Now we are ready to create our service account. You should see something like the example screenshot below.

Service account details.

Click “Create Service Account”, which should then continue onto another section that looks like the following.

Configure service account details.

Enter a service account display name and Service account ID. You can name this anything you like, as long as it makes sense and is something that will remind you of the purpose for the service account in the future. There are restrictions on the length and character set which can be used for the second field containing the Service Account ID.

Click “Create and Continue” when you are happy with the configuration of the account details.

4. Assign Roles to the Service Account

We can now optionally assign roles to the service account. Roles within the GCP environment allow an access principal (users or service accounts) the rights to access resources. We want this service account to be able to execute GBQ queries, access data within our project and then also use “Read Sessions” to access large sets of data efficiently. Clicking the “Select a Role” section we can then navigate to the BigQuery collection of roles and select the appropriate one. Using Add another Role to add more roles above and beyond the initial role selected.

Assign a role to a service account.

A good starting point for a BigQuery focused service account would look something like the following:

Example GBQ roles for a service account.

A detailed description of the different BigQuery roles which are available can be found here: Access control with IAM.

Click “Continue” after configuring the roles you require. If you wish to edit roles assigned to a service account later you can do so within the “IAM and admin” section (selecting “IAM” as the subsection).

5. Create a Key for the Service Account

The final stage of creating the service account is generating an access key. An access key is a file which allows systems to utilise the service account and access the resources that the roles granted to it provide access onto. From within the “Service accounts” subsection of the “IAM and admin” section we should have a listing of service accounts visible, including the service account that was just created.

Modify keys for service account.

From the drop-down menu under the “Actions” column for the specific service account we are interested in, select the “Manage keys” option. A listing of keys will be provided, which for our newly created service account will be empty. Click the “Add key” button and select “Create new key”. Select “JSON” as the key type and then click “Create”. The new service account key file will be downloaded onto your local machine.

It is important to keep this key file safe. You can think of this as a password or a secret. Anyone who has this private key file can access your GCP environment, impersonating the service account allowing it access to all of the resources that the roles granted to it provide rights onto.

6. Request Dimensions dataset access

Service accounts are independent of users and as such do not have the access rights onto Dimensions datasets your existing Google Account may already have. In order to have access onto the Dimensions datasets provisioned you need to contact our support team and request for a service account to gain access. You need to supply us with the following details:

  • The email address of the service account. For our example above, the email address was gbq-sa-example@ds-test-gbq.iam.gserviceaccount.com. Notice that the email address starts with the Service Account ID we elected during the creation. The domain starts with our project name ds-test-gbq and ends with .iam.gserviceaccount.com. All service account email addresses follow the same format.

  • The name and email address of the person responsible for the service account. This is who we can contact if we have need to contact someone in regards to the service account usage.

Note

You can send Service Account access requests via email to supportbigquery@dimensions.ai. Please be sure to provide the above details and specifically include in the request the need for service account data access.

We will reply back when the service account has been provisioned with access to our datasets.

ODBC/JDBC Access using Service Accounts

Google provides standard ODBC/JDBC drivers for BigQuery which can be found here: ODBC and JDBC drivers for BigQuery. Please read the Install and configuration guide document from the above link, especially with a focus on configuring a GCP Service Account for accessing data.

The “Catalog (Project)” option should be set to the GCP Project associated with the Service Account that is being used for access. In our example above we would set this to ds-test-gbq. This is the project that will be used as the billing project, that is, the project where BigQuery jobs are created to run queries, exports and other jobs. The service account needs to have the BigQuery Job User role assigned to it for the specified Catalog project in order to run jobs (such as queries).

Another configuration option to pay particular focus on is the Additional Projects option. In order to access the Dimensions datasets directly from the ODBC/JDBC driver instance you will want to include dimensions-ai as an additional project.

Note: ODBC and JDBC drivers do not fully support the nested/repeated column types used extensively in the Dimensions datasets. We do have available a set of view schemas that offer a normalised view onto the Dimensions data that could be useful in certain situations.