Google BigQuery (GCP)

BigQuery is a great cloud data warehouse to get started with: they have a free tier, and it feels totally serverless: you don't have to worry about configuring instance or node sizes like you do with snowflake.

BigQuery is also good if you need to be HIPAA compliant and don't want to sign onerous enterprise licenses (for US-based healthcare companies). In short, we recommend using BigQuery if you have moderate amount of data to query and want to set up a cloud data warehouse. We use BigQuery for Hashboard's own analytics.

If you have relatively small data and are very early, using something lightweight like Postgres or uploading csvs or parquet files to our DuckDB integration could also be good options.

How to get set up

1. Sign up for free GCP / BigQuery (opens in a new tab) account if you don't have one.
2. Set up a Service User Account in Google Cloud.
3. Set up a database connection in Hashboard.

Create a BigQuery service account

BigQuery connections in Hashboard use GCP service accounts to connect to your database. You will need to copy and paste the entire contents of a service account JSON key file into this field. See the Google documentation on authenticating with service accounts (opens in a new tab)

1. Create a service account in GCP by going to IAM > service accounts (opens in a new tab).

2. Select + Create Service Account from the top of the page.

3. When prompted you will need to add four IAM roles below:

   • BigQuery User
   • BigQuery Data Viewer
   • BigQuery Job User
   • BigQuery Metadata Viewer

4. Once you've created the service account, click into the service account and select the Keys tab.

5. Click Add Key and make sure you create a JSON key (not a P12 key). The key will be automatically downloaded to your computer - you'll need to open the file and copy the contents so you can drop them into Hashboard below.

Dataset-level permissions

If you need to limit the service account to specific datasets, you can do so using the following steps:

(1) Instead of giving the service account the 4 roles mentioned above, just give it the following 2:

• Bigquery Job User
• Bigquery Read Session User

(2) Go to the BigQuery console and click on the dataset you want to grant access to

(3) Click "SHARING", and then "Permissions":

(4) Click "Add Principal". Paste in the email from the IAM > service accounts (opens in a new tab) page as the Principal, and assign it these roles:

• BigQuery User
• BigQuery Data Viewer
• BigQuery Metadata Viewer

Create BigQuery database connection in Hashboard

1. In Hashboard, go to the Data sources (opens in a new tab) page.
2. Click + Add connection, select BigQuery and fill out the fields below.

Settings

• Connection Name: A name for this database connection. If you use the CLI, you can use the connection name to refer to this database.
• JSON Key: BigQuery service accounts use a JSON file as a credential. The entire contents of this file needs to be copied and pasted into this field.

Authenticating to BigQuery via OAuth

When creating a new BigQuery Cost Analyzer project (opens in a new tab), you will be prompted to authenticate via OAuth using your Google account. This allows Hashboard to create BigQuery data connections using your Google account's credentials.

Converting to a service account

OAuth credentials are tied to a specific user's Google account. This is convenient for fast setup, but isn't ideal for long-term use, especially when new users are added to the project.

All users in the project will be able to query the BigQuery project while authenticated as the user who created the OAuth credentials. This might allow users to query data that they should not have access to.

If you are adding new users to your Hashboard project, you should convert your OAuth credentials to a service account using the following steps:

1. To create a service account, follow the steps in the Create a BigQuery service account section above.
2. In Hashboard, go to the Data sources (opens in a new tab) page.
3. Click on the Edit button for the BigQuery connection that you want to convert.
   • You should see an alert that says: "This connection was initially authenticated via OAuth. To update it, upload service account credentials below."
4. Fill out the JSON Key field using the service account JSON key file that you created in step 1, either by uploading the file directly or pasting its contents into the text field.