# BigQuery

> Connect Google BigQuery with a service account: the two roles it needs, how to create the JSON key, and what queries cost in your project.

Source: https://tini.so/docs/data/bigquery

Connect a Google BigQuery project to query its datasets from Orcabase. Orcabase signs in as a service account you create, and queries run in your Google Cloud project, so BigQuery bills them there.

## Before you start

- A Google Cloud project with BigQuery turned on.
- Permission to create service accounts and keys in that project (IAM admin, or a project owner).

## Create a service account

A service account is a login for software rather than a person. In the Google Cloud console:

1. **Create the account**

   Go to **IAM & Admin → Service Accounts → Create service account**. Name it something you’ll recognize, like `Orcabase`.

2. **Give it two roles**

   On the project, grant it:

   - **BigQuery Data Viewer**, to read tables.
   - **BigQuery Job User**, to run queries.

   With just these two, the account can read and query, but can’t change or delete anything.

3. **Download a JSON key**

   Open the service account, then **Keys → Add key → Create new key → JSON**. A `.json` file downloads. You’ll paste its contents into Orcabase in a moment.

> **Tip: Share only some datasets**
>
> To limit what Orcabase can see, grant **BigQuery Data Viewer** on individual datasets instead of the whole project. **BigQuery Job User** still goes on the project, since that’s where queries run.

## Connect it

1. **Start a new data source**

   Go to **Data Sources → New data source** and pick **Google BigQuery**.

2. **Fill in the details**

   | Field | What to enter |
   | --- | --- |
   | Name | What your team sees, like “Analytics warehouse”. |
   | GCP project ID | The project that runs and pays for the queries, like `acme-analytics`. It’s the ID, not the display name. |
   | Service account JSON key | The whole contents of the key file, starting with `{` and ending with `}`. |

3. **Create it and test it**

   Click **Create data source**, open it, and click **Test connection**. Then delete the downloaded key file: Orcabase has an encrypted copy and never shows it again.

## Working with BigQuery in Orcabase

- **Datasets show up as schemas** in [Explore](https://tini.so/docs/explore). Each dataset’s tables load when you open it, so large projects stay quick to browse.
- **Write BigQuery SQL.** Queries run as BigQuery Standard SQL. Refer to tables as `dataset.table`, in backticks if the name needs them:

```sql
SELECT DATE_TRUNC(DATE(created_at), MONTH) AS month, SUM(amount) AS revenue
FROM `shop.orders`
WHERE status = 'paid'
GROUP BY month
ORDER BY month
```

- **Parameters** like `{{ country }}` are sent to BigQuery as named query parameters, so values are never pasted into the SQL. See [Parameters](https://tini.so/docs/queries#parameters).
- **Limits.** Each query returns at most 5,000 rows to Orcabase and stops after 30 seconds.

## What it costs

BigQuery bills queries to the project you entered, at Google’s prices, usually by how much data a query reads. Orcabase doesn’t add anything on top. To keep costs down on big tables, filter by date (or by partition) in your SQL, and select only the columns you need. Returning 5,000 rows doesn’t mean BigQuery only read 5,000 rows.

## If the test fails

| The error mentions | What to check |
| --- | --- |
| Access Denied, bigquery.jobs.create | The service account needs BigQuery Job User on the project you entered. |
| Access Denied on a table or dataset | The service account needs BigQuery Data Viewer on that dataset or project. |
| Not found: Project | The GCP project ID. Use the ID, not the display name. |
| invalid character, or invalid JSON | Paste the whole key file, including the outer braces. |

---

Previous: [PostgreSQL](https://tini.so/docs/data/postgresql.md) · Next: [Orcabase-hosted databases](https://tini.so/docs/data/hosted.md)

All docs: https://tini.so/llms.txt