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

Screenshot of select project dropdown

  1. Click Create credentials, and then Service account key

Screenshot of the "Create Credentials" button

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

Screenshot of service account details

  1. 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.

Screenshot of service account access to project

  1. Click DONE to complete set-up.

Screenshot of optional grant users access to service account

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

Screenshot of credentials page

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

Screenshot of key creation page

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

Screenshot of JSON private key creation.

  1. Download the .json file, open it in a text editor, and copy the entire file contents to your clipboard.
    alt text
  2. Within PopSQL, under the How would you like to connect dropdown, select the Service Account option.
  3. Paste the contents of your clipboard (the .json file contents) into the Service account field.
  4. 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.alt text
  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":


BigQuery bytes estimator setting

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.


BigQuery Cost Estimator

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! 🍭

Cookie settings