Connect BigQuery · EndpointOS

When to use it

Large analytical tables you already own, where you want an API in front of a dataset rather than a second copy of it. It is read-only: BigQuery is a warehouse, not a transactional store, so writes belong somewhere else.

What to grant

roles/bigquery.dataViewer on the dataset, plus roles/bigquery.jobUser on the project. Both are needed: dataViewer lets the account read the table, and jobUser lets it run the query that does the reading.

Setup

1. Create a service account

In the Google Cloud console, open IAM and admin, then Service accounts, then Create service account. Name it something you will recognise later, such as endpointos-reader. You do not need to grant it access at this step.

gcloud iam service-accounts create endpointos-reader \
  --display-name="EndpointOS reader"

2. Grant it permission to run queries

Running a query is a job, so the account needs roles/bigquery.jobUser on the project. This does not give it access to any data on its own.

gcloud projects add-iam-policy-binding YOUR_PROJECT \
  --member=serviceAccount:endpointos-reader@YOUR_PROJECT.iam.gserviceaccount.com \
  --role=roles/bigquery.jobUser

3. Grant it read access to the one dataset

Grant roles/bigquery.dataViewer on the dataset itself, not on the whole project, so the credential can only read what this API needs.

bq add-iam-policy-binding \
  --member=serviceAccount:endpointos-reader@YOUR_PROJECT.iam.gserviceaccount.com \
  --role=roles/bigquery.dataViewer \
  YOUR_PROJECT:YOUR_DATASET

4. Download the key file

Create a JSON key for the service account and download it. You paste the whole file contents into EndpointOS, where it is encrypted at rest. Treat it like a password: anyone holding it can read the dataset.

gcloud iam service-accounts keys create key.json \
  --iam-account=endpointos-reader@YOUR_PROJECT.iam.gserviceaccount.com

5. Check what your table looks like

Two things decide how well this works: whether the table is partitioned, and how wide it is. A partitioned table lets a filtered request read a fraction of the data. A narrow view costs far less than a wide raw table, because BigQuery charges for the columns it reads.

bq show --format=prettyjson YOUR_PROJECT:YOUR_DATASET.YOUR_TABLE \
  | jq '{location, numRows, timePartitioning, clustering}'

6. Consider exposing a view instead of the raw table

A view that selects only the columns you want public, and filters out rows you do not, is usually both cheaper and safer than pointing at the table directly. EndpointOS treats a view exactly like a table.

CREATE OR REPLACE VIEW `YOUR_PROJECT.YOUR_DATASET.listings_api` AS
SELECT id, vin, make, model, price, listed_at
FROM `YOUR_PROJECT.YOUR_DATASET.listings`
WHERE published IS TRUE

7. Connect it in EndpointOS

Create a resource, choose BigQuery, and paste the project id, dataset, table or view, and the key file. The dataset region is read for you. Use Test connection before saving: it runs a no-op query, so a permission or naming problem surfaces immediately rather than on your first real request.

What you will be asked for

Google Cloud project id: The project that owns the dataset. Find it in the Google Cloud console header, next to the project name.
Dataset: The BigQuery dataset holding your table. We can list your datasets once the service account is set, so you do not need to look this up.
Table or view: The table or view to expose. A view shaped for serving is usually far cheaper to query than a wide raw table, because BigQuery bills by bytes scanned.
Service account JSON, stored encrypted: Paste the whole key file, starting with '{'. The account needs roles/bigquery.dataViewer on the dataset and roles/bigquery.jobUser on the project. Stored encrypted.
Byte ceiling per query (optional): Optional. The most data a single request may bill, in bytes. Defaults to 10 GiB. Raise it only if a legitimate request is being rejected, because this is what stops an unexpected bill.
Dataset region (optional): Read from your dataset automatically, for example europe-west6 or EU. Queries fail if it is wrong, because BigQuery otherwise looks in the US.

What the API supports for this source

Generated from the same declaration the API enforces on every request, so it cannot promise something a request would be rejected for.

Filtering	equals on any declared field
Sorting	By id only
Search (q)	Not supported
Writes	Read-only
Paging	limit and offset, plus a cursor for paging past the offset ceiling
Record count	Exact: the total describes the filtered set

Worth knowing first

Your table needs an id column. It is what a single-record read (/resource/{id}) and result ordering both use.
BigQuery bills by bytes scanned, not by rows returned. Every query EndpointOS sends is capped at 10 GiB billed and reads only the columns your resource declares, but a wide unpartitioned table is still expensive to query repeatedly.
Reads are the only supported operation. Create, update and delete are rejected rather than silently ignored.
Filtering is equality only for now, and sorting is by id only. The generated OpenAPI for your resource documents exactly what is supported, so a request that would not work is rejected with a clear error instead of quietly returning unfiltered data.

If something goes wrong

Dataset was not found in location US

Why: The dataset lives in another region, and the query was sent without one, so BigQuery looked in the US.

Fix: Nothing to do: the region is read from your dataset automatically when you save the resource, and on the next request for resources created earlier. If it persists, the service account may lack permission to read dataset metadata, so grant it roles/bigquery.dataViewer on the dataset.

Permission denied, with a mention of roles

Why: The service account is missing one of the two grants it needs. Missing jobUser is the more common one, because dataViewer alone looks sufficient.

Fix: Grant roles/bigquery.jobUser on the project and roles/bigquery.dataViewer on the dataset, then test the connection again.

This query would scan more data than this resource allows

Why: The request would have billed more than the 10 GiB ceiling, usually because the table is wide, unpartitioned, or being read without a filter.

Fix: Filter on a partitioned column, or point the resource at a view that selects fewer columns and rows. The ceiling exists so an unexpected bill is impossible.

BigQuery query did not finish in time

Why: The query outran its deadline. BigQuery reports this as a success with no rows, which used to look identical to an empty table.

Fix: Narrow the query, add a partition filter, or expose a view shaped for serving. The error is deliberate: an empty answer to a slow query would be a lie.

The column X does not exist in this BigQuery table

Why: The resource declares a field that the table or view has no column for.

Fix: Remove that field from the resource, or point the resource at a table or view that has the column. Names are case-sensitive.

Service account JSON failed to parse

Why: The pasted value is not the complete key file.

Fix: Paste the whole file, starting with { and including client_email and private_key.

Unsafe BigQuery project identifier

Why: The project id contains a character outside letters, digits, hyphens and underscores.

Fix: Use the project id, not the project name or number. Hyphens are fine.