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
686686

Screenshot of select project dropdown

  1. Click Create credentials, and then Service account key
682682

Screenshot of the "Create Credentials" button

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

Screenshot of service account details

  1. Set the role to 'BigQuery Admin' and click CONTINUE for step 3.
11721172

Screenshot of service account access to project

  1. Click DONE to complete set-up.
12241224

Screenshot of optional grant users access to service account

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

Screenshot of credentials page

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

Screenshot of key creation page

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

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. 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":

13841384

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.

720720

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?
Cookie settings