Connecting to BigQuery

PopSQL allows two ways to connect to your BigQuery data warehouse: OAuth and Service Account. The initial steps for creating the connecting are the same, and then the differences are separated below. If you choose to share the connection, then it must be shared as a cloud connection. OAuth connections can only be made on cloud connections.

Creating a new BigQuery Connection

1. Open the connections page in preferences, see managing connections for more information.
2. Click the Add new Connection button at the top of the connections page.
   
3. Select BigQuery from the list.
   
4. Give a Connection name for your own internal reference.

Using a Service Account

You'll need to be an owner in your Google Cloud project to create a service account. If you're not, please contact the administrator of your BigQuery database.

1. Visit the Credentials page in Google Cloud Console
2. Select your project

3. Click Create credentials, and then Service account key

4. Name your service account (ie "PopSQL BigQuery" or your full name) and click CREATE AND CONTINUE.

5. We recommend setting the role to BigQuery Admin and click CONTINUE for step 3.
   • If you have more detailed security requirements, just be sure to add the BigQuery Job User and BigQuery Data Viewer roles for all projects you plan to run queries on, at minimum.
   • If you need to give your service account access to other BigQuery projects, copy the service account email address, navigate to the IAM page, and add the service account as a user of the other projects. Again, either give the account the BigQuery Admin role, or at minimum BigQuery Job User and BigQuery Data Viewer to ensure queries work in PopSQL.

6. Click DONE to complete set-up.

7. Click on your newly created service account under the credentials page.

8. Select KEYS and then ADD KEY then on Create new key

9. Select JSON as the key type and then click CREATE

10. Download the .json file, open it in a text editor, and copy the entire file contents to your clipboard.
    
11. Within PopSQL, under the How would you like to connect dropdown, select the Service Account option.
12. Paste the contents of your clipboard (the .json file contents) into the Service account field.
    
13. Toggle connection type for direct or cloud (only available if not sharing connection).

Using OAuth

1. Under the How to connect dropdown, select the OAuth option.
2. Click the Sign in with Google button. You will be taken to an external page hosted by Google for authentication.
   • Note: the BigQuery Admin role is recommended for whatever Google user you use. If you have more detailed security requirements, just be sure to add the BigQuery Job User and BigQuery Data Viewer roles for all projects you plan to run queries on, at minimum.
3. Upon successful authentication, return to PopSQL.
4. Enter the Project ID of your desired BigQuery Project. If you have multiple projects, this will be the default project in the PopSQL SQL editor when you run queries on this BigQuery connection.
   

BigQuery Cost Estimator

On the connections page, you will see a setting called "See BigQuery's estimate of the number of bytes read before running your query":

If you turn on that setting, you will see a cost estimate next to the Run Button in your query editor. The cost estimate is calculated by taking the bytes of data the query will process multiplied by a standard conversion of $5.00 per TB to determine the estimated cost of running that query.

Restricting access to specific tables

If you want to restrict PopSQL's access to only specific tables (or specific datasets or views) you'll need to use a service account to connect to PopSQL. You can grant/revoke access to tables, views, datasets using BigQuery IAM resource access.

Querying from Google Docs

If you wish to query from Google Docs, please contact us and we can manually enable a setting to allow this. Additionally, you will need to make sure the email address associated with the service account (e.g. [email protected]) or your oauth account has been given access to the Google Doc you are wishing to query from.

Wrapping up

Finally, share the connection with your team. Once you're connected using
either of the above methods, try the following query:

SELECT title FROM `bigquery-public-data.samples.wikipedia` LIMIT 5;

Happy querying! 🍭