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.
- Open the connections page in preferences, see managing connections for more information.
- Click the
Add new Connectionbutton at the top of the connections page.
BigQueryfrom the list.
- Give a
Connection namefor your own internal reference.
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.
- Visit the Credentials page in Google Cloud Console
- Select your project
Create credentials, and then
Service account key
- Name your service account (ie "PopSQL BigQuery" or your full name) and click
CREATE AND CONTINUE.
- Set the role to 'BigQuery Admin' and click
CONTINUEfor step 3.
DONEto complete set-up.
- Click on your newly created service account under the credentials page.
ADD KEYthen on
Create new key
JSONas the key type and then click
- Download the .json file, open it in a text editor, and copy the entire file contents to your clipboard.
- Within PopSQL, under the
How would you like to connectdropdown, select the
- Paste the contents of your clipboard (the .json file contents) into the Service account field.
- Toggle connection type for direct or cloud (only available if not sharing connection).
- Under the
How to connectdropdown, select the
- Click the
Sign in with Googlebutton. You will be taken to an external page hosted by Google for authentication. Note: the
BigQuery Job UserIAM role is required for whatever Google user you use.
- Upon successful authentication, return to PopSQL.
- 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.
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.
If you wish to query from Google Docs, please contact us and we can manually enable a setting on connections using service accounts to allow this. Additionally, you will need to make sure the email address associated with the service account (e.g.
[email protected]) has been given access to the Google Doc you are wishing to query from.
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! 🍭
Updated about 2 months ago