BigQuery - Integration with Pronto

This article outlines a series of steps to do the following: 

• Create a new Project 
• Create a new Service account
• Create Role and Add permissions
• Create Key for Service Account to download .json file 
• Authorizing BigQuery on Pronto

Pre-requisite

• Pronto Admin rights
• Google Cloud Platform Admin rights

1. Login to Google BigQuery: https://cloud.google.com/bigquery   
   
   
   
   
2. Next Click on the Project selection button.
   
   
   
   
3. Click on the [NEW PROJECT] button.
   
   
   
   
4. You can use the default settings returned for the New Project or modify the names if needed, click the [CREATE] button to continue.
   
   
   
   
5. Select the newly selected project name from the project selection box.
   
   
   
   
   
   
6. Create a service account and key associated for BigQuery Integration and store it. You will not be able to access this file again.
   
   
   
   
7. Click on the [CREATE SERVICE ACCOUNT] button.
   
   
   
   
8. Add values for the service account.
   
   
   
   For this example we used these values:
   
   Service account name:  ProntoApp
   Service account ID: prontoapp
   Service account description: Used for BigQuery integration to the app.gopronto.io instance.
   
   
9. Click the [CREATE AND CONTINUE] button.
   
   
10. Click on the [Select a role] drop down menu, then click the [MANAGE ROLES] link.
    
    
    
    
    
    
11. Click the [+CREATE ROLE] button.
    
    
    
    
12. Next add the {Title} name and then click the [+ADD PERMISSIONS] button to continue.
    
    
    
    
13. The Add permissions selection box appears, use the search bar shown the blue box below.
    
    Make sure the following permissions are granted:
    
    > bigquery.datasets.get
    > bigquery.jobs.create
    > bigquery.tables.get
    > bigquery.tables.getData
    > bigquery.tables.list
    
    
    
    
14. Add each permission by selecting the [X] checkbox and then clicking the [Add] button. When complete there should be (5) permissions selected.
    
    
    
    
15. Now click the [CREATE] button.
    
    
16. Next click on the {IAM} menu, then select [ADD] button.
    
    
    
    
17. Add the service account we created earlier under New principals. Chose the custom role we created earlier under Role. Click the [SAVE] button to continue.
    
    
    
    
18. Next we will create a Key for the Service Account. Go to IAM & Admin / Service Account / Manage keys.  Select the Service account that was created earlier.  In this example the service account name is:  prontoapp@sinuous-vortex-345723.iam.gserviceaccount.com.
    
    
    
    
19. This should bring you to the Key section.  Click the [ADD KEY] button and then select [Create new key].
    
    
    
    
20. For the Key type use JSON, and click the [CREATE] button to continue.
    
    
    
    
21. You will be promoted to save a JSON file to your computer. This file will be used to upload to Pronto.
    
    
    
    
22. On Pronto go to Settings - Integrations - BigQuery.  Click on the [View details] button.
    
    Next Click on the [Upload File] Button, then choose the {Upload from computer} option.
    
    
    
    
23. Once you select the .json file, if the upload is successful, you will see a message on the bottom left of the screen that reads "BigQuery has been authorized successfully".
    
    

Once the file is uploaded on Pronto, the next step is to perform Data mapping to pull data from BigQuery to Pronto. 

To complete data mapping on Pronto, we need to Create Datasets and Tables on BigQuery.