Using GCP Service Accounts to Write to BigQuery

Earlier versions of Local Hero requested a broad Google Cloud Platform (GCP) scope to enable its access to view Big Query projects, datasets, and tables and to allow inserts into the target BQ tables.

The new approach involves the use of GCP Service Accounts.

Before a user can use Big Query as a Local Hero target, they must:

• Create a service account with a GCP project

• Provide the account with the required permissions to the BQ instance/tables

• Download the service account key and add that to the locally running Local Hero application
• Update service account permissions, if necessary
• Create/update the BQ job - to ensure all target details and permissions are correct

Creating a Service Account

1. Navigate to Service accounts section within IAM & Admin in GCP under the appropriate project.

2. Click Create Service Account.

3. Provide a Service account name, Service account ID, and email address. The Description is optional but is recommended, even if to indicate that it is used for Local Hero BigQuery access.

Grant This Service Account Access to the Project

In step 2 in the Create Service Account stepper.

1. Suppose the BigQuery resources, datasets, and tables are within the same GCP project you are creating the service account. In that case, you can provide Big Query access at the project level in step 2 of the Create Service Account stepper.

2. Granting access entails adding one of the following roles:

   • BigQuery Admin

   • BigQuery Data Owner and BigQuery Job User

3. You can skip step 3, Grant users access to this service account, in the stepper, and click Done to create the service account.

Download the Service Account Key and Add It to Local Hero

1. From the list of service accounts in GCP, select Manage Keys from the right-hand menu.
   OR
   Click on the service account and move to the Keys tab.
2. Click the Add Key menu, then select Create new key.
3. Click CREATE in the popup to download a JSON key to your local machine. This file should be retained and kept secure. This JSON file will be imported into Local Hero and stored in an encrypted form.
   
4. In Local Hero, select the Settings icon and move to the Big Query tab.
5. Click on Add new service account, browse the downloaded JSON file, and import. Once imported, your service account name will be visible in the accounts list and available for selection when creating/editing Big Query jobs.

Updating Service Account Permissions

If you still need to assign Big Query access to your service account, you can do so within the GCP Cloud console.
You can either provide project-wide access or access to specific datasets or tables.

1. Navigate to IAM within the GCP cloud console.
2. Click GRANT ACCESS.
3. Enter the full-service account email address as the new principal.
4. Provide the appropriate BigQuery role:
   • BigQuery Admin
   • BigQuery Data Owner and BigQuery Job Use

Setup BQ Target for Job

1. When creating a Big Query job, you must first ensure that the service account has been added. 
2. Select the desired service account and click Load Projects to bring in a list of projects this account can access. 
3. Proceed to select the GCP Project, BQ Dataset, and BQ table.

Document Version	Date	Description of Change
1.0	2/26/2024	Updated links to open in new tab, best practice