Connect to Google BigQuery

To connect BigQuery to Atlassian Analytics, you’ll need to:

1. set up a service account in Google Cloud Platform

2. upload the generated JSON key to Atlassian Analytics

Create a service account

You can set either of the following permissions for your service account:

• Project-level permissions

• Dataset-level permissions

Set project-level permissions

To set project-level permissions for a service account:

1. Log in to Google Cloud Platform and navigate to the project you want to use in Atlassian Analytics.

2. In the sidebar, select IAM & admin > Service accounts.

3. Select Create service account.

4. Enter a name for the service account. You may want to name it Atlassian Analytics to remember its purpose later.

5. Add two roles to the service account: BigQuery Data Viewer and BigQuery User. Both roles must be selected for Atlassian Analytics to connect to your BigQuery data.

6. Select Continue. This will take you back to the “Service accounts” page.

7. For your newly created service account, select Actions > Manage keys.

8. Select Add key > Create new key.

9. Select JSON for the key type, then select Create.

Your JSON key will be generated and downloaded to your computer. Save the JSON file to a secure location. You can delete it after you upload the key to Atlassian Analytics.

Set dataset-level permissions

Rather than granting project-level permissions, you can alternatively grant dataset-level permissions.

To do this, you’ll need to:

1. create a custom role

2. assign the role to a service account

3. restrict which datasets the role can access

Create a custom role

1. Log in to Google Cloud Platform and navigate to the project you want to use in Atlassian Analytics.

2. In the sidebar, select IAM & admin > Roles.

3. From the top of the screen, select Create role.

4. Provide a name in the Title field (for example, atlassian-analytics-role) and an optional description.

5. Select Add permissions and add the following permissions to the role:

• bigquery.jobs.create

• resource manager.projects.get

6. Select Add to save the permissions.

7. Select Create to create the custom role.

Assign the role to a service account

After you create a custom role, you can assign it to either:

• a new service account

• or an existing one

To assign the custom role to a new service account:

1. Follow steps 1 - 4 outlined earlier in the “Set project-level permissions” section.

2. When asked to select a role, select the custom role you created.

3. Select Done.

4. Follow the remaining steps in the “Set project-level permissions” section to finish creating the service account.

5. Copy the service account’s email address. You’ll need this in the next section (“Restrict datasets”).

To assign the custom role to an existing service account:

1. In the sidebar, select IAM & Admin > IAM.

2. Locate the service account you’d like to use then select Edit principal.

3. Assign the custom role you created.

4. Select Save.

Restrict datasets

1. Navigate back to your BigQuery instance (make sure you are still in the same project).

2. Select your dataset then select Sharing.

3. From the Sharing dropdown, select Permissions > Add principal to add a new member.

4. In the New principals field, paste the email of the service account with the custom role.

5. Assign the role of BigQuery data viewer to this new principal.

Add BigQuery to Atlassian Analytics

After creating a service account and generating your JSON key, you can complete the connection form in Atlassian Analytics to add your Google BigQuery database.

To add Google BigQuery to Atlassian Analytics:

1. Select Data from the global navigation.

2. Select Add data source > Google BigQuery.

3. Upload your generated JSON key. It could take a few minutes for Google to accept the JSON key.

4. After your JSON key is accepted:

   1. Provide a Display name for your data source. This name is only used in Atlassian Analytics and can be changed later.

   2. Verify the Project ID.

   3. Specify the Maximum bytes billed, which limits queries over the fixed size for billing purposes.

5. Select Submit.

Atlassian Analytics will connect with your database to get your schema, then your data source will be ready to use.

Google BigQuery SQL syntax

Google BigQuery uses its own proprietary SQL, so you need to use that syntax when you create SQL mode queries. Refer to Google BigQuery’s query syntax documentation for more help.