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 dropdownScreenshot of select project dropdown

Screenshot of select project dropdown

  1. Click Create credentials, and then Service account key
Screenshot of the "Create Credentials" buttonScreenshot of the "Create Credentials" button

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 detailsScreenshot of service account details

Screenshot of service account details

  1. Set the role to 'BigQuery Admin' and click CONTINUE for step 3.
Screenshot of service account access to projectScreenshot of service account access to project

Screenshot of service account access to project

  1. Click DONE to complete set-up.
Screenshot of optional grant users access to service accountScreenshot of optional grant users access to service account

Screenshot of optional grant users access to service account

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

Screenshot of credentials page

  1. Select KEYS and then ADD KEY then on Create new key
Screenshot of key creation pageScreenshot of key creation page

Screenshot of key creation page

  1. Select JSON as the key type and then click CREATE
Screenshot of JSON private key creation.Screenshot of JSON private key creation.

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 textalt 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 Job User IAM role is required for whatever Google user you use.
    alt textalt text
  3. Upon successful authentication, return to PopSQL.
  4. Enter the Project ID of your desired BigQuery Project.

BigQuery Cost Estimator

On the connections page, you will see a reference to a BigQuery cost estimator. You will see this estimate next to the Run Button. It includes an estimate for how many bytes of data the query will process. We then apply a standard conversion of $5.00 per TB to determine the estimated cost of running that query.

BigQuery Cost EstimatorBigQuery Cost Estimator

BigQuery Cost Estimator

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


Did this page help you?