📘Introducing SQL Notebooks: Tell stories using data.Read more →

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.

  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.

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 alt text
  3. Click Create credentials, and then Service account key alt text
  4. Click New service account, name it (ie "PopSQL BigQuery" or your full name), give it the BigQuery Admin role, select JSON, and hit the Create button. alt text
  5. Download the .json file, open it in a text editor, and copy the entire file contents to your clipboard. alt text
  6. Within PopSQL, under the How to connect dropdown, select the Service Account option.
  7. Paste the contents of your clipboard (the .json file contents) into the Service account field.
  8. Toggle connection type for direct or cloud (only available if not sharing connection).

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 text
  3. Upon successful authentication, return to PopSQL.
  4. Enter the Project ID of your desired BigQuery Project.

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

Ready for a modern SQL editor?