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→ then Service Accounts→ then 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→ then Add key→ then Create new key→ then JSON. A .json file downloads. You’ll paste its contents into Orcabase in a moment.

Connect it

1. Start a new data source

   Go to Data Sources→ then 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. 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:

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.
• 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